Recently, I tried to use the SQL Server 2019 import certificate feature in configuration manager, however, when I import by the pfx file, it prompted me errors at the last step as below:
Errors or Warnings for certificate:C:\Users\Administrator.CONTOSO\Desktop\certtest.pfx
-----------------------
The selected certificate is a self signed certificate. Proceeding with this certificate isn't advised
Error:
The selected certificate name does not match FQDN of this hostname. This property is required by SQL Server
Certificate name: Contoso-DC-CA
Computer name: Node1.Contoso.lab
Error:
The selected certificate does not have the KeySpec Exchange property. This property is required by SQL Server to import a certificate.
Import error: 0x2, Windows Native Error: 0x80092004
The selected certificate does not contain server authentication key usage property. This property required to import certificate to SQL Server.
Actually I have configured the CN the same as my node FQDN, and 'Contoso-DC-CA' was the issuer's name, below is the CheckSQLSSL tool running result:
Store: Local Machine - Personal (CERT_SYSTEM_STORE_LOCAL_MACHINE)
**************************************
> CERT =>Node1.Contoso.Lab
>
OK > Subject name: Node1.Contoso.Lab
FQDN: Node1.Contoso.lab
OK > AT_KEYEXCHANGE is set
OK > Time stamp is valid
OK > Server Authentication 1.3.6.1.5.5.7.3.1
OK > Friendly name: Node1.Contoso.Lab
That's strange, so I begin to think of where the certificate name get from code, after debugging I found when initialize the certificate object, the certificate name has already been the issuer's name:
[+0x224] m_strCertname : 0x8054f88 : "Contoso-DC-CA" [Type: wchar_t *]
[+0x228] m_bProcessedSans : 0 [Type: int]
So what makes it get the issuer's name? After checking the certificate generate process again, I found it was due to an option during export certificate. When you export a certificate to file, "Include all certificates in the certification path if possible" is checked by default, if you check this the exported file will include certificate chain, the root cert's CN was the CA's name, that's why the certificate name always shown as the CA's name.
This option is not notable because it's a default option, you may forget to uncheck it. How do I find it? I just tried to import this .pfx file in certmgr.msc, and import the pfx here again, then I found 2 certs were imported, one is the root cert, one is the cert I applied.
So the solution is easy, just uncheck the option and export the pfx file again, you will get it succeeded 🙂
Microsoft
