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