Encryption in SQL Server (Part 4)

Finally, the last part in my Encryption in SQL Server series that I started at the end of 2013! If you have not been following along, please see Part 1, Part 2 and Part 3 before reading this post.

Now that you have data in your databases encrypted and hopefully you have automatic backups setup against these databases, you are probably wondering how you can restore these backups while maintaining the encryption. If you are restoring to the same server and you have not made any changes to the Service Master Key (SMK) it will all work seamlessly.

If however you are restoring the backup or attaching a database with encryption to another server that does not have the same SMK you will need to take a couple of extra steps to get the encrypted data visible and encrypt new data working. Keep in mind that the SMK is the key that is generated at the time the SQL Server instance is installed on the server and you have no control over how that key is created. This SMK is used as part of the whole encryption setup on the server, so you may think that all is lost, but Microsoft has put in place some commands that will still allow you to access the encrypted data and encrypt new data into that restored/attached database.

After restoring/attaching the database to the new instance, run the commands below in order.

Restore Service Master Key (SMK), but only if this instance has no other encryption currently setup on it. If you do have encryption already on this instance, just skip this step and move on to restoring the DMK and modifying it to use the SMK (based on backup sample shown in Part 1):

RESTORE SERVICE MASTER KEY
    FROM FILE = 'C:\localhost_SMK.smk'
    DECRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl';

http://technet.microsoft.com/en-us/library/ms187972(v=sql.105).aspx

 Restore the Database Master Key (DMK):

RESTORE MASTER KEY FROM FILE = 'C:\localhost_AdventureWorks_DMK.dmk'             DECRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'                     ENCRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn' 

http://technet.microsoft.com/en-us/library/ms186336(v=sql.105).aspx

Next we want to alter the DMK to use the SMK for encryption, otherwise the DMK will have to opened every time we want to do any encryption/decryption:

OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn';

ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx

Now we need to create the Asymmetric and Symmetric Keys that we created previously in Part 2, in the same way they were created previously as we could not back up these since they were not created in certificates (if you did decide to create these keys with certificates and you have the back ups, then you can just restore the certificates):

IF NOT EXISTS
 (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
 CREATE ASYMMETRIC KEY AdventureWorks_AsymKey WITH ALGORITHM = RSA_2048;

http://technet.microsoft.com/en-us/library/ms188357(v=sql.105).aspx 

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'AdventureWorks_SymKey')
    CREATE SYMMETRIC KEY AdventureWorks_SymKey
        WITH KEY_SOURCE = 'I82xskoiw820KOW>282kxow',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = 'w,xi292XWE82S92iqoxl*&23'
        ENCRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey;

http://technet.microsoft.com/en-us/library/ms174430(v=sql.105).aspx 

It is important that you use the same KEY_SOURCE and IDENTITY_VALUE when re-creating the Symmetric Key as this and the Asymmetric Key were used to do the initial encryption of the back-up and if these values are not the same the decryption of the previous data will not work.

Once all of these steps are complete you will be able to use the same commands we used in Part 3 to verify that the decryption works correctly:

SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;

http://technet.microsoft.com/en-us/library/ms365420(v=sql.105).aspx

OPEN SYMMETRIC KEY AdventureWorks_SymKey DECRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 

SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact; 

CLOSE SYMMETRIC KEY AdventureWorks_SymKey; 

http://technet.microsoft.com/en-us/library/ms181860(v=sql.105).aspx

With these steps and the previous posts in this series you should be able to take full advantage of encryption in SQL Server and have full control of that encrypted data on any of your servers/instances. With all of the data breaches that are occurring today it is even more important for you to make sure any sensitive information you have in your database is protected and this is a great way to do it in SQL Server without causing a major impact to you existing systems.