We recently ran into below issue trying to change the SQL Server Service Account. SQL Server Service was running under local service account and the instance is configured for force encryption with certificate.
Issue : Unable to load user-specified certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXX”]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.
Resolution : To resolve the issue follow the below steps
- Grant read & write permission to the Service Account on to SQL Server Instance Folder (this is installation directory of the instance)
- open the Microsoft Management Console (MMC) and add the Certificates snap-in .
File -> Add/Remove Snap-in–>Double Click the Certificates, it will popup the Certificate snap-in window.
Chose the Computer account -and Click Next
From the Select Computer , choose local computer and click finish.
You will return to Add or Remove Snap-in, click OK
From the console, Expand Certificate (Local Computer) -> Personal -> Certificates and find the Imported certificate.
Select the imported certificate (Configured with SQL Server)–>right click –> All Tasks -> Manage Private Keys.
Click the Add button under the Group or user names list box.
- Add the SQL service account name and click OK.
- By default the service account will be given Full control & Read permissions. Deselect the Full control option as SQL Server service account only need to read the private key.
- Click Ok and Closed the MMC.
Now Try restarting the SQL Server Services with the service account.