A recent project involved setting up encrypted connections on all our SQL Servers. To this we would have to install a certificate in on the server and restart the SQL Server service. Direction on how to do this on an individual instance are provided by Microsoft.  We have test environments that we are going to use one certificate and production will use a different certificate.  The hard part was copying the certificate to the all the servers and making it easy of course was PowerShell.  There is a trick to getting it to recognize the thumbprint that I will tell you about once we are at that set.

Step 1 – Create Certificate and Backup Certificate

For the purposes of this demonstration will use self-generated certificates created on our own machines.  I set it to expire in 99 years long after I will no longer be supporting the system anymore.

NOTE:  Be sure to assign your own secure password and store the password in the password management system of your choosing.

Step 2 – Editing the registry and restart SQL

Next we take the exported pfx file and copy it locally to the temp folder of each machine and import into the local certificate store.  Then we edit the registry with the thumbprint of the certificate.  After that you will have to restart SQL Server to get the changes to take effect.  We also clean up after ourselves and delete the pfx from the temp folder.

Note: To make this safe for production I commented out the restart of SQL Server.  Also, Get-CmsHosts cmdlet can found here.

Step 3 – Verify it worked

After you reboot the server you can run the following query to see if encryption is enabled against the SQL Server error log.

Also, using SSMS click on Options in the bottom corner of the connection screen. Then check off Encrypt connection and Click Connect.


Finale

Now that is done get all your applications connecting to your application specify they want to use encryption to connect so your certificate will actually be being used and/or take to the next step and configure Force Protocol Encryption.

Tracy Boggiano
Follow me

Tracy Boggiano

Database Superhero at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Management and Analytics. She has worked on SQL Server 6.5 and up including currently SQL 2017 RC1. She enjoys monitoring, performance tuning, and high availability and disaster recovery technologies. Tracy is currently a co-organizar the for special interest group for Advanced DBA Topics for the TriPASS user group.

She also tinkered with databases in middle school to keep her sports card collection organized.

Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Tracy Boggiano
Follow me