Search code examples
sql-serverwindows-services

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

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

self signed cert

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

startup error

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:

stuck on step 4

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

  • 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:

    enter image description here

    To grant permission on the private key to the account one can use Certificate Snap-In of mmc. One can start mmc.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...".

    enter image description here

    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:

    enter image description here

    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:

    enter image description here

    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

    enter image description here

    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)

    enter image description here

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

    enter image description here

    One should check "Encrypt connection"

    enter image description here

    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

    enter image description here

    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.