Unable to see (empty) SSL certificate to apply on SQL Server Configuration Manager.

Issue:

Unable to see (empty) SSL certificate to apply on SQL Server Configuration Manager.

 

 

Environmental Details:

SQL Server Name (FQDN): YPSQL1.YP.LAB

SQL Server Name (NetBIOS): YPSQL1

Domain Name: YP.LAB

 

 

Cause:

Common mistake: CN value not matching FQDN of the SQL Server Computer name or KEYSPEC value is 0.

 

How to verify and resolve:

CN value not matching FQDN of the SQL Server Computer name or KEYSPEC value is 0.

Steps: To validate whether the certificate imported on SQL Server (YPSQL1) has all the required properties which are required for SQL Server application to use.

• Click Start-> Run-> Type MMC-> Click OK -> Click File -> Add/Remove Snap-in..

 

• Select Certificates-> Click Add -> Select Computer account -> Click Next -> Click Finish -> Click OK

 

 

 

 

 

• Import the certificate into Personal->Certificates

 

Now let’s validate whether the certificate we imported above has required properties for SQL Server application to detect and accept.

Ref: Certificate requirements for SQL Server - SQL Server | Microsoft Learn / Certificate management (SQL Server Configuration Manager) - SQL Server | Microsoft Learn

• Double click the certificate

 

 

 

 

 

 

 

 

 

From the above highlights

General tab:

Issued to: YPSQL1.YP.LAB à this is matching the SQL Server computer FQDN name

Valid from: this should be valid and not expired

Private key: The certificate should have private key

Details tab:

Enhanced Key Usage: Server Authentication

Key Usage: Digital Signature, Key Encipherment

Subject: CN=YPSQL1.YP.LAB à this should be matching computer FQDN name

Certification Path tab:

Certification path: should be valid RootCA and status should be okay à This RootCA should be imported into Trusted Root Certification Authorities -> Certificates location

 

Now let’s validate whether the certificate has KEYSPEC=1

Ref: Active Directory Federation Services and certificate Key Specification property Information | Microsoft Learn

• KeySpec values and associated meanings

The following are the meanings of the various KeySpec values:

Keyspec value	Means	Recommended AD FS use
0	The certificate is a CNG cert	SSL certificate only
1	For a legacy CAPI (non-CNG) cert, the key can be used for signing and decryption	SSL, token signing, token decrypting, service communication certificates
2	For a legacy CAPI (non-CNG) cert, the key can be used only for signing	not recommended

• On the SQL Server where we have imported the above certificate, click Start -> Run -> CMD

Run the command: certutil -v -store my >c:\cert.txt -> this would dump the certificate details loaded on the server computer store

 

• From the text below is the certificate details we are interested in:

 

 

 

 

 

 

 

• The important attribute which we were interested in is “KeySpec = 1 -- AT_KEYEXCHANGE”.
• Certificate with any of the above highlighted attribute values missing is not valid for SQL Server Application to use.
• Now, let’s go ahead and validate whether the certificate is visible in SQL Server Configuration Manager to issue and apply. Then restart the SQL Server Service.