1. Check if the connection is encrypted
You can query the sys.dm_exec_connectionsdynamic management view (DMV) to see if the connections to your SQL Server is encrypted or not. If the value of encrypt_optionis "TRUE" then your connection is encrypted.
SELECT session_id, encrypt_option FROM sys.dm_exec_connections
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.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
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:
- encrypt
- trustServerCertificate
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 Subjectproperty 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 (1.3.6.1.5.5.7.3.1)' and the certificate must be created by using the KeySpec option of 'AT_KEYEXCHANGE'.
6. Check if the certificate is trusted by the client
The 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.
