In a recent question, What does the Service Master Key protect?, it was suggested that the service master key protects, among other things, data encrypted by ENCRYPTPASSPHRASE()
. Is this true?
SQL Server Encryption – Is Data Encrypted by ENCRYPTBYPASSPHRASE() Protected by Service Master Key?
encryptionsql server
Related Question
- Sql-server – When do I need to backup the Service Master Key
- Sql-server – Stored procedure encryption and master service master key, SQL Server 2008
- Sql-server – What does the Service Master Key protect
- Sql-server – SQL Server 2012 Service Master Key Regenerate
- SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key
- SQL Server – How to Check Database Master Key Encryption Validity?
- SQL Server – Are Database Master Keys Protected with the Same Password Identical?
Best Answer
No, this is not true, and there is an easy proof. On one server, create a database, and store some data using
ENCRYPTBYPASSPHRASE()
:Now, backup the database:
Now, on a different server in a different domain or wherever you feel comfortable believing that there are no hidden attachments to some service master key, restore the database:
Finally, retrieve the data from the restored copy, and observe that you can still decrypt it:
Results should be:
So, this means that if a user gets your data and knows the pass phrase (e.g. if you've stored it somewhere in the same database), they can decrypt all of the data without ever worrying about keys or anything else. You might infer from this that
ENCRYPTBYPASSPHRASE()
is not a very safe way to store passwords, unless you never store the pass phrase a user entered, or unless you use other tools and methods on top of this (scope creep).As a side note, I will confess that I have absolutely no idea how
ENCRYPTBYPASSPHRASE()
actually works internally, other than the fact that it uses Triple DES (3DES) encryption. It is non-deterministic - so it might use something likeNEWID()
orRAND()
internally - your guess is as good as mine. I do know that if you run the following, you will get a different binary value each time, and if you take any of the individual output values and ranDECRYPTBYPASSPHRASE()
against that value, you'd still end up back with the original:In fact, you can try this on your own system. Here is one of the binary values I generated, and if you reverse the process, you should get
turkey
too: