Prevent client from decrypting data....

Question

Prevent client from decrypting data....

Vicky-854895

Mr or Mrs. 500

Points: 597
More actions
January 5, 2009 at 4:50 am

#129723

Hi,
this is second time, I'm posting topic related encryption.
Well, now I have my database encrypted. I have valuable database and need to store it under my client's sql server i.e. this DB will strore under client premises.
I used certificate and symmetric key to encrypt data. Certificate is encrypted using password and not by database master key. Now if I restore this encrypted backup at client side, will it ensures that client will not able to decrypt the encrypted database?
Basically I works as developer and don't have much server admin knowledge.
So is it possible to prevent the client from accessing symmetric keys and certificates to decrypt the data?
Any help will definitely alleviate.
Thanx...

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

If they are a sysadmin on the SQL Server, I wouldn't bet on it. Sysadmins can do a number of operations that may allow them to decrypt the data, which is why you have to trust your sysadmins, or keep the data onsite. Whose data is it, yours or theirs? If you remove the DMK encryption from the cert, that would probably prevent them from using it since they would have to open it to add a password to it, but I wouldn't allow you to put a DB in my environment that I didn't have the ability to completely recover in an emergency, so you might find a DBA who expects to have the information needed to open/change the cert.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

One other thing to check is if the SMK is being used by the Key or Cert for encryption. A DBA will have access to the SMK for sure and could open anything encrypted by it directly.

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 3

I'm not sure here. The certificate is stored in the database, and with a password, I don't think it has anything to do with the DMK. So anything not encrypted, the client would be able to see. Things that are encrypted technically require the password to be submitted to open the certificate for use. However I'm not sure if a sysadmin can take ownership of the certificate or somehow recover the data.

It's a good question to ask.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Laurentiu Cristofor's blog is one the best sources of information on encryption. Some posts to look at are:

http://blogs.msdn.com/lcris/archive/2007/10/04/sql-server-2005-a-note-about-the-use-of-certificates.aspx

http://blogs.msdn.com/lcris/archive/2008/01/11/sql-server-2005-why-you-should-not-encrypt-data-with-certificates.aspx

http://blogs.msdn.com/lcris/archive/2005/10/14/sql-server-2005-using-symmetric-keys-to-encrypt-data.aspx

The last discusses how to protect the data encrypted by a symmetric key at the bottom, as well as the encryption methods to protect the key itself. The DBMK is a part of the Key encryption hierarchy and can be used to encrypt the keys and certificates in the database. The SMK protects the DBMK so a sysadmin could get to the data through the hierarchy since they have access to the SMK, which could open the DBMK which could open the symmetric key and decrypt the data.