How can I give SQL Server permission to read my SSL Key?

Question

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.

Solution 1

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.

Solution 2

One other note: If you are entering the certificate thumbprint into the registry manually by copying and pasting from the certificate manager, you must remove the leading character. It is an invisible unicode character, but it will cause the SQL Server service to be unable to start if it is present. This is in addition to making it ALL CAPS, and removing all spaces.

Comments

[Jonah]

Amazing answer! Wish I could upvote 10 times. I will try this out tomorrow.

[Oleg]

@Jonah: You are welcome! Please inform me about the final results. I updated my previous answer too. I hope you will be able to use the original SSL certificate too. The problem with self-signed certificate is the trust. One have to add it on all clients as Trusted Root certificate. Because of that one don't use the way in any real scenarios.

[eXPerience]

The part with "set "Read" permission to the account on the private key" helped in my case! I struggled for 9 days untill I tried to run the service from local system account and it worked, so I understood that my issue was about permissions. Then I found this solution! Upvote!