locked
Encryption SQL Server- Why do we need to backup the Service Master Key and Database master Key RRS feed

  • Question

  •  
     
    I am working on a project where we would need to encrypt some data store it in SQL server and decrypt it once we produce the reports. I am working on deciding the backup/ security hierachy for the such that any data loss can be avoided if there is some data base crash. The approcah i have take:
     
    1. Backup the SMK.
    2. Generate a DMK(DB Master Key), protect it by a password in addition to SMK.
    3. Create a certificate, which would be encrypted by the DMK.
    4. Create a symetric key using the certificate.
    5. Backup the certificate.
     
    I would have assumed once am done with the Step 1,2,5 i could regenrate the Symetric key in case of a data loss.
     
    However only now i came across a post as below, which gave me an idea around the that SQL server can generate the same keys on defferent servers,  so am wondering do i really need to backup the SMK/DMK/Certificates at all as i can still generate the Symetric key which would be used to encrypt my data.
     
     
    this been the case i need not do an overhead of doing step 1,2,5 and just keep the scripts as given on the above links.
     
    Any suggestions?

    Abhinav
    Wednesday, June 1, 2011 2:53 PM

Answers

  •  What i wanted to know was, why should i be worried about backing up DMK and SMK if i can follow the above steps and keep just the backup of the symmetrickeybackup.txt file and prevent data loss, as most on posts suggests.


    Abhinav

    Hi Abhinay,

    As i pointed above, if you use the SYMMETRIC KEY for encrypting data on a user-defined database, you don't need to back up the Service Master Key and Database Master Key. The information associated with the SYMMETRIC KEY has been stored on this user-defined database. Once you back up the database, you can restore it on another server and the SYMMETRIC KEY is strill available. One thing you may have to do while the database restored on another server is open the SYMMETRIC KEY, and then it can be used as the same.

    Best Regards,
    Stephanie Lv

    • Marked as answer by AB82 Friday, June 3, 2011 1:19 PM
    Friday, June 3, 2011 9:41 AM

All replies

  • service master key (SMK) is generated on and for a SQL Server instance, and a database master key (DMK) is used for a database i.e. they are unique to a service/database.

    Without SMK and DMK, a server/database cannot encrypt/decrypt data using Symmetric Keys so if you haven't backed them up and your server/database crashes, you will lose all your data.

    This article discusses Symmetric Keys (not Service Master or Database Master keys) and how to create the same key for two servers so that they can Encrypt/Decrypt data shared between them.  It does not discuss how to use the same SMK or DMK on different servers.

    Have a read of http://technet.microsoft.com/en-us/library/bb964742.aspx

     


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Thursday, June 2, 2011 2:21 AM
  • Hi Abhinav,

    The link you posted is about creating identical Symmetric Keys on two servers. The identical Symmetric Keys are used to encryption and decryption data in separate databases or on separate servers.  To create identical Symmetric Keys, they need to use the same KEY_SOURCE, ALGORITHM, and IDENTITY_VALUE key options while creating. Certainly, the certificate which is protected by the master key should be created first. In other word, servers with identical Symmetric keys are not equal to they own the same Service Master Keys and Database Master Keys. Service Master Key and Database Master Key are distinct for every SQL Server instance.

    >>I am working on deciding the backup/ security hierachy for the such that any data loss can be avoided if there is some data base crash.

    For the purpose of avoiding data lose while the database enabling TDE crashes, you should back up the certificate and the private key associated with the certificate immediately or you will not be able to pen the database.

    For more information, please refer to: Understanding Transparent Data Encryption (TDE).

    Best Regards,
    Stephanie Lv

    Thursday, June 2, 2011 5:25 AM
  • Thanks for the reply, i too have the same understading around the DMK and SMK.

    But i think essentialy they are just containers and DMK encrypts the Symetric keys, and inturn its encryted by SMK.

    What i want to ask was why do i need the same set of DMK and SMK to restore, if they are jus containers to encrypt the DML and SMK.

    Essentialy i would use the EncryptbyKey and DecryptbYkey Function which just use the GUID for Symetric key.

    In addition to that my understading is :

    a) DMK is used to protect the Symetric Key, and it just encrypts the key which has been generated and nothing else:

    d) Similarly DMK is encrypted by the SMK such that its not easliy understandle to users.

    c) Now the data which is encrypted is, been done by the Symetric Keys, and therotically if i just use the EncryptbyKey and DecryptByKey funciton which takes the KeyGUID as a parameter, so i would have assume dif i can safeguard the Symetric key alonw i would not need to backup the SMK and DMK.

    d) As per the post the Encrypted and Decrypted data would be same on both the servers though i couldnt replicate the results as I get the data as

    Server1: --0x00D1CCE9771AE7554D479F7B93A4561101000000C7815EE71C439B7485004A68F605935D078F6C141DE1D4F0B9BAE646B7601183

    Server2: --0x00D1CCE9771AE7554D479F7B93A456110100000049303E6B81F3757F0F4BF6B5BD06CD82DF0ABF265A321DDD67F79027694773EB

    These were the 2 values retunred, hald the values are same and last half changes, i think this is because of salted concept in encryption.

    also when i used the Server1 values on Server2 to run the query as below

    OPEN

    SYMMETRIC KEY [key_DataShare]

    DECRYPTION

    BY CERTIFICATE cert_keyProtection;

     

    GO

    SELECT

    CONVERT(varchar(8000), decryptbykey('0x00D1CCE9771AE7554D479F7B93A4561101000000C7815EE71C439B7485004A68F605935D078F6C141DE1D4F0B9BAE646B7601183'

    ));

    GO

     

    It retunred NULL, so i dont understand this call really be achieved.


    can someone please help with these concepts i am getting really confused now :(

     

     


    Abhinav
    Thursday, June 2, 2011 9:22 AM
  • Hi Abhinav,

    The result returns from ENCRYPTBYKEY function is a VARBINARY data type value. You need to remove the Single quotation marks or the value will be treated as a VARCHAR data type value.
    OPEN
    SYMMETRIC KEY [key_DataShare] 
    DECRYPTION BY CERTIFICATE cert_keyProtection; 
    GO
     
    SELECT
    CONVERT(varchar(8000), decryptbykey(0x00D1CCE9771AE7554D479F7B93A4561101000000C7815EE71C439B7485004A68F605935D078F6C141DE1D4F0B9BAE646B7601183));
    GO
    


    Best Regards,
    Stephanie Lv

    Thursday, June 2, 2011 10:46 AM
  • Thanks Stephanie for the reply, yes that works exactly as expected.

    Now back to my 1st question, as clear i can still get the ciphered text back on Serevr2, wihtout actually restoring the same DMK and SMK as SERVER1

    So what i wanted to ask was that why do i need to backup the SMK and DMK for a specific serevr, in case of a database crash, i can just keep a backup of the Symetric key generation scripts(symmetrickeybackup) and not backup anyting else.

    1. I restore the backed up copy of database

    2. drop the exisitng DMK,

    3. Create the Symetric keys again on the restored DB using the  on the same values back using the  symmetrickeybackup file and then again start using the encrypt by key and decrypt by key fucntions.

    Just to add i am not encryoting the whole database, i have to just encrypt a column on a single table, so i think restoration of backups is not a porblem, and it doesnt require the DMK to be the same on another server.

     

    Any thoughts?


    Abhinav


    • Edited by AB82 Thursday, June 2, 2011 11:44 AM Deleted SMK from step2 as SMK dropping is not required
    Thursday, June 2, 2011 11:25 AM
  • Hi Abhinav,

    According to your description, there are two types of keys: DATABASE ENCRYPTION KEY and SYMMETRIC KEY.

    DATABASE ENCRYPTION KEY is used for Transparently encrypting a database. In other word, the whole database is encrypted at the file level. The database encryption key is available only to the system. While creating this type of key, it needs to use the certificate created on the system database master. So, while you back up the database, the certificate is not along with this database, you need to back up the certificate and the private key associated with the certificate. After that, you can restore this database on other server with that certificate.

    SYMMETRIC KEY is used for encrypting data on database. As you mentioned, it can be used to encrypt a column on the table. It also needs a master key and a certificate, which are created on this database, not the master database. So, while you back up this database, the information associated will be along with the backup file. You can restore this database only with backup file and the SYMMETRIC KEY is available on the new database.

    For encrypting a column on a single table, please refer to the article on SQL Server Books Online: How to: Encrypt a Column of Data.


    Best Regards,
    Stephanie Lv

    Friday, June 3, 2011 2:42 AM
  • Sorry but i still couldnt get what you mean, let me try to explain what i want to knwo again:

    I understand the concept of SMK DMK certificates and Symetric keys

     

    though i am using SQL server 2005, and just encrypting a some columns inside  table, and the not the full database.

    The encrypted  tables data can be retrieved without using the DMK or SMK for the original DB with the steps below, i have confirmed that the same symmetric keys can be generated on 2 different servers, using the scripts as per post and scripts above.

    Steps to follow:

    aa              1.        I restore the backed up copy of crashed database on to a new server(server 2).

                      2.       Drop the existing DMK, on the database(Server2)

           3.       Create the Symmetric keys again on the restored DB using the  on the same values back using the 

                      symmetrickeybackup file and then again start using the encrypt by key and decrypt by key functions.

     What i wanted to know was, why should i be worried about backing up DMK and SMK if i can follow the above steps and keep just the backup of the symmetrickeybackup.txt file and prevent data loss, as most on posts suggests.


    Abhinav
    Friday, June 3, 2011 5:36 AM
  •  What i wanted to know was, why should i be worried about backing up DMK and SMK if i can follow the above steps and keep just the backup of the symmetrickeybackup.txt file and prevent data loss, as most on posts suggests.


    Abhinav

    Hi Abhinay,

    As i pointed above, if you use the SYMMETRIC KEY for encrypting data on a user-defined database, you don't need to back up the Service Master Key and Database Master Key. The information associated with the SYMMETRIC KEY has been stored on this user-defined database. Once you back up the database, you can restore it on another server and the SYMMETRIC KEY is strill available. One thing you may have to do while the database restored on another server is open the SYMMETRIC KEY, and then it can be used as the same.

    Best Regards,
    Stephanie Lv

    • Marked as answer by AB82 Friday, June 3, 2011 1:19 PM
    Friday, June 3, 2011 9:41 AM
  •  

    Thanks Stephanie you are a star, so it clarifes the point.

    Just one last thing then I am assuming the DMK and SMK needs to backed up only if

    1. We are using TDE, to encrypt the whole of the USER defined DB and this is required as when we back up the database the backed up copy is also automatically encrypted by the MASTER database key. Only once MASTER DB key is available we can restore the DB, and this would in turn require the SMK and DMK restored.

    Am i correct in saying this?

     


    Abhinav
    Friday, June 3, 2011 1:25 PM
  • Just one last thing then I am assuming the DMK and SMK needs to backed up only if

    1. We are using TDE, to encrypt the whole of the USER defined DB and this is required as when we back up the database the backed up copy is also automatically encrypted by the MASTER database key. Only once MASTER DB key is available we can restore the DB, and this would in turn require the SMK and DMK restored.

    Am i correct in saying this?

     


    Abhinav


    Hi Abhinav,

    If you use TDE to encrypt the whole user-defined database, as pointed above, you may not need to backup the DMK and SMK, the only information you must to backup is the certificate and the private key associated with the certificate on master database, or you will not able to restore the database on a new server. The certificate entrypts the Database Entryption Key in the user-defined database.

    Howerver, the DMK AND SMK should be created on the new server before the restore. Backup the SMK and DMK and restore them to the new server will reduces the overall level of security on these servers.


    Best Regards,
    Stephanie Lv

    Saturday, June 4, 2011 1:35 AM