MSSQLTREK

Back in 2011 I wrote couple of Blog posts when I was initially exploring Encryption options we have in SQL Server. Never really got a chance to work on TDE since then. Fast forward to 2016, I am participating in TDE project, where we are enabling TDE for few of our databases which are hosted on SQL Server Failover Clustered Instances. Currently we are in testting/POC phase. In this post, I will share what my findings are about TDE on clustered instances. Let’s get started…

Enabling TDE on a clustered database is no different than how we enable on a Standalone Instance. Steps are as follows:

--a.
Create database TDE_Test
GO
--1.create a master key in master database.
USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Very$ecurepwd123'
--2.Create a certificate(name it)
USE master
GO
CREATE CERTIFICATE CLUSTPRD1_TDECert
WITH SUBJECT = 'Transparent Data Encryption Certificate'
--3. Create a database encryption key. –Based on the certificate from Step 2–Can use AES, DES, Triple DES, RC4 etc
USE TDE_Test--The db to protect
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE
CLUSTPRD1_TDECert--Cert from Step 2

--4. Turn it on!
USE TDE_Test--The db to protect
GO
ALTER DATABASE TDE_Test
SET ENCRYPTION ON
GO
-----Backup Certs---V V Imp
USE MASTER
GO
BACKUP CERTIFICATE CLUSTPRD1_TDECert
TO FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert.cer'
WITH PRIVATE KEY (FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'MyAw3S0m3Pwd123#' )
GO
--Backup SMK---VVV Important</pre>
BACKUP SERVICE MASTER KEY TO FILE = 'c:\keys\SQLPRD1_SMK.bak' ENCRYPTION BY PASSWORD = 'gytj6%&amp;5gjOUytp';

Okay…so what happens when SQL gets failed over to another node? Will TDE work? Will the database be still in usable state? The answer is “YES” from my initial testing. Everything remains intact. Nothing breaks!

Note: This is different from the way how it works in Alwayson AGs.

Reason: See below Screenshot taken from BOL.

The Master Key which we created on step 1 is protected by Service master key(SMK) which is scoped at Instance level is the root of our Hierarchy, And then the certificate and the keys are scoped at master and user database levels which will be failed over and moved between nodes.So….Nothing special is required for FCIs. Please correct me If I am wrong and post any thoughts in comments.

Cheers!