SQL Server Reporting Services (SSRS) has experienced some very significant improvements in the 2016 version. As has been the case Since SQL Server 2005 SP1, it runs in either Native, or SharePoint Integrated mode. Integrated mode (the subject of this article) requires SharePoint 2016, and it is required for SharePoint to be able to render Power View reports in a browser. This article walks through the setup and configuration of SSRS 2016 Integrated mode in a SharePoint 2016 farm.
The process for setting up SSRS in Integrated mode is little changed with 2016. The process consists of installing the bits on the SharePoint server(s), creating and configuring the service applications, deploying the solution, and configuring document libraries to contain report elements.
Installing SSRS 2016 on Sharepoint Servers
When running in integrated mode, SSRS MUST be installed on a server that is part of the SharePoint farm. This only makes sense because it is deployed as a SharePoint Service Application. Unfortunately, the fact that is distributed as part of the SQL Server media causes confusion for some.
As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not support any other role than the Custom role. If your server is not running the Custom role, installation will succeed, but SSRS will be shut down by the roles engine during the next maintenance window. In order to check which role your server is using, and to possibly change it, you can use either PowerShell or Central Admin. With Central Admin, the setting is found in “System Settings”, under the “Servers” category as “Convert server role in this farm”.
Selecting this option opens the role configuration dialog, which is quite simple.
If the role is already set to Custom, you are good to go. Otherwise, it can be changed with the “New Role” drop down dialog.
Once the correct role is in place, SSRS can be installed. The first step is to mount the SQL Server media on a SharePoint server, and run the standard SQL Server installer. SSRS Integrated mode is part of the Shared Features collection (ie no SQL instances are installed), and it consists of two parts.
The first option, “Reporting Services – SharePoint” is the actual SSRS Service application. This should be installed on any SharePoint servers allocated to doing the heavy lifting of rendering reports – the “app” servers. The second option “Reporting Services Add-in …” is used to connect a SharePoint server to an instance of the SSRS Service application. This should be installed on any SharePoint front-end servers at a minimum, but I recommend installing it on all of them as a convenience.
After a few “Next”s and “OK”s, the SSRS bits should be installed on a server. The next step is to Create and configure the Shared Service Application itself.
Creating the SSRS Shared Service Application
Once the bits are installed, an SSRS Service application is created in the same manner as any other service application. From the Service Applications interface in Central Administration, select “New” from the ribbon, and then select “SQL Server Reporting Services Service Application”.
You will then be presented with a configuration dialog where you will need to specify a name for the service and a few other configuration parameters.
I typically use the same application pool as most of the other SharePoint services, and I always change the name of the database. The default database name contains a GUID, and nobody likes GUIDs in their database names. The SSRS will actually create 3 databases, one with the name specified, and two others that use this name as a base. Also, if you’ll be using other Reporting Services databases on the same SQL Server – for Native mode as an example, it’s a good idea to name it so that it’s easily distinguishable. In this example “Integrated” is added to the end.
Scrolling down, you’ll see options for activating the SSRS features in all of the farm’s site collections. The features can be activated from the site collections as well; this is simply a convenience.
Once saved, additional SSRS configuration items can be configured, and should be. At the very least, the subscription options should be configured, and the encryption key should be backed up, but these operations are not essential for basic setup, so they will not be done here. The next operation will be to enable a document library for SSRS reports.
Creating a Reporting Library
Enabling a document library in SharePoint for SSRS reports is unchanged from the past several versions. The first step is to add a new document library by going into “All Content” for a site, and selecting “Add an App”. You may be tempted to select “Reports” or “Report Document Library” at this point – don’t. The “Reports” library template that ships with SharePoint 2016 and prior contains content types for creating Excel documents in prior versions, web pages – that’s it. It has nothing to do with SSRS reports.
Select a Simple document library, give it a name (something like SSRS Reports, or SSRS library), and let it be created. Then, go into the library settings, click Advanced settings, and enable the use of content types. Next, add the SSRS content types to the library by clicking “Add from existing site content types”, selecting the “SQL Server Reporting Services Content Types” category, and then selecting “Data Connections” and “SSRS Report”. Unless you have a specific need, do not add the “Report Builder Model” content type. Models are a deprecated artifact and exist only for backward compatibility.
Once added, click OK, and you will be returned to library settings. At this point I like to remove the “Documents” content type from the list to restrict it to reports, but that will depend on your requirements. At this point you should be able to create a new report or data source by selecting new in the library’s ribbon and choosing the appropriate item. This library can now be used to store reports.
The final step is to enable and confirm support for Power View.
Power View Support
Power View support in SharePoint 2016 is provided through SSRS Integrated mode (and ONLY through SSRS Integrated mode). It is manifested in 3 different areas:
- Creating and viewing a standalone Power View report from a data connection
- Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery
- Using a browser to view a Power View report contained in an Excel workbook
1. Creating and viewing a standalone Power View report from a data connection
Standalone Power View reports utilize BISM (BI Semantic Model) connections. BISM connections can be added to a SharePoint library by adding the “BI Semantic Model Connection” content type to the library – this would normally be done for a connections library. A BISM connection can also be created through an SSRS data source by selecting “Microsoft BI Semantic Model for Power View” as its data source type.
Creating a Power View report from either connection type follows the same process. In the library, click the ellipsis for the connection, and then the second ellipsis. From there, select “Create Power View Report”
Provided that Silverlight is available on the client, Power View should launch, and you should be able to build a report on the underlying data.
2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery
Creating a Power View report is significantly simpler. Once SSRS is installed, it adds a small Power View icon to every workbook that is in a Power Pivot gallery.
Simply click on the icon, Power View will launch, and you can build a report on the data model that is contained in the workbook. There is however one additional step necessary for this to work. Because the data model is actually stored in the SSAS PowerPivot mode server(s), and it is SSRS (remember, Power View is part of SSRS) that is working with the model (not OOS), the service account for SSRS needs to be added to the Administrators list on the SSAS PP mode server(s). In our case, the service account is NAUTILUS\spServices.
3. Using a browser to view a Power View report contained in an Excel workbook
Power View reports that have been embedded in an Excel workbook require no additional configuration, they should “just work” once SSRS is configured. However, as with the PowerPivot gallery, SSRS needs access to the data models, and therefor its service account needs to be in the administrators list (see above).
Wrapping Up
Once installed and configured, you will have access to the new HTML5 based rendering engine and new visuals available to SSRS 2016. You will also be ale to work with your existing Power View investments. However, you will not be able to use the new mobile reports, Reporting Dashboards, Parameters customization, and Power BI integration. For that, you’ll need a Native mode SSRS instance, and yes, it can be connected to SharePoint. That will be the topic of an upcoming article.
Excellent post John!
So we don’t need to run “Install-SPRSService” and “Install-SPRSServiceProxy” anymore? Great to see the Service Application option available right after SQL media install.
https://msdn.microsoft.com/en-us/library/gg492249.aspx#bkmk_basic_samples
Thanks Jeff! The need to run those PowerShell commands was (and is) dependent on order of operations. If the SSRS bits are installed before the server is added to the farm, you’ll still need to (I think… haven’t tried that).
[…] Configuring SSRS 2016 Integrated Mode with SharePoint 2016 […]
Thanks John,
After setting integrated mode up did you have to do anything to get client printing to work. I don’t even see the icon on the client.
Thanks,
David
Hi Dave
I must confessed – I haven’t even looked at printing yet. However, given that everything is now based on HTML5, I’d first ask if your browser is up to speed. Assuming that it is, it may just have not made it in yet – I really don’t know.
Has the Custom MinRole requirement changed? “As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not support any other role than the Custom role.”
I ask because I have a SP2016 (general availability) farm deployed using the MinRole topology (app, wfe, dc, and search) and it seems that my only option would be to add another server for the custom role. My preference is to use the application server but I don’t want to change it to Custom. Thanks
Hi John,
I really enjoy the explanations in your article, which helps me to understand the technology and deliver my projects most of the time, however now I have a huge problem with SSRS 2016.
The environment is SP 2016 Enterprise with SQL 2016 Standard.
I have installed reporting services, activated the features, however I can’t see the content types at all. Deactivate the feature and activated using PowerShell:
Enable-SPFeature -Identity E8389EC7-70FD-4179-A1C4-6FCB4342D7A0 -Url http://SharePointSiteCollection -force
Tried to create them manually, however that doesn’t help either.
The biggest issue is that we cannot create shared data sources without the content types.
I have seen several articles mainly about SSRS2012 where this issue was quite common after patching the system and the solution was to use a previous shared data source file and once uploaded just modify the file.
I hope somebody has some idea how to fix it as I have tried nearly everything.
Many thanks,
Frank
Hey Frank – to start with, I have to ask – did you install SSRS in integrated or Native mode?
Thanks John.
I have a weird issue with SharePoint 2016 + SQL 2014 SP2 Reporting Service.
I created a new SharePoint 2016 Farm with few servers by not choosing custom role on any of them.
Then i installed Reporting Features using SQL 2014 media on all servers + updated it with SP2 for SQL 2014.
i didn’t get any Reporting Service Application in Central Admin neither after converting one of the server to custom role and then reinstalling ( removing + installing ) SQL Sp2. I even ran Configuration wizard and tried Install-SPRSService on all the servers which gives error as not a recoginzed commandlet.
Am is missing any step? please help.
Regards,
Mangat
Same problem here as Mangat.
SSRS installed, add-in installed.
But not displayed as service in CA.
Finally i have found the issue. SharePoint 2016 needs SQL 2016 RS Add-In only.
So, do not install SQL 2012, 2014 RS ad-in with SharePoint 2016. It only works with SQL 2016 add-in.
Your back-end database can be SQL 2014 or 2016.
We have sharepoint 2016 with SQL 2014 as the backend database. We are facing teh same issue when I tried to install SQL 2014 SSRS Add in. If sharepoint 2016 needs SQL 2016 RS Add-In, any idea what kind of SQL 2016 license we will have to get?
Many thanks
Hello Anu,
You don’t need any License for installing SSRS add-in. You can download it from here:
https://www.microsoft.com/en-us/download/details.aspx?id=52682
Regards,
Mangat
Thank you very much. I will try this.
we have recently installed sharepoint 2016 with shared roles and installed Reporting services on Application server with Custom Role , all worked well and we can see content types etc
We have created few reports and migrated few from sharepoint 2010 , we can view reports on Applicationserver (when using localhost url or url of the Application server)
But reports are not shown/rendered from webfront end on sharepoint page , but when we try to use sharepoint builder and open the report , it opens up the right report perfectly
does any one notice similar issue (or did any one install Sharepoint 2016 reporting services a 3 tier farm)
Noticible errors from console
1. Reserved.ReportViewerWebPart.axd Failed to load resource: the server responded with a status of 404 (Not Found)
2.
Uncaught Error: Sys.ArgumentUndefinedException: Value cannot be undefined.
Parameter name: method
at Function.Error$create [as create] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:237)
at Function.Error$argumentUndefined [as argumentUndefined] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:361)
at Function$_validateParameterType [as _validateParameterType] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:161)
at Function$_validateParameter [as _validateParameter] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:130)
at Function$_validateParams [as _validateParams] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:84)
at Function$createDelegate [as createDelegate] (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:41)
at new ReportingServices.WebFormsClient._Toolbar (Reserved.ReportViewerWebPart.axd?OpType=Resource&Version=13.0.1601.5&Name=ViewerScript:5394)
at Sys$Component$create (ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:3811)
at Array. (RSViewerPage.aspx?rv:RelativeReportUrl=/TestReport Library/Reports/In:673)
at ScriptResource.axd?d=r9rXQ4Qowls0wgnk-8S63X-IPwJFVreClBlaZhL_UPJXytNQXEzIZb8WuxPgpNsZ1j6-OcqV9hVLNG…:3484
sk – You need to start the reporting service ( from Central Admin ) on the Web Front End servers. Since your Web Front End servers does not have Reporting Service started, they cannot serve the Reporting Content.
hi Mangat ,
we felt exactly the same and with sharepoint 2016 we cannot activate it through central admin(as it doesn`t provide option of activating/deactivating), so tried provisioning through powershell , as soon as we activate it Web front end complains that it is not compatible (this can be observed by navigating to
central admin –>Manage servers in the farm –> we can see (fix) at web front end server
As soon as fix is clicked , it shuts down reporting services on web front end
please let me know if i am doing something wrong
thanks
SK
SK- Is your report rendering after starting the Report service on the Web Front End Server?
Mangat , there was no change in rendering the reports after enabling repoting services on web front end
i am getting same old error
so does your environment include web front end and App server or just standalone server
Hi Mangat,
https://www.microsoft.com/en-us/download/details.aspx?id=52682 – this links gives me rsSharePoint.msi, which will only help me to install Reporting Services Add-in for SharePoint products. However, I have to enable the other feature – Reporting Services – SharePoint. I guess this comes from the SQL Media and may require license for that.
Many thanks,
Finally i have found the issue. SharePoint 2016 needs SQL 2016 RS Add-In only.
So, do not install SQL 2012, 2014 RS ad-in with SharePoint 2016. It only works with SQL 2016 add-in.
Your back-end database can be SQL 2014 or 2016.
Dear,
I am also getting same issue, not resolved by installing sql 2016 add-in also. Can you please elaborate some more…?
hey arjun,
Totally un related to your issue
Do you have 3 server farm (wfe, APP and SQL) or just a standalone Sharepoint 2016(WFE and SQL)
Our issue is we cannot render Reports from sharepoint Wfe
regards
sk
I have installed, configured as above and ran the script to grant access for the RSEXECRole in SQL (seems to have completed successfully), but I am still getting an error saying “SQL Server Agent state cannot be determined” – any ideas?
I found my solution in case anyone else needs it:
After setting the SPN and activating delegation, I still got this Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ -message.
So I decided to check from the IIS management console what authentication providers are set for SP Central Adm. To my surprise, the NTLM -option was on top of the list. As soon as I changed the Negotiate-option as first provider,I got this: SQL Server Agent is running -message
Source: https://social.technet.microsoft.com/Forums/office/en-US/b5d025fb-7b98-4e84-9c51-bdb4a1c25a03/reporting-services-2012-for-sharepoint-and-sql-server-agent-subscriptions-and-alerts?forum=sharepointadminprevious
Hi John
thank you for your useful posts.
I have a question.
I have installed sharepoint 2016 in stand alone role , with sql server 2014 for it’s database on the same server. and i connected it to an office online server 2016 and they work fine with each other.
now I decided to add ssrs service to my farm using a separate server for it and configure it in connected(integrated) mode (not native mode). now I want to know for this scenario , should i change the role of my primer sharepoint server from stand alone to another role or I can add new ssrs server to this farm with it’s stand alone role?
if i have to change the role , what role should i choose(for first sharepoint server)?
(” i know that ssrs server should have custom role”)
sorry for bad English 🙂
Thank you very much
On 2 SP2016 farms, “New” from the ribbon does not show “SQL Server Reporting Services Service Application” to install.
I install on a custom min-role the 2 integrated items from SQL 2016 ISO and just the add-on on all other SP servers.
What else needs to be done to see it?
Hi ,
We are trying to connect Sharepoint 2016 to MS SQL Reporting Services 2016 Setup and Its not connecting for multiple server. Can I get any help?
You cannot vote on your own post
0
Hi ,
We developed MS SQL reporting services 2016 in sharepoint mode , Report builder for sharepoint 2016. But now we are unable to connect this in multiserver . So when I googled I found below link
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-the-first-report-server-in-sharepoint-mode
mentioned
A single-server installation is useful for development and testing scenarios but a single-server is not recommended for a production environment. The single-server environment refers to a single computer that has SharePoint and Reporting Services components installed on the same computer. The topic does not cover scale-out with multiple Reporting Services servers.
and also
Reporting Services integration with SharePoint is no longer available after SQL Server 2016
As Our clients already migrated to sharepoint 2016 and ssrs connection also they need in SharePoint 2016 and multiple servers. Can any one help us.
[…] Configuring SSRS 2016 Integrated Mode with SharePoint 2016 […]
so finally installed a new sp2016 farm, sql2016, powerpivot2016 and ssrs2016 integrated mode, also office online server 2016 and a ssas2016 in powerpivot mode
so far so good, Powerview works, Powerpivot datarefresh works, SSRS Report works…except in one scenario
When connect two datasets to a powerpivot datamodel from an excel file hosted in sharepoint: Cannot create a connection to data source ‘XXXXXX’. we coudn’t file the file you wanted.
Currently researching on this error,
Tq nandeep….for your inputs i have settuped it!
but having one query
Current farm
+++++++++++++++++++
•2 servers with Application with search role
•1servers with WFE with Distribution role
•1 server With Custom
On custom server i have installed “Reporting service -sharepoint” and “Reporting service add-in for sharepoint” components ,
On wfe server i have installed “Reporting service add in for sharepoint”
The Installation was successfull ,but unable to see service application (ssrs)
But when i ran get-spserverinstance -server name “custom####” ,the service was showing as on-line but iam unable to see under
SharePoint 2016 Central Administration -> “Application Management” -> “Manage Services on Server” page
SharePoint 2016 Central Administration -> “Application Management” -> “Manage Service Applications” ->”New” menu
Do i have to install these 2 components on where CA (any one of the app server ) is running too ,to get affected ?
Tq John….for your inputs i have settuped it!
but having one query
Current farm
+++++++++++++++++++
•2 servers with Application with search role
•1servers with WFE with Distribution role
•1 server With Custom
On custom server i have installed “Reporting service -sharepoint” and “Reporting service add-in for sharepoint” components ,
On wfe server i have installed “Reporting service add in for sharepoint”
The Installation was successfull ,but unable to see service application (ssrs)
But when i ran get-spserverinstance -server name “custom####” ,the service was showing as on-line but iam unable to see under
SharePoint 2016 Central Administration -> “Application Management” -> “Manage Services on Server” page
SharePoint 2016 Central Administration -> “Application Management” -> “Manage Service Applications” ->”New” menu
Do i have to install these 2 components on where CA (any one of the app server ) is running too ,to get affected ?
The first option, “Reporting Services – SharePoint” is the actual SSRS Service application. This should be installed on any SharePoint servers allocated to doing the heavy lifting of rendering reports – the “app” servers. The second option “Reporting Services Add-in …” is used to connect a SharePoint server to an instance of the SSRS Service application. This should be installed on any SharePoint front-end servers at a minimum, but I recommend installing it on all of them as a convenience.
You Mean to say “Reporting Services – SharePoint” Should be installed on all SharePoint 2016 app servers and “Reporting Services Add-in …” should be installed on all WFE-SharePoint 2016 servers ?
If I installed that 2 components on below servers all servers ……its nothing wrong right
•2 servers with Application with search role
•1servers with WFE with Distribution role
•1 server With Custom
MS suggest that farm should have one custom role server ……I hope in rest of the server mini role will stop the ssrs service expect in custom role server …..correct me if iam wrong
Thank you
Does Report Builder need to be installed on the client machine to be able to start using the library of SharePoint Document. Or on servers from SSRS?
I have 2 servers, Server X with sharepoint appl 2016 installed , and server Y where it is sharepoint sql 2016 database.
I also had a third server Z where it was before the SSRS in the integrated mode. This third server is down and I need to install it from scratch. I cloned the server Y in this server Z.
The configuration on central admin were already made before – SSRS service is created /started and Service application too, proxy as well are already configured in central admin. the appl server is in custom mode too..
I tried to edit the SQL Server Services Service Appl , and it was succesfull, but the 3 databases are not created…
pls help how can I go on with this situation, how to create this 3 databases again ?
As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not support any other role than the Custom role. If your server is not running the Custom role, installation will succeed, but SSRS will be shut down by the roles engine during the next maintenance window. In order to check which role your server is using, and to possibly change.
It is also quite safe to use as it comes with end-to-end encryption. In general, Google Duo works well on a smartphone, but if you’re having trouble getting it to work, this article may help.