Sql-server – TDE cannot encrypt database

sql serversql-server-2008-r2transparent-data-encryption

In order to encrypt a SQL db, I run the following commands:

CREATE CERTIFICATE <certname> ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
WITH SUBJECT = 'certificate subject', EXPIRY_DATE = '20291031';
go

USE <databasetoencrypt>;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <certname>;
GO

When trying to execute the CREATE DATABASE ENCRYPTION KEY command, I receive this message and don't know what to do:

Cannot use certificate 'name', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

When I omit the ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>' during the CREATE CERTIFICATE command, it works. However, I need to create a password protected certificate.

Do you have any idea?! Thank you.

Best Answer

You cannot create a password-protected-certificate, then use that certificate to encrypt a database via TDE since the SQL Server will not be able to open the database.

From Microsoft Docs:

Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM.

Steps involved in encrypting a database using TDE:

  1. Create a master key

  2. Create or obtain a certificate protected by the master key

  3. Create a database encryption key and protect it by the certificate

  4. Set the database to use encryption

An example of the above, taken from the Microsoft Docs page on TDE:

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
go  
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
go  
USE AdventureWorks2012;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  
ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION ON;  
GO

That page also says this:

TDE certificates must be encrypted by the database master key to be accepted by the following statements. If they are encrypted by password only, the statements will reject them as encryptors. 

    CREATE DATABASE ENCRYPTION KEY
    ALTER DATABASE ENCRYPTION KEY
    DROP DATABASE ENCRYPTION KEY

Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.

Related Question