SQL Server Reporting Services 2016 Integration with an Application

Artikel
09/15/2017

There are multiple ways to integrate or embed SQL Reporting Services Report in an application. This can be achieved via:-

Couple of common issue that we face while calling reports in an application are: -

How to pass credentials from application to SSRS.
How to avoid login prompt while accessing a report from an application.

In this article, we will majorly focus on URL Access Method and SOAP APIs.

Scenario 1

Let’s take an example of a Java web application that is configured to use forms authentication and user wants to embed SSRS reports into it. Now what authentication should be used to ensure the application is able to access the reports without any login prompt?

To achieve the above-mentioned requirement couple of methods can be used: -

SQL Server Reporting Services Configured for Forms/Custom Authentication: Forms authentication lets you authenticate users by using your own code and then maintain an authentication token in a cookie or in the page URL.

Sample : Reporting Services Custom Security Sample

In this case the user credentials being used by the application will be passed to SSRS. As SSRS is configured for forms authentication it will be able to understand those credentials and user will be able to access the report based on the permissions defined for that account in the web portal.

SQL Server Reporting Services Configured for Windows Authentication(NTLM): A dedicated domain account can be used (domain\username) in the application that can be leveraged to access a report. This account can be set in the code.

Note: Domain user account being used in an application should have access to the report and the web portal.

Scenario 2

Let’s take another example where we have .Net web application which is configured for Forms authentication and we want to call SSRS reports in the application such that SSRS is configured for Windows authentication (NTLM).

This can be achieved either through following way: -

Using URL Access Method: You can set identity impersonate = true along with the username and password in the web .config file of the web application. These credentials will be used by application while making a call to report.

 Example:  <identity impersonate= true, username=”domain\username “ , password=”password”>

Note: Replace the "domain\username" & "password" with correct user credential that has access to the report.

In the .aspx section add following code: -
```
  <div>
 <a href="https://servername/reportserver_sql2016?/Reports/Report2&rs:Command=Render&rs:Format=HTML5&rs:embed=true/" id="a1" title="link">link</a>
 </div>
```
Note : Embed =true will only work from SQL Server Reporting Services 2016 and onward. Replace the href section with the correct report path link.
You can also pass credentials via code as mentioned in the later part of the article.

Scenario 3

Both web application and SSRS is configured to use windows authentication(Kerberos).

If they are configured to use Kerberos, then logged in machine credentials will be delegated from application to SSRS.

In this case HTTP SPNs needs to be registered for reporting services service account and website application pool account.

Delegation would need to be enabled for both the accounts.

For setting up SPN please check: KERBEROS – Inside OUT

Scenario 4

A web application is configured for windows NTLM authentication and SSRS is also configured for windows NTLM.

In this case as mentioned in scenario 2 a dedicated account can be passed with identity impersonation being set in the web.config file or credentials can be passed via code as shown:-
Windows Authentication: The following code passes Windows credentials to the Web service.
C# Code

 private void Page_Load(object sender, System.EventArgs e)
 {
 // Create a Web service proxy object and set credentials
 ReportingService rs = new ReportingService();
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
 }

Basic Authentication: The following code passes Basic credentials to the Web service.
C# Code

 private void Page_Load(object sender, System.EventArgs e)
 {
 ReportingService rs = new ReportingService();
 rs.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
 }

The credentials must be set before you call any of the methods of the Report Server Web service. If you do not set the credentials, you receive the error code an HTTP 401 Error: Access Denied. You must authenticate the service before you use it, but after you have set the credentials, you do not need to set them again as long as you continue to use the same service variable (such as rs ).

Author: Khushboo Gupta - Technical Advisor, SQL Server BI Developer team, Microsoft

Reviewer: Selvakumar Rajakumar – Escalation Engineer, SQL Server BI Developer team, Microsoft

Freigeben über

SQL Server Reporting Services 2016 Integration with an Application

Zusätzliche Ressourcen