Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I think the SMK only ensures database master keys are protected if it has encryption by SMK applied. The crypto functions then use or don't use the DMK. –  billinkc Aug 21 '13 at 2:38
    
@billinkc right but the implication from the other question was that master keys are required and/or such encrypted data was protected by the key, by default and with no other work. –  Aaron Bertrand Aug 21 '13 at 2:56

1 Answer 1

No, this is not true, and there is an easy proof. On one server, create a database, and store some data using ENCRYPTBYPASSPHRASE():

CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.mort(floob INT, splunge VARBINARY(64));
GO
INSERT dbo.mort VALUES
  (1, ENCRYPTBYPASSPHRASE(N'kerplunk', N'secret')),
  (2, ENCRYPTBYPASSPHRASE(N'kerplunk', N'hidden'));

Now, backup the database:

BACKUP DATABASE blat TO DISK = 'C:\wherever\blat.bak' WITH INIT;

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:

RESTORE DATABASE blat FROM DISK = 'C:\wherever\blat.bak'
  WITH REPLACE, RECOVERY, 
  MOVE 'blat' TO 'C:\somepath\blat.mdf',
  MOVE 'blat' TO 'C:\somepath\blat.ldf';

Finally, retrieve the data from the restored copy, and observe that you can still decrypt it:

USE blat;
GO
SELECT floob, prying_eyes = CONVERT(NVARCHAR(4000),
  DECRYPTBYPASSPHRASE(N'kerplunk', splunge))
FROM dbo.mort;

Results should be:

floob   prying_eyes
-----   -----------
1       secret
2       hidden

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 like NEWID() or RAND() 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 ran DECRYPTBYPASSPHRASE() against that value, you'd still end up back with the original:

SELECT ENCRYPTBYPASSPHRASE(N'banana', N'turkey');
GO 5

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:

SELECT CONVERT(NVARCHAR(4000), DECRYPTBYPASSPHRASE(N'banana', 
  0x010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5))
share|improve this answer
1  
Encrypt/Decrypt by passphrase is precisely that. Only the passphrase is necessary to decrypt the key. Adding a "salt" value to the data to be encrypted (and/or the passphrase), and never storing the actual passphrase used during encryption guarantees for practical purposes that the encrypted data cannot be decrypted without massive scale computing power. –  Max Vernon Aug 21 '13 at 6:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.