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.

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 :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

notice - there is no password here.

and then we encrypt our tables by :

UPDATE tbl1
 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)
GO

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:

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

Would he still be able to access the data?

share|improve this question

2 Answers 2

up vote 8 down vote accepted
OPEN SYMMETRIC KEY MySymetricKey DECRYPTION`  
 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

share|improve this answer
    
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 Sep 26 '12 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. –  Mark Storey-Smith Sep 26 '12 at 23:00
    
@Remus can you please have a look in my new question ( very related) dba.stackexchange.com/questions/29287/… –  Royi Namir Nov 26 '12 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/

share|improve this answer

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.