Sql-server – Sharing keys and certificates between SQL Server 2014 server for encrypted backup and restoration

backupencryptionsql server 2014

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.

Best Answer

These links are a good place to start

https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

https://msdn.microsoft.com/en-us/library/ff773063.aspx

To summarize: On EACH Sql Server Instance on which you want to enable TDE:

1). In "master', create the database master key to encrypt the certificate that will be used for TDE

USE [master];
GO 
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'StrongPassw0rd!';
GO

2). Create the certificate we're going to use for TDE. Make sure you include the EXPIRY_DATE. The default is one year from creation date and even though TDE is technically unaffected by an expired certificate, it's still annoying to see the message 'The certificate you just installed has expired' when creating this certificate on another server.

CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Cert for Test',
EXPIRY_DATE = '99991231';;

3). Back up the certificate and its private key to a REALLY safe place. Remember the password! Losing this information will prevent you from being able to restore a TDE database,

BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'to
  );
GO

4). Switch over to the database you want to apply TDE to and create the Database Encryption Key so you'll be able to encrypt the database.

USE [DatabaseWithTDE];
GO 

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

5). Reconnect to master and turn on TDE for the user database

USE [master];
GO 

ALTER DATABASE [DatabaseWithTDE]
  SET ENCRYPTION ON;
GO

  1. Monitor the progress of encryption

    SELECT db_name(database_id) AS DatabaseName
,CASE 
    WHEN encryption_state = 0
        THEN 'No Database Encryption'
    WHEN encryption_state = 1
        THEN 'Unencrypted'
    WHEN encryption_state = 2
        THEN 'Encryption In Progress'
    WHEN encryption_state = 3
        THEN 'Encrypted'
    WHEN encryption_state = 4
        THEN 'Key Change In Progress'
    WHEN encryption_state = 5
        THEN 'Decryption In Progress'
    WHEN encryption_state = 6
        THEN 'Protection Changes In Progress'
    END AS EncryptionState
,percent_complete FROM sys.dm_database_encryption_keys

At this point, you have TDE on your chosen Sql Server instances and database.

For you to be able to restore databases from any other instance, you'll have to 'import' the certificates you created and 'exported' (Step 3 above) on all of the other instances.

So, let's say you're on SERVER1

Note that, when we originally created the certificates on each instance, they were all called TDECert. When 'importing' the certificates from the OTHER instances, you'll need to give them a unique name (FromServer2, FromServer3, etc.) - although, I'd shy away from using the actual server or instance name as part of the certificate name as that could change in the future as newer servers replace older ones and the certificates get moved around.

--Create the certificate from SERVER2.  
CREATE CERTIFICATE TDECert_FromServer2
--The directory where you saved the certificate for SERVER2
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER3.  
CREATE CERTIFICATE TDECert_FromServer3
--The directory where you saved the certificate for SERVER3
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER4.  
CREATE CERTIFICATE TDECert_FromServer4
--The directory where you saved the certificate for SERVER4
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER5.  
CREATE CERTIFICATE TDECert_FromServer5
--The directory where you saved the certificate for SERVER5
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER6.  
CREATE CERTIFICATE TDECert_FromServer6
--The directory where you saved the certificate for SERVER6
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

Go into each Sql Server instance and repeat the above 'importing' of the certificates that exist on OTHER instances. So, for SERVER2, you'd 'import' certificates from SERVER1, SERVER3, SERVER4, SERVER5 and SERVER6

At this point, you 'should' be able to restore any TDE database from the above example to any Sql Server instance in the above example.

Also, if, during a restore, you DO get the message about 'thumbprint doesn't match', you can go into EACH Sql Server instance and SELECT * FROM sys.certificates and look for the 'thumbprint' column to figure out which certificate you're missing.

Related Question