locked
Transparent Database Encryption - Hole in the documentation, and misleading error message RRS feed

  • Question

  • I've literally read -every- TechNet article available on TDE probably three times from cover to cover. We're working through a pilot of TDE, and our configuration is very simple, however the "warnings" produced by SQL don't make sense given what (small amount) of documentation they have on TDE using server-based certificates.

    We're creating a Service Master Key using the standard convention - CREATE MASTER KEY ENCRYPTION BY PASSWORD
    (this is a symmetric key, so you have no choice but to use as password)

    Then we're creating a Database Encryption Key using - ENCRYPTION BY SERVER CERTIFICATE

    As soon as I do this, SQL produces the following warning:

    Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

    So I assumed this meant I needed to OPEN MASTER KEY DECRYPTION, then BACKUP MASTER KEY TO FILE

    Wrong... OPEN MASTER KEY and BACKUP MASTER KEY TO FILE are only used for databases that use symmetric keys. When you use ENCRYPTION BY SERVER CERTIFICATE, you're deploying an asymmetric certificate-based key.

    So I'm confused... Questions to the forum are:

     1. Is that SQL "warning" a bug when using certificate based keys? Is it telling me I need to backup the Database cert even though it's not possible.  I did in fact backup the Service Master Key, so that's not the issue.

    2. Being that I'm using certificate based keys, are all the databases encrypted with ENCRYPTION BY SERVER CERTIFICATE basically using the same private key data?  Meaning, in a DR/recovery scenario, the saved Service Master Key from that server is all we should need to recover the data?

    Again, sorry for the post.  I'm used to finding these answers myself via MSDN or TechNet, but the info is thin on this topic.

    Thanks,

    Thursday, May 1, 2014 5:00 PM

Answers

  • Hello,

    1. Is that SQL "warning" a bug when using certificate based keys? Is it telling me I need to backup the Database cert even though it's not possible. I did in fact backup the Service Master Key, so that's not the issue.

    No, it's not a bug. It's telling you to backup the cert in the master database as you've not already backed it up. If you would lose the cert that is used to encrypt the DEK, all of your data would be lost. Forever.

    2. Being that I'm using certificate based keys, are all the databases encrypted with ENCRYPTION BY SERVER CERTIFICATE basically using the same private key data? Meaning, in a DR/recovery scenario, the saved Service Master Key from that server is all we should need to recover the data?

    The SMK is not encrypting the databases. The SMK encrypts the DMK. The DMK in turn encrypts the Cert. The Cert then encrypts the DEK. There are multiple levels of encryption at work here. There is a really nice graphic that I don't have a link to at the moment but is in BOL when it comes to encryption that would maybe help make more sense.

    I think maybe some general learning in the overall encryption cycle would be worth your while. This is but a small sample of what can fully be accomplished and the myriad of ways to encrypt and decrypt something.

    Here is an example for you to play with to get the warning message (or uncomment the backup cert command) or to not get the warning message. Please note that this is a very simple and trivial example and does not include things like key rotation that your organization may require.

    USE master
    GO
    
    CREATE DATABASE TDE_Test;
    GO
    
    -- Generate a database master key if one doesn't exist
    IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$uper$ecurePassword'
    
    -- Generate a certificate to use for TDE
    -- While no WARNING is printed, this is what the warning is referencing
    -- if you create a DEK without backing up the cert encrypting the DEK
    -- This is because if you lose the cert that is encrypting the DEK
    -- there will be no way to get any of your data back.
    IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'TDE_Cert')
    	BEGIN
    		CREATE CERTIFICATE TDE_Cert
    		WITH SUBJECT = 'Cert used to encrypt DEK for TDE'
    	END
    
    /*
    Uncomment this part to test the difference between backing the cert
    up before you create the DEK and after, notice the difference as there
    is no warning message if you FIRST back up the cert.
    
    BACKUP CERTIFICATE TDE_Cert
    TO FILE = 'C:\temp\TDE_Cert.cer'
    WITH PRIVATE KEY
    (
    FILE = 'C:\temp\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'M0r3$3crets'
    )
    */
    
    
    USE TDE_Test
    GO
    
    -- Now create the actual DEK
    -- This will generate a warning for the TDE_Cert certificate
    -- IF it hasn't been backed up
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert
    
    
    USE master
    GO
    
    DROP DATABASE TDE_Test
    GO
    
    DROP CERTIFICATE TDE_Cert
    GO


    Sean Gallardy | Blog | Twitter

    • Marked as answer by jeffhoward001 Thursday, May 1, 2014 6:46 PM
    Thursday, May 1, 2014 6:23 PM

All replies

  • I'm not sure I understand correctly. The warning urges you to backup the certificate. You are trying to back up the key.

    Using part of the code example from Move a TDE Protected Database to Another SQL Server http://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx don't you want something more like:

    USE master ;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*****';
    GO
    CREATE CERTIFICATE TestSQLServerCert
    <your bit by server cert>
    GO

    BACKUP CERTIFICATE TestSQLServerCert
    TO FILE = 'TestSQLServerCert'
    WITH PRIVATE KEY
    (
        FILE = 'SQLPrivateKeyFile',
        ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
    );
    GO

    I admit I haven't tried to reproduce your exact scenario, so I might be off base.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, May 1, 2014 5:18 PM
  • Thursday, May 1, 2014 5:59 PM
  • Hello,

    1. Is that SQL "warning" a bug when using certificate based keys? Is it telling me I need to backup the Database cert even though it's not possible. I did in fact backup the Service Master Key, so that's not the issue.

    No, it's not a bug. It's telling you to backup the cert in the master database as you've not already backed it up. If you would lose the cert that is used to encrypt the DEK, all of your data would be lost. Forever.

    2. Being that I'm using certificate based keys, are all the databases encrypted with ENCRYPTION BY SERVER CERTIFICATE basically using the same private key data? Meaning, in a DR/recovery scenario, the saved Service Master Key from that server is all we should need to recover the data?

    The SMK is not encrypting the databases. The SMK encrypts the DMK. The DMK in turn encrypts the Cert. The Cert then encrypts the DEK. There are multiple levels of encryption at work here. There is a really nice graphic that I don't have a link to at the moment but is in BOL when it comes to encryption that would maybe help make more sense.

    I think maybe some general learning in the overall encryption cycle would be worth your while. This is but a small sample of what can fully be accomplished and the myriad of ways to encrypt and decrypt something.

    Here is an example for you to play with to get the warning message (or uncomment the backup cert command) or to not get the warning message. Please note that this is a very simple and trivial example and does not include things like key rotation that your organization may require.

    USE master
    GO
    
    CREATE DATABASE TDE_Test;
    GO
    
    -- Generate a database master key if one doesn't exist
    IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$uper$ecurePassword'
    
    -- Generate a certificate to use for TDE
    -- While no WARNING is printed, this is what the warning is referencing
    -- if you create a DEK without backing up the cert encrypting the DEK
    -- This is because if you lose the cert that is encrypting the DEK
    -- there will be no way to get any of your data back.
    IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'TDE_Cert')
    	BEGIN
    		CREATE CERTIFICATE TDE_Cert
    		WITH SUBJECT = 'Cert used to encrypt DEK for TDE'
    	END
    
    /*
    Uncomment this part to test the difference between backing the cert
    up before you create the DEK and after, notice the difference as there
    is no warning message if you FIRST back up the cert.
    
    BACKUP CERTIFICATE TDE_Cert
    TO FILE = 'C:\temp\TDE_Cert.cer'
    WITH PRIVATE KEY
    (
    FILE = 'C:\temp\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'M0r3$3crets'
    )
    */
    
    
    USE TDE_Test
    GO
    
    -- Now create the actual DEK
    -- This will generate a warning for the TDE_Cert certificate
    -- IF it hasn't been backed up
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert
    
    
    USE master
    GO
    
    DROP DATABASE TDE_Test
    GO
    
    DROP CERTIFICATE TDE_Cert
    GO


    Sean Gallardy | Blog | Twitter

    • Marked as answer by jeffhoward001 Thursday, May 1, 2014 6:46 PM
    Thursday, May 1, 2014 6:23 PM
  • Thanks Sean, that was very helpful.  I've seen the graphic you're referring to, it's on the "Understanding Transparent Database Encryption" page - http://technet.microsoft.com/en-us/library/bb934049(v=sql.100).aspx

    My main gap in understanding was the role of the Service Master Key, which I believe I understand now (although I still don't think TechNet does a very good job explaining this).

    The last piece I'm struggling with is understanding the relationship of the private key data.  Using a quote from your post above:

    The SMK is not encrypting the databases. The SMK encrypts the DMK. The DMK in turn encrypts the Cert. The Cert then encrypts the DEK. There are multiple levels of encryption at work here. There is a really nice graphic that I don't have a link to at the moment but is in BOL when it comes to encryption that would maybe help make more sense.

    Makes sense that the SMK isn't doing the DB encryption now that re-read this.  However the relationship between the SMK and the "master" DB's DMK still isn't obvious to me.  You create a symmetric DMK for the "master" DB using the following command:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD

    But my understanding of symmetric keys means that the DMK for master is stand-alone private key data, protected only by password I enter.  So is the relationship to the SMK being that SQLSrvr.exe essentially needs to access my private key data without asking me to entering the password, so it does so by storing a copy of the -exact same private key data-, just encrypted with it's own SMK?  

    If my previous statement is true, then the relationship between the SMK and "master" DMK isn't necessarily a hierarchical PKI relationship where the "master" DMK can't exist without the private key data in the SMK.  It's more a "functional" relationship where the concept of "transparent" data encryption can't work without the SQLSrvr.exe having it's own privately encrypted version of my "master" DMK to stand up TDE (else, every time you started SQL Server, you would have to enter your DMK password).

    My point being, 1) Just to fully understand the technology used, but 2) to be 100% confident in what we need in a recovery/DR scenario.  It's really about having a backup of the "master" database DMK and certificate if you're encrypting your user DBs with ENCRYPTION BY SERVER CERTIFICATE, correct?  I'm obviously still going to store a backup of my SMK, this is more just educational at this point.

    Thanks,

    Thursday, May 1, 2014 7:03 PM
  • Hopefully I can answer this one.

    As you mentioned any DMK (including master DB DMK) is pretty much a standalone symmetric key used to protect secrets within the DB (i.e. certificate private keys, etc.). The DMK is encrypted by at least one password and by default (but optionally) encrypted by the SMK for functionality reasons (i.e. you don’t have to type the DMK password in order to use it).

    For example, when you run

    CREATE MASTER KEY ENCRYPTION BY PASSWORD

    You will create a new DMK encrypted by the password you choose as well as by the SMK. Because of these two copies exist, you can move the DB to another server (i.e. could be part of your DR scenario or just because you want to migrate this particular DB) without having to migrate master DB as well. If you know the password of the DB, you can recover anything protected by it and recreate the SMK protection on the new server if you desire it.

    On the other hand, if you really don’t wish to use the SMK protection for the DMK, and prefer that every time the DMK is used, the caller must open the key (with a password), use it, and close the DMK it should be possible:

    CREATE DATABASE test_dmk;

    use test_dmk;

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<< user defined password >>>'

    go

    ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

    go

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<< user defined password >>>'

    go

    SELECT * FROM sys.openkeys;

    go

    CLOSE MASTER KEY

    go

    SELECT * FROM sys.openkeys;

    go

    It is important to make an observation here, if the password for the DMK creation is a weak password, an attacker who gets hold of a copy of your database (assuming it is not TDE protected or encrypted in any other way) may be able to attack the DMK password and recover it.

    Regarding creating and storing securely backups for the SMK, DMK and certificates protecting DEK. It is highly recommended that you make backups of these objects as they can be used to recover your data at different levels of granularity:

    • The SMK can be used to recover all of the DMKs that are protected by the SMK (obviously won’t help if the DMK is not protected by the SMK).
    • The DMK backup would be specific to the DB. For this type of key, you also have the password used during creation time as an alternative for disaster recovery. The difference is that it is easier to manage the DMK backup and keep it in a safe location physically separate from the DB itself.
    • The certificates protecting DEKs would be the minimum necessary key you need in order to move your DB to a new server, even if master DB is lost/different.

    I hope this helps.

    -Raul Garcia

    This post implies no warranty


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

    Thursday, May 1, 2014 7:34 PM
  • Raul beat me to it!

    Thank you Raul, great post!


    Sean Gallardy | Blog | Twitter

    Thursday, May 1, 2014 8:30 PM
  • Thanks again guys.  I read the whitepaper on SQL Server encryption you provided - http://technet.microsoft.com/en-us/library/cc837966(v=sql.100).aspx  

    That was a great article and very helpful.  One question I wasn't able to answer from the article is how (or whether it's possible) to create a certificate that's protected by PKI outside of the SQL Server instance.  Our end-goal would be having a protection mechanism outside of SQL Server to mitigate the risk of data portability.

    So for example, we want to give our Developers sysadmin rights to SQL Server in pre-production environment. However this will allow them to export all the keys that protect the data protected with TDE.

    We do not however grant Windows Admin permissions to our developers.  This is because we have security measures in place that are protected by service accounts and applications at the Windows layer. So with that in mind, if the certificate used to encrypt our databases was protected by a PKI structure that included a certificate that was only installed on our servers, this would prevent portability of the TDE cert, thereby preventing portability of the TDE-protected data (in theory).

    What I can't figure out is whether the existing certificate chain of trust matters once you create the SQL Server cert from a file (e.g. CREATE CERTIFICATE ServerA FROM FILE 'C:\Certs\ServerA.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\ServerA.pvk', DECRYPTION BY PASSWORD = '####');

    Say for example, ServerA.cer had a certificate chain that relied on a self-signed local cert on the server (e.g. ServerA_root). If the user runs a BACKUP CERTIFICATE WITH PRIVATE KEY, then restores that certificate to a -different- server where the ServerA_root doesn't exist in the target servers local cert store, will the user be able to mount the encrypted database to the target server?

    Wednesday, May 7, 2014 6:32 PM
  • Hello,

    What you're kind of alluding to is an EKM which can be used with SQL Server. I haven't personally worked with any, but I know they aren't cheap.

    EKM Overview: http://technet.microsoft.com/en-us/library/bb895340.aspx

    EKM with TDE: http://technet.microsoft.com/en-us/library/cc645957.aspx


    Sean Gallardy | Blog | Twitter

    Wednesday, May 7, 2014 6:49 PM
  • I think you're right... I think the act of creating a certificate from a file basically "imports" all the needed certificates, thereby allowing SQL Server to do everything without needing external verification. It looks like EKM is the way you create the connection between an external CSP and a SQL cert.

    I've worked with nCipher in a previous life for file level encryption, however that seems overkill of what we're trying to do.  All I want to do is create a cert that locked to our server hardware.

    Does any know if there's a CSP that uses the TPM inside the server that's compatible with SQL's EKM services?

    Wednesday, May 7, 2014 7:21 PM