Chapter 1 Securing Your Server and Network (5): Encrypt session with SSL

Original source: http://blog.csdn.net/dba_huangzj/article/details/38063823, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

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

Objective:

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

Tabular data Stream (TDS), tabular data stream, also translated into a flat stream, is the protocol for SQL Server to transport packets across the network. If you want to protect this data from sniffer detection, you need to encrypt the interaction between the client and the server using SSL.

Preparatory work:

If you want to secure communication using SSL, you need to purchase an SSL certificate from the Credential Management Center (Certificate AUTHORITY/CA) such as VeriSign, Comodo, or Digicert. You can also generate certificates yourself, but it is not guaranteed, especially for certificates generated by untrusted third parties, which are not secure.

In order for SQL Server to use certificates, you need to install the certificate with the same account that runs the SQL Server service. Or, if the SQL Server service is running the SQL Server service by a Windows system account, a managed account, or a virtual account, use an account with administrator privileges on the server to install the certificate.

Realize:

1. Open SQL Server Configuration Manager, select "SQL Server network Configuration" and select the corresponding instance, this example uses "SQL2012 protocol"

2. Right-click the corresponding protocol and select "Properties":

3. Set the "Force encryption" entry in the "Flags" page to "yes", which will allow you to disallow non-encrypted connections:

4. On the Certificates page, add the certificates that are already installed. Note that 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:

When you select Force encryption, the client automatically uses an SSL connection. You can also specify in the client connection string, for example:

Driver={sql Server Native Client 11.0}; Server=myserveraddress;database=mydatabase; Trusted_connection=yes; Encrypt=yes;

Alternatively, you can connect in SSMs using the following method:

1. In the "Connect to Server" interface, open "options":

2. Tick "Encrypt connection" in "Connection Properties":

3. Click "Connect"

4. You can then view the encryption in SQL Server:

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

If it is encrypted, "True" is displayed, otherwise "FALSE" is displayed

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

Chapter 1 Securing Your Server and Network (5): Encrypt session with SSL