如何授予 SQL Server 读取 SSL 密钥的权限?

我最近创建了一个自签名证书并在 SQL Server 2014 中打开了加密:

问题是现在SQL Server服务无法启动:

当我打开文章中显示的窗口时，没有与建议格式匹配的组或用户名.

是否有其他方法可以确定运行 SQL Server 服务的帐户，以便我可以授予它读取 SSL 证书的权限?

也欢迎采用完全不同的解决方案.

解决方案

如果你指定了证书，SQL Server 应该使用该证书，那么 SQL Server windows 服务必须读取证书和私有证书密钥(文件夹 %ProgramData%\Microsoft\Crypto\RSA\MachineKeys 中的文件)，与证书对应.问题是:SQL Server 配置管理器不舒服，无法完成所有必需的工作.

因此，首先应该本地化 SQL Server 使用的帐户.一要启动services.msc，找到SQL Server服务的账号.它通常是一个内置帐户，例如 Local System、Network Service 本地或域帐户，例如 .\SQLServer、DOMAIN\SQLServerAccount 或下图中的 NT Service\NT Service\MSSQL$SQL2012 之类的服务帐户:

要授予帐户私钥的权限，可以使用 mmc 的证书管理单元.可以启动mms.exe，在文件"菜单中选择添加/删除管理单元"，选择证书"管理单元，选择本地计算机的计算机帐户".然后选择个人存储的 SSL 证书，然后使用上下文菜单管理私钥...".

并添加像上面找到的NT Service\NT Service\MSSQL$SQL2012这样的帐户，并在私钥上为该帐户设置读取"权限:

如果您想与域内的 SQL 服务器建立连接(客户端和服务器都必须属于同一个 Active Directory 或通过信任连接的目录)，那么应该为SQL 服务器.如果我正确理解您的要求，您希望允许删除通过 HTTPS 连接到 SQL Server.必须激活混合安全才能通过 SQL Server 身份验证连接到服务器:

创建 SQL 登录后，将上述所有内容更改并重新启动 SQL Server 服务，将能够与 SQL Server 建立 TLS(加密)连接.如果在之前未创建 SPN 的情况下尝试通过 Windows 帐户进行连接，则会出现错误:

<块引用>

已成功与服务器建立连接，但随后出现登录过程中发生错误.(提供者:SSL 提供者，错误:0 - 目标主体名称不正确.)(Microsoft SQL服务器，错误:-2146893022)

目标主体名称不正确

如果忘记将 Windows 身份验证更改为混合身份验证 ()，则会出现类似错误

<块引用>

用户OlegKi"登录失败.(Microsoft SQL Server，错误:18456)

如果以上所有步骤都完成了，例如可以使用 SQL Management Studio 建立 TLS 连接，但仍然需要选择一些选项:

应该勾选加密连接"

并设置附加连接属性 TrustServerCertificate=true

通常使用 Encrypt=true;TrustServerCertificate=true; 作为应用程序中连接字符串的一部分，用于建立与 SQL 服务器的连接.我们通过上面描述的加密连接"复选框设置 Encrypt=true 属性.可以在

我在上面已经在另一种情况下(与 Windows 帐户的连接)进行了描述.

我描述了以上所有步骤，因为配置到服务器的TLS连接真的不是那么容易，并且可能会出现奇怪的错误，直接描述没有直接提示如何解决问题.

I recently created a self-signed certificate and turned encryption on in SQL Server 2014:

The problem is that now the SQL Server service won't start:

This article from 2010 identifies the problem as a permissions issue: The SQL Server service does not have the necessary permission to read the SSL cert's private key.

The problem is that I am stuck on step 4 of the solution proposed in the article:

There is no group or user name matching the proposed format when I bring up the window shown in the article.

Is there another way I can determine the account that SQL Server service runs under, so that I can give it permissions to read the SSL cert?

An entirely different solution is welcome too.

解决方案

If you specify the certificate, which should be used for TLS by SQL Server, then the SQL Server windows service have to read the certificate and the private key (the file from the folder %ProgramData%\Microsoft\Crypto\RSA\MachineKeys), which corresponds the certificate. The problem is: the SQL Server Configuration Manager in not comfortable and it makes not all the required work.

Thus first of all one should localize the Account used by SQL Server. One should start services.msc, find the account of SQL Server service. It's typically a build-in account like Local System, Network Service a local or domain account like .\SQLServer, DOMAIN\SQLServerAccount or an service account like NT Service\NT Service\MSSQL$SQL2012 on the picture below:

To grant permission on the private key to the account one can use Certificate Snap-In of mmc. One can start mms.exe, choose "Add/Remove Snap-in" in the "File" menu, choose "Certificates" Snap-in and to choose "Computer account" of the Local computer. Then one should select the SSL certificate of Personal store and then use context menu "Manage Private Keys...".

and to add account like NT Service\NT Service\MSSQL$SQL2012, found above, and to set "Read" permission to the account on the private key:

If you would like to establish connection to the SQL server inside of the domain (both the client and the server have to belong to the same Active Directory or to the directories connected via the trust) then one should to create SPNs for the SQL server. If I correctly understand your requirements, you want to allow remove connection to SQL Server over HTTPS. One have to active mixed security to be able to connect to the server via SQL Server Authentication:

After creating SQL Login, making all above changed and restarting SQL Server service one will be able to establish TLS (encrypted) connection to the SQL server. In case of attempting to connect via Windows Account without creating SPN previously one get the error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)

The target principal name is incorrect

If one forget to change Windows Authentication to Mixed authentication () then one will get the error like

Login failed for user 'OlegKi'. (Microsoft SQL Server, Error: 18456)

If all above steps done one can establish TLS connection using SQL Management Studio for example, but one still have to choose some options:

One should check "Encrypt connection"

and to set additional connection property TrustServerCertificate=true

Typically one use Encrypt=true;TrustServerCertificate=true; as the part of connection string in the application which establish the connection to SQL server. We set Encrypt=true property by the checkbox "Encrypt connection" describe above. More detailed about the meaning of the properties and different combinations of the options can be read in "Enabling Encryption" section of the MSDN article.

If one do all the above steps and check "Encrypt connection" without setting TrustServerCertificate=true property then one will get the error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)

The target principal name is incorrect

which I already described above in a little another situation (connection with Windows account).

I described all above steps because configuration of TLS connection to the server is really not so easy and one can get strange errors, which direct description gives no direct tips how to fix the problem.