Not able to use PFX format certificate in SQL Server?
[Update 8/24/2015
This issue has been fixed by SQL Server 2014 SP1 cumulative update 2. See KB https://support.microsoft.com/en-us/kb/3082513 for more details.
]
If you have a security certificate in PFX format that is generated by Microsoft Certificate store, you can not use it directly in SQL Server. But you can use PVKConverter.exe tool to convert to PVK/DER format that can be used by SQL Server. KB “How to use PFX-formatted certificates in SQL Server” has documentation on using this tool.
We have a customer who reported to us that they were not able to use their certificate even after they did the conversion. They got various errors like below:
Msg 15297, Level 16, State 56, Line 1 The certificate, asymmetric key, or private key data is invalid.
Msg 15474, Level 16, State 6, Line 8 Invalid private key. The private key does not match the public key of the certificate.
After digging and debugging, we learned that it is because the serial number of their certificate was too long. Currently SQL Server only allows serial number up to 16 bytes. But customer’s certificate had 19 bytes for the serial number.
You can check your certificate’s serial number by using certutil.exe –dump option or just use certificate manager (certmgr.msc) and check the property details as shown below. In this example, the serial number is exactly 16 bytes.
Now the question is why customer’s certificate had 19 bytes of serial number? They told me that they generated the certificate using Microsoft Certificate store.
It turned out that you can actually have some control over the serial number through HighSerial as documented in “Custom CA Configuration”. If you set it to 0 like ( “certutil -setreg ca\highserial 0” ), you will get 10 byte serial number for future certificate generation (after you configure and restart your certificate service). There are various other options in the document that you can explore and control length and content of your certificate’s serial number.
Update
This issue has been fixed by SQL Server 2014 SP1 cumulative update 2. See KB https://support.microsoft.com/en-us/kb/3082513 for more details.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server
Comments
Anonymous
July 27, 2015
Is this going to get fixed? Or do we have to change all of the certificates generated for the domain for one issue?Anonymous
August 18, 2015
This is fixed in CU2 for SQL 2014 SP1. For more info, please refer to: 3082513 FIX: TDE certificate creation fails in SQL Server 2014 SP1 if the serial number is greater than 16 bytes support.microsoft.com/.../EN-USAnonymous
January 10, 2016
This might be fixed in 2014 SP1, but what about 2012 SP2 or SP3?