SSL certificate unbinding from SQL Server instance

Question

Hello All, In our environment, we have SQL Server instances using SSL server certificate. Applications connect to database instances using forced encryption. Offlate, we have found a few cases where the application team have reported that the certificate was automatically unlinked from the SQL Server instance. This resulted in outage and they raised incidents for us to look into it. Has anyone experienced something similar ? What could be the possible reason why the certificate gets unbind from the instance. The fix was to login to the server and re-bind the certificate using SQL Server configuration manager. Does removing/clearing a certificate create any events in windows event logs/ sql error logs that can be captured to understand who or what process/tool did the unbinding ? We have also checked the expiration date on the certificate and it is very much valid and long before it expires. The database engine service account also has the required permissions ( read ) on the certificate. Inputs appreciated. thanks and regards, S

Answer 1

Hey @SS2021

To summarize,

Certificate is unlinking from the SQL Server instance causing outages.

You rebind it with the SQL Server Configuration manager, you've validated

1. Expiration date

since the binding was successful I'll assume these have been validated too:

1. The certificate has to have a Private Key
2. The certificate has to be Trusted
3. The certificate needs to be created for Server Authentication

It is possible that Reporting Service Configuration Manager is removing the HTTPS binding due to an error in the configuration, if that is the case you can look up Program Files\Microsoft SQL Server\MSRS.<instance>\Reporting Services\LogFiles and look for WriteCallBack() or Failed with win32 error.

I would also clean any URL reservations tied to it, as well as all the SSL reservations (you can accomplish it by following this blog guidance http://thinknook.com/ssrs-ssl-certificate-nightmare-2011-06-28/)

Assuming everything is cleaned, take a backup of rsreportserver.config verify which certificates are referenced in <CertificateHash> under binding look at MaxConnections and TimeOut and <CacheLevel>.

If it looks to add up with what you've configured, stop 'Reporting Services' and start 'Reporting Services' again.

Re-bind your URLs starting with the Web Service and then Report Manager URL.

This should give you a clean re-binded Reporting Services.

As for Windows Event Logs the logs have to be logging, this is not always the case, meaning you'll have to turn them on if they haven't been.
For logs in SQL you'd find the most relevant (yet not read friendly) here "Program Files\Microsoft SQL Server\MSRS.<instance>\Reporting Services\LogFiles"

Hope this helps

all the best,
Ulv