When trying to configure the SQL Reporting Services Add-in for SharePoint 2010 from Central Admin you might see the following error:

(Error Text: Failed to establish connection with report server. Verify the server URL is correct or review ULS logs for more information. …)

Most common cause: Kerberos

There are actually a few causes for this, and usually the ULS will show the reason pretty clearly, for instance most commonly I see the following:

w3wp.exe (0x11E4)        0x1460 SQL Server Reporting Services SOAP Client Proxy  0000 High Exception encountered for SOAP method GetSystemProperties: System.Net.WebException: The request failed with HTTP status 401: Unauthorized

In that case the cause is likely to be related to Kerberos, if you’re using Windows Authentication mode and have multiple servers in the SharePoint farm then that is where you should look. A good test to prove that is if it works without error using ‘Trusted Authentication’.

Alternate cause: SSL

However recently I have found a new type of issue that causes this, here’s the ULS extract;

An operation failed because the following certificate has validation errors:nnSubject Name: CN=*.something.com, [… … snip … …]nnErrors:nn The root of the certificate chain is not a trusted root authority..

In this case the ULS log is very helpful indeed as it seems that SharePoint maintains its own certificate store which does not contain any of the usual certificates.

So the solution to this one is quite easy, you simply need to obtain and install the appropriate certificate.

With credits to this blog Calling an SSL Web Service from SharePoint 2010 (For example LinkedIn) you can download the certificates directly from Verisign:

https://www.verisign.com/support/roots.html

In my case it was the GeoTrust certificate, if you’re not sure then view the certificate by clicking the Padlock icon in IE and under Certification Path you can view the certificate hierarchy to determine who your root certificate is.

Once you have your certificate then open Central Admin, and under Security – Manage Trust add the certificate using New. It should then look something like this:

Once that’s done go back to the SSRS integration and you should now hopefully be able to complete the integration without further errors.

Final Thoughts

This SSRS integration has always had problems in my experience, all that I can suggest if you are still having issues is double check:

• SQL Reporting Services 2008 should be installed with Service Pack 2 (or SP1 with CU8)
• SQL Reporting Services 2008 R2 in my experience works without any updates, however you might like to try the latest patches: both SSRS and the SharePoint add-in are updated in SQL 2008 R2 CU4 (http://support.microsoft.com/kb/2345451)

Hope that helps someone out there!