Encryption in transit

What’s the story today?

I have been tasked with coming up with the implementation for “in transit” data encryption of all the communication flowing back and forth from the web servers to the database servers. Even though I have been asked to implement “at rest” encryption many times before (all using TDE), this is the first time I have to implement such a request; without furter ado here is the full journey, hopefully it will save some people attemtping to do the same some time …

Implementing in transit encryption forced me in a new journey of learning, not only because I have not been exposed to such implementations in the past but because I honestly have never done this even in traning sessions or as part of my different MSSQL certification.

All in all, a totally unchartered territory that I have decided to blog about not only to share with other people but to also keep it for my own reference as some sort of diary that maybe I can re-use in the future should I ever need to do this again… bring on the challenge!

Fly away with me

When I heard “encryption on the fly” the first thought that came to mind was Always Encrypted, so I decided to fire away two Google Searches: “In Transit encryption SQL Server” “and “In Transit encryption always encrypted” and see what came back.

Let’s start with the Always Encrypted search query. The first hit is an MSDN white paper that confirmed what I knew (I did search about this out of curiosity, I guess). Always Encrypted was not going to be a solution unless the client (Running SQL Server 2008R2 SPII) was planning on moving into SQL Server 2016. Since I wanted to use this opportunity not only to learn “what I need to do now” but “what would I need to in the future” I decided to take a peek at this white paper.

Always Encrypted

Turns out that Always encrypted had nothing to do with what I thought. Although you can call Always Encrypted an “in-transit” encryption solution, this is certainly a fair classification of this technology as the scope of Always Encrypted goes well beyond encrypting data on the wire. I would say that Always Encrypted is a “selective” “end-to-end” encryption solution, but I will provide you with some information and will let you judge how you want to define it.

Here is a brief description coming from that MSDN paper: “Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).”

This means that Always Encrypted is, in other words, a mechanism to totally obscure data from everyone but those with a need-to-know business reason (who are usually the ones who own the data or those who have been legally granted permission to access the data). This is a new paradigm in SQL Server who previously considered that, in order to administer a database, you somewhat had to have some access to the data it contained. Considering the times we are living today (more awareness of security risks and maturity of security practices around that while virtualization grows) this paradigm change is no surprise at all. Great feature to consider for the most stringent security customers or those operating into very tight rules and regulations (thinking about some countries in EU especially where this is of utmost priority).

Always encrypted works at the database column level and uses two types of keys: column encryption keys and column master keys. Quoting MSDN again “A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys”. For those still confused, the idea is simple: you store your column encryption keys in an encrypted format inside the database, and use the column master keys (stored externally in key stores such as Azure Key Vault) to decrypt the column encryption keys.

Always Encrypted not only decrypts data for the applications that use it but also encrypts parameters values in queries if they are related to sensitive columns.

For more details on Always Encrypted, I’m going to point out a few links

If Always Encrypted is not the solution, then what?

I am not discouraged in not finding my solution with Always Encrypted, in fact, I kind of knew right from the start that this was not the answer, but I was curious and used the opportunity of this exercise to learn what Always Encrypted was all about. Now I feel wiser and I can laser focus on my task at hand J.

In order to do that, I fired the second Google query (“In Transit encryption SQL Server”). This time the results were more what I need today: The first 3 hits were all about SSL which is what I need here. If by now you are not 100% what SSL is or how SSL cryptography works, here is a great introduction (before we go any further) https://www.digicert.com/ssl-cryptography.htm.

First hit is a white paper on Encrypting Connections to SQL Server (https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx) starting with “Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application”. BINGO!

First, the bad news

In a nutshell: SSL does come as a price in performance. This can be decomposed in:

  1. An extra network roundtrip is required at connect time.
  2. Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
  3. Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

I guess that in ideal circumstances, you should baseline your database throughput before and after SSL implementation and see how much that performance degradation impacts YOUR day-to-day operations and whether this is acceptable or not.

To Self-Sign or not to Self-Sign, that is the question

I’m not an expert on security myself, so I wouldn’t be able to say whether self-signing certificates for the purpose of validating SSL communications between a Web and a database server is a bad idea or not. I would think this requires to either go back to do some homework or involving some SecOps Engineer into this discussion.

For the time being, and since this is only a proof of concept, I will start by implementing this solution using a self-signed certificate. If it turns out to be a bad idea in the long run, I can always re-configure the settings to harden things a bit.

There are many ways to accomplish the task of creating a self-signed certificate. Myself at least I know 3

  • Create self-signed certificate using makecert.exe
  • Create self-signed certificate using IIS Manager
  • Create self-signed certificate with PowerShell

I would think the end result is more or less the same. Microsoft seems to push people onto using PowerShell (they deprecated makecert.exe) but since I believe the PowerShell approach only applies to boxes running Windows Server 2012 and above (maybe not, but that is my impression) I will stick to the easiest approach that I know, IIS Manager.

  • Start IIS Manager
  • Go to Server Certificates
  • Right Click > Create Self-Signed Certificate

Great, now I have a certificate. Although I noticed that this cert is only valid for 1 year.

Grant SQL Server rights to read the private key for the certificate and also set the Certificate purpose to the right value

  • In MMC, go to Certificates (Local computer) > Personal > Certificates
  • The certificate is listed there
  • Right click > All Tasks > Manage Private Keys
  • Add the service account for your instance of SQL Server
  • Give the service account Read permissions
  • Also on the certificate properties, go to the General tab and select “Enable only the following purposes” radio button and make sure the “Server Authentication Checkbox” is selected.

I have found that not setting the certificate to “Server Authentication” will cause that this certificate will not be displayed on the SQL Server Configuration Manager later on when you try to select this certificate to be used by SQL Server. SQL Server seems to be very finicky in their requirements for the certificate (the certificate has to be built using very precise configuration values, otherwise it will not be shown). At least the requirements seem to be:

  • The certificate must be in either the local computer certificate store or the current user certificate store.
  • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (
  • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate’s key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
  • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
  • SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.


Fig1 – Make sure you change the certificate properties to specify the right purpose (Server Authentication) 

Configure SQL Server to use this certificate

  • Start SQL Server Configuration Manager
  • Go to SQL Server Network configuration
  • Select your instance
  • Right-click > Properties > Certificate tab
  • Choose the certificate you just created
  • Restart the service


Fig2 – Protocols for MSSQLSERVER properties with my new certificate selected

Since you are on SQL Server Configuration Manager, go to the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted. Set ForceEncryption option for the Database Engine to Yes so that all client/server communication is encrypted and clients that cannot support encryption are denied access.


Fig3 – Protocols for MSSQLSERVER properties with a “YES” setting for Force Encryption

Time to Test!

There are many ways to test this solution is indeed producing encrypted communications. From the most sophisticated ones using NetMon to the more mundane ones.

On the “more mundane” terrain (since I’m all for simple yet effective!) here is this query against sys.dm_exec_connections where you just pass a session id (or the @@SPID global variable if you are testing your own connection) and you get a simple TRUE or FALSE stating whether your connection is encrypted or not.

SELECT encrypt_option

FROM sys.dm_exec_connections

WHERE session_id = @@SPID

 ssms_after_testing_encryptionFig4 – Print-Screen of SQL Server Management Studio when testing encryption

Now, on a cluster

Now that I have successfully tested my IPSec Implementation on a single SQL Server Box, it is time to shift gears and repeat this test on a cluster (to see if everything works fine). I’m not going to explain all that it takes to create a SQL Server 2008 R2 Failover Cluster on a test environment because it would take me a full post. All I can say is in the Book called “Training Kit Exam 70-462: Administering Microsoft SQL Server 2012 Databases” (Microsoft Press) on Chapter 8 (Clustering and AlwaysOn) you should have all you need to do a step-by-step installation of SQL Server 2008 R2 Failover Cluster, I highly recommend that book for that purpose. Off course there are plenty of resources online, but this one is a great end-to-end explanation (because it has a practice to do just that).

Now, even with all that “step-by-step” help I have to say I struggled LOTS with cluster configuration. I think the problems I had were basically caused by inexperience, Microsoft Windows Clustering Services installation and configuration (and on top of that the SQL Side of the clustering solution) involves literally dozens of decisions, dozens of configurations that you can somehow partially automate, but it only makes sense to fully automate that if you are doing this many times over because of the complexity of the automation process.

If you are left with a manual installation and configuration of Microsoft Windows Clustering Services then you are in for a lot of pain. The installation involves iSCSI targets and sources, DNS entries, Firewall rules, , GroupPolicy Settings, AD configurations, Microsoft Windows Clustering Services configuration, SQL Server installations (in two parts). And this is only the beginning…

My first fail was caused (I believe) by the fact that my step-by-step suggested creating a DNS “A” record for the cluster.

The second time around was just a combination of stupidity and hurry. After my first failed attempt I was running out of time on that day so instead of leaving it there and continuing the next day I decided to re-image 2 servers again and start from scratch at full speed. With that attitude, it was of no surprise that the outcome was even worse than the first one: on one hand I didn’t even get past the installation of Microsoft Clustering Services before things started going bad and on top of that this time I didn’t even know what the heck was wrong. This is when you know in your guts that the best thing to do is to turn the monitor off and sleep on it, tomorrow is going to be a brighter day on cluster-land!

Now, on a cluster, take 2

Once the cluster was up and running (finally!) I started my SSL encryption certification (not before the usual “take snapshot”, I know I don’t want to lose a running SQL Server cluster!).

Turns out that configuring SSL on the cluster, once the cluster was working was NOT pretty straight forward. I started by performing the steps to create a self-signed certificate (see the section “To Self-Sign or not to Self-Sign, that is the question” above) on node A (the first node) but that failed miserably.

I had to rely on the MakeCert.exe to create the certificates this time (that was the only way I found to create a self-signed certificate with a subject of my choice). Without further ado, these are the steps to make SSL encryption work in a cluster environment.

Step by step to make SSL work in a Cluster

  1. Install Microsoft Windows SDK for Windows 7 and .NET Framework 3.5 SP1
    • In the installation options page, only select the Windows Development Tools

installing_windows_sdkFig 5 – Installing the Microsoft Windows SDK for Windows 7 and .Net Framework 3.5 SP1 (Installation Options page)

  1. Go to All Programs à Microsoft Windows SDK v 7.0 –> CMD Shell
  2. Run this command
makecert -r -pe -n "CN=[CLUSTER_INSTANCE_NAME]" -b 01/01/2011 -e 01/01/2038 -eku -ss my 
   -sr LocalMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

This is the description of the modifiers I used

  • -r: Creates a self-signed certificate.
  • -pe: Marks the private key as exportable.
  • -n “Name”: Name for the publisher’s certificate. This name must conform to the X.500 standard. The simplest method is to use the “CN=MyName” format. For example: -n “CN=Test”. Remember what I said earlier in this post. For a Clustered environment this name has to match the Cluster Instance Name!
  • -b DateStart: Date the certificate first becomes valid. The default is when the certificate is created. The format of DateStart is mm/dd/yyyy.
  • -e DateEnd: Date when the validity period ends. The default is the year 2039.
  • -eku OID1, OID2 …: Inserts a list of one or more comma-separated, enhanced key usage object identifiers (OIDs) into the certificate. As we stated earlier in this document, the certificate must be meant for server authentication and the eku for that is
  • -ss SubjectCertStoreName: Name of the subject’s certificate store where the generated certificate will be stored.
  • -sr SubjectCertStoreLocation: Registry location of the subject’s certificate store. SubjectCertStoreLocation must be either LocalMachine (registry key HKEY_LOCAL_MACHINE) or CurrentUser (registry key HKEY_CURRENT_USER). CurrentUser is the default.
  • -sky SubjectKeySpec: Subject’s key specification. SubjectKeySpec: must be one of three possible values:
    • Signature (AT_SIGNATURE key specification)
    • Exchange (AT_KEYEXCHANGE key specification)
    • An integer, such as 3
  • -sp SubjectProviderName: CryptoAPI provider for subject. he default is the user’s provider
  • -sy nSubjectProviderType: CryptoAPI provider type for subject. The default is PROV_RSA_FULL.
  1. Go to Certificates (Local Computer) à Personal –> Certificates. Right-click your certificate, select “Properties” and change these values in the General Tab
  • Friendly Name: full virtual fqdn of the clustered server (the same as the certificate subject name)
  • Description: Certificate for SQL Server SSL encryption
  • Enable only the following purposes: Server Authentication
  1. Still in the Certificates (Local Computer) à Personal –> Certificates, Right click your certificate again and navigate to –> All Tasks –> Manage Private Keys
  • Add the service account for your instance of SQL Server
  • Give the service account Read permissions
  1. Export the certificate from the Certificates (Local Computer) –> Personal –> Certificates Folder to Certificates (Local Computer) –> Trusted Root Certification Authorities  –> Certificates Folder with the password and extended properties options enabled.
  2. Repeat the import on the other node, both on the Certificates (Local Computer) –>Personal –> Certificates folder and in the Certificates (Local Computer) –> Trusted Root Certification Authorities  –> Certificates At the end of this step the certificate should exist in 4 places: inside the Trusted Root Certification folder of nodes A and B and inside the Personal folder of nodes A and B.
  3. Repeat step number #5 (to grant the SQL Service Account permissions on the certificate) on node B.
  4. Restart SQL Server Service. Once that was done, check the Errorlog of SQL Server. You should see an entry like this:
“The certificate [Cert Hash(sha1) "0B3B8EAF69FE7D7C9EC516EDB99EFF94BB67DF85"] was 
successfully loaded for encryption.” (the long string is the thumbprint 
and it will be different for your certificate).

You can then copy this value thumbprint to the Certificate value under the following registry key (of both nodes):

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib
  1. Finally, configure SQL Server to use this certificate.
    • Open SQL Server Configuration Manager
    • Navigate to SQL Server Configuration Manager (Local) à SQL Server Network Configuration
    • Right click Protocols for [Instance_Name] and select Properties
    • Go to the Flags tab
    • Set ForceEncryption option for the Database Engine to Yes
  2. Restart SQL Server Service once again

Do not worry about the changes in the Certificate tab that we specified for configure SSL when SQL Server is not in a cluster. Actually, don’t even worry that your certificate is not showing up in the Certificate combo box (in the Certificate tab). That is just a bug in the SQL Server Configuration Manager. What it really matters is that you can verify that encryption is working by looking into the dm_exec_connections statement that I have shown above.

Another test you can perform to make sure your certificate is loaded is to run this statement against your server

xp_readerrorlog 0, 1, N'certificate', N'', '20161027 9:30', null, 'ASC'

This should return something like the message we mentioned above “The certificate [Cert Hash(sha1) XXX was successfully loaded for encryption.”

If all these tests work as expected you can consider your encryption is fully in place, relax and enjoy!

Some troubleshooting I did (just to get it on the record)

During the configuration of clusters, I had a problem that read…

Cluster network name resource 'Cluster Name' failed registration of 
one or more associated DNS names  "DNS Operation Refused"

… on the Cluster Log, one event was created every time I brought the cluster off-line and on-line again. I fixed that by adding permissions to the computer accounts of the cluster members and also to the “Authenticated Users” user group. More info can be found here:




At some point in time, when creating a Windows Server 2008 R2 failover cluster to host a SQL Server failover cluster you have to install the Failover Clustering feature: a simple powershell script to do that is:

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering

To move the cluster from one node to the other here is the PowerShell

Import-Module FailoverClusters

Move-ClusterGroup “Cluster Group”

Move-ClusterGroup “Available Storage”



About Martin Surasky

Full-time time daddy, part time DBA, and guitar player (only if I'm not administering a databases or with my daughter) and part time Coffee Crisp addict (thanks Nestle)...
This entry was posted in Simple Tricks and How To's and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s