terça-feira, 10 de fevereiro de 2009

Encrypt and Decrypt Data in SQL Server

Well this is my first post, let's see if we can keep this running ehehe.

My first post is about encrypting and decrypting data in a column in SQL.
Since I had a bad time searching this in google so I decided this will be my post.

First we have to create the key so we can use it afterwards

Create symmetric key TheSymKeyName
with algorithm = TRIPLE_DES
encryption by password = 'apasswordyouchoose'

You have other choices for the algorithm but for that googleit i will not waist my time on it.

Then we will need to open the key:

Open symmetric key TheSymKeyName
decryption by password = 'apasswordyouchoose'

You can check if the key got open doing a simple query:


Select * from sys.openkeys

Then it's time for we to encrypt the data:

Select EncryptByKey(key_guid('TheSymKeyName'), 'valuetoencrypt')

Voilá, you have your value encrypted. And finally to decrypt the key, you can simply

Select convert(varchar(50), decryptbykey(0x00191422BC15934AB4D8BB81C175331E01000000855C1A81E18EAA0DAB9F32543AF832941324625F852AB0F6EAF12628D0A53D6C))

And don't forget to close the key using:


CLOSE SYMMETRIC KEY TheSymKeyName

Any doubt's???

1 comentário:

Anónimo disse...

i wanted to ask for you service. but first i wanted you to decrypt this word 4+abmiPsrcu7ba4DWLo0Uw==

i am not familiar with the encryption used with this word. but it would be good if you could work on this. thanks. please contact me asap on my email. rvsystem@ymail.com