Speaking the language of business intelligence with an Australian accent

Saturday, January 19, 2008

PPS Data Connection Security with CustomData

There are two well-documented data source security methods available in PerfornancePoint Monitoring & Analyzing: the default Application Security model and Per User Connections. However, as a wise green creature once whispered, "No. There is another..."

More often than not security requirements for a BI solution are based around certain users only being able to access specific members of several important dimensions. CustomData security functionality in PPS serves this purpose very well and provides a solution that sits between the two already well-known data security methods.

Application Security - a single application pool account is used to make all connections to data sources.

  • Advantage: because one account makes the data source connections the monitoring server caches results providing greater speed responsiveness to all users.
  • Disadvantage: no data security is possible based on who the user is because all users are connecting via one the account; the database does not know who they are.

For more details on the common connection pitfalls of application security please see this post.

Per User Connections - each user's credentials are used to connect to data sources.

  • Advantage: highly secure, naturally this security is dependant on there being some sort of existing security framework within the data source itself.
  • Disadvantage: little or no caching of dashboard data can occur as each data request is made with the individual user's credentials. Dashboard responsiveness and speed can take a hit.

Security is important to all the clients I work with but I always advocate careful thought and consideration before simply switching on Per User connections on a PPS Monitoring server. There is definite performance hit that comes from the fact that each user gets his or her own data. For more information on the performance difference between the two principal security methods please see the PPS Performance Tuning and Capacity Planning whitepaper.

At the time of this writing, not much official documentation exists on CustomData security, hence the reason for this post. The only reason I knew of its existence originally was this post on the PPS team site some time ago. In that post lip service is paid to the CustomData option but we're really told much more than "it's possible".

What is CustomData Security?

CustomData security is a middle ground between application and per user security. We still use an application pool account to connect to the data source, but we also have access to data about the Domain\Username of each user who is interacting with the deployed dashboard. We use this user-specific data to drive security dynamically using information configured in the relational database the cube sources data from. This alleviates having to configure and maintain many SSAS database roles tied to specific domain accounts or groups. Instead we configure a single, dynamic MDX based role which will be detailed later.

NB: there is no discernable data caching advantage to using CustomData Security. The performance is much the same as when using the PerUserSecurity model. If performance is the most important factor in a PPS deployment then Application Security is certainly the best performing option.

What is Dynamic Dimension Security?

Limiting user access to certain members of a particular dimension attribute hierarchy based on their windows credentials is nothing new. For example, dynamic dimension security may decree that when I look at a dashboard I can only see Australian states in the State/Province dashboard filter.

FilterAustralia

...while on the other hand Adrian should only see a list of Canadian provinces.

FilterCanada

The majority of work to set up CustomData security involves the creation of a dynamic dimension security framework in your multidimensional data source and the relational source that feeds it, not PPS. If you already have security of this kind set up on your UDM then you're already most of the way there. The set up is minimal on the PPS side of things.

How I set up SSAS dynamic dimension security?

Providing any user-driven security access to PPS data cannot function without some form of back-end, SSAS and relational security infrastructure. There's a little bit to do to set this up, so instead of going into a long-winded walk-through I will instead point to two excellent articles/posts on the topic. Both of these documents cover similar ground. Using a dimension containing user details and a factless fact table to relate each user to the specific members of the dimension they are allowed to see e.g. Reseller Stores. Once the relational database infrastructure and data is in place, a SSAS role is set up containing MDX code to return a set of the allowed dimension members for the current user. I highly recommend reading at least one, if not both in order to full understand the remainder of this post.

  • Brian C Smith of Hitachi Consulting wrote this blog post that contains sample T-SQL code and steps to set up a simple UDM.
  • Teo Lachev wrote this article for SQLMAG in which he covers a number of different techniques including the factless fact approach.

These are just two examples of how to implement dynamic dimension security. I'm sure there are many other creative ways to implement this kind of functionality.

How do I turn on CustomData security?

As mentioned earlier, the PPS configuration is minimal. Bpm.UseASCustomData is a boolean attribute that can be found in the same web.config files as the Bpm.ServerPerUserConnection attribute. Change the BPM.UseASCustomData attribute from False to True in the appropriate web.config files as listed below.

  • Preview Site: %\Program Files\Microsoft Office PerformancePointServer\3.0\Monitoring\PPSMonitoring_1\Preview\
  • Web Service: %\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\
  • SharePoint: %\Inetpub\wwwroot\wss\VirtualDirectories\80\

WebConfigAttributes

NB It is very important to remember that the Bpm.ServerPerUserConnection be set to False when using CustomData security. We want to connect to the data source using the Application Pool account, not each individual user. Turning on Per User connections completely defeats the purpose of the security method being discussed here.

What does UseASCustomData do?

When Bpm.UseASCustomData is switched to True any PPS data source connections made to Analysis Services 2005 contain an extra attribute on the connection string: <CustomData>. The CustomData connection string attribute was added to SSAS 2005. The Monitoring server will populate this attribute with the Domain\Username of the user making the request. The data source connection itself is still being made as the application pool account.

This can be particularly useful in multi-server environment where Kerberos is not configured and the double-hop issue is encountered. The information in the CustomData field can now be used to identify the user without the need for Kerberos.

How do I confirm that CustomData is switched on?

Once you have made the adjustments to the appropriate web.config files, set up a SQL Profiler trace on the SSAS server where your cube is located. Use Dashboard Designer and run a Test Connection on one of your SSAS data sources.

As you can see from the shot below the connection to the cube is being made by my Application Pool account, NETWORK SERVICE (in a production environment you would want to be using a specific domain account for the app pool identity). More importantly note that there is a <CustomData></CustomData> attribute that has been added to the connection meta data containing my Domain\Username details.

ProfilerCustomDataAttribute

How do I make use of the CustomData attribute?

In order to get at the Domain\Username string that PPS provides within CustomData we use the CUSTOMDATA() MDX function. The CustomData connection string attribute does nothing more than hold a string of characters. The CUSTOMDATA() MDX function simply returns that character string to us, we can do whatever we want with it from there. There isn't a great deal of information out there about this function but Chris Webb posted about it a while back. In that post he pondered what this function could come in handy for. Hey Chris, I think this is a good one.

In MDX you can gain access to a string containing the Domain\Username of the currently connected user by via the USERNAME MDX function. USERNAME is the MDX equivalent of the T-SQL SUSER_NAME() function. Try running this query in SSMS to return your Domain\Username.

WITH MEMBER [Measures].[MyUsername] AS USERNAME
SELECT
[Measures].[MyUsername] ON 0
FROM [Adventure Works]

The USERNAME function is used in the articles listed above to access the Domain\Username string for the connected user to drive the dimensional security. The functionality detailed by Teo and Brian is relevant when a user connects to the cube "as themselves" i.e. they are accessing the cube with their individual Windows credentials via Excel, SSMS, ProClarity or even PPS with Per User security. Because of this SSAS knows who each user is.

With PPS CustomData security our aim is a little different: we want to avoid connecting to the cube with individual user credentials. Under the application security model the connection is always being made by the same application pool account. In this case the USERNAME function is useless to us. It will always return the same value: that of the application pool identity account (in my example trace above it would return NT AUTHORITY\NETWORK SERVICE every time). When UseASCustomData is switched on, the Monitoring Server sneakily adds the Domain\Username string to the CustomData attribute for each individual user who interacts with a dashboard. Now, if we don't do anything else the string in the CustomData attribute will simply sit there benignly minding its own business.

Here's the trick: using the same techniques detailed by Teo and Brian we replace the USERNAME MDX function with CUSTOMDATA(). This way we are able to access the Domain\Username value in the CustomData connection string attribute to implement the same dynamic functionality without connections being made on a per user basis.

Testing CUSTOMDATA() within Dashboard Designer

Here's another way of testing that CustomData security is configured on the Monitoring server. Within Dashboard Designer create an analytic grid report and enter the following MDX in the Query pane.

WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()
SELECT
{ [Measures].[MyUsername] } ON 0,
NON EMPTY { [Geography].[Geography].[All Geographies] } ON 1 
FROM [Adventure Works]

Switch to the Design pane to check that your own Domain\Username is returned.

AnalyticGridResult

Typically you will not use CUSTOMDATA() in an MDX statement within Dashboard Designer. It is best to keep the MDX code that drives the security within the roles managed by SSAS as described in the referenced articles.

How do I set up dynamic dashboard filters?

Once the Bpm.UseASCustomData attribute is set to True and your back-end SSAS security framework is set up the creation of dynamic filters could not be easier. The heavy lifting is being done by the SSAS roles. The easiest filter to create is an MDX Query filter type. All that is needed to populate the example State/Province filter that limits Adrian and my access to the states/provinces of our respective countries of birth is the following, very simple, MDX.

StateProvinceFilter

Note that I do not need to reference the CUSTOMDATA() MDX function here. All that logic should be contained in the SSAS role that defines the security behaviour.

A word on roles

If you have gone to the trouble of setting up a user-driven security infrastructure in your SSAS cube the last thing you want to do is limit the security access to just PerformancePoint monitoring. All other cube browsing applications should be able to take advantage of what has been created. The simple way is to create at least two roles: one (or more) for all users to connect with user/role based Windows security for Excel, ProClarity, SSMS etc.etc. and a second role for PPS data source connections.

The Windows based role/s grants access to whichever domain groups and users require it. This is the general windows security access role for non-PPS connections. Users are recognised by their windows credentials and the MDX USERNAME function is referenced to apply security in the dimension security MDX statements.

The PPS role need only provide access to the domain account used by the PPS application pool identity. This role uses the CUSTOMDATA() function in the dimension security MDX statements instead of USERNAME. The role name is then referenced in the PPS data source definition as seen below so that we can be sure the connection to SSAS is made using the permissions defined therein.

DataSourceRole 

A role security MDX tip

I have learned from reading Mosha's blog that you should try to avoid using STRTOMEMBER or STRTOSET functions if possible for performance reasons. The sample code in the dimension security articles use these functions in the dimension security MDX. I have provided some sample MDX code that alleviates the need for the STRTO... functions. Note that the sample code contains references to both CUSTOMDATA and USERNAME functions, you can only use one at a time.

For example, instead of

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
STRTOMEMBER("[Employee].[Login ID].&[" + CUSTOMDATA() | USERNAME + "]")
,"Security Filter"
)

Try using this

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
FILTER(
  [Employee].[Employee].[Employee].MEMBERS,
  [Employee].[Employee].CURRENTMEMBER.PROPERTIES("Login ID") = CUSTOMDATA() | USERNAME
 
)
 
,"Security Filter"
)

UPDATE: 29th Feb 2008. David Gustavson, author of the Performance Tuning and Capacity Planning for PerformancePoint Monitoring Server whitepaper has informed me that there is virtually no caching benefit when using the CustomData data connection method described here. The original version of this post indicated that CustomData provides similar caching performance to Application Security - this is incorrect. In fact the cache performance is not much better than using Per User Connections. I have altered the post to reflect this.

21 comments:

Anonymous said...

This is briliant! I've been involved in deploying a PPS solution where data is being filtered by username. As you said, there seems to be no caching going on with these reports. I'll definately try this customdata method out. Thanks!

Nick Barclay said...

Thanks Kaisa, much appreciated.

Cheers,
NB

Patrik Molin said...

So this meens that if I want a user based security model in my cube I will have to have a "Employee" dimension?

That, at least for me, means a whole lot of maintenance!?

Anonymous said...

Nice article, but in fact using using ADOMD CustomData prevents cache entry sharing much the same way that ConnectionPerUser does. You still loose the cache performance. The real benefit is that you can get some security without having to configure Kerberos for proper delegation.

Nick Barclay said...

Thanks David, you're absolutely right. I have ammended the post to reflect this.

Cheers,
Nick

Anonymous said...

Hi Nick, hope you get this blog comment. I am trying to retstrict cetain KPI's based on the user being a member of an Active Directory group, i.e if they are in the group they can see the KPI but if not then its invisible (even if its only the value thats invisible, thats ok too). I read in your Rational Guide book the chapter on Security (chapter 12) and have created two test KPIs (nothing special just actual = 1 and target = 1). The one I called Allowed and set the permissions to Authenticated users and read. The other I called Not allowed and removed the Authenticated permission. When I view the deployed dashboard containing the scorecard which contains the two KPI's, I see both which is fine because I have Administrator privelages. When I sign on as another user or use the Sharepoint Switch user functionality. I get a horrible red message "There is a problem with this scorecard. Please verify that the scorecard and all dependent items have been published correctly and that you have adequate permissions to view them. If the problem persists, please contact your system administrator." Hoping that you can help out with this issue. Trevor H

Chirag Patel said...

Nick, I get a Invalid token error when on the following line:

[User].[Username].CURRENTMEMBER.PROPERTIES("Login ID") = CUSTOMDATA() | USERNAME

It is do to the '|' in the query. I am I doing something wrong? I am new to SSAS and MDX.

BDooley said...

Nick, thank you for the great article. Thanks to you I don't have to setup Keberos!

bdooley

David Baker said...

Hi Nick,

Does the population of the CustomData field itself cause a performance hit or only when the CustomData field is actually used in the MDX? I was assuming/hoping its population alone did not somehow affect ability to use the cache.

I ask because I see a model where I use the CustomData field in MDX only for an Employee drop down filter. This is then used as a PPS filter on my dashboard, but in doing this I do not need to use the CustomData field in any other query on the dashboard. I am hoping I then only take the hit on the dropdown build which should be negligable.

Dave

Anonymous said...

hi... why is it that when i run the query below i retrieve nothing?

WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()
SELECT
[Measures].[MyUsername] ON 0
FROM [Cost DM]

Varadaraj said...

I think this works only when u are passing Windows ID to the custom data field. Do you know if this setting works with a true Forms based authentication where the users are basically stored in a database.

Saugat said...

Nick,
Thanks a ton for this. I have been banging my head literally over this one, since the security I implemented with username worked in the cube browser but not in the deployed PPS dashboards in sharepoint.

But using the procedure you outlined, I could do it within minutes. Thanks for saving my day!

Saugat said...

Nick,
The security outlined here works fine with analytic grids but not with pivot charts.

In the data source info of pivot charts I am providing the role name against the roles property but it does not work. Please help!

This is my data source info for the pivot chart

Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PPSMonitoring2;Data Source=ip specified here;Roles=Role

Nick Barclay said...

Saugat, PivotTable and PivotChart reports use their own connection. They do not use the security provided via the application pool. This is why you're having problems.

PivotChart & PivotTables are deprecated in PPS2010. I would advise you not to use these any more.

Dan said...

Great article Nick. Varadaraj asked a question that is also applicable to us. Do you know if the CustomData method will send the authenticated user name to SSAS if the SharePoint site is using Forms Based Authentication, or is it only applicable to Windows Authentication?

Nick Barclay said...

Thanks Dan. Far as I know it's only Windows Auth, but don't quote me on that. I have not implemented Forms Auth in this scenario. It's worth testing, though. Let us know if you find anything out.

Dan said...

We just tried this method with a SharePoint site that uses forms-based authentication and it passes:

NT AUTHORITY\IUSR

So, it doesn't work with FBA. IIS considers the user anonymous, while SharePoint considers the user authenticated through FBA.

Nick Barclay said...

thanks Dan :)

Mark W said...

Hi Nick, super article. I used the factless fact table idea(brian c smith) and then created a ppsrole for my service account, ran the test mdx in pps to test customdata() and it works well. I also have a role for excel etc and it works perfect. However when I try to limit my filter in pps it simply displays all the members, security never kicks in BAFFLED

Bilal said...

It is one of the best posts I have been to. It has solved all my queries and took me to achieve the end result. It's a really good blog for those who are using the combination of SSAS and performance point for developing their dashboards enabling user based security on top of it.

Cheers,

syed said...

Hi Nick,
Thanks for this great article. I got a question here. We are implementing Claims security with an external Identity Provider and unlike FBA, I do get my identifier claim value (username) as CustomData. This helps us in implementing user based security that you outlined in this article. I want to extend this security further on cube by filtering with organization in addition to logged in user. Can this be done by tweaking Monitoring service to include the string we desire in CustomData?
There are also few suggestions to go with .NET Assembly security approach.
Any thoughts on this?

Thanks
Tabarak