TDE with Existing Certificates

Question

Hi,

I'm attempting to set up TDE on one of my databases using a certificate that I already have (SQL Server 2008).  The documentation (and answers to others' questions) seem to indicate that when creating a certificate in SQL Server from an existing certificate, the only format for importing a private key is a .PVK file.

Example:

USE DatabaseName;
CREATE CERTIFICATE TDECertificate
    FROM FILE = 'c:\CertificateFile.cer' 
    WITH PRIVATE KEY (FILE = 'c:\PrivateKeyFile.pvk', 
    DECRYPTION BY PASSWORD = 'passwordHere');
GO 

Additionally, I've read that the only way to convert/extract a .CER to a .PVK is with OpenSSL for Windows, which is a third party product.  It strikes me as extremely unusual that Microsoft would only allow/support a single file type and that to obtain that file type it required the use of a another companies product...

So my two-fold question is this: Is it true that to specify a private key when creating a certificate for SQL Server 2008 you must use a .PVK file?  If so, is there no way to take a certificate already installed on a Microsoft Server (2008) and create a .PVK file using only Microsoft software?

Some of my information sources:

http://msdn.microsoft.com/en-us/library/ms187798.aspx

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/71e909f0-5644-48da-9965-f943069731e3

http://social.technet.microsoft.com/Forums/fi/winserversecurity/thread/85b2b79e-7c39-4b58-8e1e-9855a3af09f6

Answer 1

Hi jbrunssp,

Based on my test, both .pvk and .key formats of private keys can be used to restore a certificate backed up from an existing one. Below is the code used to enable TDE on the source database TDE_DB_Test in source instance, then backup the database and certificate, and restore them to a target instance.

/* Actions on source instance */
-- Create database TDE_DB_Test, and enable TDE
USE master;
GO
CREATE DATABASE TDE_DB_Test;
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password0!';
GO 
CREATE CERTIFICATE MyTEDCert WITH SUBJECT = 'My TED Certificate';
GO
 
SELECT * FROM sys.certificates WHERE name = 'MyTEDCert';
GO
 
USE TDE_DB_Test;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyTEDCert
GO
 
ALTER DATABASE TDE_DB_Test
SET ENCRYPTION ON
GO
 
SELECT DB_NAME (database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
GO
--Backup database TDE_DB_Test
BACKUP DATABASE TDE_DB_Test TO DISK = N'E:\SQL Server\TDE Test\TDE_DB_Test_Encrypted.bak' WITH NOFORMAT, NOINIT, 
NAME = N'TDE_DB_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
 
 -- Backup certificate with private key 
USE master
GO
 
BACKUP CERTIFICATE MyTEDCert TO FILE = 'E:\SQL Server\TDE Test\MyTEDCert.cer'
WITH PRIVATE KEY
(
FILE = 'E:\SQL Server\TDE Test\EncryptionPrivateKey.key', --You can change the file extention to '.pvk'
ENCRYPTION BY PASSWORD = 'Password01!'
)
GO
 
 
/* Actions on target instance*/
USE master
GO
 
DROP CERTIFICATE MyTEDCert
GO
DROP MASTER KEY
GO 
 
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO
 
--Create certificate from the backup files. Please make sure the files existed on the specified directories
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password01!'
GO
CREATE CERTIFICATE MyTEDCert
FROM FILE = 'E:\SQL Server\TDE Test\MyTEDCert.cer'
WITH PRIVATE KEY (
FILE = 'E:\SQL Server\TDE Test\EncryptionPrivateKey.key', --Change the file extention if the backup file extention is '.pvk'
DECRYPTION BY PASSWORD = 'Password01!'
)    
GO
--Restore the database 
RESTORE DATABASE TDE_DB_Test
FROM DISK = N'E:\SQL Server\TDE Test\TDE_DB_Test_Encrypted.bak'
WITH FILE = 1,
MOVE N'TDE_DB_Test' TO N'E:\SQL Server\TDE Test\TDE_DB_Test.mdf',
MOVE N'TDE_DB_Test_log' TO N'E:\SQL Server\TDE Test\TDE_DB_Test_log.ldf',
NOUNLOAD, STATS = 10
GO
 
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
GO

---

Stephanie Lv

TechNet Community Support