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.