Enable SSL Encryption for an MSSQL Network Transport Layer

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.

  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

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.

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)

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:

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.

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.

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.

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.

4. Enable Encrypt Connection in SSMS

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

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.

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com