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

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

image

 

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

 

image

 

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: