SQL 2005 Symmetric Encryption

  • I am unclear on how encrypting a column makes the data anymore secure than simple restricting its access via permissions. 1. Restrict a single user to open a key to decrypt the contents of a column. 2. Allow only a single user to view that table (or column using a view). How is #1 any more secure than #2? In both cases only a single user (not counting sysadmins) can view the data.

  • to Noeld ..

    About the restore of a db at another server, you can reanable automatic key management for the db using the old dbpassword :

    if not exists ( select MKP.*

         , D.name as DbName

         from sys.master_key_passwords MKP

         inner join sys.credentials C

         on MKP.credential_id = C.credential_id

         inner join sys.database_recovery_status DRS

         on MKP.family_guid = DRS.family_guid

         inner join sys.databases D

         on DRS.database_id = D.Database_id

         Where D.name = 'yourdb'

        &nbsp

     begin

        Print 'Adding dbmasterkey for yourdb';

     -- using the same password that has been used the first time !

     EXEC sp_control_dbmasterkey_password @db_name = N'yourdb',

       @password = N'P@ssword', @action = N'add';

     end

    GO

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA,

    Thanks! I haven't had the time to play enough with the encryption stuff latetly so I do appreciate your comments.


    * Noel

  • http://www.marketwatch.com/News/Story/Story.aspx?guid=%7B5EB7D976-2922-4E99-9F6E-B1F323A884FA%7D&siteid=yhoo

    http://www.privacyrights.org/ar/ChronDataBreaches.htm

    http://www.usa.gov/veteransinfo/

    Stealing passwords isn't the only avenue of attack for hackers.  Data can be retrieved from a stolen hard drive or lap-top with unencrypted data fairly easily.  I used to do it all the time when I needed to recover data from a swapped out hard drive on my PC.  There are also several regulatory requirements for encrypting personal and confidential data "at rest" (HIPAA, SOX, FCRA, FACTA, etc.)  I haven't heard of anyone going to jail over it yet, but there are some pretty hefty fines for non-compliant businesses, plus billions of $ worth of bad publicity.

  • In addition to this nice article, I also enjoyed Laurentiu Cristofor's blog at http://blogs.msdn.com/lcris/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • keassnow (3/20/2006)


    Say your malicious person deletes your symmetric key? You can restore a master key, but I've seen nothing that tells me how to restore a symmetric key.

    You can recreate the key, but if the KEY-GUID isn't identical, nothing encrypted with the old key can be decrypted with the new one. And with adhoc changes to system tables eliminated, your only choice is to restore a backup of the database to a new location, decrypt all the old info with the old key, and reencrypt it using the new key.

    Correct me if I'm wrong. Please!

    According to what I read, the GUID generated will be the same GUID as long as everything else used to create the Symmetric Key is exactly the same. The GUID being generated is not using NewID() or something similar. I imagine they are taking some textual data in the Key and converting it to varbinary and then converting every character to a byte, take 32 of those bytes and voila, you have a GUID!

  • Hi Mike,

    I'd be interested to hear you view on this

    Since the cert is stored in the database, anyone obtaining access to the database could simply take a look in the certs folder and call decryptbycert on sensitive data, spinning throught the cert id's seen there, until one works.

    The only requirement is credentials which have associated VIEW DEFINITION and CONTROL rights to cert in question.

    Effectively, if these credentials are compromised this encryption only protects against simple reading of the sensitive data (assuming your attacker is naive enough not to know about decyptbycert)

    Am I missing something?

    Thanks in advance,

    Matthew Evavns

Viewing 7 posts - 16 through 21 (of 21 total)

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