Understanding of database master key in SQL Server

Question

Let's say I have a production server and I have a database master key ##MS_DatabaseMasterKey## (master_key1) on it. I created a certificate c1, which is encrypted by master_key1 by default. Then I backed up c1 with the following statement.

BACKUP CERTIFICATE c1 TO FILE = 'c:\c1.crt'
WITH PRIVATE KEY
(
 FILE = 'c:\c1_private_key.key',
 ENCRYPTION BY PASSWORD = 'c1_private_key_password'
);

After that, I copied the three c1 backup related files to a dev server. On the dev server, I created a new database master key. It's still called ##MS_DatabaseMasterKey##, but it's a newly created one (let's call it master_key2). Now I restore c1 on the dev server with:

CREATE CERTIFICATE c1
FROM FILE = 'c:\c1.crt'
WITH PRIVATE KEY
(
 FILE = 'c:\c1_private_key.key',
 DECRYPTION BY PASSWORD = 'c1_private_key_password'
);

Question:

• After I restore c1 on the dev server, is it encrypted by master_key2? It seems I haven't touch master_key2 here.
• When I restore c1 on the dev server, can I change its name to like c2 (CREATE CERTIFICATE c2 ...)? If I do this, can the TDE encrypted backups on the production server be restored on the dev server?

Answer 1

I created a certificate c1, which is encrypted by master_key1 by default. Then I backed up c1 with the following statement. ENCRYPTION BY PASSWORD = 'c1_private_key_password'

At this point the file c1.crt is not encrypted and the file c1_private_key.key is encrypted by PASSWORD = 'c1_private_key_password'

After that, I copied the three c1 backup related files to a dev server. Now I restore c1 on the dev server with:

At this point the certificate c1 exists in whatever database this was restored to which is by default encrypted by the database master key for that database.

After I restore c1 on the dev server, is it encrypted by master_key2? It seems I haven't touch master_key2 here.

Correct.

When I restore c1 on the dev server, can I change its name to like c2 (CREATE CERTIFICATE c2 ...)?

If you want.

If I do this, can the TDE encrypted backups on the production server be restored on the dev server?

TDE DEK keys are protected by a server cert (in your case but can be protected other ways) in the master database. If a cert with that thumbprint does not exist or is not able to be opened, TDE will not function for that database. In your case, it shouldn't have any issues because the thumbprint is the same and the cert can be accessed to decrypt the DEK. Yes, it can be restored. I would keep the cert names the same, though, or better yet, use different certs since now your production cert is on your dev server. TDE, is for data at rest so it really only matters if you expect someone to steal your backup or your drive, other than that it isn't protecting anything.

• Fajela Tajkiya: Thanks Sean for the answer. "or better yet, use different certs since now your production cert is on your dev server", how can I have a different cert on the dev server? I need to restore production db backups to dev server, which I suppose the dev server should have the same certification. Right?
• Sean Gallardy: Yes, it would need the prod cert to decrypt it if taking straight from prod. If you wanted to be more secure (which I have no idea if it is required, I just pointed it out) going to an intermediate server to restore the database and then rotate the cert via ALTER command could be used, then moved to dev or load the cert in dev, rotate, then drop the cert. learn.microsoft.com/en-us/sql/t-sql/statements/…