Enabling client side SSL encryption to SQL Server

  • Article

Hello all,

In this blog, I am covering the scenario of enabling SSL encryption on the client while connecting to SQL Server.

 

SSL encryption with SQL server can be achieved by enabling Server side encryption or client side encryption.

Server side encryption requires, enabling Force encryption= True on SQL Server which will enable encryption on all the incoming connections to SQL Server.

Client side encryption can be achieved by using Encryption=true in the connection string of the application or setting encryption=true at the client driver which is used. To enable Force Protocol Encryption on the client, certificate must be present on the SQL server and the client must have the Trusted Root Authority updated to trust the server certificate.

 

To demonstrate this, I am using   Win7ClientPC as the client machine and Win2k8R2SQLProd as the SQL instance:

Creating and Installing SSL certificate:

To setup SSL encryption, using a test certificate. Test certificates can be created and installed by using the tool makecert using the below command: Replace the Subject name (CN) with the FQDN of SQL Server.

makecert -r -pe -n "CN=WIN2K8R2SQLPROD.xxxxx.xxxxx.com" -e 12/12/2020 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Capture2

The above command will automatically install the certificate on the SQL Server box and install it on personal folder.

2

Make sure that the properties of the certificate includes the private key.

3

 Exporting SSL certificate from SQL Server:

Next step is to export the Server certificate with the private key:

4 5 6 7 8 9 10 11

 

Importing the SSL certificate on Client machine:

Copy the certificate.pfx file created to the Windows 7 client PC.

12

To import the certificate on the client machine and install it on trusted root folder:

13 14 15 16 17 18 19 20

Ensuring that the certificate imported has the private key intact.

21

 

Test the connectivity with encryption:

To test the connectivity with encryption ON from client machine, create an user DSN using ODBC data source administrator tool.

22 23 24 25 26 27 28

The above screenshots indicate that the client is encrypting the connection to SQL Server.

Verifying encryption from SQL query:

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

 

29

 

Tracking the Network traffic using netmon tool shows encrypted conversation.

 

Capture

To force the encryption in the connection string, set the parameter value "Use Encryption for Data" to true.

For example:
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog="";Data Source=Win2k8R2SQLProd;Initial File Name="";Use Encryption for Data=True

Reference: https://support.microsoft.com/en-in/kb/316898

 

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.