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…..how 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.