Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.
Now let us look small T-SQL practice for this.
–create database master key before creating Asymmetric key
–if it is not already exist
–creaet asymmetric key with
–let us encrypt and decrypt data with Asymmetric key
DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi’–, this is first Assymetric test, created by Ritesh Shah’
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.