Setting up TDE on SQL Server Failover Cluster

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:

Create database TDE_Test
--1.create a master key in master database.
USE master
ENCRYPTION BY PASSWORD = 'Very$ecurepwd123'
--2.Create a certificate(name it)
USE master
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
CLUSTPRD1_TDECert--Cert from Step 2

--4. Turn it on!
USE TDE_Test--The db to protect
-----Backup Certs---V V Imp
TO FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert.cer'  
--Backup SMK---VVV Important</pre>


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.


SQL Server Encryption – Part5 Encrypting Data(TDE) vs Encrypting Connections

This is the final part of Encryption series where i’m going to show how to encrypt connections in SQL Server. In the previous 4 parts of this series we’ve seen how to enable TDE for your Database, what EFS is and what Bitlocker is. If you recall correctly, all we discussed was how to encrypt our Database(Physical Files on the Disk), not securing Connections. In other words the data will be encrypted as long as it stays on the disk but not encrypted while SQL is sending data over to clients as you can see below!!

Note: TDE encrypts Data on Disk, Connections are not Encrypted using TDE.

So… to encrypt data across Wires(in other words how to Encrypt Connections)?

We have to use 128 Bit SSL(Secure Socket Layer) within SQL Server to encrypt Connections btw SQL Server and Client(s), which leads to slower performance but your SQL Server is Very Very Secured! Basically we’ve to Install a trusted certificate in our windows Certificate Store on our Physical Server where our SQL Server Instance is living on and we’ve to let SQL know that “Hey Mr.SQL Server! we’ve a certificate for you..make use of it for every connection”.

Step 1: Install Certificate(We can work with our Server/Network Team on this)

Step2: Go to your SQL Server Config Manager and enable Encryption as shown below.(I’ve 3 Instances on this Server, I’ll show you on PROD Instance)

Navigate to network Configuration, Right click on Network Protocols and select Properties as shown below.

Now, all your Certificates will be shown in the drop down menu under “Certificates” tab as shown below(I don’t have any on my Server)

Now, on Flags tab you can choose either Force Encryption to Yes or leave to default No as shown below.

If you Choose YES – Clients who are not encrypted are not allowed to connect.( In other words, any client which doesn’t allow encryption is declined to connect)

If you choose NO – Clients will be seeing encryption as optional.

Note: SSMS(Let’s assume it as a client application now for time being) knows how to encrypt connections. We can choose an Option to Encrypt my connection while connecting to the Database Engine as shown below.

As you can see, I’m checking the Box to Encrypt my connection from SSMS client while connecting to SQL Server DB Engine.

Hope this helps for a Kick Start on Encryption!!

SQL Server Encryption – Part4(How to Restore an (TDE enabled Database) encrypted Backup?)

Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.

As i mentioned earlier, we can’t restore a backup on a different instance without Certificates and Keys. We’ve to restore Certs and Keys, then we should be able to restore the Database from the Backup.

Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.

My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)

To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀

Soo…..what should we do???

We’ve to Restore all our Keys and Certificates, Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.

On DR Instance: 

Create a Master Key and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below

USE master;
    WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
    DECRYPTION BY PASSWORD = 'VeryStrongPassword');

/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )

Now let’s do the actual restore and see how it goes….

Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database!!!

SQL Server Encryption – Part3(GOTCHAS – A TDE Enabled Database)

In the Previous two Posts of this series, we’ve seen how to actually encrypt our Database(s). In this post, let’s focus on things to consider on Databases with TDE enabled. Let me tell you something…there are lot of GOTCHAS as a DBA for Encrypted databases you should be aware of. You should not be in a situation saying “Oops! I missed it, what should i do now?”.  Reminding again….If you loose your Master Key, You’ve lost your Database, No Ifs and buts! If you are creating a DB Encryption key with AES_128/AES_256 Algorithm and if you lost it, i believe it would be a very tough challenge even for an ethical hacker to decrypt it ;)(I might be wrong, but I would expect the same level of encryption what i’m saying!!) Okay!! Enough Scaring you all, let’s get into the actual content!

Very first thing, Encryption adds a little Overhead on your CPU. Thorough testing is mandatory!

Enabling TDE on a Database might require an Application outage, It takes some time depending on your database size(I think it acquires some locks on objects while enabling encryption for the first time, I’m not 100% sure). As a safe bet, do this after Business hours taking on a maintenance window. (Make sure that you’ve tested in your lower environments.)

If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. Soo…there’s a high possibility impacting other Databases which are actually not using TDE feature while you are in the process of enabling TDE!!!

You’ve to backup your Service Master key, Certificates and Master key and secure it in a safest location(Far away from where you store your Database Backups. The idea is not to have access to your Keys for any intruder even though he/she got hold of your DB Backups!

How to Backup your Service Master Key??


How to Backup your Master Key?

BACKUP MASTER KEY TO FILE = 'D:\Backs\Master_Key.bak'

How to Backup your Database Key?

BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )

Very Very Important:

We can’t Restore a Database or Detach/Attach a Database or Copy a Database with Copy DB wizard without the KEYS!! So..if you lost the keys, you are gone! Only Solution is to URLT (Update Your Resume and leave town :D)

That’s all for the day! Let’s see how to actually restore a Database which is Encrypted on a completely different server in the upcoming post of this encryption series.

SQL Server Encryption – Part2(TDE-Transparent Data Encryption)

This is the second part of the Encryption Series, In this Blog post let’s see what can be achieved within SQL Server in terms of Encrypting the Data.

In Legacy days……..Prior to SQL Server 2008(Enterprise Edition), we only had an option to encrypt at Column level/Cell level not at the Database level. Even in SQL Server 2008 if you are using standard edition of SQL Server, you don’t have an option to encrypt at Database level. So…Basically we’ve to make this possible manually on every column by making use of either of the following inbuilt functions which SQL Server offers us..





So…basically you’ve to deal with all the annoying Symmetric Keys/ Asymmetric Keys/PassPhrases etc..and you’ve to make sure that the routine is followed each time you add a new column or make any change to your table…and we’ve to make use of Decrypt routine while retrieving the data, which is a huge hugeeeeeeeeee deal especially for developers!! You are dealing with tons and tons of Code changes and huge huge amount of testing is required!(Also a hugeeee…CPU hit depending on how many columns you are encrypting)

If you are on Enterprise Edition of SQL Server 2008, we’ve a brand new feature TDE(Transparent Data Encryption) which is at Database level and completely transparent to developers or anyone who is actually accessing our Database!! Nothing needs to be done from developers standpoint, basic testing is necessary though(Since it could cause 3-8% CPU hit under the hoods as per Microsoft documentation). All the Encryption and Decryption routines are completely transparent:) which is AWESOME!

How to Enable TDE???

1. Very first Step would be creating a Master Key in Master Database.(Immediately Backup your Master Key)

2. Now, create a Certificate(in Master Database) based on this Master key.

3. Now, create a Database Encryption Key(In the Database which we are trying to Encrypt) and the final Step would be Turning on Encryption at Database level.


If you try directly Enabling TDE on a Database without a DB Encryption Key you’ll be yelled at with the below Error Message

Msg 33106, Level 16, State 1, Line 1

Cannot change database encryption state because no database encryption key is set.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


Please see the below Screenshot which covers all the Required Steps to Enable TDE on a given Database.

Technically, That’s all we need to do for encrypting a Database! Pretty Simple huh?

Note: You’ve to provide a very very Secure Password(I would say 20-30 Characters including all kinds of Alpha Numeric Combinations and store it in a safe) for your Master Key. If you loose it , basically you’ve lost the Database, there’s no way you can decrypt it without the password.

How to Verify whether your database has been enabled with Encryption?

You can Go to Database Options from SSMS as shown below or Just Querying the Sys.Databases view!



Int the next post of this Encryption Series, let’s see what are the challenges we’ve while dealing with Encrypted Databases! Believe me, Setting up TDE is matter of Seconds…but there are lot of steps and measures that we need to take as a DBA in order to Support your TDE enabled Database. Believe me, once TDE is enabled on a Database, there is a hugeeeeeee curve from a normal Database which you should be aware of.

SQL Server Encryption – Part1

This is the First part of the Series Which Covers how to handle the Encryption piece in SQL Server. The Term “Encryption” is one of the Most Interesting and annoying things which we as DBA’s see, especially if we never dealt with Database Encryption,Certificates, Keys in the Past and all of a sudden we got a request from the Application team saying “Hey My Dear DBA! Our Database for this Application is going to contain PII(Personally Identifiable Information) Such as SSN, Address etc and should be SOX (Sarbanes-Oxley Act) Complaint, and we need the Data to be Encrypted. What are the Options we’ve in SQL Server?…” So what are the Options we really have in SQL Server?

Well, In this part of the Encryption Series let’s try to explore what are the Options we’ve to Encrypt Data from Windows Server Perspective. In the future Posts I’ll be showing you what are the options from SQL Server stand point. For now..Let’s look at basic Windows Encryption techniques.

Encrypting File System(EFS):

EFS basically is at File(s)/Folder(s)  Level. It Encrypts the Files or Folder(s) based on the User accounts. It doesn’t Encrypt all the contents of the Drive independent of any user. let’s say…you’ve multiple User accounts or groups, they can individually encrypt their own Files and Folders Independently. You need not be even an Administrator on the Server to implement EFS and encrypt your Files!! No Special Softwares, no special Hardware pieces required to be attached to your motherboard! It’s pretty Basic level of encryption, in which all your encryption keys are stored in your OS Drive(typically C$). So, what if hacker is too smart and got into your C$?? yes, He/she can decrypt all your encrypted stuff in no time!!

Bitlocker Encryption:

Bitlocker is at Drive/Volume level. It encrypts all the contents(files/folders) on all the Drives Including System Files in Operating System Drive and Removable Drive(s) as well! It’s independent on User accounts. The Contents will be encrypted irrespective of the User account(s). i.e, Once Bitlocker Encryption is turned ON, all the Files/Folders will be Encrypted for all the Users. You have to be a a member of Local Admins Group at minimum to turn on Bitlocker Encryption and it requires a special hardware piece called Trusted Platform Module(TPM) to encrypt the contents at Drive level. Since the OS Drive itself is encrypted, for a hacker even if he/she got hold of C$, they really can’t get into Encryption Keys.(So..It’s quite possible, we can use EFS to let Users to encrypt individual Files and Windows Server Admin Encrypts the OS Drive using Bitlocker. Few Companies use this Technique as their Security practice)

As per the Microsoft Documentation – Enabling EFS for SQL Server Database Files is not a good idea and they both together doesn’t work well in terms of performance. They’ve(EFS and TDE – Transparent Data Encryption, this is something which we enable at SQL Database level which I’ll be exploring in future posts) concurrency Issues working together! Bitlocker for SQL Server(Bitlocker and TDE) has no noticeable concurrency issues working together. If you reallyyyy…need that level of Encryption, Yes you can definitely think Bitlocker/TDE combination as an efficient solution.