Windows Azure SQL Reporting – Report authentication without credentials
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:
- Getting Started with SQL Azure Reporting Service–Part 1
- Getting Started with Windows Azure SQL Reporting
- Get Started with SQL Azure Reporting Services (level 100) webcast with Mike Benkovich
- Introduction to SQL Azure Reporting Services
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:
And your report will likely have a Connect HyperLink at the bottom left of the page
![clip_image001[4]](https://msdntnarchive.z22.web.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/70/34/metablogapi/1440.clip_image0014_254F6468.jpg "clip_image001[4]")
If you click on this you can pass it your credentials
From there you can save your RDL file from Report Builder up to your SQL Azure Reporting Server directly. See SAVE AS button.
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.
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.
The last task, since anonymous access isn't possible, is to provide some form of credentials. Otherwise you will be challenged thus:
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: