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

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.

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.

2024-05-02