In one of the remote sites, users started complaining that they are unable to access SCCM reports. These were the users who regularly accessed SCCM reports to pull out data. Similar issues started being reported from other sites. So basically something was wrong with SSRS. Since everything was working fine, my wild guess was ConfigMgr SQL Server identification certificate would have expired. When I accessed the reports using browser i got the below error.
- 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.)
Seems like the last line reveals exactly why the SCCM reports were inaccessible. Furthermore I logged in to Configuration Manager server where SSRS was 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.
ConfigMgr SQL Server Identification Certificate Expired
In one of my old post I had shown how to create configmgr sql server identification certificate. I will be using similar steps in this post. First of all I had to verify whether the certificate was actually expired. Yes the certificate had expired. I deleted this expired certificate from personal store.
To generate a new ConfigMgr SQL server identification certificate, open the IIS management console. Select the server then double click Server Certificates.
Probably this step is more important. Launch SQL Server Configuration Manager. Expand SQL Server Network Configuration, right click Protocols for MSSQLSERVER and click Properties. Select the Certificate tab and use the drop down 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 do the above steps you should see the new certificate under server certificates. You can now click SQL server services and restart the services once. The reports worked fine after i performed these steps.