Forum Discussion

YPGOWDA's avatar
YPGOWDA
Icon for Microsoft rankMicrosoft
Oct 04, 2024

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.

 

 

 

 

2 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Have "installed" the certificate, means registered in the crtificates store on your server?

Resources