Service Master Key (Encryption – Decryption in SQL Server 2008 Part 2)

You might have read my first article on Encryption-Decryption series that was an introduction only; herewith we are moving one step ahead.
Service Master Key is a base in hierarchy of SQL Server Encryption and Decryption, it directly access Windows Data Protection API. Only one Service Master Key can exist per SQL Server instance. I have already mentioned that Service Master Key (SMK) used to get generated by its own whenever it is needed first time than question may pops up in your mind that what should we need to do if it is generated by its own? Well, interesting question but the answer is also interesting. You need to do some administrative task for SMK as there is a possibility that sometime your SMK key gets corrupt and you need to restore it in your instance. If you don’t have it, you will definitely lose your encrypted data as there will not be any possibility that you can decrypt your data and use it if you don’t have your SMK.
I highly recommend whenever you install new instance of SQL Server 2008, get the copy of your SMK in file at some safe place so that you can restore it whenever you need it.
Now let us see some important administrative commands for managing SMK.
–backing up Service Master Key
–recommended that you take backup of
–your service master key as soon as you install new instance
BACKUP SERVICE MASTER KEY TO FILE= ‘D:\SQL2K8.SMK’
ENCRYPTION BY PASSWORD = ‘$qlhub’
GO
–restoring service master key
–you can use file which we have backed up, whenever you need
RESTORE SERVICE MASTER KEY FROM FILE=‘D:\SQL2K8.SMK’
DECRYPTION BY PASSWORD = ‘$qlhub’
GO
–alterring service master key
–SMK use current service account of SQL Server
–so it is good practise to regenerate SMK whenever you
–make any change in your service account.
 ALTER SERVICE MASTER KEY REGENERATE;
 GO
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 ofhttp://www.SQLHub.com
Advertisements

Encryption – Decryption in SQL Server 2008 – Part 1

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!!!!

 

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