Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: Column Encryption in SQL Server

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  3. #3
    Join Date
    May 2009
    Posts
    17

    Trigger code

    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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    May 2009
    Posts
    17

    Column Encryption in SQL Server

    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

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Somasundaram.Balu
    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.

  7. #7
    Join Date
    May 2009
    Posts
    17

    Column Encryption in SQL Server

    Hi,

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

    Thanks

  8. #8
    Join Date
    Jul 2009
    Posts
    9
    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.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by benzfire
    I'd rather opt for transparent database encryption solutions such as Oracle's TDE but SQLServer doesnt have that
    Yes it does:
    Understanding Transparent Data Encryption (TDE)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •