0

On Pinal Dave's blog he gives a simple tutorial for creating a database master key (protected with a password), a certificate and a symmetric key:

CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SQLAuthority';
CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert;

He then shows the encrypted data...

enter image description here

...and states that

hackers will not be able to make use of it even if they somehow manage to reach the data.

Now I assume that the database master key is stored within the respective database? If so, if a hacker downloads the entire database, or gains access to the server, if the DMK is protected via a password, can't the hacker just use the same code Pinal Dave does to decrypt it? e.g.

OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert;
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable;

If so, what is the correct way to mimic a database on a second server via password encryption, without affecting any service master key settings?

Cœur
  • 37,241
  • 25
  • 195
  • 267
EvilDr
  • 8,943
  • 14
  • 73
  • 133

1 Answers1

1

Not exactly. Automatic opening of a database master key works only if the service master key matches the one from the server where the database has been deployed originally. If the encrypted copy of DMK does not match it, you have to provide the original password.

Here is a related thread with enough links to satisfy your curiosity: Restoring SQL Server Database - Master Key Not Opening

Community
  • 1
  • 1
Roger Wolf
  • 7,307
  • 2
  • 24
  • 33