How to configure SSL encryption in SQL Server

By:   |   Comments (11)   |   Related: > Security


Problem

I read in a PCI security tip that I should configure Secure Sockets Layer (SSL) encryption on our SQL Servers, but this requires a trusted certificate. Could you please help me on how to complete this task?

Solution

The Secure Sockets Layer (SSL) can be used to encrypt data transferred on your network between your SQL Server instance and a client application. SSL uses certificates to validate the server and the client should verify the certificate using the chain of trust where the trust anchor is the root certificate authority. This requires that the client computer should trust the root authority of the certificate used by your SQL Server.  SQL Server can do this using 128-bit encryption.

The following steps should be completed to setup SSL connections:

  1. Obtain a certificate which fulfills the requirements (see below).
  2. Install the certificate on the server.
  3. Enable encrypted connections in SQL Server.
  4. Enable encryption on the client.
  5. Pay extra attention for a clustered environment (see below).

SQL Server Certificate Requirements

The first step to secure the connections is to obtain a security certificate. There are several requirements which should be fulfilled by the certificate:

  • It must be valid thus the current system date and time should be between the Valid From and Valid To properties of the certificate.
  • The Common Name (CN) in the Subject property of the certificate must be the same as the fully qualified domain name (FQDN) of the server computer.
  • It must be issued for server authentication so the Enhanced Key Usage property of the certificate should include 'Server Authentication (1.3.6.1.5.5.7.3.1)' (see below).


    • Enhanced Key Usage property

  • It must be created by using the KeySpec option of 'AT_KEYEXCHANGE'.
  • It must be placed in the certificate store of the local computer or current user (see below for details).

It is possible to use self-signed certificates, but I recommend only doing this for test purposes because this significantly lowers the level of security.

Install the SQL Server certificate using Microsoft Management Console

If you obtained a certificate which fulfills the above requirements then you should import it to the certificate store on your server. Follow these steps:
  1. Open the Microsoft Management Console (MMC): click Start, then click Run and in the Run dialog box type: MMC
  2. On the File menu, click Add/Remove Snap-in...
  3. Select Certificates, click Add.


    4. Adding the Certificates Snap-in to Microsoft Management Console

  4. You are prompted to open the snap-in for your user account, the service account, or the computer account. Select the Computer Account.
  5. Select Local computer, and then click Finish.
  6. Click OK in the Add/Remove Snap-in dialog box.
  7. Click to select the Personal folder in the left-hand pane.
  8. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate...
  9. Click Next in the Certificate Request Wizard dialog box. Select certificate type 'Computer'.
  10. You can enter a friendly name in text box if you want or leave it blank, then complete the wizard.
  11. Now you should see the certificate in the folder with the fully qualified computer domain name.

Configuring SQL Server to accept encrypted connections

You can configure SSL using the SQL Server Configuration Manager. First, you should run SQL Server Configuration Manager under the SQL Server service account. The only exception is if the service is running as LocalSystem, NetworkService, or LocalService, in this case you can use an administrative account.

  1. Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, then click Properties.
  2. On the Certificate tab, select the certificate you would like to use.
  3. On the Flags tab, select Yes in the ForceEncryption box, then click OK.
  4. Restart the SQL Server service.
Enforce the encryption

Configuring the SQL Server clients to use encrypted connections

You should export the certificate from your SQL Server and install it on the client computer to establish the encryption.

  1. Open the MMC Certificates Snap-in as described above.
  2. Right-click the Certificate, point to All Tasks, and then click Export.
  3. Complete the Certificate Export Wizard, storing the certificate file in a selected location.
  4. Copy the certificate to the client computer.
  5. Use the MMC Certificates Snap-in on the client computer to install the exported certificate file.
  6. In the SQL Server Configuration Manager right-click SQL Server Native Client Configuration, and then click Properties.
  7. On the Flags tab, select Yes in the ForceEncryption box, then click OK.

You can also encrypt the connection from SQL Server Management Studio:

  1. Click Options in the Connect to Server dialog.
  2. On the Connection Properties tab, tick the Encrypt connection checkbox.


    3. Encryption in Management Studio

SSL encryption for failover clustering in SQL Server

If you would like to use encrypted connections in a clustered environment then you should have a certificate issued to the fully qualified DNS name of the failover clustered instance and this certificate should be installed on all of the nodes in the failover cluster. Additionally, you will have to edit the thumbprint of the certificate in the registry because it is set to Null in clustered environment.

The following steps should be performed on all of the nodes in the cluster:

  1. Navigate to the certificate in the MMC Certificates Snap-in and double click to open the certificate.
  2. Copy the hex value from the Thumbprint property on the Details tab to Notepad and remove the spaces.
  3. Start Regedit and copy the hex value to this key: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<YourSQLServerInstance>\MSSQLServer\SuperSocketNetLib\Certificate
  4. You will have to reboot your node, so it is recommended to failover to another node first.
Next Steps
  • Obtain a certificate and try to configure it in your test environment.
  • You can find more articles in the Security category.
  • Read more tips by the author here


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tibor Nagy Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, October 9, 2021 - 1:27:43 AM - Long Nguyen Back To Top (89323)
Hi,

In the ‘Install the SQL Server certificate using the Microsoft Management Console’ section, I though the once you already had a certificate you would need to import it using an Import option, rather than creating a new certificate using the Request New Certificate option?

Thanks
Long

Monday, April 8, 2019 - 11:12:23 AM - Feroz Back To Top (79498)

***If you would like to use encrypted connections in a clustered environment then you should have a certificate issued to the fully qualified DNS name of the failover clustered instance***

Thank you for your post. Incase of using encrypted connection in SQL cluster servers that is hosting 2 SQL Failover Clustered instances, do we required 2 seperate certificate like below

virtualsqlins1.example.com

virtualsqlins2.example.com

Monday, May 28, 2018 - 9:01:47 AM - ali Back To Top (76034)

 Thank you man

 

Tuesday, April 26, 2016 - 4:01:37 AM - Thomas Franz Back To Top (41335)

When I enable and force SSL encryption on the SQL server, will I still be able to connect to the server with any app as long the client trusts the certificat chain? I'm unsure, because I know many apps that have no separate option to enable / disable encryption in their connect dialogs....

Tuesday, September 30, 2014 - 8:38:45 AM - sqlbi Back To Top (34773)

Thank you very much for the reply Tibor.

I went to "SQL Server Configuration Manager" on my local and checked "Trust Server Certificate" property of the SQL Native Client. It is set to NO.

Is that what you are referring to ?

Also I came across this article on the msdn but it looks like the above settings only apply for encrypting loin information and not to SSL. 

 

http://msdn.microsoft.com/en-us/library/ms131691(v=sql.105).aspx

 

Thanks for help.

 

Thursday, September 25, 2014 - 10:59:23 AM - Tibor Nagy Back To Top (34714)

Hello sqlbi,

If the client has been configured to trust the root authority of the certificate used by your SQL Server then you can take advantage of the chain of trust: the client will trust your SQL Server's certificate so it is not required to import the certificate on the client machine.

Regards,
Tibor

Wednesday, September 24, 2014 - 1:53:15 PM - sqlbi Back To Top (34703)

One more thing I need to add to the previous comment. I created self-signed cert on the server and I did not get any option to KeySpec option. I am not sure if it is set to 'AT_KEYEXCHANGE'. Is there a way to check that ? I do not see KeySpec when I go to the certificate properties

Wednesday, September 24, 2014 - 1:49:40 PM - sqlbi Back To Top (34702)

I created a self signed cert on the server for testing and set ForceEncryption to YES in the SQL Server Network Configuration. I restarted the service and tried to connect to the server from my local machine through SSMS. I was able to connect w/o having to install the cert on my local machine. Also when connecting to the server through SSMS, when I check the "Encrypt Connection" checkbox to "YES" the connection property shows that the connection is encrypted. When I uncheck the Encrypt Connection checkbox to "No" the connection property shows that the connection is NOT encrypted. I am confused as to why is the server allowing client to connect to the server w/o installing the self-signed cert that was created on the server.

Wednesday, August 13, 2014 - 3:58:47 PM - eric81 Back To Top (34117)

 

If have generated certificates does having 'force encryption' enabled do anything?

Tuesday, August 12, 2014 - 3:31:40 PM - Tibor Nagy Back To Top (34101)

Thank you for the valuable notes. Generating the certificate was in the scope of this tip but I agree that OpenSSL is one of the most widely used solution when a company does not purchase certificates from an external CA.

Tuesday, August 12, 2014 - 11:43:32 AM - SSL Additional Notes Back To Top (34095)

 

First, note that "Configuring the SQL Server clients to use encrypted connections" step 8 should be restart the SQL Server service for this to take effect.

 

Second, once you've set connection encryption up, you can use

SELECT * FROM sys.dm_exec_connections

to verify that encryption is working (or see that it's failing!)

 

Third, note that you may need to use the FQDN (i.e. YourSQL.localdomain, or YourSQL.MyCompany.int, or YourSQL.MyCompany.int\YourInstanceName) to get to SQL Server rather than just YourSQL.

 

Fourth, note that the SQL Server service account needs Read permission to the private key; in "Install the SQL Server certificate using Microsoft Management Console", step 12 would be right-click the new key, All Tasks, Manage Private Key, then grant Read(but NOT Full Control) to whatever account is running the SQL Server service.  I am unsure about the SQL Server Agent service account.

 

Fifth, you can use OpenSSL to generate certificates as well, using modern algorithms:

openssl genrsa -aes256 -out keys/YourSQLKey.key 4096

 

openssl req -sha512 -new -key keys/YourSQLKey.key -out requests/YourSQLKey.csr

 

openssl x509 -req -sha512 -days 5479 -setalias YourSQLKey -extfile X:\Internal_CA_Based_Certs\YourOpenSSL_SQLConfig.cfg -extensions server_encrypt -in requests/YourSQLKey.csr -CA certs\YourInternalCA.cer -CAkey keys\YourInternalCA.key -CAserial YourInternalCA_serial.txt -out certs\YourSQLKey.cer -modulus -serial -pubkey -fingerprint -subject_hash -issuer_hash -subject -issuer -email -startdate -enddate -purpose -dates -serial -ocspid -ocsp_uri -alias

 

To create YourOpenSSL_SQLConfig.cfg, take the normal openssl.cfg and modify it as follows:

1) Add (replacing the commented out extensions option):

  extensions        = server_encrypt

2) Add a whole new extension section, for instance just above the line [ v3_req ]:


#
[ server_encrypt ]
# Original code signing version From http://poshcode.org/1049 which is from http://huddledmasses.org/code-signing-with-openssl-and-powershell/
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
 
 
# This goes against PKIX guidelines but some CAs do it and some software
# requires this to avoid interpreting an end user certificate as a CA.
#
basicConstraints=CA:FALSE
#
 
# If nsCertType is omitted, the certificate can be used for anything *except* object signing.
# We just want to allow everything (but not object signing):
#
nsCertType = server, client, email
#
# serverAuth should be the critical piece for SQL using it for encryption per http://msdn.microsoft.com/en-us/library/ms189067.aspx
#
extendedKeyUsage       = critical, serverAuth,clientAuth
 
#
# This is typical in keyUsage for a client certificate.
#
keyUsage = nonRepudiation, digitalSignature, keyEncipherment, dataEncipherment
#
 
#
# This will be displayed in Netscape's comment listbox.
#
nsComment                       = "OpenSSL Generated Certificate"














get free sql tips
agree to terms