Using Per-User Identity with Reporting Services and SharePoint

Almost anyone that has dealt with business intelligence in the Microsoft space is aware of the double hop problem. Simply put, when a user wants a server to execute a process on their behalf, that process will have a different identity, if any at all from the originating user, and therefore user focused operations such as per-user security trimming can’t be performed. The traditional solution to this problem in the Windows world has been to use Kerberos authentication, which allows server to pass on secure user tokens to other servers on behalf of originating users.

Unfortunately, Kerberos requires additional infrastructure, and is not adopted in all organizations. Smaller to mid sized organizations tend to stay away from adopting it, primarily due to its perceived complexity, which leaves them with the double hop problem. In the long term, claims based authentication promises to solve this problem, but it is still in its infancy, and is not adopted at all yet in the SQL Server suite of products, which is where double hop pain is felt most keenly.

The new BISM tabular model with Power View works around this problem by taking advantage of the EFFECTIVEUSERNAME property available in Analysis Services (I’ll be writing more about that later), as does Excel Services and PerformancePoint Services in SharePoint 2013. With EFFECTIVEUSERNAME, the server makes its connection with the data source using its service credentials, but then every query identifies the originating user, and those queries are executed in the context of that user. It’s a clever workaround, and takes care of much of the BI stack, but what about Reporting Services and relational data?

As it turns out, Reporting Services has been able to do this sort of thing for a long time. It’s just been rather cleverly concealed or misunderstood. SQL Server itself has a function, SetUser() that can be used by someone with sufficient privileges to impersonate another user. Originally implemented for testing purposes, Reporting Services takes advantage of it in much the same way that EFFECTIVEUSERNAME works in Analysis Services. The place to turn on this function is within a data source editor.

Using SharePoint Integrated mode, there are 3 main tools that can be used to define a data source. A data source can be created from any library that is configured to use the Report Data Source content type. In addition, SQL Server Report Builder can be used to edit a data source, or to create an embedded data source in a Report. Finally, Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (depending on your version of SQL) can be used to create shared data sources. And the best news is, the interfaces are all different!

image                                        Setting Connection credentials in SQL Server Data Tools

image                                Setting Connection credentials in SharePoint 2013

image                                      Setting  Connection credentials in Report Builder

The option that we’re interested in, in all three cases is “Use this user name and password” (stored credentials). In the case of Data Tools or BIDS, the impersonation option isn’t available. Normally what would happen is that the connection would be deployed to the server, and then edited afterwards. We can therefore ignore this interface. However, examine the description of the impersonation option in both SharePoint and Report Builder. Although worded completely differently, they mean exactly the same thing. And in my opinion, Report Builder has it right. In fact it’s a stretch to say that the SharePoint screen isn’t dead wrong.

The SharePoint form can only be considered correct if the word “this” applies to the currently logged in user. To my mind, when I read that option, I am led to believe that it will be set to the account that is being used to make the connection (the stored credentials), when it is in fact the opposite. The setting in Report Builder is far clearer and more accurate.

When the option is checked, the SetUser() SQL function will be used in advance of any queries performed by the report, and the value will be set to the currently logged in user, NOT to the credentials specified under “Use this name and password”. The credentials specified here are used to make the connection to the SQL server, and because of the security restrictions of SetUser(), MUST have at least the dbOwner role on the queried database, or the SYSADMIN fixed role on the server. My recommendation is the latter if possible. This requirement also makes it very important to restrict the editing of the Data Source.

I imagine that most of the confusion around this feature comes from the fact that in most cases, when you are using impersonation, you are saying “I want to execute this function as this account”. However, in this case, with stored credentials, it’s always the stored credential executing the function, and what we’re asking it to do is to impersonate the currently logged in user. It’s sort of backwards to the way we normally think about impersonation. It’s not immediately intuitive as to why a logged in user would need to impersonate themselves, but that’s basically what’s happening.

Using this option therefore allows you to utilize per user permissions through Reporting Services on SharePoint without having to use Kerberos. This is a good thing, but you should be aware that in many cases, using a proxy account (by not selecting the impersonate option) may be a better option. There are several drawbacks to using per-user authentication. Impersonation should only be used when it is absolutely required.

If you open up the report processing screen for a report that uses a data source defined for impersonation, and select the “used cached data” option, you will receive the warning “This report can not be cached because one or more of the data sources credentials are not stored”.

image

The reality is that although the credentials are in fact stored, the report could be different for different users, and caching depends on a consistent result. Therefore, to prevent confusion, it’s not allowed. If the report is particularly large, this removes an important performance optimization technique.

From the same screen, it’s also made very clear that snapshotting is not an option when impersonation is being used, from the error “This report can not run from a snapshot because one or more of the data sources credentials are not stored”.

image

The reasons are the same as for caching, and the implication is that snapshots cannot be created, preventing us from storing a version history for the reports.

Finally, subscriptions are also rendered useless through the use of impersonation. An attempt to create a subscription to a report using an impersonated data connection results in the following error:

image

Per-user identities also require management on the SQL server side. Because we are impersonating the currently logged in user, that user must be managed from within SQL itself, whereas a proxy account only requires that account to have access to the data in question.

To summarize, Kerberos is not required to do per user queries in Reporting Services for relational data. The SetUser() function in SQL Server combined with the Execution Context option in data connections allow this to be performed quite effectively. However, just because it can be done, doesn’t mean that it should be. In many cases a simple proxy account can do the job, and should likely be allowed to, unless the requirements absolutely demand otherwise.

8 thoughts on “Using Per-User Identity with Reporting Services and SharePoint

  1. Peter

    I couldn’t set up the excel reporting services on sharepoint 2013. My students couldn’t join the domain so we should set up something like impersonated account so they can access the report through BIreportviewer.

    I tried to read your post several times but some sections that i don’t know how and where to look for.

    Can you help me with this problem? I couldn’t find microsoft instruction on this matter!

    Thank you so much

  2. Sylvain

    I needed to know if Kerberos is really required for “per user identity” connection. The option “Impersonate the authenticated user after connecting” is very confusing.
    Thanks for this post.

  3. Andy Taylor

    John, this is an excellent post. This is something I’ve been struggling with for days, & who would have thought it was so simple once it was well explained…
    Many Thanks
    Andy Taylor

  4. Roman

    Thx John for the excellent post and the presentation at Toronto SharePoint User Group Meetup.

    Question: Does this best practice change any in light of the new SharePoint 2013 delegation feature called “EffectiveUserName”?

    In what scenarios is it still warranted to configure Kerberos with SPNs?

    Thanks,
    Roman

  5. jpw@unlimitedviz.com Post author

    Roman – EffectiveUserName simply brings this (SetUser) capability to Analysis services. This is even more relevant in the 2013 world. You’ll still need Kerberos to use per user auth to connect Excel Services or PerformancePoint to SQL Server

  6. Josip

    Hello John,

    Excellent article that clears up a lot of confusion!

    Regarding a statement mentioned in the article:
    “The credentials specified here are used to make the connection to the SQL server, and because of the security restrictions of SetUser(), MUST have at least the dbOwner role on the queried database, or the SYSADMIN fixed role on the server.”

    It seems like the only way to get this to work is to give the login used in the Data Connection sysadmin permissions on the SQL server. Giving it db_owner permission still returns the following error:
    “Setuser failed because of one of the following reasons: the database principal ‘DOMAINuser’ does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.”

    Seems like I’m not the only one expiriencing this issue:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f12795b0-a899-49a9-9983-d45681685fd6/data-sourceimpersonate-the-authenticated-user-afterand-setuser

    It is mentioned that SETUSER does not work with Windows credentials however that does not make sense since everything works fine if the login is given sysadmin permissions.

    Could you please shine more light on this subject?

    Thank you and best regards,
    Josip

  7. Rick Searle

    Thanks John!

    Your article finally ended my endless Google searches for a solution to my dilema.

    Regards,
    Rick

  8. Pingback: How to Configure Credentials For Your Reporting Services Data Source | www.TavisLovell.com

Leave a Reply

Your email address will not be published.