How to configure Database Mirroring on Transparent Data Encryption (TDE) enabled Database on SQL Server via T-SQL Script

How to configure Database Mirroring on Transparent Data Encryption (TDE) enabled Database on SQL Server via T-SQL Script


Note

Be aware we can not configure via GUI using TDE enabled database on db mirroring

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.

Encrypting any user database using TDE, the TempDB system database will be encrypted. As this database contains temp data from the TDE-Encrypted database

Encrypting a SQL Server database using TDE is a straightforward operation. First a master key should be created. Then you should create a certificate which is protected by that master key. After that a database encryption key (DEK) secured by the certificate is created in order to protect the user database. Finally you need to enable the encryption in your database.

The dependency of the database encryption key that is secured by the certificate which is protected by the master key prevents the database files from being restored or shown outside the current instance without these keys. Also, this encryption protect the backup files from being opened by the text editors to view its content.

If you decide, for any valid reason, to restore the TDE-Encrypted user database to another SQL Server Instance, you need to have a copy of the same master key on that new instance, in addition to the certificate backup files and certificate private key.

SQL Server TDE is a light encryption method that will not affect the queries performance, as the encryption is at the database files level. On the other hand, using the TDE, the database backup files will not take benefit from the backup compression feature completely.

The scenario

After the brief introduction about the SQL Server Transparent Data Encryption (TDE), and in order to simulate creating SQL Server Mirroring site on a database with TDE enabled on it, we will assume the below scenario:

• We have two SQL Server 2012 Enterprise instances.
• The first SQL Server is USER-PC\MSSQL2012SOURCE hosts the TestTDE_DBmirroring database and will act as the Principal server in the mirroring site.
• The second SQL Server is USER-PC\MSSQL2012DESTSQL and will act as the Mirrored server.
• TestTDE_DBmirroring recovery model is FULL.
• SQL Server Transparent Data Encryption will be enabled on the TestTDE_DBmirroring database.
• A SQL Server disaster recovery site will be created using SQL Server mirroring between the USER-PC\MSSQL2012SOURCE and USER-PC\MSSQL2012DESTSQL SQL Servers.

Demo:

Source Server:

Destination Server:

we can see source db server table info

Create/Backup Master Key and Create Certificate on master

We need to create master key and open master key and backup master key

and also create certificate on master @ source server

Backup Certificate on master

Also we need to take backup of certificate as see below

We can see the certificates on source

Create Database Encryption on User Database

We need to create Database Encryption Key on user database

Enable Database Encryption on User Database

We can enable encryption on user database and see as below

Destination server

Create/ open Master Key on maser @ Destination

Now we need to logged into Destination server and create /open master key

Master key location as see below

Restore  Master Key on maser @ Destination

We should need to restore master key on Destination server (It is already created so it gives identical)

Certificate location info on both



On Destination  we need to create Certificate from backup file

now we are going to backup database on source

database backedup successfully

on Destination server we are going to restore database

locate the backup file from drive

it selected

be aware if we are not restore master key and certificate we will receive following error as example

our case we restored master and certificate from source so no issue

now we need to give relocate option and edit file

click overwrite option with restore norecovery

Restored database successfully

we can see restored db info on destination

Trying to Configure Mirroring via GUI(Graphical User Interface):

We can not do but we can try that as we are choose right click db and choose tasks-->mirror

once wizard launched choose configure security

Principal info

Mirror info

success info on configure endpoints

we should  click do not start mirroring

After we click Start Mirroring we got error as see below it is expected before as we can not configure db mirroring via GUI

Configure DB Mirroring via TSQL

Now we are execute Script as see below

on Mirror Server execute below script

ALTER DATABASE [TestTDE_DBmirroring] SET PARTNER = 'TCP://USER-PC.LOCAL:5022'

on Principal Server execute below script

ALTER DATABASE [TestTDE_DBmirroring] SET PARTNER = 'TCP://USER-PC.LOCAL:5023'

that is it DB mirror is  configured

we need to refresh on both servers

we can see on principal server as see below

we can see on Mirror  server as see below

complete script:

Execute on Source server:


Create master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Open master Key

USE master

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

Backedup master Key

use master

BACKUP MASTER KEY TO FILE = 'C:\SQL activity\TDE\Certificate\Master\MasterKeyBack'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO

Create Certificate on Master


use master

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring
WITH 
SUBJECT='TestTDE_DBmirroring_Encrypt_DBmirroring';
GO

Backup  Certificate with encryption by password on Master 

BACKUP CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
TO FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO

see Certificates info  

USE master
GO
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date 
 FROM sys.certificates
where name='TDE_Cert_TestTDE_DBmirroring'

Create Database Encryption Key on User Database

use [TestTDE_DBmirroring]

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_TestTDE_DBmirroring
GO

Enable  Database Encryption on User Database

ALTER DATABASE [TestTDE_DBmirroring]
SET ENCRYPTION ON
GO

To see Database Encryption 

SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END

FROM sys.dm_database_encryption_keys

Drop certificate

DROP CERTIFICATE certificate_name

Execute on Destination server:

Create master Key on Destination master

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Open master Key on Destination master

USE master

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

RESTORE MASTER KEY
    FROM FILE = 'C:\SQL activity\TDE\Certificate\Master\MasterKeyBack'
    DECRYPTION BY PASSWORD = 'Ws@2012'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO

Create Certificate on Destination master

USE MASTER

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
FROM FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    Decryption BY PASSWORD = 'Ws@2012'
);

GO

Create Endpoint witness on master

CREATE ENDPOINT Mirroring  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=WITNESS)  

GO 

To see Witness endpoint info

SELECT @@servername[ServerName],role_desc, state_desc,* FROM sys.database_mirroring_endpoints

Errors and suggestions

1)If you are trying to restore certificate with encryption by password you will get error 

Resolution:

It should be create certificate with decryption by password

2) you should need to create certificate on destination with private key otherwise you will get error

USE MASTER

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
FROM FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    DECRYPTION BY PASSWORD = 'Ws@2012'
);
GO


3) Trying to configure Witness Server but got below error ALTER DATABASE command could not be sent to the remote server instance

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://USER-PC.LOCAL:5025'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

till not solved working on to solve this error

To set up a witness for a database, the database owner assigns a Database Engine instance to the role of witness server. The witness server instance can run on the same computer as the principal or mirror server instance, but this substantially reduces the robustness of automatic failover.

We strongly recommend that the witness reside on a separate computer. A given server can participate in multiple concurrent database mirroring sessions with the same or different partners. A given server can be a partner in some sessions and a witness in other sessions.

The witness is intended exclusively for high-safety mode with automatic failover. Before you set a witness, we strongly recommend that you ensure that the SAFETY property is currently set to FULL.

https://msdn.microsoft.com/en-us/library/ms190430(v=sql.120).aspx




Ref:

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