SharePoint 2016, 2019, Subscription Edition: Configure the SSRS Report Viewer web part
Starting with SQL Server 2017, there’s only one installation mode for Reporting Services: Native mode. As such, the SharePoint integration with SQL Server Reporting Services (SSRS) is pretty much limited to getting the Report Viewer web part to work.
Installing SharePoint, SQL and SSRS are beyond the scope of this post, so let’s pretend you already have those set up, and we’ll start from there.
Install the web part:
The SSRS Report Viewer web part comes as part of a custom farm-level solution. You can read more about the SharePoint-to-SSRS integration story and find a link to download that farm solution here: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/report-viewer-web-part-sharepoint-site?view=sql-server-ver15
Then you’ll need to deploy the SQL Server Reporting Services Report Viewer web part on a SharePoint site: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/deploy-report-viewer-web-part?view=sql-server-ver15
And add SQL Server Reporting Services Report Viewer web part to a SharePoint page: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/add-report-viewer-web-part-to-page?view=sql-server-ver15
Then edit the web part and point it at one of your reports:
At this point, the web part is likely to throw the following error because we haven’t yet configured the necessary infrastructure to allow it to work.
“Error
For more information about this error navigate to the report server on the local server machine, or enable remote errors“.
Now that we have the web part in place, we can focus on configuring the infrastructure that is required to make it work. That’s where things typically go wrong, and where it takes and understanding of how it works in order to properly troubleshoot it.
How this works:
- The client browses the SharePoint page that contains the Report Viewer web part.
- The client can use either NTLM or Kerberos to authenticate to SharePoint. It works either way.
- The web part must contact the SSRS server to execute the report.
- This is done by the SharePoint worker process (W3WP.exe). You will NOT see any network calls directly from the client to SSRS.
- Inside of SharePoint, the user is a “claim”. SQL does not know what that is, so we must use the Claims to Windows Token Service (C2WTS) to turn that claim into a Windows token.
- The C2WTS turns the claims token into a Windows token and hands it back to the SharePoint worker process.
- The SharePoint worker process uses Kerberos delegation to pass that Windows Token to the SSRS server.
- For this to work, Delegation must be configured for the SharePoint application pool account to delegate to the SSRS service account.
- With the delegated Windows token, the SSRS server contacts the SQL server to get the raw data and execute the report. It hands that back to SharePoint for display in the Report Viewer web part.
Here’s a high-level diagram showing a logical representation of how this works:
IMPORTANT: Because the SSRS web part must leverage the C2WTS for this to work, and because the C2WTS only works with Windows auth, out of the box, the SSRS web part will only work when the incoming authentication method to the SharePoint web application is Windows.
References:
– Kerberos authentication and claims-based authentication"It is important to understand that these service applications can use the C2WTS only if the incoming authentication method is either Windows claims or Windows classic mode. Service applications that are accessed through web applications and that use Security Assertion Markup Language (SAML) claims or forms-based authentication claims do not use the C2WTS. Therefore, they cannot translate claims to Windows credentials."
– Fact #3: SharePoint: Facts and Troubleshooting the Claims To Windows Token Service (C2WTS)
SSRS Configuration:
The SSRS site must be set up to use Kerberos authentication.
You do that by adding a <RSWindowsNegotiate/> tag as the top value within the <AuthenticationTypes> tag in the RsReportServer.config file at C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer.
Of course, you must also create an HTTP Service Principal Name (SPN) for the SSRS service account.
For example: SSRS service account = CONTOSO\SSRSSVC, and the SSRS URL is http://ssrs.contoso.com. You’d create an SPN like so: setspn -s HTTP/ssrs.contoso.com contoso\ssrssvc.
If you are not sure which account is the SSRS service account, just check in the SSRS Configuration Manager:
Active Directory Configuration:
Delegation must be enabled on the SharePoint app pool account to delegate to the SSRS service account.
If you’re not sure about your SharePoint app pool account, you can check in IIS manager on a SharePoint web front end (WFE):
For example: SP App pool account = CONTOSO\SPWebApps. SSRS service account = CONTOSO\SSRSSVC.
In Active Directory Users and Computers (ADUC), find the CONTOSO\SPWebApps account and go to the Delegation tab.
Important: If the app pool account does not have a “Delegation” tab, that means that no SPNs have been assigned to that account. If the SharePoint site is using Kerberos authentication, the app pool account should have at least one SPN assigned to it. However, if the SharePoint site is using NTLM, it’s normal for the account to not have any SPNs assigned, in which case, there will be no Delegation tab. In order to get a Delegation tab, you can set a fake SPN on that account. For example, I like to set an SPN like “HTTP/makeSSRSdelegationWork”. That serves the purpose of making the Delegation tab visible, reminds me why it exists, and hurts nothing.
Back on the Delegation tab, choose: Trust this user for delegation to specified services only | Use any authentication protocol. Click add.
Find the SSRS service account = CONTOSO\SSRSSVC. Under available services, it should show an HTTP service for the SSRS SPN (for example, ssrs.contoso.com).
Choose that and click ok.
SharePoint Configuration:
The Claims to Windows Token Service (C2WTS) is required for this to work. You need to run the C2WTS on all of your web-front-ends.
Proper configuration of the C2WTS includes several permission changes for the C2WTS account, and edits to the c2wtshost.exe.config file.
The configuration of the C2WTS is outlined in the SSRS documentation here: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/claims-to-windows-token-service-c2wts-and-reporting-services?view=sql-server-ver15
And detailed more thoroughly here: https://joshroark.com/sharepoint-facts-and-troubleshooting-the-claims-to-windows-token-service-c2wts/
Important: The edits to the c2wtshost.exe.config file must be made on all of the servers running the C2WTS, which at a minimum, should be all of your SharePoint web-front-ends.
Troubleshooting:
This is not a fun one to troubleshoot, largely because any number of configuration mistakes can all result in the same symptom: You get a generic error in the Report Viewer web part that says: “Error For more information about this error navigate to the report server on the local server machine, or enable remote errors“.
My advice would be to start by going back through this article and make sure you have everything (Kerb for SSRS, C2WTS, Delegation, etc) configured properly.
For troubleshooting the C2WTS, see my blog post about that: https://joshroark.com/sharepoint-facts-and-troubleshooting-the-claims-to-windows-token-service-c2wts/
If you can get the C2WTS Tester Tool to successfully fetch a Windows token on your SharePoint WFEs, then you can be pretty confident that the C2WTS is ok, and you can look in other directions, like your SSRS Kerberos configuration and the Delegation settings for the SharePoint app pool account.
Fiddler can be helpful in a few places.
For example, I said earlier that the SSRS site must be configured to use Kerberos. Keep in mind that just because you set SSRS to use Kerberos by adding the “RSWindowsNegotiate” tag in the “RsReportServer.config” file, and you are able to browse to the SSRS site does not mean that Kerberos was actually used to authenticate you.
So how do you verify that Kerb is actually working?
There are several ways to do that, but the easiest may be to just run Fiddler and browse to your ReportServer page. Example: http://ssrs/reportserver
In the Fiddler trace, find the 200 response for the request to the ReportServer site. In the upper-right-hand pane, choose Inspectors | Auth. It should say that a Kerberos Ticket was used.
By contrast, this is what it would look like if NTLM was used:
That’s step #1 for ensuring Kerberos is working. Step 2 is to verify that the SharePoint worker process is using Kerberos when it contacts SSRS on behalf of the end-user. To get a look at that request, we could use a network trace tool like Netmon or Wireshark, but I find it’s easier to get a feel for the traffic with Fiddler.
Because the SharePoint w3wp.exe process is making these requests to SSRS (not the end-client), we need to insert Fiddler as the proxy in the web.config file on the SharePoint web-front-end (WFE) to properly capture it. To do that, open IIS manager on the WFE and find your SharePoint site. Right-click on it and choose Explore. That’s where you’ll find the web.config file.
Before you do anything, make a backup copy of that web.config file.
Then edit the file and find the “<defaultProxy />” tag. Paste in the following text in place of the <defaultProxy /> tag:
<defaultProxy>
<proxy usesystemdefault=”false” proxyaddress=”http://127.0.0.1:8888″ bypassonlocal=”false” />
</defaultProxy>
Install Fiddler on the WFE. Make sure it’s running and make sure it’s set to Capturing | All Processes.
Now browse to the SharePoint page that contains the Report Viewer web part from a client machine. Within Fiddler on the SharePoint WFE, you should see several requests to /ReportServer/ReportExecution2005.asmx, and at least one request for the report itself. You should also notice that these requests are being made by the W3WP process.
Inspecting those requests should give you some clues as to what is going wrong. For example, we should see that Kerberos is being used:
Important: With the above “defaultProxy” change in the web.config, the Report Viewer web part will only work when Fiddler is running on the WFE. If you close Fiddler and leave that web.config change in place, the Report Viewer web part (and any other function that requires an external HTTP call) will fail. So when you’re done troubleshooting, revert the web.config changes (or simply restore your backup copy) so that HTTP requests that the W3WP.exe process makes are no longer run through Fiddler.
Other Tips:
- In my testing, I found that any time you make a change to the Delegation settings in AD, you should reset IIS and restart the C2WTS on the SharePoint WFEs to make sure the effects of the change are seen immediately.
- The Report Viewer web part must be pointed at the ReportServer
URL, not the “Reports” directory. Example: http://ssrs.contoso.com/ReportServer
If there’s any question about the proper URL to use, check the “Web Service URL” in the SSRS Configuration Manager:
