Recently I encountered issues on my Microsoft Endpoint Configuration Manager (MECM) server and especially its management point role regarding some certificate issues.

By accident I deleted the SQL Server Identification Certificate. After a restart from the computer the SQL Server won’t start anymore and I encountered the following errors.


Unable to load user-specific certificate [Cert Hash(sha1) …… The server will not accept a connection. You should verify that the certificate is correctly installed.


To solve this issue we need to re-create the Self-Signed Certificate. This can be done either by using the Internet Information Services (IIS) console which is already installed on the site system server or by using a PowerShell Cmdlet.

If using the IIS console click on the server node and Server Certificates.


Click on Create Self-Signed Certificate …


Enter a friendly name for the certificate, I will use the origin friendly name which is ConfigMgr SQL Server Identification Certificate.

For the certificate store we can use the by default selected Personal store.

Click on OK.


The certificate is created.



As mentioned we can also use a PowerShell Cmdlet to re-create the Self-Signed Certificate as shown below.

New-SelfSignedCertificate -DnsName MECM.braintesting.de -FriendlyName “ConfigMgr SQL Server Identification Certificate” -CertStoreLocation “cert:LocalMachineMy”

Source: https://learn.microsoft.com/en-us/powershell/module/pki/new-selfsignedcertificate


Both ways will create our Self-Signed Certificate in the computers personal certification store.


Now finally we have to assign the certificate to the SQL Server. Right click on Protocols for MSSQLServer and select Properties.


Within the Certificate tab select the previously created certificate.


After applying the certificate we try to start the service.


Right click on SQL Server (MSSQLSERVER) and click on Start.


Looks good.




Links

Certificate Management (SQL Server Configuration Manager)
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16