Objective: This article provides how to enable ssl encryption over SQL server communication.
Steps to enabling Encryption for SQL Server (Standalone Server) from Server side
1. Create Self Signed Certificate (CN should match the Fully Qualified Domain Name (FQDN) of the server) in the Server
2. Export with a Private Key and keep.
3. Open SQL Server Configuration Manager
4. Select SQL Server Network Configuration
5. Right click Protocols and open Properties
6. Set Force Encryption to Yes under Flags tab.
7. Select previously created Certificate from Certificate tab and Apply
8. Go to SQL Server Native Client Configuration Properties
9. Set Force Protocol Encryption to Yes
10. Now go to SQL Server Services and Restart SQL Server to enable Encryption from Server-side.
11. For each of the clients, import the server certificate into Trusted Root Certification Authorities
12. Also based on Sql Client components being used to connect, the connection string settings need to be changed to enable Encryption and Trusting Server Certificate.
e.g Encrypt=True;Trust Server Certificate=False;
Steps to enabling Encryption for SQL Server (Clustered Environment) from Server side
1. Generate SSL Certificate (CN should match the FQDN of the Cluster)
2. The steps provided above cannot be performed using SQL Server Configuration Manager, as certificate will not be appeared in the list to select in the nodes.
3. Need to set registry values of certificate thumbprint and enabling force encryption on all the nodes.
4. The powershell script to set the registry values is as follows
Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib").PsPath -Name "Certificate" -Type String –Value “CertThumbprint”
Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib").PsPath -Name "ForceEncryption" -Type DWord -Value 1
Verify the Connection encryption status using the following SQL command on server
SELECT a.spid, a.hostname, b.encrypt_option from sys.sysprocesses a
INNER JOIN sys.dm_exec_connections b
ON a.spid=b.session_id
