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.
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:
We will go through each one of these steps below:
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
Open MNM and click New Capture > Start to start the time capture in the network layer.
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:
After checking events in MNM, we find that the network transport layer between the client and the SQL Server uses plaintext transmission:
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.
You can also see that the connection is not encrypted by using the dynamic connection view in SQL Server.
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.
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:
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.
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
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.
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.
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.
Select the certificate file generated on the SQL Server.
Click Next > Finish > OK.
On the Connect to Server page in SSMS, select Options.
Select Encrypt connection.
Perform the connect test we did before. In the connection management view, we can see that the connection has been encrypted:
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.
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.
Alibaba Clouder - March 19, 2021
Cherish Wang - January 17, 2019
Cherish Wang - September 16, 2019
Alibaba Clouder - September 23, 2019
Alibaba Cloud Community - January 31, 2022
Alibaba Clouder - January 22, 2018
Fully managed and less trouble database servicesLearn More
Protect, backup, and restore your data assets on the cloud with Alibaba Cloud database services.Learn More
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilitiesLearn More
Alibaba Cloud SSL Certificates Service allows customers to directly apply, purchase and manage SSL certificates.Learn More
More Posts by Cherish Wang