In this article, I will show you how to fix the SCCM SQL Server identification certificate expired error. Upon resolving this error, you can access the SCCM reports again.
In one of the remote sites, reporting users started complaining they were unable to access SCCM reports. The reporting admins who regularly accessed SCCM reports to gather data were unable to perform their daily tasks.
Similarly, the same issue of reports not accessible were reported from multiple sites too. The reports were inaccessible from Configuration Manager console and even via SSRS web URL. This means there was something was wrong with SCCM Reporting Services and required attention.
Related: Create ConfigMgr SQL Server Identification Certificate
Troubleshooting SQL Server Identification Certificate Errors
Normally, when the ConfigMgr reports are inaccessible, the first troubleshooting step that you perform is to check the SQL services. If the SQL server reporting services are not running, SCCM reports cannot be accessed.
In my case, there were no issues with SQL services as they were working correctly. After performing multiple troubleshooting steps, I found that it was the SQL Server identification certificate that had expired.
When the SQL Server Identification Certificate expires, you cannot access the reports and the following errors are logged:
- An error has occurred during report processing. (rsProcessingAborted).
- Cannot create a connection to data source ‘ID5C6358F24BB64a1bA16E8D96795D8602’. (rsErrorOpeningConnection)
- A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The received certificate has expired.)
Recommended: List of Configuration Manager Log files for Troubleshooting
It seems like the last line, “The received certificate has expired” reveals exactly why the SCCM reports were inaccessible. Furthermore, I logged in to the Configuration Manager server where reporting services were running. I ran a random report right from the console and I got the below error.
An error has occurred during report processing. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. System.Exception: For more information about this error, navigate to the report server on the local server machine, or enable remote errors. Microsoft.ConfigurationManagement.ManagementProvider.SmsException. An error has occurred during report processing.
Fix SCCM SQL Server Identification Certificate Expired
Let’s look at the steps to fix the SCCM SQL Server identification certificate expired error. The first thing you need to do is delete the expired certificate and create a new one because the existing SQL Server identification certificate has expired. I started by removing the expired SQL server certificate from my personal store.
To delete the expired SQL Server identification certificate, run the command certlm.msc and go to Personal store. Select the SQL server identification certificate and click delete.
To generate a new ConfigMgr SQL server identification certificate, open the IIS management console. Select the server, then double click Server Certificates as illustrated below.
In the Create Self-Signed Certificate wizard, specify a friendly name for the certificate and select the certificate store as Personal. Click OK. Complete the remaining steps in the wizard.
Finally, click Start to launch the SQL Server Configuration Manager. Expand SQL Server Network Configuration, right-click Protocols for MSSQLSERVER, and click Properties. Select the Certificate tab and use the dropdown to select the self-signed certificate you created. You can double-check this by making sure the Issued By field matches the server name. Click OK.
Once you complete the above steps, you should see the new SQL server certificate under server certificates. You can now click SQL Server Services and restart the services once. After completing the above steps, all the reporting users were able to access the SCCM reports without any issues.
