SQL Server Management Studio 20 Preview 1 is out, and the new connection dialog has a big change:
When you click Connect, you’re likely going to get an error:
The fastest fix is to click the Encryption dropdown, and change it to Optional:
And then you’ll be able to connect fine.
The long term fix is to read this announcement post, then read this post about the certificate requirements, then set up certificates on all your SQL Servers. As far as I can tell, it’s not as easy as just copying a sample script from Stack Overflow, bucko – for example, if you’re using an AG listener, you have additional steps to take for each cert. Even when properly configured, you have to be careful because some features don’t work, like DTA, saving Profiler traces to tables, or viewing the errorlog files via Object Explorer.
It’s a good idea long term. We’ll go through some growing pains while folks build out a checklist of how to put in certs quickly and work around common problems. DBAtools already has a Set-DbaNetworkCertificate cmdlet, and I bet that’ll get a lot more activity in the coming weeks as people prepare for SSMS 20.
14 Comments. Leave new
“Trust server certificate” should also work? (Best of both, encryption + it works ?)
So, not only do we have to set certificates for our Internet facing servers, and for our Oracle servers, but now we get to do it for SQL Server too. Whee. Which of course means that they’ll regularly have to be recreated, and distributed to all the PCs on the network…
I know, it’s more secure, prevents man-in-the-middle attacks internally, blah blah blah… But if someone has access to our internal network, they’re going to go after privileged AD accounts anyway, so it doesn’t matter – last time they hit us they just encrypted all the DBs on disk anyway.
(and yes, get off my lawn you crazy kids!)
[…] Read This Before Your Users Install SSMS 20 (Brent Ozar) […]
[…] Brent Ozar notes a change: […]
One big change to the login, but also, many broken features in this preview.
Definitely skipping this one until it doesn’t break dozens of the other components of SSMS…
Funny timing. Just a month ago I met with the security team regarding certificates since more and more connections using ODBC 18 require encryption by default (hence needing the certificate). I agree that we gotta hop on this wave
If you’re connected to a CMS, SSMS 20 also defaults all the connections to “Mandatory” and the “Trust server certificate” box is unchecked. If you click on each server and save it either as Optional or Mandatory and without checking the “Trust server certificate” box the settings are reset to the default after either deleting the CMS connection and re-registering it or if you restart SSMS. So, so far unless I find a workaround or a solution, SSMS 20.0 is useless with CMSesses.
Ok, so if I set the options for each server in the CMS to either Optional no-check or yes-check OR Mandatory with ywa-check and then export it to XML, I can re-import it when I open SSMS and the settings are still there. Not a great workaround but so far it works. Other than that not seeing any major differences with 20.0 (or improvements) so not sure if this is worth it. I won’t be recommending it to the dev team for awhile.
All our servers have certificates and connecting to network servers and running scripts works for me but modifying SQL Agent job step thows up:-
‘The certificate chain was issued by an authority that is not trusted’ and won’t display the configuration.
Regardless of what I have set the connection properties to.
I am facing the same error that Brian W when modifying SQL Agent job steps. In one of our servers I got the “The certificate chain was issued by an authority that is not trusted” in other :
TITLE: SSIS Execution Properties
——————————
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The received certificate has expired.) (Microsoft SQL Server, Error: -2146893016)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver–2146893016-database-engine-error
——————————
ADDITIONAL INFORMATION:
The received certificate has expired
——————————
BUTTONS:
OK
——————————
Great article as always Brent. But dang, I sure wish I had read this before spending a couple hours updating the trust server certificate across about 300 of the servers in our CMS list and finding out it didn’t stick. I think MS has forgotten CMS along the way. It could be sooo much better and allow the saving of all the connection properties.
Upvote fixing this “feature” here: https://feedback.azure.com/d365community/idea/27452b60-0af8-ee11-a1ff-00224827349c
I came up with a workaround solution that some of you may find useful.
1) Export your CMS,
2) Import it to the Local Server Groups,
3) Edit the regsrv file in your local appdata\roaming\microsoft\SQL Server Management Stutio\RegSrvrNN.xml file.
A) replace “security=true<" with "security=True;pooling=False;multiple active result sets=False;connect
timeout=30;encrypt=True;trust server certificate=True;{{{"
B) then replace "{{{" with "packet size=4096;command timeout=0
Note: I had to do steps A & B because WordPad has a limit of the number of chars you can replace.
4) Then re-open SSMS and Viola. It’s pretty quick, so you can maintain CMS and import/export it as a local registered server and make this change quickly.
I’m sure some cleaver soul could come up with a PowerShell script to take care of this! But it sure beats “right click, properties, click, save” 500 times….
lol John, I did pretty much the same thing but I was too lazy to type all that. I’m also too stubborn to bail on 20.0 now that I’ve been using it for a few weeks. It’s the only flaw I’ve ran across but I also don’t notice any improvements either other than it’s newer and the icon has purple in it.