Chapter 1 Securing Your Server and Network (5): use SSL to encrypt sessions, securingssl

Chapter 1 Securing Your Server and Network (5): use SSL to encrypt sessions, securingssl
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38037457

 

Preface:

 

Between SQL Server and client, data and SQL query are transmitted in the form of network packets. You can use some packet sniffer, such as Wireshark (http://www.wireshark.org/), to convert these network packets into readable form.

Tabular Data Stream (TDS), a table Data Stream, is also translated into a flat Data Stream. It is a protocol for SQL Server to transfer packets over the network. To protect the data from being detected by the sniffer, you must use SSL to encrypt the interaction between the client and the server.

 

Preparations:

 

If you want to use SSL to protect communication, you need to purchase an SSL Certificate from the Certificate Management Center (Certificate Authority/CA) such as VeriSign, Comodo, or DigiCert. You can also generate your own certificate, but it does not guarantee the effect, especially the certificate generated by untrusted third parties, which is not highly secure.

To enable SQL Server to use the certificate, you must install the certificate with the same account that runs the SQL Server service. Alternatively, if the SQL Server service runs the SQL Server service by a Windows system account, managed account, or virtual account, you must use an account with administrator permissions on the Server to install the certificate.

 

Implementation:

 

1. Open the SQL Server Configuration Manager, select SQL Server network configuration, and select the corresponding instance. In this example, the SQL Server protocol is used]

 

 

2. Right-click the corresponding protocol and choose properties ]:

 

3. Set "Force encryption" on the "sign" page to "yes", which will prevent you from allowing unencrypted connections:

 

4. On the certificates page, add the Installed Certificate. Note: If you do not select a certificate, SQL Server automatically creates and uses a self-signed certificate.

5. Click OK and restart the SQL Server service.

 

Principle:

 

After you select forced encryption, the client automatically uses SSL connections. You can also specify it in the client connection string, for example:

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;

You can also use the following method to connect to SSMS:

1. On the connect to server interface, open option ]:

 

2. Check [encrypted connection] in [Connection Properties ]:

 

3. Click Connect]

4. Then, you can check whether the data has been encrypted in SQL Server:

SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID;

If encrypted, "True" is displayed; otherwise, "FALSE" is displayed]

 

Note: The certificate must be valid and must be updated before the certificate expires.