SQL Server Management Studio issues with Central Management Servers in v20.1

Registered Servers

I've recently been doing work with a site that makes extensive use of Central Management Servers. And that's an issue if you upgrade past v19.3 of SSMS.

Central Management Servers

If you haven't used these, it's the Registered Servers window that you can open in SSMS. (View -> Registered Servers) What it lets you do is set up groups of servers, and execute multi-server queries against all the servers in the group. You can also have a hierarchy of groups with nested folders and then work at different group levels.

The details for these are all these groups and servers are stored in tables in the msdb database. In fact, it's easy to programmatically add/remove servers from these lists. We do that all the time.

And if you haven't used them, they are quite magical. It's awesome to be able to easily run a query against a large number of servers, and to combine the results into a single result grid in SSMS, along with the server name that each row of data came from.

What it has never really dealt with though, is any form of authentication except Windows authentication. (i.e., no SQL logins)

So what's changed?

In v20 and later of SSMS, you'll notice a new entry on the first page of the connection dialog.

Trust server certificate dialog

The options for Connection Security have moved to the first page, but more importantly, the defaults have changed. The default option now (Mandatory) requires you to either have a certificate on the server that you already trust, or to check the box to trust the self-signed certificate on the server.

By having a certificate that you trust, I mean that SQL Server has a certificate installed on it manually, and that certificate is issued by a publisher that your client computer has in its list of trusted publishers. You need to install a certificate on each SQL Server, and get a trusted publisher pushed out to every client, if the certificate is from a publisher that the clients don't already trust.

So what's the challenge for Central Management Servers (CMS)?

I totally understand the desire to tighten up the security, but it's an issue for people using Central Management Servers and not already having trusted certificates in place.

As an example, the people I'm helping at the moment have hundreds of servers, all related to different business groups, and changing any one of them requires change processes with change requests, approvals, external system management teams to implement it, etc. Bottom line is that even though it would be desirable, it's not going to happen any time soon.

There is no option with CMS to automatically select an option to trust the server certificates. You need to do that server by server, and it's stored on a per-user basis, not as a value in msdb. That means that even if I went through and edited every server's configuration, every other person using the CMS would need to do the same.

So how do I work around it?

Bottom line is that there's no easy fix for this. Clearly, installing trusted certificates on every server, and pushing out the certificate authority as a trusted publisher to every client is the desirable outcome.

If you can't do that, you need to keep using v19.3 of SSMS, at least for now.

 

 

 

 

2 thoughts on “SQL Server Management Studio issues with Central Management Servers in v20.1”

  1. There is a workaround for ticking that Trust setting through export-import. I have a CMS group called CMS1 with a sub group called CMS2 where all my servers are.
    o Export the CMS1 group to a file (right mouse, tasks, export)
    o Edit this file with Notepad++ (normal Notepad will note work), replace all "integrated security=True" with "integrated security=True;trust server certificate=True". Save the file.
    o Important: delete the CMS2 group
    o Import the file again. Right mouse on CMS1, tasks, import.
    o Refresh the Central Management Servers
    o Verify that Trust Certificate is enabled

    Et voila. In my case the server landscape is dynamic and servers are being added all the time. I therefore automated everything where I use PowerShell to get a list of all SQL Servers in the domain. It takes me a few minutes to set up the whole CMS completely.

    1. Have you tested if other users can use this CMS group without checking all the boxes themselves? The challenge seems to be that it's not stored in msdb, only in your user settings. So while it works for a single person, it would be interesting to see what outcome you see for other users of the same groups.

Leave a Reply

Your email address will not be published. Required fields are marked *