Wednesday 25 May 2011

SSRS 2008 - Logged In User within Data Extension

For those of you that don't know what a Data Extension is, it essentially allows the developer to define how to retrieve data from various different data sources. Microsoft provide some of the core extensions, for example, an extension exists for Oracle and MS Sql Server databases. Then, because Microsoft have taken this modular approach with extensions, you, the developer, can build your own extension that defines how to connect and retrieve data from different types of data source, you can then plug that straight into SSRS and you're good to go. To be able to create a data extension, your classes need to implement specific interfaces, and there's a bit of configuration file tinkering required, for more information regarding this, you can read this article.

In order to maintain this modular design, all the details about a particular instance of a data source are defined outside the extension and are then passed to the extension when the report is run. For example, the extension may require a username, password and/or connection string. These pieces of information are set up when the data source is first created within SSRS, or, if it's credential based, the user may be prompted just before the report is run.

However, I've recently come across the need to find out who the user currently running the report is from within a Data Extension. There are a variety of reasons you may want to do this, in our particular example, we have database level security and so all our users have their own database user. We also use SSRS with Forms Authentication connecting to an Active Directory user store, the user logs in to SSRS using that active directory name but, the database user that they connect to is different. The user is never aware of this and so does not know their database credentials. We needed a way of finding out what user was connected, then, using that information we could find out their database credentials on the fly and run the report for that user.

At first, this seems a pretty simple problem, first of all, we create a new data extension for our data source type using the tutorial in the above link. Then, we just need to grab the user that's logged in. That should be pretty simple right? After all, the whole of SSRS seems to run as a web application so surely, we can just use


string user = HttpContext.Current.User.Identity.Name;


And in the majority of cases, you would be correct, this works, however, when you actually go to run the report, HttpContext.Current is magically set to null and then you start getting NullReferenceExceptions.

So, why is this?

After searching through many a DLL, I eventually found that, maybe unsurprisingly, that to run the report, the application uses a separate thread. This separate thread obviously doesn't have access to HttpContext.Current. But, fortunately for us, threads also have a user associated with them and that can be found with this piece of code:


string user = System.Threading.Thread.CurrentPrincipal.Identity.Name;


So, if  you stick these two pieces of code together, you'll have a reliable way of getting the user that's currently running the report. Your final bit of code should look something like this:


string name;
if (HttpContext.Current != null)
    name = HttpContext.Current.User.Identity.Name;
else
    name = System.Threading.Thread.CurrentPrincipal.Identity.Name;


Now with this information, we can query a seperate database, grab the database credentials and use them as the username and password of the data extension. Hey presto, everything works seamlessly without the user ever knowing.

Yes, this does break some of the modular design of extensions but, in this particular scenario, it seems like the best, and only option.