TDE using external certificate

Question

I am new to TDE and trying to enable TDE for the new project. We have been asked to use a certificate generated by the internal CA.

i have got the certificate as TDE_migration.cer, and did the following stuffs

1. CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Rescdjban#567@Rtes'

<success fully executed the above>

2. CREATE CERTIFICATE TDE_migration_CERT FROM FILE = 'E:\DBA\TDE_migration.cer'

<success>

3. Use <DBName>

CREATE

 

DATABASE ENCRYPTION

KEY

WITH

 

ALGORITHM =

AES_256

ENCRYPTION

 

BY SERVER CERTIFICATE TDE_migration_CERT

<failed with the following error>

Msg 15556, Level 16, State 1, Line 1

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.I am stuck here, can somebody help ?

Answer 1

  The reason why TDE failed is that you only imported the public certificate, but not the corresponding private key (required by SQL to decrypt the DEK). You can use CREATE CERTIFICATE (or ALTER CERTIFICATE ) with the WITH PRIVATE KEY clause to import the private key. 

  NOTE: SQL Server currently only supports private keys for the certificates in PVK format.

   An alternative may be to have a HSM-controlled asymmetric key to use for TDE. For more details please visit “HOW TO: Enable TDE using EKM” from BOL (http://msdn.microsoft.com/en-us/library/cc645957.aspx).

‘

  I hope this information helps,

  -Raul Garcia

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

thanks Rahul,

 

let me check if i can get the PVK file and keep this thread updated