Step by Step Configure TDE Transparent Data Encryption Enabled Database on Log shipping

Step by Step Configure TDE Transparent Data Encryption  Enabled Database on Log shipping 

SQL Server Transparent Data Encryption

SQL Server Transparent Data Encryption (TDE), is an encryption mechanism, available in the Enterprise edition of SQL Server 2008 and later. It is used to protect the database physical files, rather than protecting the data itself. This includes the database data, log, backup and snapshot files as mentioned previously. Using the SQL Server Transparent Data Encryption, unauthorized users will not be able to access the database’s data, by preventing them from attaching or restoring the database files to another SQL instance. Also it will be protected inside the backup media if it is stolen.

The SQL TDE encryption, as the name indicates, transparent to the applications that connects to the database, as the encryption is at the page level. The data is encrypted while it is at rest on the disk and decrypted during the read process on its way to the memory. An authorized user will be able to access the encrypted database content, without being aware that the database is encrypted or any action or extra code required from his side.

more info Ref:

https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/

Demo:

Primary: User-PC\MSSQL2012Source

Secondary:1 User-PC\MSSQL2012DESTSQL

Secondary 2: User-PC\SQL2012CONVTEST

Log shipping Database: Testdbgrowth

Primary: User-PC\MSSQL2012Source

Secondary:1 User-PC\MSSQL2012DESTSQL

Here you can see the table info @ Primary

Create and Backup Master KEY @ Primary

We should need to create Master Key if not exists and Backup Master Key

See the Master Key info 

Create Database Encryption Key and Enable Encryption of Database

See the Certificate here

Backup Full Database and Transaction Log backup @ primary

Destination (Secondary)

see the master key info @ secondary

Restore Master Key of Source(Primary) to Destination (Secondary) and Open Master Key @ Secondary and Create Certificate which create on Primary

We should need to restore Master key which we backedup on primary and Open Master Key and 

Create certificate which we created and Backed up on Primary 

Note: 

Be aware  difference below to avoid confusion while create certificate

Create certificate  is using DECRYPTION BY PASSWORD 

Backup Certificate is using ENCRYPTION BY PASSWORD

RESTORE Database Backup @ Secondary:

 give overwrite option

We should need to give new path while restore on destination and

We should need to give WITH restoring option (WITH NORECOVERY) To allow more file like transaction log backup to restore again

Now we are going to restore Transaction log backup

We should need to mention as WITH Standby option to read users

Transaction Log backup Restored Successfully

Configure Log shipping @ Primary

Click Enable this as primary option and click Backup settings and give path(network and local)

Now give Add button to add secondary

connect option connect secondary and select third option as we already backup/restore database from primary to secondary

Copy  files to give Copy location (network folder path) 

Restore option to give Standby mode and give 1 minute delay to ensure proper process on copy/restore

Now Log shipping configured successfully

Backup job info @ Primary

Copy and Restore job info @ Secondary

you can see table reflected on secondary

+

Run backup job on primary

Run copy job on secondary

Run restore job @ secondary

To see the reports on log shipping

see the report shows copy and restore file same

we can query the Backup file info @ primary as see below

To see Backup file info @ primary

SELECT distinct 

    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn

FROM

    msdb..backupset AS s INNER JOIN

    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN

    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN

    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id

WHERE

    (s.database_name = 'ALL_Test_TDE_LogShipping')

ORDER BY

    s.backup_finish_date DESC;

Compare of both as see below

Script 

To Create Master key @Primary

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='Ws@2012';

To Open Master key @Primary

USE master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';

USE master

GO

To see Master key @Primary

use master

SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date  

from sys.symmetric_keys

To Backup Master key @Primary

use master

BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'

    ENCRYPTION BY PASSWORD = 'Ws@2012';

GO

To Create Certificate  @Primary

use master

CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'

To Backup Certificate @Primary

use master

BACKUP CERTIFICATE ATTL_Logshipping_TDE 

TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'

WITH PRIVATE KEY 

(

    FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',

    ENCRYPTION BY PASSWORD = 'Ws@2012'

);

GO

To see Master key info  @Primary

USE master

SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date  

FROM sys.certificates where name = 'ATTL_Logshipping_TDE'

GO

To Create Database Encryption @Primary

USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE

GO

To Enable Encryption on Database @Primary

USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here

ALTER DATABASE [ALL_Test_TDE_LogShipping]

SET ENCRYPTION ON

GO

To see Encryption status @Primary

use master

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 1 THEN 'Unencrypted'

WHEN 3 THEN 'Encrypted'

END

FROM sys.dm_database_encryption_keys

To Backup Full Backup  @Primary

use [ALL_Test_TDE_LogShipping]

BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak' 

WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup', 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

To Backup Log Backup  @Primary

use [ALL_Test_TDE_LogShipping]

BACKUP LOG [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn' 

WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Log Backup', 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Destination:

To see Symmetric Key file info @Secondary

USE master

GO

SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date  

from sys.symmetric_keys

To Restore Master key @Secondary

USE master

RESTORE MASTER KEY

    FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'

    DECRYPTION BY PASSWORD = 'Ws@2012'

    ENCRYPTION BY PASSWORD = 'Ws@2012';

GO

To Open Master key @Secondary

USE master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';

To Create Certificate key @Secondary which we created and backedup on Primary

USE master

CREATE CERTIFICATE ATTL_Logshipping_TDE

FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'    

WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',   

DECRYPTION BY PASSWORD = 'Ws@2012');

GO

To see Backup file info @ primary

USE master

SELECT distinct 

    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn

FROM

    msdb..backupset AS s INNER JOIN

    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN

    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN

    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id

WHERE

    (s.database_name = 'ALL_Test_TDE_LogShipping')

ORDER BY

    s.backup_finish_date DESC;