Hi,

I have some queries with regards to column encryptions in SQL Server....

I need to encrypt columns in SQL Server and I have prepared documents to do it and in all online references the procedure is to add a column with varbinary datatype to original table and update the values from the original column(may be varchar or numeric datatype) and drop the original column and rename this column to the actual column name which is fine.

Few clarifications....

1)Is there a direct way to encrypt columns in SQL server ie . Alter table tablename modify column name encrypt with <key name> like in Sybase
2)In Sybase any datatype for ex varchar encrypted will internally store it as varbinary and no need for code changes as most are automatic
3)How point 2 is handled in SQL Server?
4)Lastly I don't want to make application code changes to implement column Encryption in SQL Server as the eventual column will be varbinary so the application when it tries to insert/update or delete an encrypted column which is expected to be varchar? How this is handled internally

Please throw some light on the above

I have only worked with encryption in SQL 2005. They added a number of new features in 2008 including "Transparent Encryption". I am not sure if that feature will help you, though. As far as SQL 2005, though:
1) No. You can only encrypt items with a function (EncryptByAsymKey, EncryptBySymKey). You can intercept the inserts with a trigger that will take the value of a column, and divert it through an encryption function to a varbinary column. It seems a bit complex to me, but it is possible.
3) You will need to specifically add a varbinary column for any encypted data in SQL 2005. Again, this may have changed in SQL 2008.
4) As I mentioned, you can handle inserts and updates with the trigger, and you can handle selects "internally" with a view that will decrypt the data on the way out.

Hi,

Thanks for the reply. Can you send a sample trigger code to me please? I mean the intercept trigger which will capture the value of the column and convert it to varbinary....

So to confirm there will be no application changes required..... Should the trigger code be written for all encrypted tables....?

One more thing is the varbinary column which is being added is it a virtual column something like identity? so that application need not insert values....

So to confirm the steps could be

add column
encrypt values
remove original column
and rename the encryped column to original column
write a trigger to intercept insert/update/delete...

Thanks

To say there would be no application changes whatsoever is a bit strong. You will need to make sure that any insert statements specify a full field list. Otherwise, the second you add a column, all of those insert statements will blow up. Also, in order to decrypt the data, you need to put a view in the way. This means all the select statements are going to have to be altered.

The varbinary column is as real as any other column, and will need to be accounted for in all inserts, and will get carried along with any select * statements. I think you need to look into the SQL 2008 features to see if transparent encryption is what you need.

Hi,

So to finally confirm I would need a trigger and view for each of the encrypted tables.

Final questions and clarifications...

1)I create a insert/update/delete trigger on each of the encrypted tables to convert the encrypted column and insert. Should this trigger be on the table directly as I am a bit confused as many online information shows instead of trigger on view? So in my case the resultant columns would be the same so no changes to application inserts(i am adding a varbinary column and removing the original column and finally renaming the column to the original name. So only the datatype changes here because of encryption.

2)Finally a view on each of the tables encrypted to decrypt the value to show the users. So all 'selects in the application' should be changed to select from the view instead of the table right because the object name changes here.

Please confirm on the above

You haven't explained what kind of authentication scheme you will use to make sure only authorised users have access to the encryption key (private, certificate or public). Otherwise the whole exercise is completely pointless.

Hi,

I will be using a symmetric key and use a certificate.

Thanks

Column level encryption solutions all have huge performance problems due to index corruption, not mentioning tedious 2nd development efforts, schema and db object changes. If I were you, I'd rather opt for transparent database encryption solutions such as Oracle's TDE but SQLServer doesnt have that, so you might take a look at stuffs from Bloombase, Netapp or Brocade, they fit your bill on the file/storage level.

Yes it does:
Understanding Transparent Data Encryption (TDE)