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.
