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.

4 comments:

  1. Hello,

    I want to know that weather below scenario is possible or not?

    I am encrypting my data on one machine using encryption steps that are define above.
    After that i am transferring my data on other sql server on other machine using bulk copy command. Over there i again create encryption and decryption key using same password. And i try to decrypt the data.

    I am not able to decrypt the data that come using bulk copy and encrypted over other server, how ever on same server i am able to do it easily..

    Is there any way to do it?

    Thanks

    ReplyDelete
  2. Nice post. Does this Encrypt/Decrypt require any code or are they options in CTP of the services?

    ReplyDelete
  3. I have a doubt in the below line..

    DECRYPTION by Password=N'Haresh';

    What does N stands for before 'Haresh'??

    Also,
    1)DES
    2)RC2
    3)RC4
    4)DESC
    5)AES_128
    6)AES_192
    7)AES_256

    Only these seven are the algos??

    What is the default algo used in ms sql 2005 and 08?



    Third, I ve created a user login page using asp.net (used default asp controls for creating signup page).

    I 'd used the db aspnet_db. It had passwords encrypted in it. The encryptions must be in some XXX algo. How would I decrypt it? Is that more secured? I dont think and I wont believe if you say me, 'it is impossible in decrypt'..

    W8ing for ur reply.. .:) Pl reply me @ mathes . btech [at] gmail . com

    ReplyDelete
  4. MathesWaran,

    N prefix refer unicode data, see blow link:

    http://www.sqlhub.com/2009/07/deal-with-indian-regional-language-in.html

    for more details about encryption & decryption, have a look at following article:

    http://www.sqlhub.com/2009/10/backup-database-with-encrypted-data-and.html

    ReplyDelete

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author