Re: Encrypting Data using SQL Server 2005

This is a multi-part message in MIME format.Hello Greg,

GL> So if I understand you correctly the encrypted data can not be decrypted without the appropriate Service Master Key, even if you have the correct symmetric key password. Meaning you can't move a dataase backup of the encrypted data from one server to another and decrypt it using the only the symmetric key. Is this true?

It was certainly the understanding I had from reading BOL and the testing I did. I couldn't get your backup example to work and wondered if there wasn't maybe so vodoo getting done during the restore process so a did a dettach/attach insead (attachment #1.)


GL> Now so I'm wondering why I can move a database that has encrypted data from one server to another by just doing a database backup and restore and then issuing the open symmetric key using the password from the target server, like so.

There's a note in BOL that gave me a different understanding of this:

"When a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE_DES encryption algorithm is used. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm."

This was added in December 2006. So when you sign a symmetric key with a password, it looks like it just internalizes the key under 3DES and makes it transportable. That sucks because now its way easier to brute force attack that key. UGH!

Even more annoyingly, the same behavior seems to apply to symmetic keys at are encrypted by asymmetric keys where that key is encrypted by a password. See attachment #2.

Attachment: ex1.sql
Description: Binary data

Attachment: ex2.sql
Description: Binary data

Relevant Pages

  • Re: Need your recommendations for TCP Server/Client design
    ... Client sends back the *time *received from server together with the ... Of course all communication must be ciphered. ... which is where you use a public key exchange to wrap a symmetric key ...
    (comp.lang.ruby)
  • SQL 2000 Standard Edition : Log Shipping copy problem..
    ... database backup has been transferred and restored to the ... standby server and log shipping is set up, ... and then set up log shipping from scratck while ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • Re: Backup across the Network
    ... Can I do a database backup onto a file on another server? ... You Should start SQL server using Domain user who got access to remote ... > Can I do a database backup onto a file on another server? ...
    (microsoft.public.sqlserver.server)
  • Re: Using db files on another server
    ... need to connect these files to working server. ... How, exactly, did you take this 'file-copy database backup'? ...
    (comp.databases.oracle.server)
  • Encrypting Data using SQL Server 2005
    ... If you encrypt some data using a symmetric key with a password. ... that the database master key is not used at all to encrypt the data. ... server, and retrain the password for the symmetric key from the old server. ...
    (microsoft.public.sqlserver.security)