SQL Server Performance

When to use encryption in SQL Server 2005?

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Sep 25, 2007.

  1. pcsql New Member

    Since it is possible to encrypt data before saving it, what is the benefit of using encryption in SQL Server 2005?
  2. ndinakar Member

    You can either use third party tools to encrypt your data and send encrypted data to the db or you can use db encryption provided by MS.
  3. bradmcgehee New Member

    Some of the benefits of server-side encryption include:
    • Often easier to set up than from the client
    • Potentially better performance
    • More encryption options available, such as certificate-based encryption and different encryption routines
    • Encryption is centrally managed
  4. satya Moderator

  5. dineshasanka Moderator

  6. evilDBA New Member

    Always remember that you pay a performance penalty (sometimes - severe perf penalty!) for the encription.
    So encrypt only what you really need. Dont even think to encrypt everything 'just in case'
  7. pcsql New Member

    The encryption is going to encrypt sensitive data like SSN, credit card #, and other similar data.
    Besides the performance penalty, what are other disadvantages using SQL Server encryption? My understanding is that symmetry key is stored with the database. What happen if the symmetry key is corrupted? Does encryption require using Windows Authentication?
  8. Simpler New Member

    Yes, but don't forget: if you encrypt SSN, then you can't use an index on SSN.
    And I am almost sure that there are many WHERE SSN=@SSN in your application. All such conditions will require FULL TABLE SCAN with a decription of record by record.
    Also, based on what I found, Symmetric keys are much much slower.
    Regards
  9. pcsql New Member

    Thanks. There is no where clause using SSN but there is where clause using credit card #.
  10. Simpler New Member

  11. chrislampley New Member

    If you encrypt a column that is frequently used in WHERE clauses, try this trick. Add a second column that is a one-way hash of the encrypted column's original value. Now, you can do a WHERE hashed_col = hash(var). You can place an index on this column, and now you can use indexes on an encrypted column.
  12. satya Moderator

  13. chrislampley New Member

    Sorry, that is not what I meant. I meant that you can place an index on the hashed column. That way the WHERE clause is sargable. So, you still encrypt the data in the encrypted column, but by adding a hashed column, you are able to perform searching on this encrypted column by using the hashed column using an index on the hashed column..
  14. evilDBA New Member

    But if you store a hash, then you can decrypt SSN just because there are not so many combinations.
    For other data, if you store HASH value, then you can not claim 'I am using AES-256 encryption' for example, because your encryption is much weaker,
  15. satya Moderator

    I haven't seen any such activity in corruption of symmetric keys, anyway refer to http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx blog post (again) in order to accomplish the way out.
    All the posts here says same thing, test your application on the pre-production environment that will replicate like-to-like with live server. That will ensure what kind of implications you might have as it is very hard to say encryption is bad without a PROPER testing.

Share This Page