msSQL2014 AlwaysON transparent data encryption(TDE)

Quick note:

SQL management studio dont allow encrypted database to be added from the GUI. So we have the following work around to have encrypted database in msSQL2014 AlwaysON.

Briefly the procedure is as follows:

Primary node : add master key, create a certificate, backup key and certificate.

Primary node : create a dummy database, create the main database which will be encrypted.

Primary node : encrypt main database, backup the database.

Secondary node: import the key and certificate

Secondary node: restore encrypted database.

Primary node : create AG with the dummy database and both node as asycn, manual failover.

Primary node : add the main encrypted database using SQL command.

Secondary node: add the main database using SQL.

Primary node : change failover and sync mode to auto and sync.

In detail the procedure is described in sequence:

Primary node:

/* create master key*/

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’;

/* create certificate*/
Use Master
GO
CREATE CERTIFICATE cryptdbcert
WITH SUBJECT = ‘crype db certificate’;
GO

/* database encryption */

USE cryptdb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [cryptdbcert]
GO
ALTER DATABASE [cryptdb] SET ENCRYPTION ON
GO

then take a backup of the master key and the certificate. keep the files in a secure place and copy securely to secondary node.( which will be imported into the secondary database)

/* backup master key*/
backup master key
to file = ‘c:cryptdb_master_key.key’
encryption by password = ‘V3RyC0mplicated#K3Y’;
go

/* backup certificate*/
backup certificate prod1cert
to file = ‘c:cryptdbcert.crt’;
go

/* backup the database */

BACKUP DATABASE cryptdb
TO DISK = ‘c:cryptdb-encrypted backup.trn’
With compression,
ENCRYPTION
(
ALGORITHM = AES_128,
SERVER CERTIFICATE = [cryptdbcert]
);
GO

BACKUP LOG cryptdb
TO DISK = ‘c:cryptdb-log.bak’
WITH INIT, STATS ;
GO

After this step create a dummy database which will be used to create availability group.

once AG is created add the encrypted database into the AG by the following SQL:

ALTER AVAILABILITY GROUP tde ADD DATABASE cryptdb

On the secondary node:

Copy the certificate and key that was backup in primary node to secondary node. Then import the certificate by the following( assuming the files are copied in C: drive)

/* import certificate */

CREATE CERTIFICATE cryptdbcert
FROM FILE = ‘C:cryptdbcert.crt’
WITH PRIVATE KEY
( FILE = ‘c:cryptdb_master_key.key’,
DECRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’);
GO

Now import encrypted database using management studio:

<< this is very important “restore with no recovery”

the join cryptdb in secondary node into AG by simply right clicking it.!

Now you can check AG status from the dashboard.

Should you have any query don’t hesitate to ask me.

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 encrypted backup

msSQL2014 offers encrypted backup into local or share or cloud(azure) backup.

Quick notes:

1. Create a database master key
2. Create a certificate
3. select a new media set, certificate and location to take the backup in local/share disk

Detail steps:

1. Create a database master key and certificate using the following SQL

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘C0mplicated#K3Y’;
Use Master
GO
CREATE CERTIFICATE prod1cert
WITH SUBJECT = ‘Production1 Backup Encryption Certificate’;

2. first do a database encryption management then take backup using the certificate.

    

Done! Backup is taken in encrypted file with server certificate named prod2dbCERT.

To restore this backup, administrator would required to use the same certificate and master Key, hence backing up this certificate is very important along with the master key. you can only restore a encrypted database if you have the certificate and master key which was used to take the encrypted backup.

Cheers
Jamal Ahmed
jahmed@zoonetix.com

msSQL2014 AlwaysON solution design diagram

msSQL2014 AlwaysON support automatic failover( either on node failure or network failure or service failure) to secondary node without any data loss in 3-6 sec.

It also does a sync commit on each database commit in primary database to  2 additional database. Among these 2 additional database 1 is the auto failover node and another one is manual failover node, manual failover database allowed planned activity to be carried node by node basis without any downtime.

msSQL2014 also have capability to have async commit, which assures additional high availability of database and data.

A recommended installation is 3 node cluster, however 4 node cluster offers primary and secondary site complete replica and in an event of primary site unavailability the secondary site will have full functionality to run production environment.

 

Cheers
Jamal Ahmed
jahmed@zoonetix.com