Home | About | Resources | Contact | Partners
MUST Logo

Layout for printing

Download Free MUST Trial Today



"MUST allows our business to use Access to flexibly RAD our systems and then easily move production systems to SQL Server"
Karen Spencer, Partner and Business Manager,
Gravitas IT solutions

"FANTASTIC! What was going to take me hours hand (re)coding Stored Procs just worked..."
David Peach, I.T. Management & Support Services,
Qbt Consulting

"It has saved me a lot of time"
Julian Baker,
The Access Man



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