問(wèn)題描述

我最近創(chuàng)建了一個(gè)自簽名證書(shū)并在 SQL Server 2014 中打開(kāi)了加密:

問(wèn)題是現(xiàn)在SQL Server服務(wù)無(wú)法啟動(dòng):

當(dāng)我打開(kāi)文章中顯示的窗口時(shí)，沒(méi)有與建議格式匹配的組或用戶(hù)名.

是否有其他方法可以確定運(yùn)行 SQL Server 服務(wù)的帳戶(hù)，以便我可以授予它讀取 SSL 證書(shū)的權(quán)限?

也歡迎采用完全不同的解決方案.

如果你指定了證書(shū)，SQL Server 應(yīng)該使用該證書(shū)，那么 SQL Server windows 服務(wù)必須讀取證書(shū)和私有證書(shū)密鑰(文件夾 %ProgramData%\Microsoft\Crypto\RSA\MachineKeys 中的文件)，與證書(shū)對(duì)應(yīng).問(wèn)題是:SQL Server 配置管理器不舒服，無(wú)法完成所有必需的工作.

因此，首先應(yīng)該本地化 SQL Server 使用的帳戶(hù).一要啟動(dòng)services.msc，找到SQL Server服務(wù)的賬號(hào).它通常是一個(gè)內(nèi)置帳戶(hù)，例如 Local System、Network Service 本地或域帳戶(hù)，例如 .\SQLServer、DOMAIN\SQLServerAccount 或下圖中的 NT Service\NT Service\MSSQL$SQL2012 之類(lèi)的服務(wù)帳戶(hù):

要授予帳戶(hù)私鑰的權(quán)限，可以使用 mmc 的證書(shū)管理單元.可以啟動(dòng)mms.exe，在文件"菜單中選擇添加/刪除管理單元"，選擇證書(shū)"管理單元，選擇本地計(jì)算機(jī)的計(jì)算機(jī)帳戶(hù)".然后選擇個(gè)人存儲(chǔ)的 SSL 證書(shū)，然后使用上下文菜單管理私鑰...".

并添加像上面找到的NT Service\NT Service\MSSQL$SQL2012這樣的帳戶(hù)，并在私鑰上為該帳戶(hù)設(shè)置讀取"權(quán)限:

如果您想與域內(nèi)的 SQL 服務(wù)器建立連接(客戶(hù)端和服務(wù)器都必須屬于同一個(gè) Active Directory 或通過(guò)信任連接的目錄)，那么應(yīng)該為SQL 服務(wù)器.如果我正確理解您的要求，您希望允許刪除通過(guò) HTTPS 連接到 SQL Server.必須激活混合安全才能通過(guò) SQL Server 身份驗(yàn)證連接到服務(wù)器:

創(chuàng)建 SQL 登錄后，將上述所有內(nèi)容更改并重新啟動(dòng) SQL Server 服務(wù)，將能夠與 SQL Server 建立 TLS(加密)連接.如果在之前未創(chuàng)建 SPN 的情況下嘗試通過(guò) Windows 帳戶(hù)進(jìn)行連接，則會(huì)出現(xiàn)錯(cuò)誤:

已成功與服務(wù)器建立連接，但隨后出現(xiàn)登錄過(guò)程中發(fā)生錯(cuò)誤.(提供者:SSL 提供者，錯(cuò)誤:0 - 目標(biāo)主體名稱(chēng)不正確.)(Microsoft SQL服務(wù)器，錯(cuò)誤:-2146893022)

目標(biāo)主體名稱(chēng)不正確

如果忘記將 Windows 身份驗(yàn)證更改為混合身份驗(yàn)證 ()，則會(huì)出現(xiàn)類(lèi)似錯(cuò)誤

用戶(hù)OlegKi"登錄失敗.(Microsoft SQL Server，錯(cuò)誤:18456)

如果以上所有步驟都完成了，例如可以使用 SQL Management Studio 建立 TLS 連接，但仍然需要選擇一些選項(xiàng):

應(yīng)該勾選加密連接"

并設(shè)置附加連接屬性 TrustServerCertificate=true

通常使用 Encrypt=true;TrustServerCertificate=true; 作為應(yīng)用程序中連接字符串的一部分，用于建立與 SQL 服務(wù)器的連接.我們通過(guò)上面描述的加密連接"復(fù)選框設(shè)置 Encrypt=true 屬性.可以在

我在上面已經(jīng)在另一種情況下(與 Windows 帳戶(hù)的連接)進(jìn)行了描述.

我描述了以上所有步驟，因?yàn)榕渲玫椒?wù)器的TLS連接真的不是那么容易，并且可能會(huì)出現(xiàn)奇怪的錯(cuò)誤，直接描述沒(méi)有直接提示如何解決問(wèn)題.

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.

這篇關(guān)于如何授予 SQL Server 讀取 SSL 密鑰的權(quán)限?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！