encryptbypassphrase:decrypt or read values without passphrase

Question

encryptbypassphrase:decrypt or read values without passphrase

IT researcher

SSCertifiable

Points: 7600
More actions
April 2, 2013 at 5:25 am

#291832

Just interested to know some details about encryption. If i have admin access to the sql server then i can access anything in it,even databases which are encrypted using master key can be decypted easily.
But How i can decrypt the columns of a table without knowing the passphrase which are encrypted using ENCRYPTBYPASSPHRASE if i have admin access to sql server? If possible then how?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

as far as i know, it's not possible; without the pass phrase you'd have to try a dictionary attack on decrypting the values, and see if any if the encrypted items decrypt successfully.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

e4d4 SSCertifiable Points: 5777 More actions · Answer 2

winmansoft (4/2/2013)
Just interested to know some details about encryption. If i have admin access to the sql server then i can access anything in it,even databases which are encrypted using master key can be decypted easily.
But How i can decrypt the columns of a table without knowing the passphrase which are encrypted using ENCRYPTBYPASSPHRASE if i have admin access to sql server? If possible then how?

Certificates that protect a symtertic keys can also be protected by a password and not by the DMK. In both cases a key or ENCRYPTBYPASSPHRASE you must call the statement directly without use any SP, etc.

IT researcher SSCertifiable Points: 7600 More actions · Answer 3

e4d4 (4/2/2013)
winmansoft (4/2/2013)
Just interested to know some details about encryption. If i have admin access to the sql server then i can access anything in it,even databases which are encrypted using master key can be decypted easily.
But How i can decrypt the columns of a table without knowing the passphrase which are encrypted using ENCRYPTBYPASSPHRASE if i have admin access to sql server? If possible then how?
Certificates that protect a symtertic keys can also be protected by a password and not by the DMK. In both cases a key or ENCRYPTBYPASSPHRASE you must call the statement directly without use any SP, etc.

If a user has admin permission then he can alter the symmetric key using alter SYMMETRIC KEY command and change password. So he can decrypt the data then easily. Now my question is how a user with admin permission can view or decrypt the data which are encrypted using ENCRYPTBYPASSPHRASE??Is there any method you aware of?

Lowell SSC Guru Points: 323518 More actions · Answer 4

that's one of the advantages/disadvantages of Encrypt by PassPhrase:

you can prevent the DBA from getting to the data if he doesn't have the passphrase. By The Same Token, if you lose the PassPhrase, The DBA cannot recover it for you.

Now if a procedure exists that was using EncryptByPassPhrase, the DBA could view the definition of the procedure and recover the password that way, the same as if someone were to read the application code.

This is the same issue you might see if the encryption is handled inside an application; the dba can only store the encrypted values.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

winmansoft (4/2/2013)...
If a user has admin permission then he can alter the symmetric key using alter SYMMETRIC KEY command and change password. So he can decrypt the data then easily.....

No, he can't as long as he don't know the password. To alter a symmetric key/certificates protected only by password you must provide a valid password.