Hi All,

I have SSRS 2012 service application setup in SP 2010 and everything is working fine.  However, I cannot get this setup to work in our production environment.

The setup in UAT is a bit different from the production.  The SQL database and SSRS are on the same box for UAT, but SQL and SSRS are on different servers in production.

When I try to create reporting services service application and point it to SSRS I keep getting error

A
network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was not accessible. Verify
that the instance name is correct and that SQL Server is configured to allow
remote connections. (provider: Named Pipes Provider, error: 40 - Could not open
a connection to SQL Server)

Troubleshoot
issues with Microsoft SharePoint Foundation.

Correlation ID:
fa80dc91-0521-4878-b108-4d7b9bc8cbac

Date and Time: 6/25/2015 12:59:33 PM

I think it could be related to SPN not being set.  I have never set a SPN before and I'm not sure which server and which account I will need to set the SPN on.

My understanding of this is that when a reporting services service application creates a database, SSRS will talk to SQL database server and creates required databases there, but uses SSRS itself to render reports.  Our UAT has SQL and SSRS setup on the same box and that's why there's no extra hop for authentication.

Can someone please shed some light on this?

Thanks.

Can you share ULS logs associated with Correlation ID. Try below:

http://itfreesupport.com/2014/12/sharepoint-2013-2010-uls-logging-to-troubleshoot/

Use UDL file and check  if you can connect to SQL server

Try to disable firewall on all server and check 

I'm guessing you want the reporting services installed in Integrated mode. SSRS installed in integrated mode, needs to be installed on the SharePoint server and not on the SQL server.

In other words:

Reporting Services should be on SharePoint Server.

Databases should/could be on SQL server.

SPN's are only if your using Kerberos authentication and even if  your domain is using Kerberos, SharePoint will fallback to NTLM if Kerberos is not working.

• Edited by Jesper Arnecke Friday, June 26, 2015 11:05 AM

I'm guessing you want the reporting services installed in Integrated mode. SSRS installed in integrated mode, needs to be installed on the SharePoint server and not on the SQL server.

In other words:

Reporting Services should be on SharePoint Server.

Databases should/could be on SQL server.

SPN's are only if your using Kerberos authentication and even if  your domain is using Kerberos, SharePoint will fallback to NTLM if Kerberos is not working.

• Edited by Jesper Arnecke Friday, June 26, 2015 11:05 AM

I'm guessing you want the reporting services installed in Integrated mode. SSRS installed in integrated mode, needs to be installed on the SharePoint server and not on the SQL server.

In other words:

Reporting Services should be on SharePoint Server.

Databases should/could be on SQL server.

SPN's are only if your using Kerberos authentication and even if  your domain is using Kerberos, SharePoint will fallback to NTLM if Kerberos is not working.

• Edited by Jesper Arnecke Friday, June 26, 2015 11:05 AM

Hi James,

For me this resolved the issue, please check - http://www.sswug.org/articlesection/default.aspx?TargetID=44331

Also, I believe yours is three-tier architecture. Let us know.

Thanks.

-Anuja

Hi,

Yes, we want to install reporting services in Integrated mode.  Here's what been done so far.

- SP2010 environment was upgraded with SP2 + Dec CU

- MS SQL server 2012 RS add-in for SharePoint installed

I'm stuck at creating SSRS service application when pointing to reporting server with the error message mentioned earlier.  Reporting server and SQL database server are NOT on the same box.

I have gone through all 7 steps http://www.sswug.org/articlesection/default.aspx?TargetID=44331 and none of them helped.  SharePoint, Reporting, and SQL database servers can communicate with each other via port 1433 and 80.  Firewall is DISABLED.  There is no firewall between these servers.

Reporting server is currently serving reports for MS Dynamics CRM and is working fine.  Our UAT is setup successfully, but the difference between UAT and PROD environments is that SQL database and reporting service are on the same box.

Can someone verify that the name of the database server within the service application should be the reporting server?  I tried using the SQL database server and was able to create the service application, but I couldn't configure anything within it so I think using SQL database instead of reporting server is incorrect?

I've also enabled verbose logging and I couldn't see anything useful at all.

Edited:  I've recreated the service application and pointed it to SQL database server and it got created successfully.  When I tried to go into it and click on system settings, I get error below.  Service application won't obviously create if I point it to reporting server.

System.Net.WebException: The remote server returned an error: (503) Server Unavailable.  

at System.Net.HttpWebRequest.GetResponse()   

at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout

• Edited by james2k8 1 hour 47 minutes ago adding text

Hi,

Yes, we want to install reporting services in Integrated mode.  Here's what been done so far.

- SP2010 environment was upgraded with SP2 + Dec CU

- MS SQL server 2012 RS add-in for SharePoint installed

I'm stuck at creating SSRS service application when pointing to reporting server with the error message mentioned earlier.  Reporting server and SQL database server are NOT on the same box.

I have gone through all 7 steps http://www.sswug.org/articlesection/default.aspx?TargetID=44331 and none of them helped.  SharePoint, Reporting, and SQL database servers can communicate with each other via port 1433 and 80.  Firewall is DISABLED.  There is no firewall between these servers.

Reporting server is currently serving reports for MS Dynamics CRM and is working fine.  Our UAT is setup successfully, but the difference between UAT and PROD environments is that SQL database and reporting service are on the same box.

Can someone verify that the name of the database server within the service application should be the reporting server?  I tried using the SQL database server and was able to create the service application, but I couldn't configure anything within it so I think using SQL database instead of reporting server is incorrect?

I've also enabled verbose logging and I couldn't see anything useful at all.

Edited:  I've recreated the service application and pointed it to SQL database server and it got created successfully.  When I tried to go into it and click on system settings, I get error below.  Service application won't obviously create if I point it to reporting server.

System.Net.WebException: The remote server returned an error: (503) Server Unavailable.  

at System.Net.HttpWebRequest.GetResponse()   

at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout

• Edited by james2k8 Wednesday, July 01, 2015 5:49 AM adding text

Hiya,

Your absolutely right, the Report server = SharePoint server. So when creating the Service Application you point it to the SQL Database server. Think of this as creating any other SharePoint service application.

Hi,

Just tried this and couldn't get the reports to display.  I don't know much about reporting services so I've asked the guy who does to help me out.  He added /documents/xxxx.rdl under the report option but we cannot see the graphs.  This works fine in UAT.

With using SQL database server within service application.  Is this SQL server that hosts SharePoint databases or SQL server which hosts dynamics CRM?  Our SSRS pulls data from SQL that hosts dynamics CRM of course.

So when you create the service application do you get any errors?

Do you have all the prerequisites installed? (Reporting services add-in on SP server?

When you create your reporting services, service application. It creates configuration databases for reporting services. These databases are SharePoint related and not related to your data in anyway.

So when your reporting services is working. Try to create a Test site, using the BI template and test using the Report Example located in the document library.

If that works, it's your reporting services connection file that is not configured correctly.

Hi,

Service application is created with no errors.  Databases are also created fine within SP.  I've created a test site with BI template and asked the CRM team to configure the reporting bit for me.  I would like to get to the point where my work here is done so that I can hand the rest over to the CRM team.  Unfortunately, their argument is that the whole thing sits within SP so this kind of falls into my space, even though I think everything that is required has already been done.

Is there anything else I could look for to see why the report isn't rendering?

Thanks.

Hello James, 

You are right when you specified SQL Server for creating the database. 

However, we need to follow additional steps in configuring Report Server Integration with SharePoint, After database is created. Below are some links which talk about these configurations.

https://technet.microsoft.com/en-us/library/bb326213%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

http://www.codeproject.com/Articles/88285/Integrating-Sharepoint-and-SQL-Reporting-Serv

Thanks,

Ved

Hi Ved,

Thanks for the links.  What you've provided me with is for SSRS 2008.  I'm after assistance on SSRS 2012.  I believe the two versions are not the same.

James.