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!!!

Instant File Initialization won’t work on TDE Enabled databases and Log truncation won’t happen through the Encryption process(While Background Encryption scanner is in progress). So, keep a close eye on your LDF file when actual encryption process is in progress.

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' )

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.

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! There are quite a few things to keep in mind as a DBA in order to Support TDE enabled Databases.

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.

BGINFO – Sysinternals

Let’s deviate a little bit from SQL Server in this Post;)
Let’s get Straight to the point! Have you ever been in a situation logged in to your Windows Server and searching for “Service Packs Installed, IP Configurations, DNS Details, Boot Time, Drive/Volume(s) Information with the Free Space, Logged on Domain, Installed Memory and all that Cool Info..”? Yes, As a SQL Server DBA we need to know that information(at least few from the above mentioned) in few scenarios while troubleshooting few issues. Basically to get all the above mentioned details, we need to dig into multiple tools which are available from Windows Server..

What if you can pull that information from a single location just at a Glance, that too on your Desktop Just as a Wallpaper?? Exciting Right!! This might be already known to most of folks out there….But in case, if you are not aware of this, We can achieve this using a fabulous Free tool from our Microsoft SysInternals called “BGINFO“.

You can download this Tool from http://technet.microsoft.com/en-us/sysinternals/bb897557. Again, this is a free tool from Sysinternals! Actually I’ve downloaded and Installed on my Server and now When i logged in to the Server, I’m welcomed with a Background all the cool Info as you can see in the below Screenshot.


So, is this the only information what you can get?? Noo….You can actually edit what you want to see on your Desktop. Just double click on bginfo.exe and you’ll be getting many other options and you can also change the position of text to be displayed as you can see below.

Download this Fabulous little tool and Install it on your Laptops and See what i’m talking about:)

How to Transfer(Copy) Maintenance Plans from One Server to other???….

Update: Don’t copy or transfer Maintenance Plans. Create new ones Instead if needed or checkout Ola Hallengren’s Maintenance solution or Minionware suite.

This would be a very short blog post where I’m going to show how maintenance plans can be copied over from one SQL Server to another. If you are not aware, Maintenance Plans are nothing but SSIS packages which SQL Server creates behind the scenes and deploy them under MSDB Database as you can see below.

I’ve created a Maintenance Plan(Test_Maint) in my Prod Instance and connected to local SSIS, as you can see in the above screenshot SQL automatically created an SSIS package with the same name and deployed under MSDB. So…to Copy this Maintenance Plan to another Server, all you need to do is…Just Right click on the package and Export to FileSystem. Now, copy the DTSX file(Actual SSIS package) to the new server and you can deploy under MSDB->Maintenance Plans Node by importing it.

Follow the below Screenshots in the same order.

Saved in my File System as shown below.

Now,connect to new Server and Import the package as shown below

Click Ok…and Connect to Database Engine and Expand your Management->Maintenance Plan Node to Verify Package being Created 🙂 That’s it Guys!!!

Hope this is Useful!

Uninstalling SQL Server 2008/2008R2 Service Pack(s)

Uninstalling Just the Service Pack/CU without uninstalling the entire SQL Server Instance….!! Yes, A definite improvement, Starting SQL Server 2008. In SQL Server 2005 days  if you want to Uninstall a Service Pack, basically you’ve to uninstall the entire SQL Server Instance. In other words…we didn’t had Roll Back Capability on any Updates we install over our SQL Instance.  Let me explain with a simple Scenario…

You are running on SQL Server 2005 SP3 and because of “End of Support” announced by Microsoft, you decided to apply SP4 over it.  You were able to successfully patch the prod Instance with SP4, but all of a sudden your application breaks and users are screaming and you identified the reason – “patching”. Well,  you decided to fail over to your DR(BCP) site and meanwhile your plan is “rolling back your production Instance to SP3”  so that users and you as a DBA can take a peaceful nap! How do you achieve that??? Unfortunately you’ve to Uninstall your SQL Server and Install your SQL Server 2005 RTM and apply SP3 and restore all of your Databases…and all that hectic work, which really is a very painful process.

Fortunately, starting SQL Server 2008 MSFT offered us a great enhancement – we can Just Uninstall whatever Update we’ve Installed:) Yeyyyyy. So let’s see how to UnInstall Just your Service Pack without loosing your SQL Server Instance!

In this Post, I’m going to show you Uninstalling SQL Server 2008R2 SP1 and basically rolling back my Instance to RTM. As of now I’m running SQL Server 2008R2 SP1 as you can see in the below screenshot.

FYI…I’m on Win7 X64 machine. To Uninstall we’ve to go to “Control Panel” and “Programs and Features”  and you Will be seeing SQL Server 2008R2 SP1 as any other typical Program which you can Uninstall from Control Panel.

Note: We’ve to select View Installed Updates in order to see this item under your Installed Programs(You can see that highlighted in the below Screenshot.)

As you can see in the above screenshot, there is no SP1 listed.  See the below Screenshot once I clicked on View Installed Updates.

All we need to do is just Select that and Click on Uninstall Button or Just Right Click on it and Choose Uninstall. You’ll be automatically welcomed with SQL Server Installation Center as shown below.

Click Next and As you can see below, I’ve two Instances of SQL server 2008R2 SP1(PROD and DR) on this Machine. I’ll Choose only Prod Instance( you can see below)

Once you click on Next..It’ll do a file check for any outstanding issues which might prevent un installation, Once you passed the test, It’s matter of Just clicking “NEXT” and “REMOVE”….That’s it guys:).  After 4 minutes or so, I got a message “Uninstalled Succesfully” as shown below.

See the below Screenshot, my PROD Instance is back to RTM where as my DR Instance is still on SP1

Prestooooooooooo! Pretty Simple huh??

Hope this is Useful information!

Installing SQL Server from Command line(CMD)

How often do you Install SQL Server in your Environment?? Monthly Once? In that case, I prefer just double clicking setup.exp in my installation media and follow the instructions from GUI. How about Weekly Once? Well, i would go with GUI again…considering the time i’ve for completing the task..What if you got a new SQL Farm where you are assigned with a task to Install SQL Server on all your Non Prod environments in a single day or two..Assuming you’ve DEV,SIT and UAT as your Non-Production environments and you’ve to install 8-10 Instances on a single day, i strongly prefer installing SQL Server from command line. It might be little annoying initially, but believe me you’ll be saving 65-70% of your precious time just by providing few parameters to your setup.exe from cmd line. Basically you’ll be skipping all the screens in GUI where you are supposed to provide all the required information through out the installation process.  Let me show you what exactly I’m saying….

I’ve inserted my Installation Media(actually mounted my ISO using Deamon Tools) in my DVD drive and navigated to setup.exe from cmd(Run as Administrator). You can enter SETUP.EXE /? if you want to know all the available Options and Switches which you can make use of in this Installation as shown below.

Before proceeding any further, this is my game plan for Installation:

SQL Server 2008 DB Engine, No SSRS, no SSAS, no SSIS, no Tools(SSMS/BIDS). FYI…My Laptop already has 2 SQL 2008R2 Instances and 1 DENALI Instance….So I’m limiting my installtion with just SQL Server 2008 DB Engine.

InstanceName: SQL2008

SQLSVCACCOUNT (Service Account for DB engine): NT Authority\System

AGTSVCACCOUNT (Service account for SQL Agent): NT Authority\System

SQLSYSADMINACCOUNTS: Sysadmins on SQL Server. I’ll add myself here.

Features: SQL(This implies Just DB Engine, If you want to Install SSIS, SSRS, SSAS as well on the same server – which you should never do; you can enter IS, RS and AS as well)

SECURITYMODE: Windows Authentication/SQL Authentication – I’ll choose SQL in this demo.


So, combining all together my syntax would become

All i did was just navigating to G:\(MY DVD Drive) and typed(ctrl+c and Ctrl+v 😉 from my scripts toolbox) the below and just hit ENTER on my Keyboard!!


I’m welcomed with the installation progress(you can see screenshot below)

After 4 minutes I was returned to the main command prompt without any issues…and you can also see my Config manager with our new Instance(SQL2008) being successfully installed in the below Screenshots

Perfecttttttttt……..:) We avoided all the PAUSE and halts which we usually face while Installing from GUI. At this point, even if you want to change any service accounts or even Data/Log file locations, It’s Just matter of few minutes…. from our config manager!! So..all you need to do is Just save the above Syntax somewhere in your documentation and you can Just Copy Paste by changing the required parameters such as Instance name, Service accounts…etc as needed. Having a standard Drive(s) naming conventions and standard Drive Letters across your enterprise on your SQL Servers makes this process much simple as you can see!

Hope this is useful and you’ll start exploring this if you haven’t yet!!