Windows Azure SQL Reporting – Report authentication without credentials

  • Article

I recently build a demonstration for an Australian retailer wishing to give their store managers a service reporting application on their new mobile devices. The application has two primary goals: allow managers to place service requests and report on service status for their store. The service request part of the application we built by our partner Readify and the latter part we built in Windows Azure SQL Reporting. The application is all HTML 5 deployed on Windows Azure Web Sites with its source managed through GitHub.

Creating my report in Windows Azure SQL Reporting was painless as there are lots of traditional ways to build reports, upload them to the Windows Azure Portal and then apply them to my existing Windows Azure SQL Databases. There are ample starting guides out there from which I borrowed advice from the following:

The dilemma I had was that the Windows Azure Web Sites application had an authentication mechanism built off Windows Azure Active Directory and the example SQL Report I published to my Windows Azure SQL Reporting Server had different credentials.

For demonstration purposes it would be ideal just to use anonymous access but currently, there is no option in Windows Azure SQL Reporting, to set anonymous access, instead we need to login to Windows Azure SQL Reporting Server portal using your specified user name and password and then run the report. Whilst this is inconvenient, you will likely find that a second request for credentials is asked for, for each report.

So how to remove or embed the pesky credentials?

Since I was using SQL Server 2012 Report Builder 3.0, I tried and failed to save these report specific credentials. So I was unable to bypass the query each time the mobile application redirected to my report. I was lucky that I have great colleagues like N.Raja and Stephanie Lemus who have a never say die mentality and suggested a couple of solutions to this challenge.

To walk you through some approaches, you will probably have a credentials dialogue looking something like this:

image

And your report will likely have a Connect HyperLink at the bottom left of the page

clip_image001[4]

If you click on this you can pass it your credentials

image

From there you can save your RDL file  from Report Builder up to your SQL Azure Reporting Server directly. See SAVE AS button.

image

Alternatively, you can add the data source from the Report Builder to be one specific in the Windows Azure portal and give the data source credentials within the portal.

image

Within the Report Builder you can add the data source from Use a shared connection or report model which prompts for the data source on your Windows Azure SQL Reporting Server.

image

The last task, since anonymous access isn't possible, is to provide some form of credentials. Otherwise you will be challenged thus:

image

One way would be to create a web role, or on premises ASP.NET application using ReportViewer and hard wire the SQL Reporting Server logon credentials in web.config. An easier path would be to provide the credentials from the client to the Windows Azure SQL Reporting Server  – see this example for some help

Other resources: