Understanding MS SQL Server encryption and backups

Question

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?

Answer 1

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

Answer 2

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/