How to troubleshoot SSL encryption issues in SQL Server
By: Tibor Nagy | Comments (1) | Related: > Security
I tried to configure SSL encryption for my Microsoft SQL Server, but it does not work properly. Could you please help me identify the root cause of the issue?
The Secure Sockets Layer (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. To establish an encrypted connection, the certificate should be valid and both the server and the client should be properly configured.
How are certificates validated during the SQL Server SSL handshake?
We can better understand the SSL certificate issues if we take
look at the certification validation process. The client application
checks the following properties during the SSL handshake when they
connect to your SQL Server using SSL encryption:
- The certificate was issued by a trusted certificate authority and none of the certificates in the chain have been revoked.
- The certificate must be enabled to be used for server authentication.
- The certificate is not expired: the Valid To property is greater than the system time.
- The Common Name (CN) in the Subject property or any of the DNS names in the Subject Alternate Name (SAN) property of the certificate matches the serverName value specified in the connection string or the optionally supplied hostNameInCertificate value.
If all of the above validation steps pass, then the authenticity of the server has been validated and the encrypted connection can be established.
SQL Server SSL Troubleshooting Steps
1. Check if the connection is encryptedYou can query the sys.dm_exec_connections dynamic management view (DMV) to see if the connections to your SQL Server is encrypted or not. If the value of encrypt_option is "TRUE" then your connection is encrypted.
SELECT session_id, encrypt_option
2. Check if SQL Server service has been restarted after the configuration changed
You can query the sys.dm_os_sys_info DMV to see when the SQL Server service has been started, just look for the sqlserver_start_time value.
Alternatively you can check this tip to see other methods to find out the last restart time.
3. Try to connect using the Fully Qualified Domain Name
It can cause an issue if you use only the computer name in the connection string. It is better to use the Fully Qualified Domain Name (FQDN) e.g. YourSQLServer.YourCompany.int\YourSQLServerInstance
4. Check the connection string of your application
Pay attention to the following properties of the connection string:
The value of the encrypt property should be 'true' to enable SSL encryption. If trustServerCertificate=true then it is possible to connect to the SQL Server using a self-signed certificate, but this scenario is recommended only in test environments.
5. Check the certificate properties
The certificate should be valid (Valid From and Valid To properties), 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, the Enhanced Key Usage property should include 'Server Authentication (22.214.171.124.126.96.36.199.1)' and the certificate must be created by using the KeySpec option of 'AT_KEYEXCHANGE'.
6. Check if the certificate is trusted by the clientThe client machine should trust the certificate so there are two options:
- The SQL Server's certificate should be installed on the client machine to establish a direct trust.
- The certificate of the root certificate authority and the intermediate/chain certificates should all
be trusted. This way you can take advantage of the chain of trust, the core principle of SSL certificate hierarchy.
- Follow the steps to find out the root cause of your SSL problem.
- You can find more articles in the Security category.
- Read more tips by the author here.
About the author
View all my tips