Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 2)

Sat, 30 Jun 2012 19:44:36 +0000

You can visit Part 1 of this troubleshooting series by clicking here.

During our configuration of Reporting Services for SharePoint I ran into another common issue.

I consulted MSDN once again and followed the directions given at this MSDN article. Even after following all these instructions, when I would attempt to create a new BI Semantic Model Connection in the SharePoint library, I would receive the following error:

Cannot connect to the server or database.

Even after adding the Reporting Services service account to the SSAS Tabular instance as a server administrator, we still hit this error.

Once again I found little documentation out there as I was troubleshooting, presumably because SQL Server 2012 is still quite new. Here’s what we did to fix our problem:

The Analysis Services service must be running under a domain account. When I installed SSAS, I took all the defaults for the server installation. The SSAS service was running under some default built-in account which ended up being the problem.

Once we switched this to run under a domain account, I was able to create BI Semantic Model Connections using this library document type with no issues.

Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 1)

Sat, 30 Jun 2012 19:06:39 +0000

I recently worked with our SharePoint administrator to install SQL Server 2012 Reporting Services in SharePoint Integrated Mode to take advantage of PowerView. By following the installation instructions found at this MSDN article, we were able to painlessly install both Reporting Services and the Reporting Services Add-in from the SQL Server installation media.

Then began the configuration process. Since everyone’s environment is different, I found it difficult to troubleshoot some of the odd behavior that we were running in to. In Part 1 of this post I want to focus on what we did to overcome a specific error we were receiving:

After installation, I created a Business Intelligence Center site without any trouble. In the Connections library, I would attempt to add a Report Data Source and receive a SharePoint page that detailed the following error:

This SQL Server Reporting Services (SSRS) functionality is not supported. Use Central Administration to verify and fix one or more of the following issues:

A report server URL is not configured. Use the SSRS Integration page to set it.
The SSRS service application proxy is not configured. Use the SSRS service application pages to configure the proxy.
The SSRS service application is not mapped to this web application. Use the SSRS service application pages to associate the SSRS service application proxy to the web application.

Other times, I would receive a little bit more of a generic message saying something to the effect of “This SQL Server Reporting Services (SSRS) functionality is not supported” or “Unsupported Reporting Services Functionality”.

As it turns out, our specific problem was unrelated to any three of those bullet points.

Our solution?

Make sure to install the "Reporting Services Add-in for SharePoint Products” to all Web Front-End (WFE) nodes of your SharePoint farm.

On the Feature Selection screen of SQL Server setup, you can simply choose “Reporting Services Add-in for SharePoint Products” on your other WFE nodes. You don’t need to install the Database Engine Services or Reporting Services – SharePoint all over again…just the Add-in.

We began to suspect it was something related to missing components on the other nodes because as I was troubleshooting, I noticed that if I attempted to create a Report Data Source enough times, I would be able to proceed to different stages of the creation process. Sometimes I would hit the error right away. Sometimes I would get to the page where I could fill out the connection details but when I would attempt to save the data source I would get one of the error pages described above.

After we installed the Reporting Services Add-in on all WFE nodes, the problem stopped and we have not experienced any difficulty since.

Reporting Services on Andrew Bancroft

Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 2)

Troubleshooting SQL Server 2012 Reporting Services and SharePoint 2010 Integration (Part 1)