Forum Discussion
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.
2 Replies
- olafhelperBronze ContributorHave "installed" the certificate, means registered in the crtificates store on your server?
- YPGOWDA
Microsoft
olafhelper, yes importing the certificate into Certificate store on the server.