Steps to create a symmetric key in sql server 2005.

1.Drop Master Key –Use this statement if you have any existing master key in the database.

2.Create Master Key Encryption Password = ‘Pawan’ — create master key using create master key command.

3.CREATE CERTIFICATE Pawan WITH SUBJECT =’Pawan’,START_DATE =’11/6/2009′ — Create a certificate with certificate name.

4.CREATE SYMMETRIC KEY Pawan WITH ALGORITHM = TRIPLE_DES, KEY_SOURCE =
‘TB_TableName’, IDENTITY_VALUE = ‘pawan’ ENCRYPTION BY CERTIFICATE
Pawan

5.Now your symmetric key is ready and you can insert the data in your table.

6.DECLARE @v_Password varbinary(300)
OPEN SYMMETRIC KEY Pawan DECRYPTION BY CERTIFICATE Pawan
SET @v_Password = EncryptByKey(Key_GUID(‘Pawan’),’sharlee’)
INSERT INTO dbo.Employee(LoginID, User_Password, User_FName, User_LName, User_EmpCode, User_Email_Address)
VALUES (‘pawan’, @v_Password, ‘Pawan’ ,’Kumar’,11111,’Pawankkmr@hotmail.com’)

Please note that user_password is a varbinary(max) field.Why is this
type of field because we need a datatype that can hold that many
characters.

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements