There was one little feature in SQL Server that went pretty much unnoticed by the most people, and the reason for that is not something to be mostly happy about – most people do not use this or any other security features.
Of course in this case I am writing about the certificates. :)
If you have ever used them for connection encryption (TLS 1.2), you might have had some battles with the certificates, having to go into the registry to edit the thumbprint and if you doing a Failover Cluster or Availability Group installation – you would have to enjoy this operation on the multiple nodes. Not-so-very-user-friendly to say at least!
So many times, it would scare-off a non Server/Database Administrator or a junior Server/Database Administrator from trying those features.
In SQL Server 2019 the situation has finally improved and the properties window for the SQL Server Network Protocols has got a new button (on the left of this text you can see on how to get to the window that has changed). In the case of my test I will be using a standalone (non-clustered) default Instance of SQL Server 2019, but we shall dive into the multiple server effect later.
The real difference is in the window where we are used to have just 2 options – to view the certificate and to clear it from SQL Server instance (as in remove), but now as you can see on the screenshot on the right, there is a new option that allows to import the certificates and which promises to do the non-SQL management stuff easier.
The new button is marked with a red round-edged rectangle for better visibility. For the comparison below is the picture taken from the SQL Server 2017 Configuration Manager, where you can see no IMPORT possibility directly in the Configuration Manager.
SQL17 Certificate Properties
After clicking on the import button we shall be taken to the Certificate Selection screen where the choice between PFX and PEM will be presented with the respective path to indicate certificate location. Notice that above the import path the radio button with the indication of the “Only import for this node”, which will change to allow other nodes inclusion, in the case when we are working with a Windows Cluster, allowing us to do the job for the multiple nodes (and in this case you will need to specify relative UNC path of course).
Warning: I am not touching here on the full requirements for the Certificates for the Failover Cluster or Availability Groups, this is a different topic, which is very good documented for years.
After indicating the password that was used for the Certificate protection (please always use a good random password generated by a good password manager software and never use internet pages for that), you will bee taken to the screen for the certificate selection that comes inside the specified file (yes, you can have multiple ones).
The certificate import confirmation screen is pretty much all that one can expect from it, reminding you that you will need to restart your instance(s) in order for the certificate to come into effect. The warning of course will come again, just making sure that the person doing installation understands it.
Voilá – all the work shall be done for you and no more regedit with HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\SuperSocketNetLib sufferings (this one is especially true for the Cluster, where you would need to remove the spaces from the Certificate Thumbprint or/and remove the question mark from the first position, aargh!)
After restarting your SQL Server instance you will be all set and of course do not forget the option to Force Encryption, otherwise the eventual willingness of the clients connecting to SQL Server might be a little problem (yeah, saw one just last week).
Do not forget that sometimes you might need to exit the SQL Server Configuration Manager in order to see the newly imported Certificate (I do consider this to be a bug, but a GUI bug and I saw it on multiple instances already).
Final Thoughts
Lovely nice tool feature that is will ease installation of the new certificates on Windows Server !
According to the documentation the new SQL Server Configuration Manager is compatible back to SQL Server 2008 and it will allow to do the same task on the older versions of the SQL Server, which brings us to a funny question – can I install just SQL Server 2019 Configuration Manager on the Windows Servers :)