locked
Column Encryption with symetric key - Service Master Key questions RRS feed

  • Question

  • We support SQL 2005 to SQl 2012. We are sending out tsql updates to our clients that will add a new table that will have an encrypted column. I am mostly following the steps in this URL.

    column-level encryption example using symmetric keys/

    The URL basically uses the following

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
    
    CREATE CERTIFICATE Certificate1
    WITH SUBJECT = 'Protect Data';
    
    CREATE SYMMETRIC KEY SymmetricKey1 
     WITH ALGORITHM = AES_128 
     ENCRYPTION BY CERTIFICATE Certificate1;

    So the clients host their own SQL Server and anything is possible. It is possible that they might not have a Service Master Key already setup? If they do not, how can tsql create one?

    They will provide the password used in the Database Master Key. We will not retain it.

    So there is a password on the Service Master key and the Database Master Key. When are the passwords ever used? When you backup a database, do you need the Service Master password to restore? I am confused about when the passwords are used and it backup and recover is changed now that there is a Database Master Key.

    Tuesday, February 10, 2015 10:01 PM

Answers

  • Can you change the DMK password? Not add another one, but change it? How would you do it, and what would be affected?

    No, an "Alter Password" for DMK does not exist.

    If you want to change the password, you add a second password for encryption, and then remove the old one.. That results in the same.

    like so:

    CREATE MASTER KEY
    	ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'
    
    -- Adding a 2nd encryption key
    ALTER MASTER KEY
    	ADD ENCRYPTION BY PASSWORD = 'Pa$$w0rd2'
    
    -- dropping the former key		
    ALTER MASTER KEY
    	DROP ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'

    Nothing besides the key itself is directly affected by that. Anything protected by the DMK just remains the same. Of course you should do a fresh backup of the key.

    Changing a key is not the same as "REGENERATE" - that would do just what it sounds like :)


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Wednesday, February 11, 2015 9:23 PM

All replies

  • Books Online says It is generated automatically the first time it is needed to encrypt another key. Thus, you should not worry whether the site has an SMK or not.

    Yes, those keys and passwords are confusing, not the least how they achieve any real security against an intruder that has gained access to the database through SQL injection.

    When you restore a database on a different server, you will get a problem if that server has a different SMK.

    This topic in Books Online is well worth reading:
    https://msdn.microsoft.com/en-us/library/ms189060.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 10, 2015 10:27 PM
  • I have read all those articles. Mind you, I have just started the project and this is not a comfortable topic for me. I am sure later in the project the articles will make more sense. But for now, I am trying to find out how you could backup a database that uses column level encryption with a database master key, certificate and symetrical key and then restore to another SQL Server.

    Read how to do a backup and restore of the Service Master Key and the Database Master key. But when do you ever use the actual Password that you provided when creating the Database Master key?

    Wednesday, February 11, 2015 12:50 AM
  • I can understand that you feel uncomfortable with the topic - I can't say that I like it either. As I hinted, there is a lot of red tape, and the end I still understand how real security has been achieved.

    I would suggest that you do some hands-on. That is, encrypt a column, backup the database and restore it on another.

    It seems that I can't Books Online working on my machine right now, but I seem to recall that one situation when you need the password for the database master key is exactly when you retore it on another server, since you need to reencrypt it with that machine's Service Master Key.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, February 11, 2015 8:21 AM
  • ...

    Read how to do a backup and restore of the Service Master Key and the Database Master key. But when do you ever use the actual Password that you provided when creating the Database Master key?

    Erland remembers correctly:

    The Service Master Key (SMK) you rarely need.
    Under normal conditions you only need the respective Database Master Key (DMK) in order to restore on a lets say standby server. Then you will need to provide the key file and use your password to re-encrypt the DMK with the now different SMK. If you do not do that you have to use that password each time you want to decrypt something in that database that is protected by the DMK.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Wednesday, February 11, 2015 1:29 PM
  • The SMK does not have a password ... correct?

    Wednesday, February 11, 2015 4:58 PM
  • Can you change the DMK password? Not add another one, but change it? How would you do it, and what would be affected?

    Wednesday, February 11, 2015 5:11 PM
  • The SMK does not have a password ... correct?


    Right. It's encrypted using the Windows data protection API

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Wednesday, February 11, 2015 9:06 PM
  • Can you change the DMK password? Not add another one, but change it? How would you do it, and what would be affected?

    No, an "Alter Password" for DMK does not exist.

    If you want to change the password, you add a second password for encryption, and then remove the old one.. That results in the same.

    like so:

    CREATE MASTER KEY
    	ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'
    
    -- Adding a 2nd encryption key
    ALTER MASTER KEY
    	ADD ENCRYPTION BY PASSWORD = 'Pa$$w0rd2'
    
    -- dropping the former key		
    ALTER MASTER KEY
    	DROP ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'

    Nothing besides the key itself is directly affected by that. Anything protected by the DMK just remains the same. Of course you should do a fresh backup of the key.

    Changing a key is not the same as "REGENERATE" - that would do just what it sounds like :)


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Wednesday, February 11, 2015 9:23 PM