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
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.
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.
