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