Passing SQL Server Authentication to SQL Azure Report Services
(22nd April 2012)
In ReportServer on SQL Azure you can create an account with a user name and password which allows a report to be viewed,
but you then need to enter the users SQL Azure username and password (unless you embedded that in the report). We need to make our reports
user sensitive; because users are only allowed to see data that belongs to them (a multi-tenanted database is a good example of this).
ReportViewer Control
An example of the ReportViewer control is shown below in design view :-
The body of the source looks as follows:-
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="rptReferral.aspx.vb"
Inherits="HouseCallsWebRole.rptReferral" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Client Referral Report</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" CssClass = "cssReportViewer"
ProcessingMode="Remote" ShowBackButton="True" Height = "4000px" Width="100%">
</rsweb:ReportViewer>
</div>
</form>
</body>
</html>
There is nothing special about the above, but the key to making things happen is the code behind the form:-
Imports Microsoft.Reporting.WebForms
Public Class rptReferral
Inherits System.Web.UI.Page
Private Sub rptReferral_Init(sender As Object, e As System.EventArgs) Handles Me.Init
ReportViewer1.ShowCredentialPrompts = True
Dim cred As New DataSourceCredentials
cred.Password = Session("SQLAzurePassword")
cred.Name = "DataSource1"
cred.UserId = Session("SQLAzureLoginName")
ReportViewer1.ServerReport.ReportPath = Session("ReportViewerFolder") & "rptReferral.rdl"
ReportViewer1.ServerReport.ReportServerUrl = New Uri(String.Format("https://{0}/reportserver", Session("ReportViewerDomain")))
ReportViewer1.ServerReport.ReportServerCredentials = _
New ReportServerCredentials()
ReportViewer1.ServerReport.SetDataSourceCredentials(New DataSourceCredentials() {cred})
End Sub
End Class
In Report Server you will have setup a user with permissions to run the reports:-
When you view a report you are first prompted to login with the Report Server credentials.
We hold this information in our global.asax in Session level variables
Session.Add("ReportViewerDomain", "xxxxxx.reporting.windows.net")
Session.Add("ReportViewerFolder", "/ApplicationFolder/")
Session.Add("ReportViewerPassword", "xxxxxxxxx")
Session.Add("ReportViewerLoginName", "ReportServerUsers")
Looking back at the code we see the following use of these variables,
in specifying the folder and domain to be used:-
ReportViewer1.ServerReport.ReportPath = Session("ReportViewerFolder") & "rptReferral.rdl"
ReportViewer1.ServerReport.ReportServerUrl = New Uri(String.Format("https://{0}/reportserver", Session("ReportViewerDomain")))
You are then prompted for the login information for the datasource
(when we created the report we did NOT save the credentials in the datasource).
These are the values which we capture when a user logs into the application and hold in
the following session level variables:-
Session.Add("SQLAzureLoginName", "")
Session.Add("SQLAzurePassword", "")
Session.Add("AzureServerName", "xxxx.database.windows.net")
Looking back at the code for the ReportViewer Control we see the following:-
ReportViewer1.ShowCredentialPrompts = True
Dim cred As New DataSourceCredentials
cred.Password = Session("SQLAzurePassword")
cred.Name = "DataSource1"
cred.UserId = Session("SQLAzureLoginName")
ReportServerCredentials Class
The following link provides more details on the required class:-
IReportServerCredentials Interface
The example shown below is the one we use when working with the Azure Platform.
The class (ReportServerCredentials) you need to create to assist with this:-
Imports System.Web.SessionState
Imports System.Net
Imports System.Security.Principal
Imports Microsoft.Reporting.WebForms
Imports System.Runtime.InteropServices
<Serializable()> _
Public NotInheritable Class ReportServerCredentials
Implements IReportServerCredentials
Public ReadOnly Property ImpersonationUser() As WindowsIdentity _
Implements IReportServerCredentials.ImpersonationUser
Get
'Use the default windows user. Credentials will be
'provided by the NetworkCredentials property.
Return Nothing
End Get
End Property
Public ReadOnly Property NetworkCredentials() As ICredentials _
Implements IReportServerCredentials.NetworkCredentials
Get
'User name
Dim userName As String = _
Session("SQLAzureLoginName")
If (String.IsNullOrEmpty(userName)) Then
Throw New Exception("Missing user name ")
End If
'Password
Dim password As String = _
Session("SQLAzurePassword")
If (String.IsNullOrEmpty(password)) Then
Throw New Exception("Missing password ")
End If
'Domain
Dim domain As String = _
Session("AzureServerName")
If (String.IsNullOrEmpty(domain)) Then
Throw New Exception("Missing domain ")
End If
Return New NetworkCredential(userName, password, domain)
End Get
End Property
Private Shared ReadOnly Property Session() As HttpSessionState
Get
Return HttpContext.Current.Session
End Get
End Property
Public Function GetFormsCredentials(<OutAttribute()> ByRef authCookie As Cookie, _
<OutAttribute()> ByRef userName As String, _
<OutAttribute()> ByRef password As String, _
<OutAttribute()> ByRef authority As String) _
As Boolean _
Implements IReportServerCredentials.GetFormsCredentials
authCookie = Nothing
userName = Session("ReportViewerLoginName")
password = Session("ReportViewerPassword")
authority = Session("ReportViewerDomain")
Return True
End Function
End Class
The above code includes:-
Imports System.Web.SessionState
And the following property:-
Private Shared ReadOnly Property Session() As HttpSessionState
Get
Return HttpContext.Current.Session
End Get
End Property
The above allows the code to then make direct use of the session level variables.
The NetworkCredentials section sets up information for the users SQLAzure login name, password and server name.
The GetFormsCredentials section sets up information for the Reportserver.
Summary
The technique described in this article has been used to pass through both report server and sql server authentication on SQL Azure.
MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or
bringing together multiple databases and simplifying security. Click below to improve your
development productivity.
Top of Page