3

I have six SQL Server 2014 servers. I am not using TDE, Transparent Data Encryption, on any server.

I would like to be able to take an encrypted backup of a database on ANY server and restore that backup to ANY OTHER server.

What actions do I need to take on each server with the service master key, master database key, and certificate? I would like to use the minimum number of keys, certificates, and backup files for these keys and certificates.

What actions do I need to take if I add an additional SQL Server 2014 to the group?

Thank you all. Server Fault and Stack Overflow have helped me many times in the past.

John Lofgren
  • 31

1 Answers1

0

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key. The SMK is encrypted by using the local computer key using the Windows Data Protection API (DPAPI).

The DPAPI uses a key that is derived from the Windows credentials of the SQL Server service account and the computer's credentials. The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials.

The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. It can also be used to encrypt data, but loker it has length limitations that make it less practical for data than using a symmetric key.

When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the SMK. It is stored in both the database where it is used and in the master system database.

The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

or visit https://msdn.microsoft.com/en-us/library/bb964742.aspx

Spammer
  • 1