Generating a trusted TDE Certificate in the proper format from a Certificate Authority

I recently worked with a customer who was attempting to deploy Transparent Database Encryption using a trusted certificate which was generated by a certificate authority (CA). They were unable to import the certificate using the CREATE CERTIFICATE command as it kept failing with a 15468 error. This blog post attempts to explain this error and demonstrates a solution to the problem.

The process of creating a trusted certificate involves using a cryptographic tool to generate a private key which is then submitted to a certificate authority (CA), which will in turn generate a certificate. Microsoft offers a MAKECERT utility that is useful for testing but not recommended for production environments. Other cryptographic tools like the open source OPENSSL are useful for generating private keys that adhere to the strict x.509 cryptography formats.

As a general best practice using EKM is preferable to generating the keys manually as it makes a distinct separation between the key and the database being protected by making the key inaccessible by the SQL Server engine.

SQL Server is capable of using certificates which incorporate the .DER (Distinguished Encoding Rules) file format. These files are binary encoded certificates which can typically have a CER or CRT extension. While Certificate Authorities and cryptography tools like OpenSSL can encode in .DER file format, they can also encode certificates using .PEM or Privacy Enhanced Electronic Mail which uses Base64 formatting. Unfortunately the Base64 format is not compatible with SQL Server.

Some Certificate authorities store both the public and private keys in a personal exchange format or PFX format. SQL Server won’t be able to import these PFX files directly since the CREATE CERTIFICATE command is expecting a DER-encoded certificate (.CER). As a workaround, the Microsoft PVK converter utility can be used to convert the PFX certificates to PVK/DER format. More information can be found in KB Article 2914662 “How to use PFX-Formatted certificates in SQL Server

Any certificates that are used for encryption in SQL Server must use the DER formatting. Certificates coded in Base 64 format which are subsequently imported into SQL Server using the CREATE CERTIFICATE command will generate the following error:

Msg 15468, Level 16, State 6, Line 1

An error occurred during the generation of the certificate.


To correct this problem, the certificate needs to be converted into the DER format so that it can be read by SQL Server. This can be accomplished by having the Certificate Authority re-issue the certificate in the DER format, or optionally the certificate can be converted using the OpenSSL tool.

I will demonstrate the process of creating the Private Key using OpenSSL and Windows Certification Authority tool on the Windows Server operating system to create the certificates in both formats

1. First we must install OpenSSL 1.x and the Visual C++ redistributables from

2. Open a command prompt, change to the ‘c:\openssl-win32\bin’  and execute

set OPENSSL_CONF=c:\openssl-win32\bin\openssl.cfg

3. From the same directory, generate the private key

openssl.exe genrsa 2048 > private.key

4. Now we have to generate the certificate signing request file that will be used to request the certificate from the Certificate Authority or CA.

openssl.exe req -new -key private.key > certificate.csr

5. As part of the creation of the certificate request or CSR file, you will be prompted to answer several questions, where the Common Name will be the subject of the certificate.

6. Submit the CSR file to the Certificate Authority in order to request a certificate. I used the Windows Certification Authority tool to open the CSR file, then issue a certificate:


I then exported it in two formats. The first file ‘certificateDER.cer’ was a DER encoded certificate while the second file ‘certificateb64.cer’ was in Base64 format.


7. When I attempt to import the base64 version of the certificate, it fails with a 15468 error:

FROM FILE = 'D:\Temp\CertTest\certificateb64.cer'
WITH PRIVATE KEY (FILE='D:\Temp\CertTest\private.pvk',

Msg 15468, Level 16, State 6, Line 1

An error occurred during the generation of the certificate.

8. If I switch to the DER encoded certificate, I am able to import the certificate successfully.

FROM FILE = 'D:\Temp\CertTest\certificateDER.cer'
WITH PRIVATE KEY (FILE='D:\Temp\CertTest\private.pvk',

Command(s) completed successfully.

9. I then Confirm that the Certificates are imported:

SELECT * FROM SYS.certificates

10. Finally, I use the DER encoded certificate to encrypt the database:


In summary, when importing a certificate in SQL Server from a certificate authority be sure that the certificate is encoded in DER format. Otherwise the certificate will have to be converted to DER using third party tools like OpenSSL, or a DER version of the certificate will have to be requested from the Certificate Authority.

Greg Husemeier

Comments (6)

  1. Miron says:

    Thank you Microsoft Premier Team for nice article.

    Though a good update worth sharing in comment. Microsoft recently created tool PVKConverter.exe to extract PVK private key in appropriate format from PFX file. It works well and lets you use certificates with stronger encryption keys in SQL Server. These keys meet US NIST Special Publication 800-131A recommendation.

    Please try to download at this link:…/details.aspx

  2. Manohar says:


    Could you please let us know which one is secured between Self-Signed Certificate and Signed Certificate(CA)?

     Thank you in advance

  3. Dinesh vishe says:

    generate the certificate signing request file that will be used to request the certificate from the Certificate Authority or CA.

    openssl.exe req -new -key private.key > certificate.csr

    error showing following.

    Unable to load config info from /usr/local/ssl/openssl.cnf

    Please guide..

  4. John Tracy says:

    We had a situation where we were using a .pfx certificate generated by a certificate authority.  We followed the steps above and continually got:

    Msg 15297, Level 16, State 56, Line 1

    The certificate, asymmetric key, or private key data is invalid.

    With the help of Microsoft Support we found this to be this issue:

    After debugging this further, I was able to figure out the reason for the failure. We are seeing this error due to the length of the “Serial number” property of the certificate. SQL Server would not allow creating a certificate whose “serial number” is greater than 16.

    In the debugger, the serial number was of length 19 and hence the failure.

  5. Jeremy Lubich says:

    Through trial and error, found that even though certificates up to 4096 bits can be created, TDE only works with certificates up to 3456 bits

    The only message to indicate a problem was:

    Msg 15209, Level 16, State 26, Line 2

    An error occurred during encryption.

    Also, I found that the *.key file that openssl.exe makes is not MS compatible. I found a utility called pvk.exe which converts it from the openssl format to the format that SQL likes

  6. Jeremy Lubich says:

    I should have added that the size issue is on SQL 2012. In previous versions, the certificate length was documented as 3456 bits. On 2012, you can make the certificate 4096 bits, but its too long to use for TDE.