Compartilhar via

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

  • Artigo

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 https://slproweb.com/products/Win32OpenSSL.html

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:

clip_image002 

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.

clip_image004

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

 CREATE CERTIFICATE My_New_Cert
FROM FILE = 'D:\Temp\CertTest\certificateb64.cer'
WITH PRIVATE KEY (FILE='D:\Temp\CertTest\private.pvk',
DECRYPTION BY PASSWORD = 'password');
Go

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.

 CREATE CERTIFICATE My_New_Cert
FROM FILE = 'D:\Temp\CertTest\certificateDER.cer'
WITH PRIVATE KEY (FILE='D:\Temp\CertTest\private.pvk',
DECRYPTION BY PASSWORD = 'password');
Go

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:

 USE TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE My_New_Cert
Go
 
ALTER DATABASE TDE
SET ENCRYPTION ON
GO

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

  • Anonymous
    March 10, 2014
    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: www.microsoft.com/.../details.aspx

  • Anonymous
    November 20, 2014
    Hi, Could you please let us know which one is secured between Self-Signed Certificate and Signed Certificate(CA)?  Thank you in advance

  • Anonymous
    December 14, 2014
    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..

  • Anonymous
    January 15, 2015
    The comment has been removed

  • Anonymous
    January 21, 2015
    The comment has been removed

  • Anonymous
    January 21, 2015
    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.

  • Anonymous
    August 03, 2018
    Useful Article. One Query.Is it possible to use external CA certificate with 4096 Bit strength in SQL 2014/2016 for TDE.