Partager via

SSL on SQL - Troubleshooting

  • Article

It is one of the pre-requisites for the KEY-SPEC value of the SSL self-signed certificate to be set to 1, for it to be loaded to the SQL server. Due to the glitches during the creation of the certificate there may be scenarios arising where in this value might not be set to 1. If this happens to be 0 or 2, then the AT_KEYEXCHANGEparameter of the certificate is not said to be set. Once when I was working with one of my partner we encountered the exact issue of AT_KEYEXCHANGE not set or the KEY-SPEC value of the SSL certificate used at their end is not set to 1.  I also found that, this is often a question that has been asked a lot of times in the various forums and discussions. On trying to find if there were any resolutions, couldn’t find any blogs on the internet to help us resolve this issue.

As a result of which I researched on the same and came up with a couple of action plans by reproducing the issue at my end by intentionally creating certificate violating the KEY_SPEC parameter value and blogged the resulted observations which will help us resolve the issue and thus instead of creating a new certificate, the existing certificate can be re-used.

Scenario :

I will be creating a SSL self-signed certificate with the KEY_SPEC not set to 1 and explain how to correct it and make the certificate eligible to load it to SQL server.

Approaches:

  1. By creating a ‘PSEUDO’ REGISTRY KEY
  • Firstly, I have created a faulty SSL certificate with the KEY_SPEC not set to 1.
  • If in case any assistance is needed to create the certificate refer to my previous blog wherein I have explained the different ways of creating the SSL certificate.
  • On creating the certificate, running the checkSQLssl tool on the server gives the following validation report for the certificate created.

1

  • Now to address this issue, create a DWORD value called  “NoProviderName”  and set it to 1 at the path HKCU\Software\Microsoft\Windows\CurrentVersion\PFX.
  • Export the certificate in the  .PFX format and deleted it from the store through the MMC.
  • To export, Right click on the certificate -> All Tasks -> Export and the Certificate Export Wizard appears, click on NEXT to continue.
  • Note that for the certificate to be exported from the MMC, initially during its creation itself, the private key should be made exportable. If not the Yes, export the private key radio button as shown will be greyed out and that will be the last nail in the coffin.
  • In the Export Private Key page click on the Yes, export the private key radio button as shown and click on NEXT to continue.

2

  • In the Export File Format page, make sure radio button for  .PFX format is checked as shown and click on NEXT to continue.

3

  • In the Security page, check the Password check box, enter a password and confirm it which will be needed during the re-importing of the certificate. Click on NEXT to continue.
  • In the File to Export page browse to the physical location where you need the certificate to be exported on the machine. Click on NEXT and follow it by clicking on FINISH to complete the export process.
  • Now navigate to the location where you have exported the certificate and then run the below command from elevated CMDprompt:

      Certutil -importpfx <PFXFILENAME.pfx> AT_KEYEXCHANGE

where PFXFILENAME is the file name of the exported certificate.

4

Now the issue of the certificate will be resolved and can be verified by running the checkSQLssl tool again and the certificate will be eligible now to be loaded to SQL server.

5

2. Without creating any REGISTRY KEY:

 

Say for instance, due to security or the product functionality concerns if the ‘PSEUDO’ registry key creation is not permissible, then this approach can be used where in the course of action remains almost the same as the previous one except for the fact that there is no requirement of any registry key to be created.

  • Even here the procedure remains almost the same.
  • We have to export the certificate and then we have to re-import it but there is no need of a registry key creation in this case.
  • Re-import the certificate using the following command:

certutil -csp "Microsoft Strong Cryptographic Provider" - importpfx <PFXFILENAME.pfx> 6

  • Thus the issue with the certificate will now be resolved and thus it will be now eligible to be loaded to SQL server.

Hope this helps.. Happy authenticating!!