Encrypt and Decrypt in SQL Server: Part 1

Introduction

In this article we will discuss Service Master Key, backup, restore and alter Service Master Key.



Service Master Key

The Service Master Key is created at the time of installation of SQL Server. There is only one Service Master Key per SQL instance. The Windows data protection API uses the SQL Server service account credentials to encrypt the Service Master Key. And the Service Master Key secures all other keys on the server.

Backup Service Master Key

Since there can be only one Service Master Key per instance, it is advisable to take a backup of this key.

BACKUP SERVICE MASTER KEY TO FILE= 'D:\SQLServer2008R2.SMK'
ENCRYPTION BY PASSWORD = '@k$h@yPatel'
GO

If you execute the SQL statement above, the following error might be thrown:

Cannot write into file "D:\SQLServer2008R2.SMK". Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

It is a SQL Server service account permission issue. Rather than granting permission to the account, we can take a backup from the default path. So for that remove the full path and just pass in the file name.

BACKUP SERVICE MASTER KEY TO FILE= 'SQLServer2008R2.SMK'
ENCRYPTION BY PASSWORD = '@k$h@yPatel'
GO

Now you can find the "SQLServer2008R2.SMK" file on the following path:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLXPR2008\MSSQL\DATA

Note: Take a backup of the Service Master Key and store it in a secure location immediately after installing SQL Server.

Restore Service Master Key

We can restore this file whenever it is required. The syntax is as follows:

RESTORE SERVICE MASTER KEY FROM FILE= 'SQLServer2008R2.SMK'
DECRYPTION BY PASSWORD = '@k$h@yPatel'
GO

Note: Password should be the password previously used to encrypt the backup.

Alter Service Master Key

ALTER SERVICE MASTER KEY REGENERATE;
GO

Note: It is advisable to regenerate the SMK whenever we make any changes in the service account.

Conclusion

In this article we have discussed Service Master Key. In the next article we will discuss Database Master Key.