Freigeben über

Passing user names to Analysis Services with personalized connection strings in SQL Server 2016 Reporting Services

  • Artikel

Today’s post is from Kay Unkroth, a program manager who’s been working on Reporting Services and Analysis Services here at Microsoft since 2010.

A common challenge in Microsoft SharePoint environments configured for claims-based authentication at the front-end concerns the delegation of user identities to backend data services requiring Windows authentication, such as Microsoft SQL Server Analysis Services. SharePoint simply does not resolve claims identities into Windows identities. A common workaround is to deploy Web Application Proxy in front of the SharePoint farm, which can map claims users to Windows users before they reach the farm, but this is a complex deployment in an already complex environment. Another workaround is to use stored credentials, but then the actual user identity does not flow to the backend, making it next to impossible to apply row-level security based on user IDs in an Analysis Services data model. Of course, the ideal solution would be to enable end-to-end claims support, but this work still lies ahead of us. Yet, there is some good news! Thanks to longtime Microsoft Reporting Services architect and blogger Robert Bruckner for his work on making this feature a reality, Reporting Services 2016 now introduces support for personalized connection strings in paginated reports (RDL) and Power View reports (RDLX) with embedded data sources, or shared data sources (RSDS), and shared datasets (RSD), which can be used to flow user IDs as custom data to Analysis Services. Strictly speaking, personalized connection strings are not a security feature, and I will highlight the limitations in a moment, but row filters can work with user IDs submitted as custom data in order to return the desired information subsets to the corresponding users—and because practically all versions of Analysis Services support custom data, personalized connection strings in Reporting Services 2016 can work with basically any Analysis Services backend.

Using a Personalized Connection String in Report Builder

So, how does this personalized connection string feature actually work? Let’s illustrate this based on a sample Tabular model in Analysis Services. I used the Adventure Works Tutorial available at https://msdn.microsoft.com/en-us/library/hh231691.aspx as a starting point and added a trivial measure called CustomDataValue, defined as CustomDataValue:=CUSTOMDATA() , which simply returns the custom data value back to the client application. This isn’t a row filter yet, but displaying the custom data value might be useful when defining an actual row filter later on. Next, I built an RDL report with a personalized connection string in order to display the custom data value, as the following screenshot illustrates. Unlike Power View reports, RDL reports can display measures that return a string value by using the value expression =First(Fields!CustomDataValue.Value) .

clip_image002

Figure 1   A Personalized Connection String Test in Report Builder

There are a couple of things to point out in the screenshot above. First and foremost, the data source definition contains the following important element: CustomData={{ USERID }} . This is the personalized part of the connection string. Reporting Services 2016 replaces the {{ USERID }} tag with the actual identity information of the Reporting Services user. In a SharePoint environment, this would be the identity of a SharePoint user. In a native environment, this would be the user logged into Reporting Services. On the other hand, if you run the report directly in Report Builder, note that the tool does not resolve the {{ USERID }} tag (only the report server does). Report Builder sends the {{ USERID }} tag unprocessed as a regular string to Analysis Services. This might be helpful if you want to provide report designers with access to some sample data. Just treat the “{{ USERID }}” string as an identity name in that case.

The next step is to upload the report to a SharePoint document library or publish it to a native SSRS server instance. I chose a claims-enabled SharePoint site. After uploading the RDL file, I edited the data source definition via the Manage Data Sources option in the SharePoint context menu, and provided stored Windows credentials as required to run the report, as shown in the screenshot below. The other valid option would be to Prompt for credentials, as long as the Use as Windows credentials checkbox is selected, but this a less common use case. With stored credentials that have access to the data model, the report can run and it shows the correct identity information of the currently logged on SharePoint user as the custom data value. clip_image004

Figure 2 A Data Source Definition with a Personalized Connection String in SharePoint

Identity Information Flow based on a Personalized Connection String

Here’s what’s happening under the covers (see also the figure below):

1. The report server replaces the {{ USERID }} tag with the identity information of the currently logged on user to create a personalized connection string. In my case, this is a SharePoint claims user with the identity i:0e.t|adfs|spuser@lab.contoso.com.

2. The report server passes the personalized connection string to the Analysis Services client library and connects to Analysis Services using the stored credentials specified in the data source definition.

3. For every request that the report server sends to Analysis Services, the Analysis Services client library adds the custom data value in the property list.

image

Figure 3   Identity Information Flow using Personalized Connection Strings

Defining a Row Filter using Custom Data

In the data model, the custom data is retrievable by using the CUSTOMDATA() function, as the previous CustomDataValue measure abundantly illustrated. Now the task is to define an actual row filter based on the CUSTOMDATA() function. This is as easy as following the steps documented in the supplemental tutorial lesson Implement Dynamic Security by Using Row Filters at https://msdn.microsoft.com/en-us/library/hh479759.aspx. Specifically, I added a calculated table named Employee Security and added a row my SharePoint test user Id as well as another one for the {{ USERID }} tag for Report Builder. And then in the Sales Employees by Territory user role, I added the Windows user account that the reports use to connect to the data model as a member. In my lab environment, this is ContosoLab\SSRS_Service. And most importantly, I modified the row filter formula for the Sales Territory table to use the CUSTOMDATA() function instead of the USERNAME() function, as follows:

='Sales Territory'[Sales Territory Id]=LOOKUPVALUE('Employee Security'[Sales Territory Id], 'Employee Security'[Login Id], CUSTOMDATA(), 'Employee Security'[Sales Territory Id], 'Sales Territory'[Sales Territory Id])

Of course, you could also follow Marco Russo’s suggestion in the comments to the tutorial and use CONTAINS() instead of LOOKUPVALUE(), in which case the filter expression would be:

=CONTAINS ('Employee Security', 'Employee Security'[Login Id], CUSTOMDATA(), 'Employee Security'[Sales Territory Id], 'Sales Territory'[Sales Territory Id])

Whichever way you prefer, the point is the same. Replace USERNAME() with CUSTOMDATA() to act on the user identity information Reporting Services submits as custom data instead of the use identity that SSRS actually uses to connect to the data model. The following screenshot depicts my Employee Security table and row filter expressions.

clip_image008

Figure 4   Defining a Row-Level Filter based on Custom Data using SSDT

Using Personalized Connection Strings in Power View

At this point, you can make the data model accessible to your users by creating a shared RSDS file in a location where your users only have read access, for example. Although personalized connection strings also work in embedded data sources, read-only shared data source files are more appropriate because the latter contain both the personalized connection string definition and the stored credentials, so your users do not need to know the password of the login account and cannot modify the connection string. Your users can hit the ground running, whether they want to create shared datasets, paginated reports, Power View reports, or datasets used in Mobile BI reports. The following screenshot shows a Power View report based on a shared data source using the configuration settings displayed in earlier screenshots. Just remember to select the correct data source type of BI Semantic Model for Power View instead of SQL Server Analysis Services for all other scenarios. For my SPUser account, the Power View report displays only data for the United States according to the row filter and the parameters defined for the SPUser identity in the Employee Security table.

clip_image010

Figure 5   A Power View Report flowing Identity Information as Custom Data to Analysis Services

If you are familiar with previous versions of Reporting Services, you might be wondering if personalized connection strings aren’t creating a dynamic user context because any user-dependent connection information was not supported at all for shared data sources. This has changed in Reporting Services 2016. When associating a dynamic user context with a shared dataset/report, the query results are now considered user-dependent. The report server takes this into account for report session caching behaviors (user-dependent vs. shared), and user-dependent restrictions/behaviors are applied when it comes to report history snapshots, scheduled cache refresh plans, as well as subscription execution and delivery.

Also keep in mind that personalized connection strings are strictly speaking not a security feature, as mentioned earlier. Any Windows user with permissions to access the data model can send any custom data. For example, if ContosoLab\SQLAdmin had access to my data model, that user could simply use Excel and manually specify CustomData="i:0e.t|adfs|spuser@lab.contoso.com" on the connection string to see the same data as the SPUser account. So make sure that only the stored credentials account of your shared data source has access and do not disclose the password to your users. Also make sure that this account does not have server admin permissions in Analysis Services and is not a member of the Windows Administrators group on the Analysis Services server because row filters do not apply to server administrators in SSAS.

And that’s it for a whirlwind tour of personalized connection strings in Reporting Services 2016. I hope this feature will help you bridge some gaps in identity flows whether you use SSRS in SharePoint-integrated mode with non-Windows authentication or plug in custom or forms-based authentication modules into your native SSRS deployment.

Comments

  • Anonymous
    June 04, 2016
    Hi Chris,What's the difference or benefit of using Custom Data vs. EffectiveUserName? I believe it's more common by using EffectiveUserName in SP & SSRS now.BTW, is EffectiveUserName still the same usage in SQL 2016? Thanks, Di
    • Anonymous
      June 30, 2016
      Yup, EffectiveUserName still works as before in SQL 2016, but EffectiveUserName supports only Windows/Active Directory user accounts. It doesn't support other, non-Windows types of user accounts. If you're using, say, SharePoint forms-based authentication or custom authentication, you're often using non-Windows authentication and EffectiveUserName isn't an option. In contrast, you can set CustomData to any string you want - it doesn't need to be a Windows user name - and do whatever you choose with that string.