Skip to content

Enable SSL Encryption and Import Certificate for Multiple Instances of SQL Server using PowerShell

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.

4 thoughts on “Enable SSL Encryption and Import Certificate for Multiple Instances of SQL Server using PowerShell

  1. Pingback: Enabling SQL Server SSL Encryption With Powershell – Curated SQL

  2. Ameena says:

    Hi Tracy,

    We did SSL encryption certificate on servers last month. We did the similar way you explained in your blog. We had a problem on the SQL Server in cluster. We used the clustername when creating certificate. Did the registery part and enabled on each sql instance. But after rebooting we could not start sql server so we have to revert the changes. Do you have instructions for doing it on sql server in cluster setting?

    BTW, I was just watching you on the Group BY with your AG Session. Informative.

    Thanks.
    Ameena

    Reply
  3. Ameena says:

    Actually me and the infrastructure guy did this using non-Powershell way and followed the steps in the same link you provided. But I think issue could be we have strong CIS OS level security in place. We can try removing this policy from the servers and can see if SSL encryption work this way in cluster. I don’t know when we will get to it. But if resolved, I will let you know here.

    Tracey, Keep up the good work of sharing your knowledge. We love you in SQL community. I am now the co-chapter leader of Chicago user group so let me know if you are planning to stop by in Chicago and we can have you as a speaker. We usually line up speakers 3-months in advance.

    Ameena

    Reply

Leave a Comment