Data encryption and Decryption in SQL Server 2008

No.of Views1956
Bookmarked0 times
Downloads 
Votes1
By  Chinna Srihari   On  07 Nov 2010 06:11:35
Tag : Sql Servers , How to
In recent past there are many projects around ETL just only Data transfer from the legacy databases. But because of security concerns some of the data needs to encrypted and pushed across the Server. Here I just want to give a brief detail. And just to help the beginners.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In recent past there are many projects around ETL just only Data transfer from the legacy databases. But because of security concerns some of the data needs to encrypted and pushed across the Server. Here I just want to give a brief detail. And just to help the beginners.
Cryptography can be implemented in the SQL Server. This article explains about the encryption and decryption of data in SQL Server 2008. Following are the steps that we need to follow. SQL Server 2008 supports AES_128 or AES_192 or AES_256 or TRIPLE_DES_3KEY encryption algorithms.
Step 1: Create a Master Key
The database level cryptographic feature in SQL Server depends on a database master key. There can be one master key per database and has to be created manually by administrators because it is not created automatically during installation.
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
By default master key is encrypted by using the Triple DES algorithm and a user-supplied password. This default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of ALTER MASTER KEY. A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.
 
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '23987hxJ#KL95234Chinna';
 
PASSWORD à Specifies a password with which to encrypt or decrypt the database master key. Password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
Example:  Before creating Master Key Check if it already created or not; use the following quesry.
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101
-- PASSWORD should meet the SQL Server BOX OS
-- Password policy
CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';
 
Step 2: Create Certificate
A certificate is a database level securable that follows the X.509 standard.
CREATE CERTIFICATE can also load a certificate from a file or assembly. This statement can also generate a key pair and create a self-signed certificate. Private keys generated by SQL Server are 1024 bits long. Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 3,456 bits. The length of an imported private key must be an integer multiple of 64 bits.
CREATE CERTIFICATE Cert_Password
   ENCRYPTION BY PASSWORD = 'Password!2'
   WITH SUBJECT = 'Password protection',
   EXPIRY_DATE = '12/31/2099';
Execute and check certificate creation
SELECT * FROM sys.certificates;
Step 3: Create Symmetric Key
The symmetric key must be encrypted by using at least one of the following or multiple keys. Those are by Certificate or password or symmetric key or asymmetric key or PROVIDER.
When a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm. There many more options to create Symmantic key, but here I am showing one simple approch
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_Password;
Check the database how it is been created
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 256
Step 4: Encrypt Data
Now it is time to encrypt some test data and see how it works. To encrypt the data first we have to open the Symmantic key and the use the Certificate to encrypt the data. Make sure to close the symmantic key.
If certificate is created with password then we’ll need to use password to open the certificate.
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
 
INSERT INTO Security (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'Chinna Srihari'))
CLOSE SYMMETRIC KEY Sym_password;
 
Step 5: Decrypt Data
Now we see how to decrypt the same data back to text. To decrypt the data first we have to open the Symmantic key and the use the Certificate to encrypt the data. Make sure to close the symmantic key.
If certificate is created with password then we’ll need to use password to open the certificate.
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM Security
CLOSE SYMMETRIC KEY Sym_password;

hope this helps 

Download Sample Project

Download source files -60 kb 

Thanks,

 
Sign Up to vote for this article
 
About Author
 
Chinna Srihari
Occupation-Software Engineer
Company-
Member Type-Junior
Location-India
Joined date-17 Jun 2010
Home Page-
Blog Page-
Having 12 + years of IT experience, 2.5 years as Technical Management experience and 1.5 year of onsite coordinator experience and rest 8+ years of technical activities including Project Leader, Tech Lead, Designer, PQL, and Developer experience in Quality Management System (CMM-level 5, SEI-CMM-level 5).
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</