When you are dealing with Database Master Key and provide encryption password, you have to give strong password there. It uses Windows Complexity Password policy if exist any. In you won’t provide password according to your Windows Complexity Password policy, it won’t accept it and you will greeted with following error.
Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
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.
–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
–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
–restoring DMK from file whenever needed
DECRYPTIONBYPASSWORD=‘$qlhub’–used for decrypt the DMK restored from file
ENCRYPTIONBYPASSWORD=‘$qlhub.com’–this password will be used to encrypt DMK after it gets loaded into the DB
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.
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.
Though Encryption and Decryption process is resource intensive, it is become necessary in some cases. If you look back in SQL Server 2000 and 7.0 days, you didn’t have any in-built mechanism and had to depend on 3rd party tools. But from SQL Server 2005, Microsoft started providing in-built support for encryption and decryption. Let us look in details about what is it? How does it work?
Since this is pretty big topic, it is not desirable to have it in one article so I will upload it in part. Before we move further in topic, let us find out Architecture (Hierarchy) of Encryption and Decryption mechanism in SQL Server 2008. Following image will give you an idea about that.
SQL Server 2008 encryption model inherits Windows Crypto API to encrypt and decrypt data in your database and supports layered approach. At the second level of encryption, there will be SMK (Service Master Key) . You can find one SMK (Service Master Key) per instance. It used to get generated by its own when it needs to encrypt any other key. Well as I just told you that each server instance can have only one SMK but every database in your instance can have separate DMK (Database Master Key) which is encrypted by SMK.
At the bottom level of Encryption, you can find Certificates, Asymmetric key and Symmetric key. Detailed article with example about each of these are going to come soon. Keep Reading!!!!