In my last post, I discussed the changes in Excel and PowerPivot as they pertain to SharePoint 2013 and SQL Server. This post will walk through the steps required to set up SQL Server Reporting Services in Integrated mode with SharePoint 2013. As was the case with the new Excel data model, you will need at least SQL Server 2012 SP1 to get this to work as I describe (it’s currently available here as CTP 3).
Fundamentally, there are no real differences with how this installs when compared to installing SSRS 2012 on a SharePoint 2010 farm in SharePoint mode, so if you’ve landed here looking for 2010 information, it should be valid, but the screens will look a little different.
To start with, it’s important to understand that SSRS will install as a SharePoint service application. This obviously means that it must be installed on a machine that is part of the the SharePoint farm. What this does NOT mean is that you should install SharePoint on your SQL server and join it to the farm (please DON’T do that!). In a single SharePoint front end environment it is much better to add SSRS to your SharePoint server than it is to add SharePoint to your SQL server. Obviously, if you have a separate SharePoint application server, that’s the best place for it.
To install, obtain the SQL Server 2012 SP1 (or greater) media and mount it on tyour SharePoint server. Run the installer, choose new install and follow the prompts. Eventually you will get to the feature section screen, and assuming that machine has no prior SQL on it will look something like the following when completed.
You’ll notice that everything selected is under the Shared Features section, which means that it is not installed as part of a SQL instance. In fact, you’ll notice that we don’t have the data engine installed at all. The two Reporting Services options shown are the only items that are actually required for SSRS Integrated mode to work. As you can see, I’ve also selected SQL Server Data Tools (formerly BIDS) and Management Tools as well. I like to install these tools as a matter of course on SharePoint servers, as they can come in handy for connectivity testing or quick BI project building.
Follow the remaining prompts until the installation is complete.
Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application
Install-SPRSService Install-SPRSServiceProxy
Once registered, the service application can be created as below. If you install Reporting Service – SharePoint after the server has been joined to the farm, then the above steps are taken care of for you automatically.
The next thing that you need to do is to provision the service application. From Central Administration, navigate to Manage Service applications. Then, from the new menu, Select SQL Server Reporting Services Service Application.
Fill out the resulting form as appropriate, and select OK. Make sure that you navigate to the bottom of the form and select the applications to activate SSRS on.
Once the service application and proxy have been created, click on it to access the management screen.
You’ll want to access each of the sections and fill out the appropriate options for your installation. The instructions are fairly self-explanatory, so I won’t go into them here. At a minimum, you should back up your encryption key in the key management section, Set your unattended execution account (the default account to use when no credentials are available), and your email server settings if you want to be able to deliver reports via email. If you want to enable self service subscriptions and alerts, fill out that section, and it contains instructions for setting up the SQL agent service to support it.
The most important section is System Settings, which controls the bulk of how Reporting Services will run. Clicking on it accessed the service itself, and it’s the first place that you’ll see an error if you have configuration problems. In early builds, I have seen an error similar to the following:
The requested service, ‘http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc/actas’ could not be activated
(xxxxx is a local port which varies from farm to farm)
This indicated a problem with the SecurityTokenService, which you can see by accessing IIS. After doing a little poking around, I tried to access the service directly in a browser via its base url:
http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc
I was then presented with an error indicating that the server was too low on memory. The solution? Allocate more RAM. It was running with 4 GB and only SharePoint installed, but it did have most of the service applications activated. The lesson – if you want all the services to work, give your server enough memory. Bumping it to 8 GB did it in my case.
If you can access your system settings, then you should be good to go. The next step is to enable SSRS in you site collections, and I plan on doing a post on that in the very near future. Stay tuned.
Update – July 26 2012 – If you’re interested in trying out SSRS on SharePoint, or PowerPivot for SharePoint, you can use a pre-built environment that’s been set up on Cloudshare. Click here to sign up and access the environment.
[…] http://whitepages.azurewebsites.net/2012/07/setting-up-reporting-services-2012-with-sharepoint-2013/ […]
[…] Setting Up Reporting Services 2012 With SharePoint 2013 […]
I am running into errors when trying to execute the Install-SPRSService command. I am getting an error “The Term Install-SPRSService is not recongized 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.” any thoughts on how to fix this?
[…] up the features in this article, as there is quite a bit of good guidance on that available. I also wrote one up for installing on SharePoint 2013 which is pretty much identical to 2010 for […]
[…] Setting Up Reporting Services 2012 With SharePoint 2013 […]
Hey There,
Thank you for the walk through of setting up PowerPivot.
I was able to configure everything properly however PowerPivot and PowerView stopped working after connecting App server to Office web app. Once i disconnect Office web app everything is functional again. Have you had a chance to configure OfficeWebApp in your environment?
Thanks,
Soheil
I followed the steps but there is no trace of reporting services on my SharePoint 2013 (RTM brand new)
Same as Andrew Alexander
Any Idea?
Ofer-Gal – Was SharePoint installed before you installed the SSRS Service application? If so, does does the SSRS Service application appear in the list of possible service applications to add? Finally, did you use SQL Server 2012 SP1 to do the install?
Soheil – Yes, I’ve had it working bot with and without Web App, albeit in an earlier build. Does the App server work generally? Can you render Word documents in a browser?
Andrew – Provided that everything is set up right, I’m guessing that it might be a permissions issue. Did you run Powershell as an Administrator?
Having the same problems, could it be we are running Foundation rather than Enterprise? Is SQL Server 2012 integration supported on SahrePoint 2013 Foundation?
-Eric
Eric – that combination SHOULD work, based on past licensing. That said, i havent tested with SP2013 foundation. i’ll check with some contacts at msft and report back here.
Confirmed – Foundation should be just fine. Not sure what you’re running into if not order of operations.
Same problem here. I have a SharePoint 2013 environment with a single web/app server in the farm. Separate SQL 2012 server. I went to add Reporting services using your steps which are the same as Microsoft’s. In the admin SharePoint shell: Install-SPRSService is not a recognized command.
ok. i’m at spc next week and will get the definitive scoop.
Having the same issue with SSRS and found this blog while trying to diagnose.
The issue I’ve found so far is that all of the farm solutions etc have been installed into the 14 hive rather than the 15 hive. And there are a lot of not found exceptions related to SSRS because of this. I can see the service application to add, but doing so gives a filenotfound exception, as does attempting to do anything with PowerView. The cmdlet doesn’t exist either for obvious reasons.
We used the SP1 slipstreamed SQL Server 2012 installation media.
Thanks, you my day! 🙂
The fix for the SQL Server 2012 / Sharepoint 2012 problem is to install SQL Server 2012 SP1, EVEN if you install from the SQL Server 2012 SP1 DVD.
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/b59d7be4-777b-48df-9065-18f302071cec
Thanks applying SP1 allowed me to set up the service application. It is running but http://sharepoint:32843/ gives 503 errors. 🙁
Hi Guys,
Just got this working with my customer. SP2013, SQL2012 and all servers are Server 2012 OS. It’s a bit confusing because when you have a multi-tier SharePoint Farm.
1. Install “Reporting Services – SharePoint” and “Reporting Services add-in for SharePoint Products” must be installed on the Central Admin server and the dedicated SSRS server; otherwise clicking new “SQL Server Reporting Services Service Application” will error. (Recomend installing SQL Server 2012 SP1)
2. Run the Insall-SPRSService and Install-SPRSServiceProxy on Central Admin and SSRS server.
3. Start SSRS Service only on desired SSRS server(s).
4. Use guide above to finsih configuring.
Thanks
Matt
Hi
I can’t install RS Add-in for SharePoint.
It gives an error ar rcCustomAction step.
Installing the SharePoint Web Application content files…
Failed to install the application content files.
An exception of type System.NullReferenceException was thrown. Additional exception information: Object reference not set to an instance of an object.
….
2013-01-25 08:17:26: Install error: C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15binpsconfig.exe return error code: -1
2013-01-25 08:17:26: Error details: System.Exception: C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15binpsconfig.exe return error code: -1
at RSCustomAction.BaseProcessLaunch.LaunchCommand(String fileName, String arguments, Boolean ignoreExitCode)
at RSCustomAction.PsconfigCommands.CopyAppBinContents()
at RSCustomAction.RSFeatureSetup.Install(Boolean repair, Int32 addRetries)
2013-01-25 08:17:26: Uninstalling Report Server feature.
What should I do ?
[…] these guidelines from TechNet and this blog article pointing out some gotchas, we can extract the gist of what the steps […]
This is great article. I have successfully completed setting up reporting service as a single server farm installation (sharePoint 2013 and SSRS 2012 on same box). I could access the report on the server. I could also access the URL using FQDN (as my installation was on different domain then my own computer). However, when tried running report, i got error about not having access mapping configured. Do you have any suggestion on how to resolve it?
Did you find out if SSRS can be integrated with SharePoint Foundation? The links I’ve turned up seemed to indicate this is not possible, but if that is so, it is a departure from previous versions. See the links below.
http://sharepointtaskmaster.blogspot.com/2010/11/intergration-reporting-service-with.html#!/2010/11/intergration-reporting-service-with.html
http://blog.blksthl.com/2013/01/14/sharepoint-2013-feature-comparison-chart-all-editions/
We are facing this error on System setting at Reporting Service Application
The remote server returned an error: (404) Not Found.
[…] I am keen to point out every opportunity I get, when installing SQL Server Reporting Services in SharePoint Integrated mode, it is important to ensure that it is installed on a SharePoint server, and not on a SQL Server. […]
[…] these guidelines from TechNet and this blog article pointing out some gotchas, we can extract the gist of what the steps […]
Hi John,
I have a database server which is running the sql database engine that holds the SharePoint databases. The DB server is also running reporting services. SharePoint is installed on a different server. I followed the instructions in this article to install reporting services for SharePoint and the add-on. When I try to provision the SQL reporting service application, I get an error saying “cannot find specified file”. The SQL reporting service itself was installed successfully and it is running on the sharepoint server.
Any idea what’s going on?
thanks
Hani
Thank you for this blog. The below two commands helped me. I have been spending a lot of time trying to find out why SQL reporting service application wasn’t coming up.
Install-SPRSService
Install-SPRSServiceProxy
– Shibu
I am using Ms SQL SERVER 2014 and SP2013 on Standalone server. SSRS was already installed in native mode. To add SharePoint – Integrated mode, I can not see an option to select integrated mode while adding features to an exisitng installation of SQL and not even while installing New instance. Any help would be much appriciated.
Thanks very helpful
What’s up, all the time i used to check website posts here in the early hours in the break of
day, since i like to find out more and more.
We have Reporting services installed in SharePoint mode, and since of late we facing an issue that the reports don’t load.
We found that if we restarted IIS on the SSRS nodes things got back to normal, but 15-20 mins later the same problem arises.
Looking at the application log and and SharePoint log we found the following errors, and based on this we restarted the MetaData Service on all SP and SSRS nodes. This resolved the issue for a few days, and once again we have the problem.
Any idea why this might be happening ? or anyone who faced similar?
Our farm has one SP WFE, 2 SSRS (SharePoint mode) nodes, and one SQL DB server.
SharePoint version is 2013, while the db is SQL 2014.
The errors we get routinely on the application log are are the following.
These appear on the SP server (it fires two errors regularly one for each SSRS notes, while one of the SSRS nodes also fires one about the other SSRS node) – Event ID: 8313 – http://[reportserver]:32843/24516db652d0450eba890a35a5e1619a/