Post reply

Encrypt SQL tables

  • August 28, 2011 at 3:00 pm

    #256329

    What is the best method to encrypt some table columns in SQL 2008 ?

  • August 28, 2011 at 3:10 pm

    #1374338

    Hello,

    Depending on what you are trying to achieve but to selectively encrypt some columns you need to make them a varbinary data type and use the EncryptByKey and DecryptByKey functions.

    If this is going to cause a problem for an existing application, you can use TDE (Transparent Data Encryption) which protects the whole database, including TempDB and your backups. This doesn't however stop your data being visible using a SELECT statement.

    cheers.

  • August 28, 2011 at 4:18 pm

    #1374343

    BrentMc (8/28/2011)

    use the EncryptByKey and DecryptByKey functions.

    you may also use certificates (DecryptByCert and EncryptByCert) and asymmetric keys (DecryptByAsymKey and EncryptByAsymKey)

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 28, 2011 at 5:31 pm

    #1374354

    Depends. What are you trying to protect against?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 29, 2011 at 3:39 pm

    #1374755

    And will you have a need to index those columns?

  • August 31, 2011 at 8:30 am

    #1375517

    remember also that with TDA you will lose any compression (I love sql 2008 bk compression!!!)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply