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.

3 comments:

Anonymous said...

I'm the sort of guy who passions to try innovative stuff. Right now I'm constructing my own photovoltaic panels. I am making it all alone without the help of my staff. I am using the net as the only way to acheive this. I ran across a very awesome site that explains how to create pv panels and wind generators. The site explains all the steps needed for solar panel construction.

I am not sure bout how precise the data given there is. If some people over here who have experience with these things can have a look and give your feedback in the page it will be grand and I'd really value it, cause I truly lav [URL=http://solar-panel-construction.com]solar panel construction[/URL].

Thanks for reading this. U people are the best.

Anonymous said...

Amiable brief and this post helped me alot in my college assignement. Say thank you you on your information.

Anonymous said...

Easily I acquiesce in but I contemplate the brief should have more info then it has.