C# – How to implement user impersonation in reporting services

asp.netcreporting-servicessql-server-2008-r2ssrs-2008

Software involved in this question is:

  • SQL Server Reporting Services 2008 R2
  • SQL Server Analysis Services 2008 R2
  • SQL Server 2008 R2 Database Engine
  • ASP.NET 4.5 Web Forms
  • ReportViewer component

We have several dozen reports. Some reports use T-SQL queries to our Data Warehouse database and some use MDX queries to our SSAS Cube.

Active Directory Security Groups secure which reports a user can access on the Report Server.

We additionally have made SSAS Roles that have Dimension Permissions on them which effective secures which data each user can access. We used AMO code to generate and maintain these roles and membership due to how many there are but that is beside the point and unrelated to the question.

I understand that there is a feature of SSAS called EffectiveUserName that we can pass to the cube for impersonation.

However, how can we impersonate a user within SSRS such that we will see only the reports that that user has access to?

We are currently trying to work out the software design of a custom report manager with ASP.NET and using the ReportViewer component. We would like to expose a text box or drop down to Administrators that allows them to put in or select employees and effectively run as that employee.

So in other words, even though I am authenticated into the ASP.NET Report Manager site as DOMAIN\User1, if I am in some role on the report server as an Administrator, I want to be able to type into a text box a username like User2 and be able to view all reports on the report server as DOMAIN\User2 would see them.

Thanks for any advice or answers you can offer.

Best Answer

A few things:

  1. You need to do this in code behind in my experience.
  2. You want a 'ReportViewer' object.
  3. I believe if you are hosting you need a reference to 'Microsoft.ReportViewer.WinForms' dll.

The code I used was done with xaml for WPF hosting a ReportViewer (abridged):

< Window x:Class="WPFTester.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:rv="clr-namespace:Microsoft.Reporting.WinForms;assembly=Microsoft.ReportViewer.WinForms"
    >
  ......
<WindowsFormsHost Grid.Row="2">
        <rv:ReportViewer x:Name="reportViewer"></rv:ReportViewer>
    </WindowsFormsHost>
......
</Window>

The important part you get is that I have a 'ReportViewer' Object named 'reportViewer' for my code behind. ASP.NET has some equivalent of this object but you also will also need the dll in the alias of 'rv' or similar. The code works similar to this:

private void ResetReportViewer(ProcessingMode mode)
        {
            this.reportViewer.Clear();
            this.reportViewer.LocalReport.DataSources.Clear();
            this.reportViewer.ProcessingMode = mode;
        }


        private void ReportViewerRemoteWithCred_Load(object sender, EventArgs e)
        {
            ResetReportViewer(ProcessingMode.Remote);
            reportViewer.ServerReport.ReportServerUrl = new Uri(@"(http://myservername/ReportServer");
            reportViewer.ServerReport.ReportPath = "/Test/ComboTest";

            DataSourceCredentials dsCrendtials = new DataSourceCredentials();
            dsCrendtials.Name = "DataSource1";  // default is this you may have different name
            dsCrendtials.UserId = "MyUser";  // Set this to be a textbox
            dsCrendtials.Password = "MyPassword";  // Set this to be a textbox
            reportViewer.ServerReport.SetDataSourceCredentials(new DataSourceCredentials[] { dsCrendtials });

            reportViewer.RefreshReport();
        }
Related Topic