Blog

Implementation of Log Shipping for TDE (Transparent Data Encryption) Encrypted Database In SQL Server

posted Apr 17, 2015, 2:55 PM by Admin Home   [ updated Apr 25, 2015, 8:54 AM ]

(Still working on it...)

I have searched online for a step by step instruction on log shipping for TED encrypted SQL Server database, but could not find one. Here I put together what I have done on a project. Any comments or questions are welcome. 

1. Create file shares on primary and secondary database for log shipping. 

2. Service Account Configuration 
(1) Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions. 
(2) Add the same account to the share permissions as well. 
(3) Add the permissions on the log destination folder on the secondary server. 
(4) Use the SQL Server Configuration Manager on the secondary instance to configure the SQL Server Agent to use the account that you just created: 




(5) Change the SQL Server Agent from using Local System in the "Log on as:" configuration to the service account you are configuring: 



(6) You will need to restart SQL Server Agent after changing its log on account: 




3. Make sure database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases 

SELECT name, recovery_model_desc FROM sys.databases 
WHERE name = '[DBName]' 

USE [master] 
GO 
ALTER DATABASE [DBName] 
SET RECOVERY FULL WITH NO_WAIT 
GO 

4. Primary database backup 
   --full database backup 
   --backup transaction log 

BACKUP LOG [DBName] TO DISK = N'F:\backup\name.trn' 
GO 

   --after the backup, shrink the log file 

USE master 
GO 
ALTER DATABASE [DBName] 
SET RECOVERY SIMPLE WITH NO_WAIT 
GO 

use [DBName] 
go 
DBCC SHRINKFILE (DB_log, 1) 
GO 

ALTER DATABASE [DBName] 
SET RECOVERY FULL WITH NO_WAIT 
GO 

  --After switching back to full recovery model, make a full backup of the database. 

DECLARE @MyFileName varchar(50) 
SELECT @MyFileName = (SELECT 'F:\\backup\DB_' + convert(varchar(50),GetDate(),112) + '.bak') 
BACKUP DATABASE [DBName] TO DISK=@MyFileName 

5. Backup and restore database keys: 

  --Drop master key and certificates in primary (optional)  

ALTER DATABASE [DBName] SET ENCRYPTION OFF --> user database. 
DROP DATABASE ENCRYPTION KEY --> user database.
DROP CERTIFICATE [CertName] --> master database
DROP MASTER KEY --> master database.

  --Recreate master key and certificates in primary (optional)  

USE [master]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$word1234' 
GO
  -- Create the server certificate. 

          select * from sys.certificates;

CREATE CERTIFICATE [CertName] WITH SUBJECT = 'Certificate to encrypt backup';
GO

  -- Backing up Database Master Key (DMK)
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@$word1234'
BACKUP MASTER KEY TO FILE = N'C:\PrimLogShipping\DMK.key'
ENCRYPTION BY PASSWORD = 'P@$word1234'
GO

  -- Backing up Certificate
BACKUP CERTIFICATE [CertName] TO FILE = N'C:\PrimLogShipping\cert_LS.cer' WITH PRIVATE KEY (FILE = N'C:\PrimLogShipping\[CertName].key' ,
ENCRYPTION BY PASSWORD = 'P@$word1234')
GO

  --Restore the master key on the Secondary Server
USE [master]
GO
RESTORE MASTER KEY FROM FILE = '\\SQLLOGSEC-TEST\SecLogShipping\DMK.key' DECRYPTION BY PASSWORD = 'P@$word1234'
ENCRYPTION BY PASSWORD = 'P@$word1234';
GO
open master key decryption by password = 'P@$word1234'
alter master key add encryption by service master key
  
  --Restore the certificate with master key password on the Secondary Server
select * from sys.certificates;
create certificate [CertName] from file='\\SQLLOGSEC-TEST\SecLogShipping\[CertName].cer' with private key (file='\\SQLLOGSEC-TEST\SecLogShipping\cert_LS.key',
decryption by password='P@$word1234');


6. Log shipping configuration:




1-1 of 1

Comments