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
  • John - Have a look at https://blogs.msdn.microsoft.com/mvpawardprogram/2014/06/02/sql-server-2014-backup-encryption/ which seems to be pretty thorough step-by-step, mentions about **`Restoring a database from an encrypted backup file on another SQL Server 2014 instance`** and don't forget about http://dba.stackexchange.com/ too. – Pimp Juice IT Sep 04 '16 at 00:59
  • PIMP_JUICE_IT, Thank you, I've moved the question to the DBA community. I've read the blog post you referenced and I've been able to do an encrypted backup on one server and restore on another. – John Lofgren Sep 04 '16 at 03:46
  • John - That's great news. I would absolutely LOVE to see an answer on your question even if it's your own and I'd upvote it +1... I gave you the pointer and you figured out the answer so let me know once you add it as this may be helpful to others looking for such an answer in the future of this configuration type. Keep me posted what you do with it. – Pimp Juice IT Sep 04 '16 at 03:49

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

john smith
  • 1
  • 1
  • John Smith, Thank you. SQL Server 2014 service master keys and database master keys are protected by using the AES-256 algorithm. – John Lofgren Sep 06 '16 at 16:37