如何设置和使用加密SQL Server连接

As the standard for securing the host-server interaction, Secure Sockets Layer or SSL is implemented in a Web environment. However, the SSL can provide the encrypted connection and data transfer between a particular SQL Server instance and a client application. A trusted SSL certificate validates the SQL Server instance when the client application requests encrypted connection (or vice versa), while the SQL Server must be configured to follow the certificate authority (CA). This means that a certificate must be “signed” by a trusted source.

作为保护主机-服务器交互的标准， 安全套接字层或SSL在Web环境中实现。 但是，SSL可以提供特定SQL Server实例与客户端应用程序之间的加密连接和数据传输。 当客户端应用程序请求加密的连接（反之亦然）时，受信任的SSL证书将验证SQL Server实例，而必须将SQL Server配置为遵循证书颁发机构 （CA）。 这意味着证书必须由可信来源“签名”。

Generally, trusted sources are specialized companies (digital certificates vendors). However, self-signed certificates can be sufficient in SQL Server environment, as long as this kind of certificate follows the specified requirements (which are out of the scope of this article).

通常，可信来源是专业公司（数字证书供应商）。 但是，只要这种证书符合指定的要求 （不在本文的范围之内），则自签名证书在SQL Server环境中就足够了。

The main reason why a connection between SQL Server instance and any client application should be encrypted is authorized communication between them. This security layer can prevent unwanted sensitive data leak, or exclude the possibility for any SQL injection attack, e.g.

应当对SQL Server实例与任何客户端应用程序之间的连接进行加密的主要原因是它们之间的授权通信 。 该安全层可以防止不必要的敏感数据泄漏，或排除任何SQL注入攻击的可能性，例如

先决条件和必要步骤 (Prerequisites and necessary steps)

These are the steps to utilize the SSL encryption, which will be thoroughly explained separately:

这些是利用SSL加密的步骤，将分别进行详细说明：

• Check the prerequisites for certificate management and usage
  检查证书管理和使用的先决条件
• Review existing trusted SSL certificate from certification stores (local machine or current user)
  查看证书存储（本地计算机或当前用户）中现有的受信任SSL证书
• Configure SQL Server protocols for a desired SQL Server instance and enable encryption forcing option
  为所需SQL Server实例配置SQL Server协议并启用加密强制选项

先决条件 (Prerequisites)

In order to ensure that certificate management and encrypted connection configuration will be successful, as prerequisites, several options in Local Group Policy Editor (within current user) need to be enabled/disabled.

为了确保证书管理和加密连接配置将成功，作为先决条件，需要启用/禁用本地组策略编辑器 （当前用户内）中的多个选项。

To open Local Group Policy Editor, click

+ R (Run) and type gpedit.msc. Alternatively, seek for it in Windows 10 search, under the full title.

要打开本地组策略编辑器 ，请单击 + R （运行），然后输入gpedit.msc 。 或者，在Windows 10搜索中以完整标题查找。

Expand the User Configuration item and the Administrative Templates, and click the Windows components folder:

展开“ 用户配置”项和“ 管理模板” ，然后单击Windows组件文件夹：

Click on the folder Microsoft Management Console and right-click the setting as shown below (Restrict the user from entering author mode), and choose Edit option:

单击文件夹Microsoft Management Console并右键单击如下所示的设置（“ 限制用户进入作者模式” ），然后选择“ 编辑”选项：

The dialog for enabling/disabling the particular setting will appear. Disable this setting and confirm:

出现启用/禁用特定设置的对话框。 禁用此设置并确认：

When disabled, this setting allows the current user to configure the Management Console.

禁用后，此设置允许当前用户配置管理控制台。

Next step is to choose Restricted/Permitted snap-ins:

下一步是选择“ 受限/允许的管理单元” ：

Enable settings marked above (Certification authority, Certificates and Computer management), to ensure the current user’s authority.

启用上面标记的设置（ 证书颁发机构 ， 证书和计算机管理 ），以确保当前用户的权限。

查看当前用户存储中的现有证书 (Review the existing certificate from the current user store)

To check the existing certificates within local computer, run the following pre-defined Microsoft Management Console snap-ins:

要检查本地计算机中的现有证书，请运行以下预定义的Microsoft管理控制台管理单元：

• certlm.msc – certificates from the local machine store certlm.msc –本地计算机存储中的证书
• certmgr.msc – certificates related to a current user (which will be used in this article) certmgr.msc –与当前用户相关的证书（将在本文中使用）

Note
Execute these snap-ins from command line or PowerShell with administrative privileges (right-click → Run as administrator), in order to confirm root certificate authority.

注意
从命令行或PowerShell以管理特权（右键单击→以管理员身份运行）执行这些管理单元，以确认根证书颁发机构。

Open certmgr.msc snap-in, and seek for the Trusted Root Certification Authorities/Certificates folder.

打开certmgr.msc管理单元，并找到“ 受信任的根证书颁发机构 / 证书”文件夹。

In this case, we will use the certificate marked above (name of this certificate represents the machine name).

在这种情况下，我们将使用上面标记的证书（此证书的名称代表计算机名称）。

Let’s look at the properties of chosen certificate (double-click it to open), in order to the requirements which must be met in order to embed appropriate certificate to the SQL Server configuration. The strong requirement for using certificates is that they are placed in one of the mentioned certificate stores, because of validity and trust.

让我们看一下所选证书的属性（双击将其打开），以便将适当的证书嵌入到SQL Server配置中必须满足的要求。 使用证书的强烈要求是，由于有效性和信任度，它们必须放置在上述证书存储区之一中。

In Details tab, information like version, signature algorithm, enhanced key usage and many other are present.

在“ 详细信息”选项卡中，显示了诸如版本，签名算法，增强的密钥用法等信息。

The first thing that should be confirmed is CN, which stands for Common Name, or in other words, issuer – it must be fully qualified domain name of the machine (in this case, SLAV). In needed moment, the certificate must be available for use (Valid from and Valid to).

首先要确认的是CN ，它代表通用名称，或换句话说， 颁发者 –它必须是计算机的完全合格域名 （在本例中为SLAV）。 在需要的时候，证书必须可供使用（ 有效期自和有效期至 ）。

Enhanced Key Usage field should have value displayed in the picture below, in order to fulfill the main purpose of this certificate – authentication of the SQL Server instance.

为了实现此证书的主要目的-SQL Server实例的身份验证， 增强的密钥用法字段在下图中应显示值。

Next, click on Edit properties… button. Choose Enable only the following purposes option, and, optionally, set the friendly name (Administrator, e.g.). Confirm the changes.

接下来，单击“ 编辑属性...”按钮。 选择“ 仅启用以下用途”选项，并选择设置友好名称（例如Administrator ）。 确认更改。

配置SQL Server以使用加密连接 (Configure SQL Server to use encrypted connection)

Open SQL Server Configuration Manager, expand SQL Server Network configuration, choose Protocols properties for a desired SQL Server instance (in this case, it is a default instance). Enable ForceEncryption option in Flags tab,

打开“ SQL Server配置管理器” ，展开“ SQL Server网络配置”，为所需SQL Server实例（在本例中为默认实例）选择“协议”属性。 在[标记]标签中启用ForceEncryption选项，

and choose the certificate from drop-down menu:

并从下拉菜单中选择证书：

Confirm the settings and restart the SQL Server service to apply changes.

确认设置，然后重新启动SQL Server服务以应用更改。

常见问题 (FAQs)

Q: Can I check whether connection is already encrypted for a certain SQL Server instance?

问：我可以检查某个SQL Server实例的连接是否已经加密吗？

A: Yes, with this customized PowerShell script:

答：是的，使用此自定义的PowerShell脚本：

 
# First part of the script which creates dialog and forms, and hold input in the textbox
#
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
 
$dialog = New-Object System.Windows.Forms.Form 
$dialog.Text = "Enter SQL Server instance name:"
$dialog.Size = New-Object System.Drawing.Size(400,100) 
$dialog.StartPosition = "CenterScreen"
 
$check = New-Object System.Windows.Forms.Button
$check.Location = New-Object System.Drawing.Size(250,20)
$check.Size = New-Object System.Drawing.Size(75,23)
$check.Text = "Check"
$check.Add_Click({$x=$input.Text;$dialog.Close()})
$dialog.Controls.Add($check)
 
$input = New-Object System.Windows.Forms.TextBox 
$input.Location = New-Object System.Drawing.Size(40,20) 
$input.Size = New-Object System.Drawing.Size(200,20) 
$dialog.Controls.Add($input) 
 
$dialog.Add_Shown({$dialog.Activate()})
[void] $dialog.ShowDialog()
 
$x 
 
#Second part of the script, which executes specific SQL statement and passes result in pop-up dialog
#
$script = Invoke-Sqlcmd -Query "SELECT DISTINCT encrypt_option 
FROM sys.dm_exec_connections WHERE session_id = @@SPID" -ServerInstance $input.Text
$wshell = New-Object -ComObject Wscript.Shell
$wshell.Popup($script.ItemArray,0,"Connection encryption enabled for instance " + $input.Text + ":")
 
 

After executing, the following dialog will appear:

执行后，将出现以下对话框：

To get valid information, input a desired SQL server instance name in the corresponding form, like shown above.

要获取有效信息，请以相应的形式输入所需SQL Server实例名称，如上所示。

Note
To input the correct server name: use (local) or local/domain host name for a default SQL Server instance, and for the named instance use domain\server_name format (DB1\TestEnvironment, e.g.)

注意
要输入正确的服务器名称：对于默认SQL Server实例 ，请使用（本地）或本地/域主机名，对于命名实例，请使用domain \ server_name格式（例如，DB1 \ TestEnvironment）

In this case, we will query a default SQL Server instance, with enabled connection encryption. After input, click Check button, and result in next dialog will show TRUE or FALSE value:

在这种情况下，我们将查询具有启用的连接加密的默认SQL Server实例。 输入后，单击检查按钮，下一个对话框的结果将显示TRUE或FALSE值：

Q: Is installation of system root certificates in Windows required?
A: No, Windows has built-in certificates. However, users are allowed to add as many certificates as they need and use them only, as long as they are trusted, issued by CAs.

问：是否需要在Windows中安装系统根证书？
答：否，Windows具有内置证书。 但是，允许用户根据需要添加任意数量的证书，并仅在由CA颁发的信任证书中使用它们。

Q: Is it possible to use self-signed certificates for this purpose?

问：是否可以为此目的使用自签名证书？

A: Yes, it is, but self-signed certificates have significantly lower level of security, and that is the reason they are recommended only for testing purposes.

答：是的，但是自签名证书的安全级别明显较低，这就是为什么仅将它们推荐用于测试目的的原因。

Q: Is it possible to install trusted certificate from current user certificate store in order to encrypt connection with remote SQL Server?

问：是否可以从当前用户证书存储区安装受信任的证书，以加密与远程SQL Server的连接？

A: Yes, if that certificate is imported to the Trusted Root Certification Authorities/Certificates folder on the remote machine, SQL Server instance protocols configuration has enabled ForceEncryption option, along with embedded certificate and remote access to that SQL Server instance is granted.

答：是的，如果将该证书导入到远程计算机上的“受信任的根证书颁发机构/证书”文件夹，则SQL Server实例协议配置已启用ForceEncryption选项以及嵌入式证书，并且授予了对该SQL Server实例的远程访问权限。

Q: Can I manage my certificates with other methods than described here?

问：我可以使用此处所述以外的其他方法来管理证书吗？

A: Yes, all certificates can be managed within Internet Explorer (ran with administrator privileges). Go to Internet options -> Content tab -> Certificate button.

答：是的，所有证书都可以在Internet Explorer中进行管理（以管理员权限运行 ）。 转到Internet选项 -> 内容选项卡-> 证书按钮。

翻译自: https://www.sqlshack.com/how-to-set-and-use-encrypted-sql-server-connections/