We’ve had an open issue with MS for a while now, regarding poor SharePoint-integrated SSRS performance,namely:
- Long delays when opening up integrated SSRS reports first thing in the morning (several minutes in some cases)
- Random delays when opening integrated SSRS reports throughout the day (several minutes in some cases)
When troubleshooting a replacement SSL certificate issue I stumbled across the solution.
Identification of the Issue
Certificate related issues can be logged by CryptoAPI 2.0 (CAPI2) Diagnostics, although this needs to be enabled as it is disabled by default.
First, enable CryptoAPI 2.0 Diagnostics logging using Event Viewer. Browse to Application and Services Logs | Microsoft | Windows | CAPI2 and left click “Operational,” select “Enable Log”.
In our case the event log quickly populated with error-level entries that all referred to timeouts checking CRLs. I was easily able to show that when running an SSRS report multiple timeout events were logged in the CAPI2 log.
Troubleshooting Steps
Initially I decided to try and disable CRL checking on our DEV environment following guides such as:
-
- http://support.microsoft.com/kb/2625048
- http://www.microtom.net/microsoft-sharepoint-products/how-to-make-sharepoint-faster
- http://tech.lanesnotes.com/2014/02/sql-server-reporting-services-service.html
- http://joelblogs.co.uk/2011/09/20/certificate-revocation-list-check-and-sharepoint-2010-without-an-internet-connection/
- http://blogs.msdn.com/b/spses/archive/2012/08/29/crl-verification-failure-sharepoint.aspx
- And most notably: http://blogs.msdn.com/b/chaun/archive/2014/05/01/best-practices-for-crl-checking-on-sharepoint-servers.asp
However, despite running through the above ‘solutions’ we were still getting errors in the CAPI2 log for crl.microsoft.com and the third-party CA CRL/OCSP points used by our SSL certificates.
Our Solution
Allow CRL Access
Configure perimeter firewalls to allow access to crl.microsoft.com and as equally important any CRL or OCSP loctaion for any third-party SSL certificate you are using.
If you cannot do this, then create HOSTS file entries for crl.microsoft.com and any third-party CRL that could be accessed, depending upon your SSL certificate configuration.
Trust the SharePoint STS Certificate
Use the following SharePoint Management Shell script (RunAs Administrator) to export the per-farm certificate:
{code lang:text showtitle:false lines:false hidden:false}$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export(“Cert”) | Set-Content C:\Temp\SharePointRootAuthority.cer -Encoding byte{/code}
Now, manually import to each member of the SharePoint Farm, under Trusted Root Certificate Authorities.
With these fixes in place integrated SSRS reports now open up near instantly in our SharePoint 2010 environment.
Of course, your mileage may vary, but CRL related issues and the associated impact on performance across .NET applications are well document – as you can see for the various links above.