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

i created self-signed certificate , turned encryption on in sql server 2014:

the problem sql server service won't start:

this article 2010 identifies problem permissions issue: sql server service not have necessary permission read ssl cert's private key.

the problem stuck on step 4 of solution proposed in article:

there no group or user name matching proposed format when bring window shown in article.

is there way can determine account sql server service runs under, can give permissions read ssl cert?

an entirely different solution welcome too.

if specify certificate, should used tls sql server, sql server windows service have read certificate and private key (the file folder %programdata%\microsoft\crypto\rsa\machinekeys), corresponds certificate. problem is: the sql server configuration manager in not comfortable , makes not required work.

thus first of 1 should localize account used sql server. 1 should start services.msc, find account of sql server service. it's typically build-in account local system, network service local or domain account .\sqlserver, domain\sqlserveraccount or service account nt service\nt service\mssql$sql2012 on picture below:

to grant permission on private key account 1 can use certificate snap-in of mmc. 1 can start mms.exe, choose "add/remove snap-in" in "file" menu, choose "certificates" snap-in , choose "computer account" of local computer. 1 should select ssl certificate of personal store , use context menu "manage private keys...".

and add account nt service\nt service\mssql$sql2012, found above, , set "read" permission account on private key:

if establish connection sql server inside of domain (both client , server have belong same active directory or directories connected via trust) 1 should create spns sql server. if correctly understand requirements, want allow remove connection sql server on https. 1 have active mixed security able connect server via sql server authentication:

after creating sql login, making above changed , restarting sql server service 1 able establish tls (encrypted) connection sql server. in case of attempting connect via windows account without creating spn 1 error:

a connection established server, error occurred during login process. (provider: ssl provider, error: 0 - target principal name incorrect.) (microsoft sql server, error: -2146893022)

the target principal name incorrect

if 1 forget change windows authentication mixed authentication () 1 error like

login failed user 'olegki'. (microsoft sql server, error: 18456)

if above steps done 1 can establish tls connection using sql management studio example, 1 still have choose options:

one should check "encrypt connection"

and set additional connection property trustservercertificate=true

typically 1 use encrypt=true;trustservercertificate=true; part of connection string in application establish connection sql server. set encrypt=true property checkbox "encrypt connection" describe above. more detailed meaning of properties , different combinations of options can read in "enabling encryption" section of the msdn article.

if 1 above steps , check "encrypt connection" without setting trustservercertificate=true property 1 error:

a connection established server, error occurred during login process. (provider: ssl provider, error: 0 - target principal name incorrect.) (microsoft sql server, error: -2146893022)

the target principal name incorrect

which described above in little situation (connection windows account).

i described above steps because configuration of tls connection server not easy , 1 can strange errors, direct description gives no direct tips how fix problem.