Data Encryption using SQL Server 2005

Many times need to encript/decript data from my database. If I want to Encrypt/Decrypt my database using SQL Server2005

I need to do some task.

Step:1
I have to enable database encryption.

Syntax: ALTER DATABASE <<db_name>> SET ENCRYPTION ON

Step:2
I have to create a master key using my own password

Syntax: CREATE MASTER KEY ENCRYPTION BY PASSWORD = <<my_password>>

Step:3
I have to create a certificate

Syntax: CREATE CERTIFICATE <<certificate_name>> WITH SUBJECT = <<subject_name>>

Step: 4
I have to create symmetric key

Syntax: CREATE SYMMETRIC KEY <<key_name>> WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE <<certificate_name>>

Step: 5
I have to Open symmetric key when required insert/update/select operation.

Syntax: OPEN SYMMETRIC KEY <<key_name>> DECRYPTION BY CERTIFICATE <<certificate_name>>

Step:6
I have to use built in function for encrypt data.

Syntax: CREATE TABLE Test(Name varchar(50) not null);

INSERT INTO test VALUES (EncryptByKey(Key_GUID('key_name'), 'habib1'));

SELECT CAST(DecryptByKey(name) AS varchar) FROM test;

Step:7
Close My open symmetric key

Syntax: CLOSE SYMMETRIC KEY <<my_key>>;


There are also many algorithms you can use for sqlserver dataencryption. I use only TRIPLE_DES.