Database master Key (Encryption – Decryption in SQL Server 2008 Part 3)

After writing general summary about this topic and Service Master Key this is time the time to give something about Database Master Key (DMK). Actually every database in SQL Server 2008 instance can have one DMK which used to encrypt and decrypt Asymmetric Key and Certificate Key. I will describe what Asymmetric Key is and what Certificate key is in later article as the scope of this article is DMK.
Well, after reading short description above, you must have came to know that DMK is somehow MUST to go further in native encryption and decryption of SQL Server 2008. Let us now look at some small practical snippets about DMK.
Use AdventureWorks
GO


–create database master key, it is good to kepp password with it
–Password use windows password complexiti policy, if there is any.
–when you create DMK with password, it uses triple Data Encryption Standard to protect it
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’
GO


–for backing up DMK, follow the script given here,
–it will backing up DMK in adv.dmk file to D drive
–with password ‘$qlhub’
–don’t forget to remember password as it will be need
–while restoring DMK from file in crisis situation
BACKUP MASTER KEY TO FILE = ‘D:\adv.DMK’
ENCRYPTION BY PASSWORD=‘$qlhub’
GO


–restoring DMK from file whenever needed
RESTORE MASTER KEY FROM FILE = ‘D:\adv.DMK’
DECRYPTION BY PASSWORD = ‘$qlhub’ –used for decrypt the DMK restored from file
ENCRYPTION BY PASSWORD=‘$qlhub.com’ –this password will be used to encrypt DMK after it gets loaded into the DB
GO
By default, when you generate the Database Master Key (DMK), it is encrypted by SMK (Service Master Key) so that anybody with sysAdmin role can decrypt your DMK, this could be a security thread in some environment so you have to turn this feature off by following command.
–altering DMK
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;



Now, let me share you one of my practical example I have used so many times. Generally while developing the project, I used to create DMK in development environment, when it is needed to put the work in live environment, I used to take backup of DMK from development server, put a .DMK file to live server, restore that .DMK file in live server and execute following commands so that certificates I created on my development DB works well on my live server too.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘$qlhub’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY



Hope this will be helpful to you.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

5 thoughts on “Database master Key (Encryption – Decryption in SQL Server 2008 Part 3)”

  1. Ritesh,
    I am just learning sql server 2008 encrypt/decrypt password in order to convert from Oracle package that encrypts/decrypts password.
    Your very elegant explanation is very appreciated and I need to study it thoroughly for our Los Angles County mileage application.
    Linda
    lgoo@isd.lacounty.gov

Comments are closed.