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
Advertisements

8 Responses to “Database master Key (Encryption – Decryption in SQL Server 2008 Part 3)”

  1. Anonymous Says:

    at last, your practical example is really cool, wanted to do the same. –H. Mahajan.

  2. Anonymous Says:

    at last, your practical example is really cool, wanted to do the same. –H. Mahajan.

  3. Pinal Dave Says:

    Good Work Ritesh for sure. A perfect working example.

  4. Pinal Dave Says:

    Good Work Ritesh for sure. A perfect working example.

  5. Linda Goo Says:

    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

  6. Linda Goo Says:

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: