none
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

    Tuesday, May 01, 2012 1:22 PM

Answers

  • 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

    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:18 AM
    Wednesday, May 02, 2012 8:19 AM