If you specify the certificate that should be used for TLS using SQL Server, then the Windows SQL Server service should read the certificate and the private key (file from the %ProgramData%\Microsoft\Crypto\RSA\MachineKeys folder) that corresponds to the certificate. The problem is this: SQL Server Configuration Manager is not very convenient and does not do all the necessary work .
Therefore, you should first localize the account used by SQL Server. You need to start services.msc , find the SQL Server service account. Typically, this is a built-in account, for example Local System , Network Service local account or domain account, for example .\SQLServer , DOMAIN\SQLServerAccount or a service account, for example NT Service\NT Service\MSSQL$SQL2012 in the figure below:
To grant permission for the private key for the account, you can use the mmc certificate snap-in. You can run mms.exe , select "Add / Remove Snap-in" in the "File" menu, select "Certificates" of the snap-in and select "Computer Account" on the local computer. Then, select the SSL certificate of the personal store, and then use the "Manage Private Keys ..." context menu.
and add an account of the type NT Service\NT Service\MSSQL$SQL2012 , found above, and set the "Read" permission of the account in a private key:
If you want to establish a connection to the SQL server within the domain (both the client and the server must belong to the same Active Directory or to directories connected through trust management), you need to create an SPN for the SQL server. If I understand your requirements correctly, you want to allow to delete the connection to SQL Server via HTTPS. You must have active mixed security in order to be able to connect to the server using SQL Server authentication:
After creating SQL Login, all of the above has changed and restarted the SQL Server service, you can establish a TLS (encrypted) connection to the SQL server. If you try to connect through a Windows account without creating an SPN, you will receive an error earlier:
A connection to the server was successfully established, but then an error occurred during the login process. (provider: SSL provider, error: 0 - invalid member name). (Microsoft SQL Server, error: -2146893022)
Invalid member name
If you forgot to change Windows authentication to mixed authentication (), then you will get an error, for example
Login failed for user "OlegKi". (Microsoft SQL Server, Error: 18456)
If all the above steps can be established, for example, a TLS connection using SQL Management Studio, but you still need to select some parameters:
Need to check "Encrypt connection"
and set the additional connection property TrustServerCertificate=true
Commonly used is Encrypt=true;TrustServerCertificate=true; as part of a connection string in an application that establishes a connection to an SQL server. We set the Encrypt=true property using the "Encrypt connection" checkbox described above. You can read more about the meaning of properties and various combinations of parameters in the "Enabling Encryption" section of the MSDN article .
If you do all of the above steps and check the "Encrypt connection" without setting the TrustServerCertificate=true property, then you will receive an error message:
A connection to the server was successfully established, but then an error occurred during the login process. (provider: SSL provider, error: 0 - invalid member name). (Microsoft SQL Server, error: -2146893022)
Invalid member name
which I already described above in a slightly different situation (connecting to a Windows account).
I described all the above steps, because the configuration of the TLS connection to the server is really not so simple, and you can get strange errors that the direct description does not give direct tips on how to fix the problem.