Tuesday, September 16, 2008

Encrypt/Decrypt Data with SQL Server 2005 : Security with SQL Server 2005

Now a days security is very much concern for websites. We can use many security options. Like use an SSL, encrypt/decrypt with C# code and another option is to use the encryption/decryption in SQL Server 2005.

There is Great Functionality in SQL Server 2005 Which Comes Handy to make your web site Secure.I will Give You Some Magic Code to Encrypt and decrypt data in SQL server.To Encrypt/Decrypt in SQL Server you need One Key(Secured Key), which we can use. Here is the syntax for creating a key to Encrypt/Decrypt data in SQL Server.

Create Symmetric Key SecuredKey

With ALGORITHM=DESX

ENCRYPTION BY PASSWORD = 'Haresh';

Here “Haresh” is Password and “Secured Key “is Key Name To process data key generated above should be open first to Encrypt/Decrypt in SQL Server.

Open Symmetric Key SecuredKey

DECRYPTION by Password=N'Haresh';

After Opening key you can perform Encryption or decryption in SQL Server

Below In The Complete Code For Above Operations.

Declare @Encrypted varbinary(max)

Declare @Decryptrd varchar(100)

Open Symmetric Key SecuredKey

DECRYPTION by Password=N'Haresh';

set @Encrypted= EncryptByKey (Key_GUID('SecuredKey'),'Haresh Dhameliyua')

set @Decryptrd =DecryptByKey (@Encrypted)

Print @Encrypted

Print @Decryptrd

CLOSE SYMMETRIC KEY SecuredKey;

Please let me know if you have further questions regarding Encrypt/Decrypt in SQL Server.