locked
TDE and ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY RRS feed

  • Question

  • We're currently investigating the use of TDE, one requirement is to prevent access to a database in the event of the server being stolen. Although we have already addressed the issue of physical access to the server we would like to introduce another layer.

    I understand that TDE requires a MASTER KEY in the master database and that, by default, this key is protected by the SERVICE MASTER KEY and a password that must be specified at creation time.

    This is all fine, but SQL Server still allows access to the database in a simulated server 'theft' as the master database's MASTER KEY is automatically opened upon startup due to the MASTER KEY being protected by the SERVICE MASTER KEY, which, in turn, is encrypted by the machine key and service account credentials - at least one of which is still available to SQL Server in the event of a theft.

    It is possible to alter the master database's MASTER KEY to drop encryption by the SERVICE MASTER KEY, and then bring the encrypted database online manually by opening the MASTER KEY using the password then issuing ALTER dbname SET ONLINE. This works fine, but I'm not sure if this is a supported solution - does anyone have any experience or knowledge in this area?

    Here's a sample script:

    CREATE DATABASE TDE
    GO
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
    GO
    ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
    GO
    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
    GO
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
    GO
    USE TDE
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert
    GO
    ALTER DATABASE TDE
    SET ENCRYPTION ON
    GO
    
    --Now stop and restart SQL Server
    --TDE database should not be available.
    
    --Run the following statements and the database will be
    --brought online.
    USE master
    GO
    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
    GO
    ALTER DATABASE TDE SET ONLINE
    GO


    Thanks
    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Wednesday, September 9, 2009 11:46 AM

Answers

  •   In order to use TDE with the native SQL Server key hierarchy (Service Master Key, etc.) requires that the server CERTIFICATE protecting the DEK is protected by the master DB Master Key (DBMK), and that the DBMK is protected by the SMK. This way the server would be able to access the DEK whenever the DB needs to be opened without intervention.

      An alternative is to use an ASYMMETRIC KEY stored outside the server (i.e. using an HSM) using Extensible Key Management (EKM). I would recommend reading the following article that explains how to enable TDE with EKM.

      I hope this information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 9, 2009 5:03 PM

All replies

  •   In order to use TDE with the native SQL Server key hierarchy (Service Master Key, etc.) requires that the server CERTIFICATE protecting the DEK is protected by the master DB Master Key (DBMK), and that the DBMK is protected by the SMK. This way the server would be able to access the DEK whenever the DB needs to be opened without intervention.

      An alternative is to use an ASYMMETRIC KEY stored outside the server (i.e. using an HSM) using Extensible Key Management (EKM). I would recommend reading the following article that explains how to enable TDE with EKM.

      I hope this information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 9, 2009 5:03 PM
  • Hi Chris,

    I have the question as you.

    I test "DROP ENCRYPTION BY SERVICE MASTER KEY" in hope to prevent automatically open DMK after restarting SQL Server and prevent access to encrypted data without explicitly open DMK. But I see that DMK automatically open and access to data is possible without open key by password.

    Indeed, My goal is prevent access to data in event of computer stolen. Do you have a solution to this problem (Expect use of EKM)?

    Tuesday, February 3, 2015 12:47 PM
  • Hi,

    Although you didn't answer the question "It is possible to alter the master database's MASTER KEY to drop encryption by the SERVICE MASTER KEY, and then bring the encrypted database online manually?" directly, am I right to assume the answer is NO because the encryption chain will be broken.

    Is this right understanding?

    Thursday, February 5, 2015 5:12 PM
  • No. SQL Server will prevent this.  You'll get this error when trying to create the DEK:

    Msg 33101, Level 16, State 1, Line 18

    Cannot use certificate 'MyPrivateCertificate', 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.

    This could work in theory, but it's so dangerous and operationally awkward that it's blocked.

    You should look at Azure Key Vault, which provides a cloud-based EKM solution

    Azure Key Vault

    SQL Server Connector for Microsoft Azure Key Vault

    You can also consider BitLocker, especially with  Network Unlock. See:

    BitLocker: How to enable Network Unlock

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, February 5, 2015 5:36 PM