Moving Encrypted data between Server/Database

I came across the situation where one of my old colleagues has used encryption/decryption feature of sql server 2005/2008 and a problem has been reported by our client that they are unable to view the encrypted data on live server. I found that the keys are not same on the staging and production server. After brainstorming I concluded few things.

Encryption and Decryption is depends on four keys.
1. Server Master Key (SMK)
2. Database Master Key(DMK)
3. Certificate.
4. Symmetric Key.

Before explaining you the solution of the problem, I will brief you little bit about these keys.

1. Service Master Key (SMK): The service master key is the root of the sql server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. There can be only one such key for a sql server.

2. Database Master Key (DMK): There can be only one such key per database. The DMK is encrypted by password and by default an addition encryption by SMK.A DMK is used to protect database level secret such as certificate and keys. The purpose of encryption by SMK is to allow server to be able to internally decrypt the DMK without providing password.

You can confirm whether your DMK can be decrypted by SMK by firing the following query.

select name,is_master_key_encrypted_by_server from sys.databases where lower(name) like 'dbName'

If the value of is_master_key_encrypted_by_server is 1, means DMK can be decrypted by SMK.

If this feature is not desire (Because every sysadmin would have access on the DMK and data can be decrypt without providing password), the SMK encryption can be remove by using the following queries.

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Unlike DMK you can’t drop the SMK. Because the SMK is the key that by default encrypts the database master keys, which in turn by default encrypt the other keys. It’s the one of most important piece of information in the server. I would defiantly recommend that we should always take the backup of this Key.

Symmetric Key: This is the key which is used by sender and receiver to encrypt and decrypt the data.

In order to resolve this issue we have to re-generate the same keys on the production server. The following are the steps

1. Backup the master key and certificate on staging server by using the following script.

BACKUP MASTER KEY TO FILE = 'c:\dbbck\master.bck'

ENCRYPTION BY PASSWORD = 'gHt56$8abc'

BACKUP CERTIFICATE EncryptCert TO FILE = 'c:\dbbck\EncryptCert.cer'

WITH PRIVATE KEY ( FILE = 'c:\dbbck\EncryptCert.pvk' ,ENCRYPTION BY PASSWORD = N'gHt56$8abc'

);

2. Restore the master key on production server.

RESTORE MASTER KEY FROM FILE = 'c:\dbbck\master.bck'

DECRYPTION BY PASSWORD = 'gHt56$8abc'

ENCRYPTION BY PASSWORD = 'gHt56$8abc'

Note. If the production database and staging database is hosted on different server and if the database master key (DbMK) needs a service master key (SMK) encryption, you need to regenerate this encryption. Note that this encryption is made by default when you create the DbMK, but it may be intentionally dropped, if you want tighter control of access to the encrypted data. Anyway, if you did have such SMK encryption for the DbMK, the steps to regenerate it are the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'gHt56$8abc'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

CLOSE MASTER KEY

3. Restore the certificate

CREATE CERTIFICATE GskEncryptCert

FROM FILE = 'c:\dbbck\GskEncryptCert.cer'

4. There is no option to restore the symmetric key. In order to create a clone able symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

• The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
• The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it.

CREATE SYMMETRIC KEY Imagekey

WITH KEY_SOURCE = 'I am implementing encryption',

IDENTITY_VALUE = 'encryption identity',

ALGORITHM = Triple_DES

ENCRYPTION BY CERTIFICATE EncryptCert;

Thats it for today.

Encryption in SQL Server using symmeteric key

Today I learnt a very exciting feature encryption mechanism of SQL server 2005/2008.

SQL Server provides the following method to encrypt your data…

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric key
• ENCRYPTION by asymmetric key
• ENCRYPTION by certificate

I learnt using Symmetric keys so I will explain you that.

To achieve this we will create database, database master key, certificate and symeteric key.

Script for create database.

-- First Create a DATABASE

CREATE DATABASE EncryptSample ON PRIMARY

(NAME=N'EncryptSample' ,FILENAME=N'C:\EncryptSample.mdf')

LOG ON

(NAME=N'EncryptSample_LOG' ,FILENAME=N'C:\EncryptSample.ldf')

Using this database we will create database master key, certificate and symeteric key.

Use EncryptSample

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'gHt56$8abc'

Go

CREATE CERTIFICATE EncryptCert

WITH SUBJECT = 'EncryptCert'

Go

CREATE SYMMETRIC KEY Imagekey

WITH KEY_SOURCE = 'I am implementing encryption',

IDENTITY_VALUE = 'encryption identity',

ALGORITHM = Triple_DES

ENCRYPTION BY CERTIFICATE EncryptCert;

Now create a sample table and see the result.

USE EncryptSample

GO

CREATE TABLE TestTable (Id INT, [Rank] VARCHAR(50))

GO

INSERT INTO TestTable (Id,[Rank])

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 4,'Fourth'

UNION ALL

SELECT 5,'Fifth'

GO

Check the content of the TestTable

SELECT *
FROM TestTable
GO









Let’s add a new column of type varbinary to original table, which will store the encrypted value of [Rank] Column

ALTER TABLE TestTable

ADD EncryptRank VARBINARY(256)

Let’s update the new column with encrypted data created by certificate and symeteric key.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'gHt56$8abc'

OPEN SYMMETRIC KEY Imagekey DECRYPTION

BY CERTIFICATE EncryptCert

UPDATE TestTable

SET EncryptRank = ENCRYPTBYKEY(KEY_GUID('ImageKey'),[Rank])

GO

Close Master key

Check the content of the first table

SELECT *

FROM TestTable

Fetch the encrypted data.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'gHt56$8abc'

OPEN SYMMETRIC KEY Imagekey DECRYPTION

BY CERTIFICATE EncryptCert

SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptRank)) AS DecryptSecondCol

FROM TestTable

Close master key