Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Enable SSL Encryption for an MSSQL Network Transport Layer

Enable SSL Encryption for an MSSQL Network Transport Layer

In this tutorial, you will learn how to enable SSL encryption for the network transport layer of your SQL Server.

By Feng Yi

In SQL Server relational databases, or more specifically MSSQL ones, you can ensure the security of the database engine layer by using technologies like Transparent Data Encryption (TDE), Row-level Security, Dynamic Data Masking, and Backup Encryption. However, in the network transport layer, by default, no data encryption is enabled for the data transmission between clients and servers. To improve the security of this later, we can enable SSL (Secure Sockets Layer) encryption, which encrypts network connections in the transport layer and improves the security of data tunnels. However, one downside to this is that SSL encryption also increases the response time of network connections and the CPU overhead.

Prerequisites and Preparations

In the example given in this tutorial, Microsoft Network Monitor 3.4 (hereinafter referred to as MNM) is used to monitor events in the network transport layer. Therefore as a prerequisite to this tutorial, you'll need to download MNM.

When downloading it, remember to choose the corresponding version. For this tutorial, we downloaded NM34_x64.exe (64-bit). Then, to install MNM, directly run NM34_x64.exe and follow the wizard instructions to complete installation. Then, last restart the OS.

By default, before the SSL certificate encryption is enabled, your network transport layer between the client and the SQL Server is not encrypted. We can verify this through the following steps:

  1. Create a Test Table
  2. Start MNM and Add New Capture
  3. Conduct a Connection Query Test
  4. Check Events in MNM
  5. View the Encryption Status in the Dynamic View

We will go through each one of these steps below:

1. Create a Test Table

To make the demo test simple, we can first create a test table called CustomerInfo and store data about three customers, including customer names and customers' phone numbers in it.

USE [TestDb]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
    DROP TABLE dbo.CustomerInfo
CREATE TABLE dbo.CustomerInfo
(
CustomerId        INT IDENTITY(10000,1)    NOT NULL PRIMARY KEY,
CustomerName    VARCHAR(100)            NOT NULL,
CustomerPhone    CHAR(11)                NOT NULL
);

-- Init Table
INSERT INTO dbo.CustomerInfo 
VALUES ('CustomerA','13402872514')
,('CustomerB','13880674722')
,('CustomerC','13487759293')
GO

2. Start MNM and Add New Capture

Open MNM and click New Capture > Start to start the time capture in the network layer.

1

3. Conduct a Connection Query Test

Connect to the corresponding SQL Server from the client and run the following query statement to observe the MNM capture.

USE [TestDb]
GO
SELECT * FROM dbo.CustomerInfo WITH(NOLOCK)

The result is shown as follows:

2

4. Check Events in MNM

After checking events in MNM, we find that the network transport layer between the client and the SQL Server uses plaintext transmission:

3

From the lower-right the red box in the following figure, you'll be able to see the three customers' names and phone numbers. MNM shows that the data in the network transport layer is transmitted in plaintext without being encrypted. This may lead to data theft.

5. Check the Connection Status in the Dynamic Vew

You can also see that the connection is not encrypted by using the dynamic connection view in SQL Server.

4

We can draw the same conclusion either by using MNM or the dynamic view in SQL Server: Data on the client side and server side of SQL Server is transmitted as plaintext without being encrypted in the network transport layer and may be stolen. Therefore, we can enable the SSL certificate to have encrypted data transmission and improve the security.

Enable an SSL Certificate

Now, let's go on to enable SSL encryption. The first thing we'll need to do is to enable an SSL certificate. To do so, you'll need to follow these steps:

  1. Request a Certificate
  2. Force All Connections to Use SSL
  3. Encrypt Specific Client Connections
  4. Enable Encrypt Connection in SSMS

1. Request a Certificate

To request a certificate, click Start, enter the mmc.exe and click File > Add/Remove Snap-ins > Certificate > add > Computer account > Next > Local Computer > Finish > OK.

5

Expand the Certificate, right-click Personal, select All Tasks > Request New Certificate, click Next, select Compute and click Enroll > Finish.

Right-click the corresponding certificate, select All Tasks > Manage Private Keys and grant the read permission to the local account NT ServiceMSSQLSERVER.

2. Force All Connections to Use SSL

To do this, you'll want to first force all the connection to use SSL encryption. On the SQL Server, click Start > Run > sqlservermanager13.msc, right click Protocols for MSSQLSERVER > Properties > click Flags, set Force Encryption to Yes, select the corresponding certificate in the Certificate tab and click OK.

6

Next, after making the connection settings, restart SQL Service to make these settings take effect immediately.

Note: For applications that are running normally online, carefully test them before you force all the connections to use SSL.

3. Encrypt Specific Client Connections

Of course, you can also encrypt specific client connections instead of forcing all the connections to use SSL. Consider SSMS for example.

To do it, Click Start > Run, enter certmgr.msc, click to expand Trusted Root Certification Authorities, right-click Certificates and choose All Tasks > Import.

7

Select the certificate file generated on the SQL Server.

8

Click Next > Finish > OK.

4. Enable Encrypt Connection in SSMS

On the Connect to Server page in SSMS, select Options.

9

Select Encrypt connection.

10

Perform the connect test we did before. In the connection management view, we can see that the connection has been encrypted:

11

At this point, the experiment has been successfully conducted to encrypt client and SQL Server connections by using an SSL certificate.

When an SSL certificate is used to encrypt client and SQL Server connections, the data communication security is improved. However, the consequent encryption and decryption can lead to increased network connection response time and higher CPU usage, causing a certain impact on the performance of the business systems. Therefore, we recommend that you enable SSL encryption only when links in an internal network require encryption. Links over the Internet are usually safe and therefore do not need to be encrypted.

Summary

In this tutorial, you have learned how to enable an SSL certificate to encrypt client and SQL Server connections. SSL encryption improves the communication security in the network transport layer and allows data to be transmitted as ciphertext, ensuring the best link security.

Database Security SQL Server SSL Tutorials Transparent Data Encryption
1 1 1
Share on

Read previous post:

Enable Always Encrypted for Your MSSQL Databases

Read next post:

Mask Privacy Data Columns in MSSQL

Cherish Wang

11 posts | 0 followers

You may also like

Comments

Dikky Ryan Pratama May 8, 2023 at 3:48 pm

thanks, this is very helpful.

Cherish Wang

11 posts | 0 followers

Related Products

More Posts by Cherish Wang

See All