SQL Tips by Namwar Rizvi

May 13, 2007

Secure Your Data: Simple solution to Encrypt and Decrypt values in SQL Server 2005

Filed under: Encryption,New Features,Query,Security,SQL Server 2005,tips,TSQL — namwar @ 3:57 PM

Saving the sensitive data in database always have the concerns of data privacy. I have seen some database designs where database architects saves clear text passwords in tables which is very very dangerous.
People have different reasons to justify this approach but in my opinion, whatever you do…there is always a chance to access your database table for a dedicated hacker. Once he or she is able to read the password column then the whole security collapse……So the simple rules for security is:

– Never ever save clear text passwords
– Divide the encryption mechanism in different layers accessible by different roles so even if hacker is able to break a database layer, he or she will still have half information to decode so you are still safe.
– Never trust a single person for all of your security. Always try to assigns roles, responsible for security, to different people.

As a simple solution for above mentioned issue, here is a basic SQL Server 2005 implementation. A person having sysadmin role even can not decode these values without having the actual passphrase or encryption key which will be provided by Business Layer through another encrypted configuration file.

–Create a temporary table to hold encrypted data for our example
Declare @m_testTable Table
encryptedText varbinary(8000)

—Encrypt sample data and insert it into above defined table
Insert into @m_testTable (encryptedText)
Select EncryptbyPassPhrase(‘myPassword’,‘Text to be encrypted’)

—Display the encrypted value
Select encryptedText as EncryptedValue from @m_testTable

–Decrypt the above encrypted value and display it
Select Cast(DecryptbyPassPhrase(‘myPassword’,encryptedText) as varchar(8000)) OriginalText from @m_testTable


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: