Sqlserver007

I have been recently working on an Sql Server Encryption Project. It includes getting the data from a source and importing them into the Datawarehouse via the ETL(SSIS in this case) and then Encrypting it. I decided to blog on Encryption to share my experience.

a. When you Encrypt the Data in SSIS make sure you Encrypt in Batches(i.e dont encrypt the same data again).
b. The datatypes should be valid for the Encrypted Content and should have enough space to store it(I used nvarchar(max))

Below is a small Example of the Encryption Process:

Encrypting the Database

use EncryptionTest

--Create a Master Key by Encryption
create master key Encryption by password = 'Sql5erver'

--Create the Certificate
create certificate testcert with Subject = 'Testing the Symmetric Encryption'

--Create the Symmetric Key with Algorithm and Encrypt it with the certificate
create Symmetric Key testkey with algorithm =  AES_256 encryption by certificate testcert;

Sample Tables and Data

create Database Encryptiontest
go

create table Source
(
	UserID int
	,[Name] varchar(100)
	,Password varchar(max)
)
go

insert into Source
select 1,'Vivek','Sqlserver007'
union
select 2,'Jermey','MungingData'
union
select 3,'Marcus','SirMarcus'
union
select 4,'Cesare','Berlusconi'
go

create table EncryptedData
(
	userID int
	,[Name] varchar(100)
	,EncryptedPassword nvarchar(max)
	,Logid int
)

go

Procedure for Encryption

The Detailed Syntax for EncryptbyKey is given in this MSDN article

create procedure EncryptData
(
	@Logid int
)

as

Open Symmetric Key Testkey Decryption by Certificate TestCert

update EncryptedData
set EncryptedPassword = ENCRYPTBYKEY(Key_guid('testkey'),EncryptedPassword) where Logid = @Logid

Now Coming to the SSIS Task. I have used the Logid as the variable which controls the records to be encrypted in every flow(i.e the Log id changes for every iteration of the dataflow task). Once the records are transferred to the Destination table EncryptedData the procedure Encryptdata @Logid is executed.

Variables and the DataSourcetask

DataFlow task and Derived Column

How to Decrypt the Data?

Once the Data is encrypted the table is displayed as follows

For Decryption the End user should have the following permissions
Grant Control on Certificate
Grant View Definition on Symmetric Key

Following is the way to Decrypt. You need to open the Symmetric key to Decrypt it.

Open Symmetric Key TestKey Decryption by Certificate TestCert

select UserID,[name],CAST(DecryptByKey(EncryptedPassword) as nvarchar) as Password from
EncryptedData
 

The following are the DMV’s(Dynamic Management Views) that can be used for Encryption

--Checking the Symmetric keys
select * From sys.symmetric_keys

--Checking the certificates
select  * from sys.certificates

--Checking for any openkeys
select * From sys.openkeys

There are some good articles on Encryption that may be useful to you in future.

Encryption HierArchy
http://blogs.technet.com/keithcombs/archive/2005/11/24/sql-server-2005-data-encryption.aspx