SharePoint 2010 - SQL Server 2012 Reporting Services Integration Steps (Multiple Server Farm)
Most SharePoint customers suffer no issues when integrating SQL Server Reporting Services 2012 (SSRS 2012) with SharePoint 2010 (SP 2010), I however, had quite a bit of trouble. So for those at home who may also be having some trouble, I thought I'd prescibe you some troubleshooting medicine along with a few spoonfuls of errors.
- Install SharePoint on the Reporting Services Server and connect that server up to the farm (using Configuration Wizard)
- Install the rsSharePoint.msi file provided by Microsoft on all your front-end servers running SharePoint.
- Run these two commands on all front-end servers running sharepoint
That's it! You should now be winning at SharePoint life. I did spend quite a bit of time on this, so if anyone has any questions, please feel free to comment.
The official steps you may have read about:
- Install SQL Server with Reporting Services Add-in for SharePoint- Install SharePoint on the Reporting Services Server and connect that server up to the farm (using Configuration Wizard)
- Install the rsSharePoint.msi file provided by Microsoft on all your front-end servers running SharePoint.
- Run these two commands on all front-end servers running sharepoint
- Install-SPRSService
- Install-SPRSServiceProxy
- Navigate to Central Administration > System Settings > Manage services on server. Start the SQL Server Reporting Services Service
- Navigate to Central Administration > Application Management > Manage Service Applications. Create a new SQL Server Reporting Services Service Application
----------------------------------------------------------------------------------------
Here's the issues you may come across and the solutions I found to the issues:
Issue 1:
"Install-SPRSService : The term 'Install-SPRSService' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again"
Solution:
You need to install the Reporting Services SharePoint Add-In (Found on your SQL Server 2012 installation media) on every server running SharePoint. Please don't use the rsSharePoint.msi provided on Microsoft's website, here's why....
----------------------------------------------------------------------------------------
Issue 2:
"When I try to create a SQL Server Reporting Services Service Application under 'Manage Service Applications', I receive an error relating to permissions to a registry key"
Solution:
There's nothing wrong with your registry except that SharePoint is looking for files that don't exist. This is because you most likely installed the Reporting Services SharePoint Add-In with the rsSharePoint.msi file that Microsoft provides on their website. That file is a cut-down version of what gets installed when you install the Add-In from the SQL Server 2012 installation media. Uninstall it from Control Panel, and re-install it with the SQL Server 2012 installation media.
Things to note:
- Your Windows servers running SharePoint will need to be updated to SP1 if WS2008R2, or SP2 if WS2008
- Trying to create the Service Application may have created the databases on your SQL Server but not deleted them. You will have to remove them manually (at your own risk!).
----------------------------------------------------------------------------------------
Issue 3:
"I've created my SQL Server Reporting Services Service Application, but when I try to access any of the links inside it (system settings for instance), I'm receiving a 503 unauthorised error message"
Solution:
This may vary from case to case, but for me it was because I ran these commands on every front end server:
- Install-SPRSService
- Install-SPRSServiceProxy
When in actual fact, depending on your scenario (SharePoint Standard Licensing I think is the cause), you should only run these scripts on the server that you wish to be running the Server Service. You will need to uninstall them by using the following scripts (remember to delete the application and stop the service in Central Admin first)
- Install-SPRSServiceProxy -uninstall
- Install-SPRSService -uninstall
That's it! You should now be winning at SharePoint life. I did spend quite a bit of time on this, so if anyone has any questions, please feel free to comment.
I am having issue here as well. I have a single server DEV box, I get everything set up, but when I try and click on System Settings, I get an access denied. My users are correct, so not sure what the issue is, any ideas?
ReplyDeleteHi James, strange one! Have you tried adding the users to the Service Application Permissions. It's not necessary, but might solve the issue.
ReplyDeleteThe only other explanation I can come up with is that maybe you are getting the Access Denied page because those user accounts don't have proper access to the Reporting Service Database that the System Settings page is retrieving the information from?
If it's just a DEV box, try giving the users SysAdmin access to the RS Database/s.
Cheers,
Brett
I have been working on this for 3 days and have tried everything you have suggested. What is weird, I can look through the logs even with verbose on SP foundation and the web app, don't show anything to pin down the issue either. I have gone as far as giving my 2 accounts I use on the box both SA and the RS roles from the script on the provisioning page.. Checked permissions in registry and to admin folder in 14 hive. I even have tried a complete re-install and get back to the same place and get access denied on system settings and the manage jobs pages.
DeleteSorry should have service app not web app.
DeleteI remember reading that certain Farm setups were incompatible, but can't find the link to give you.
ReplyDeleteI did find this link, which looks like a single server farm is compatible with a reporting services setup: http://technet.microsoft.com/en-us/library/gg492276.aspx
But maybe not SP2010 with RS2012 single server farm.
What about the RSConfig Files, have you made any changes to them? I remember having to modify a setting in the RSReportServer.config file.
Sorry I'm a bit short on specifics, but hopefully I might push you in the right direction..
Well, day 4 :)-- I have once again retried everything, being sure to uninstall using powershell ,plenty of reboots after steps,etc. the whole 9.. Still an access denied. So, looking into the report server config file. Not seeing much here so just researching what everything here does. I looked in the logs I see everything initializing, the wcfserviceoperation is executing. The next line in the log is where I then get the unexpected error:
ReplyDeleteSystem.ServiceModel.Security.SecurityAccessDeniedException: Access is Denied at ... Microsoft.SharePoint.ReportingServicesSystemSettings.LoadSettings() ... .OnInit
What is interesting, I can call all of the web services and see them in the browser. Just I cannot load the ReportServicesSystemSettings.aspx page from the service app management screen..
Well, said screw it, went to Stage as it is a farm like PROD. Installed everything and it works. It uses a failover cluster for the core DB's and the APP server already had another copy of SQL 2012 on it (from the guy who I replaced failed attempts I can only guess). I ran the commands on the app server, started the service, made the service app specifying the app server as the db server for it. I was able to get this to work 98%, the only thing is I need to figure out why it can't tell if the agent is running on the screen where you download the script. But all pages work in CA, site collection pages work now. It actually went pretty fast for me.
DeleteI have an issue when click click on System Settings:
ReplyDeleteError
The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.
Troubleshoot issues with Microsoft SharePoint Foundation.
Correlation ID: f77b8fd0-08f6-4d50-be3a-fabc568c8557
Date and Time: 9/3/2013 1:51:31 PM
I found something in LOGS
09/03/2013 13:51:31.04 w3wp.exe (0x10EC) 0x1568 SharePoint Foundation Runtime tkau Unexpected System.ServiceModel.FaultException: The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter) at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.Ser... f77b8fd0-08f6-4d50-be3a-fabc568c8557
09/03/2013 13:51:31.04* w3wp.exe (0x10EC) 0x1568 SharePoint Foundation Runtime tkau Unexpected ...viceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.ReportingServices.ServiceContract.IReportServiceManagement.GetSystemProperties(ICollection`1 properties) at Microsoft.ReportingServices.SharePoint.SharedService.ProxyHelper.<>c__Displ... f77b8fd0-08f6-4d50-be3a-fabc568c8557
09/03/2013 13:51:31.04* w3wp.exe (0x10EC) 0x1568 SharePoint Foundation Runtime tkau Unexpected ...ayClass9.b__8(IReportServiceManagement mchannel) at Microsoft.ReportingServices.SharePoint.SharedService.ProxyHelper.DoManagementChannelCall(Action`1 caller) at Microsoft.ReportingServices.SharePoint.SharedService.ProxyHelper.GetSystemProperties() at Microsoft.ReportingServices.SharePoint.SharedService.UI.ReportingServicesSystemSettings.get_SystemProperties() at Microsoft.ReportingServices.SharePoint.SharedService.UI.ReportingServicesSystemSettings.LoadSettings() at Microsoft.ReportingServices.SharePoint.SharedService.UI.ReportingServicesSystemSettings.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAs... f77b8fd0-08f6-4d50-be3a-fabc568c8557
09/03/2013 13:51:31.04* w3wp.exe (0x10EC) 0x1568 SharePoint Foundation Runtime tkau Unexpected ...yncPoint) f77b8fd0-08f6-4d50-be3a-fabc568c8557
Hi Thanh,
ReplyDeleteThat error looks as though the Reporting Services Service Application was not installed successfully. Have you tried deleting the Service App & it's related Databases and tried to re-install?
Liam from the following site has also advised that sometimes a reboot is required: http://blog.helloitsliam.com/Lists/Posts/Post.aspx?ID=66
Good Luck!
Brett
Hi I have an issue here.. we have a multi server farm with two WFE server which run as the app servers as well.
ReplyDeleteI have installed SSRS 2012 binaries including the Reporting services Add-in in one of the servers and only the Add-in on the other server. Everything went well and I was able to generate report as well after configuring the service application.. Cool!!
But, when I went back to Services on the server in CA, I see the SQL reporting services is in a starting mode for the server where I have installed only the Add-in and in the other server, where I have installed the binaries as well the service is in started mode...
So my doubt is the server where I haven't installed the service but only the Add-in is trying to start (starting mode) a service, I just wonder what it could be trying to start here..!!
We performed IIS reset --> didn't work..
Rebooted the server at last --> The starting status turned to Error Starting..
Could you please help me with this ? Thanks!
BR,
Mukesh
SharePoint Admin
Hi Mukesh,
ReplyDeleteI'll do my best to give some advice on some things to check. In my experience. 'Error starting' is usually caused by lack of permissions to start the service. From memory, what you'll be looking for is the Application Pool that is running that Web Service In IIS, and User Account is set to run that App Pool.
If that's not the problem, it might be an issue with the second server not knowing where to find the RSServer, so you might need to update the RSConfig files on that server to point to the right location.
Sorry I can't be more specific, but it has been a little while since I've had to look at this!
Let me know how you go.
Cheers,
Brett
I'm getting a 503 when trying to access any of the links in my RSAplication (System Settings, Manage Jobs, Key Management, etc). My site and farm are on the same server, and when I try to do the uninstall commands, it tells me that RS is being used, and I have no idea how to delete the RS App. I absolutely HATE SharePoint!
ReplyDeleteHey Hill, All SharePoint Pro's have a Love/Hate relationship with the product, so welcome to the club! lol. However I would counter by saying SharePoint is great, it just doesn't integrate well with SSRS!
DeleteOn a more serious note, James in the comments earlier also had trouble getting working on a single-server farm so not sure if it just doesn't work in the configuration.
Uninstalling RS App - When you install RS, it probably also sets itself up as a Windows Service (from my vague memory), so go to Services (Start > type in Services) & check if there's a service called SQL Reporting Services or something similar. If it's there, stop it and then try uninstalling.
One more word of wisdom, if an error just doesn't make sense, always restart the server and try again before giving up! SharePoint loves a good restart.