How can you tell if SQL Server successfully loaded the SSL certificate?
- The SQL Server Error Log will indicate that the certificate “ABCXYZ” has been loaded for encryption once you restart your SQL Server service, indicating that the certificate has been loaded.
- You can use the following query to see if the requests are being encrypted:
select * from sys.dm_exec_connections where encrypt_option = ‘TRUE’
- In order to discover which computer is making encrypted requests, you can identify the client information with the help of the columns “client net address” and “session id.”
Note: Since all requests are encrypted by default due to server-side authentication, you should see that for all requests! To view each connection request, execute the command “select * from sys.dm exec connections.”
What if the SSL certificate is still not shown in SQL Server Configuration Manager?
- In SQL Server Configuration Manager, there is a chance that you won’t initially see the SSL certificate in the drop-down list for Certificate.
- When we viewed the certificate’s details in one of the cases, we discovered that the Subject was “abcxyz-corpau.abc.com,” while the Subject Alternative Name had different names.
- In our instance, the customer chose to use this certificate on numerous servers. SQL Server’s domain name is ABCXYZ-SQLServer.abc.local
- Now, the following conditions were not satisfied, hence we were unable to see the certificate in SQL Server Configuration Manager:
- The common name (CN) and host name or fully qualified domain name (FQDN) of the server computer must match, according to the Subject attribute of the certificate.
- Therefore, in our case, we proposed that the Certificate Authority update the Subject name from ABCXYZ-Corp.ABC.com to ABCXYZ-SQLServer.ABC.Local (FQDN of SQL Server).
- After making this modification, we loaded the certificate in MMC once more, and SQL Server Configuration Manager now displayed the loaded certificate.