TDE with trusted certs

Question

TDE with trusted certs

TheMaskedDBA

SSCrazy

Points: 2787
More actions
March 27, 2018 at 2:26 pm

#410368

Hello. We are switching from self signed certs to trusted 3rd party certs and we are able to do so on SQL 2012/2014 and SQL 2016. However, when we try to import the cert/key into a SQL 2008 R2 instance, we get the below error:
Msg 15297, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key data is invalid.

Has anyone seen this behavior ? I can take the same exact cert/private key files to a SQL 2016 Server for example, and it will import successfully, and I am able to encrypt databases with it successfully as well.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Key size or algorithm? 2008 doesn't support the same ones that 2014/2016 do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 2

Thanks Gail. Yes, the certs / keys we generated are 2048 bits, from Comodo, which I can import into SQL 2016 successfully, but unable to do so into SQL 2008 R2. Trying to understand what the limitations are on SQL 2008 R2, so we can work with the provider to get the appropriate certs/keys.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What algorithm does the cert use?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 4

2008 doesn't support the same ones that 2014/2016 do

Details about that is exactly what I'm looking for. Is there any documentation about the limitations anywhere ? We are using AES 256.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlGDBA - Wednesday, March 28, 2018 6:53 AM
2008 doesn't support the same ones that 2014/2016 do

Details about that is exactly what I'm looking for.

And yes, SQL's documentation does say what algorithms and key sizes are accepted by what versions. Look for the docs on CREATE CERTIFICATE for the different versions

AES-256 is a 256 bit key, not 2048.2048 sounds like a symmetric key size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 6

Gail - Thanks for your reply and appreciate your help. A quick check of this documentation : CREATE CERTIFICATE reveals the following text in the remarks section:

The Private Key must be <= 2500 bytes in encrypted format. Private keys generated by SQL Server are 1024 bits long through SQL Server 2014 and are 2048 bits long beginning with SQL Server 2016. Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 4,096 bits. The length of an imported private key must be an integer multiple of 64 bits. Certificates used for TDE are limited to a private key size of 3456 bits.

At the top of the article, in the "Applies to:" section, the below is mentioned:

THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

And yes, you are correct. We are using AES-256 bit for the encryption algorithm, but using a 2048 bit private key to protect the certificate. Is there any other place I should be looking for version specific documentation ?

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 7

I checked the SQL 2008 R2 version here: https://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx

This has no info other than the below:

A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE CERTIFICATE can load a certificate from a file or assembly. This statement can also generate a key pair and create a self-signed certificate.

Private keys generated by SQL Server are 1024 bits long. Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 3,456 bits. The length of an imported private key must be an integer multiple of 64 bits.

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 8

To anyone interested: We found that SQL 2008 R2 cannot use a certificate with a serial number thumbprint of greater than 16 bytes. It has to be 16 bytes or below. Similar limitations were addressed in later versions thru service packs, but Microsoft will not be patching SQL 2008 R2 since it is an old version. Examples where later versions have been patched:

https://support.microsoft.com/en-us/help/3082513/fix-tde-certificate-creation-fails-in-sql-server-2014-sp1-if-the-seria

Although there is no documentation for SQL 2012 either, it has been patched in later service packs. An attempt to import a certificate with a serial number greater than 16 bytes in length for SQL 2012 SP1 will fail with the same error. However, I was able to successfully import it into SQL 2012 SP3, so I am guessing they patched it somewhere along the way.