Azure hybrid connection for on prem SSRS (SQL Server Reporting Services)

Question

I have hosted an ASP.Net application and it connects with on premise SSRS reports. Application is connecting properly when working locally but when i publish on azure, it does not connect to SSRS. I have created Azure hybrid connection for on prem SSRS (SQL Server Reporting Services) but still not reaching to SSRS server. any idea?

I have created Azure hybrid connection for on prem SSRS (SQL Server Reporting Services) but still not reaching to SSRS server. any idea?

Answer 1

I just recently deployed a local web app as an Azure App Service. I created the hybrid connections necessary to pull the data from our on premises databases. I ran into issues when I attempted to view a report generated from our on premises report server. At first I received an error that the report server could not be reached. This was resolved by adding an additional hybrid connection for the report server itself. Once I did this I received a 401 error - Not Authorized. This is because of the double hop involved when sending the generated report data back to Azure or any webserver in a different domain other than the report server.

In order the address this issue you must do the following:

Implement the IReportServerCredentials class. Here is a good example: https://learn.microsoft.com/en-us/answers/questions/1285143/how-send-credentials-using-reportviewerformvc?page=1&orderby=Helpful#answers.

Note: Just skip directly to the code and use it as an example.

After implementing the class do the following:

1. First you must have a user, which can also be a local user account on the server hosting the Report Server or a domain user account if using Active Directory (AD).

2. That user must then be added using the SQL Reporting Services Web page and given the "Browse" role on the report directory for which you want them to view the report. This is important. You will find it under "Manage Folder" -> "Security".

3. You then use that user's username as the "username" field, their password as the "password when creating a new CustomReportCredentials you implemented for IReportServerCredentials.

4. You then enter domain name. For a local account it would the server (or PC's) name like "myReportServerHost" or whatever. For an AD user it would be the AD domain name like "myAD" or whatever. This was the trickiest part for me, and apparently for others as well based on other articles.

So the take away is:

1. Setup a hybrid connection to the on-premises report server.
2. Implement the IReportServerCredentials class (see example at the above URL).
3. Set the appropriate username, password, and domain name.
4. Add the user, if not already present, to the report server's report directory where the report resides, and configure with a minimum of the browse role.

That should do it.