Skip to content

SSL Certificate not visible in SQL Configuration Manager Issue

  • by

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.

Leave a Reply

%d bloggers like this: