Encryption in SQL Server 2012–Creating Service Master Key and Database Master Key

 

All Version of SQL Server 2012 including the Express Edition supports Encryption

The first steps to start Encrypting data in SQL Server 2012 are as follows.

 

1. Backup Service Master Key and keep it in a secure location

Use master

BACKUP SERVICE MASTER KEY TO FILE = N'C:\Workspace\MYServiceMasterKey.Key' 
                                       ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

2. Create Database Master Key for Encryption for the databases you want to protect

Use AdventureWorks2012
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

 

3.  Backup the Database Master Key to a secure location

Use AdventureWorks2012
BACKUP MASTER KEY TO FILE = N'C:\Workspace\DatabaseMasterKey.Key' 
    ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

4. Open the Database Master Key and Test it

Use AdventureWorks2012
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';

CLOSE MASTER KEY;

 

If you specify the wrong password to open the database master key, you will be getting the below error

 

To view the current database master key in your database, you can use the following system security catalog view

 

 

Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.