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.

New-SelfSignedCertificate -DnsName *.yourdomain.com -CertStoreLocation cert:\LocalMachine\My -FriendlyName sqlserverssql -KeySpec KeyExchange -NotAfter (get-date).AddYears(99)

$thumbprint = $(Get-ChildItem Cert:\LocalMachine\My).thumbprint
 
$Pwd = ConvertTo-SecureString -String "Password1!" -Force -AsPlainText
 
Export-PfxCertificate -Cert "Cert:\LocalMachine\My\$thumbprint" -FilePath "\\server.domain.com\share\Backup\sqlserverssql.pfx" -Password $Pwd -Force

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.

[string] $UNCPfxPath = "\\server.domain.com\share\Backup\sqlserverssql.pfx"
 
Get-CmsHosts -InstanceList 'c:\temp\servers.txt' | % { New-PSSession -ComputerName $_ -EnableNetworkAccess; Copy-Item $UNCPfxPath "\\$($_)\c$\temp\sqlserverssql.pfx" | out-null}
$sessions = Get-PSSession
$scriptblock = {
    $Pwd = ConvertTo-SecureString -String "Password1!" -Force -AsPlainText
    [string] $PfxPath = "C:\temp\sqlserverssql.pfx"

    Import-PfxCertificate -CertStoreLocation Cert:\LocalMachine\My -Exportable -Password $Pwd -FilePath Filesystem::$PfxPath
    
    $certificateObject = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
    $thumbprint = $certificateObject.Import($PfxPath, $Pwd, [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::DefaultKeySet)

    Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib").PsPath -Name "Certificate" -Type String -Value "$($certificateObject.Thumbprint)"

    #Restart-Service -Name mssqlserver -Force
    #Sleep 10
    #Start-Service SQLServerAgent

    Remove-Item $PfxPath
}

Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock
$sessions | Remove-PSSession

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.

EXEC sp_readerrorlog 0, 1, 'encryption'

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.

Related Posts

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

  1. 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

  2. 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

  3. hi Tracy, we have a windows core 2016 STD edition. we are trying to deploy MSPKI certificate. could you please confirm if the same steps to follow to enforce the MSPKI certificate on SQL server configuration manager. if not, could you please provide the steps. thanks in advance.

    1. This is article shows you how to do it locally and tells you how to remotely if you know a couple of PowerShell commands. I would have to setup a test machine or two to test and still would not be 100% sure it it works. https://mcpmag.com/articles/2014/11/18/certificate-to-a-store-using-powershell.aspx?m=1. If you want to email me I will try to give you can outline of the code I would attempt to use and once you get it working you can post it back here.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.