MS SQL 2005 SERVER - Data Encryption

[ggordon]

Hi.

I need some desperate help from someone who know about data encryption in MS SQL 2005 Server.

I saw:

http://www.microsoft.com/sql/editions/express/default.mspx

And .. I was curious if I can use this and if this free version will allow me to encrypt data.

Here's my current scenario. I am using a software application that helps to automatically create online forms, applications, surveys, etc. online. The application will allow me to send an email notifying someone that a form has been completed and that they need to look in the application - under reports - to view the data that was collected in the form. (I also have SSL on the server.)

Once the data is stored in our current MS SQL 2000 server database, it is currently not encrypted.

So, I am thinking of moving to MS SQL 2005 (free version if possible) to use since 2005 supposidely has encryption built in. But I'm not sure if it's built in .. as part of the free version.

If it is, then that part of my question would be resolved.

Part 2 of my question is .. if the free version will encrypt the data on the SQL server, how difficult is it for me to see the data in a decrypted manner.

Meaning .. in this application, as I said, it generates reports, within the application. I can click on a link that will retrieve the data and display it for me on a page.

Therefore, I am looking to see if and how I can get the information to be encrypted so that the actual information would be encrypted and display encrypted until the user maybe enters a password to decrypt the information.

So, is it possible to somehow have the information stored in an encrypted format (as it's collected and stored in the database) ... and then would it be able to be decrypted (while within the online form application) to a user who knows the password.

Please contact me back if you feel you can help me with this.

Thanks,
Gary

 

[mrdenny]

Yes SQL 2005 Express includes data encryption.

To encrypt the data you would need to modify the way the app works that loads and displays the data to encrypt the data on the way it, and decrypt it on the way back out.

If you wanted to display encrypted data on the web page until a key was entered you would need to do your encryption at the UI level, not the database level.

When the data is encrypted in the database it's stored an binary data in a varbinary data type.

This FAQ faq962-5964 has some sample code for using the SQL 2005 database encryption.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog