So I was asking in chat about how secure SQL Server backup files are.

I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data.

So lets look at this scenario :


notice - there is no password here.

and then we encrypt our tables by :

 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)

Now lets say a hacker got my bak file. All he has to do in order to view the data (in his own computer & sql-server) is:

       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM tbl1

Would he still be able to access the data?

2 Answers 2

 BY CERTIFICATE MyCertificate`

notice - there is no password here.

Yes, there is. You opened the certificate MyCertificate somehow, didn't you? There could be several cases, but any of them ends up with a password:

  • you opened the certificate using a password (obviously, there is a password in this case)
  • the certificate is encrypted with the database master key and you opened the database master key using the DBMK password -> that's the password
  • the certificate is encrypted with the database master key and the DBMK is encrypted with the service master key. The service master key was in turn opened using the service account DPAPI storage key, which is encrypted with the account password -> and that is the password

In the last key there seem to be no password, but there is one: the service account password. The backup, when restored on a different host, it will lack the service master key encryption of the database master key and this will make the data inaccessible.

See Encryption Hiearchy

  • HI , yes there is a password but in a previous stage. when I created by :CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master key password!' GO - I did created it with a password. but the strange thing is that when i open it - i dont need the password this is excatly the code youve pasted. This is the thing which I dont understand. If im a hacker - i dont care about the password used to create it. all I need to do is OPEN SYMMETRIC KEY MySymetricKey DECRYPTION BY CERTIFICATE MyCertificate` and there is NO password here. please correct me
    – Royi Namir
    Commented Sep 26, 2012 at 22:29
  • 3
    @RoyiNamir See Remus's last bullet point. You aren't specifying the password but the certificate is protected, ultimately in this case by the service account password. Commented Sep 26, 2012 at 23:00
  • @Remus can you please have a look in my new question ( very related) dba.stackexchange.com/questions/29287/…
    – Royi Namir
    Commented Nov 26, 2012 at 10:12

The short answer is no as they would need the database master key and it's password.

Here is a good overview of certificate encryption: http://www.mssqltips.com/sqlservertip/1319/sql-server-2005-encryption-certificates-overview/

And one for master keys: http://www.mssqltips.com/sqlservertip/1312/managing-sql-server-2005-master-keys-for-encryption/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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