This article is contributed. See the original author and article here.

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:UsersAdministrator.CONTOSODesktopcerttest.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. 

 

Capture.PNG

 

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 :)

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: