Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

PerformancePoint 2013 Dashboards: Applying Dynamic Security using CustomData and EffectiveUserName

  • 26 August 2013
  • Author: Mike Milligan
  • Number of views: 23487
  • 0 Comments

I recently had the great pleasure of solving a PerformancePoint 2013 puzzle and I thought I'd share how I did it to help others and as a reference for my fellow colleagues.  Our team was trying to implement dynamic active directory security for PerformancePoint 2013 dashboards.  A variety of PerformancePoint web parts were tested as part of this endeavor including Analysis Services scorecards, analytic charts and grids, SSRS reports (native mode), and Excel Services.

 

Devin Knight does an excellent job of explaining how to get this to work in his blogpost:  Analysis Services Dynamic Security.

 

For this method to work with PerformancePoint and Excel Services we will need two Roles:  ReadUserName and ReadCustomData.  The first one, ReadUserName is set up exactly how Devin describes.  This will be the role that Excel and SSRS will use.  The second one, ReadCustomData is the same except you need to replace the function UserName() with CustomData().  This ReadCustomData role will be the role that is referenced in the data source editor of PerformancePoint dashboard designer. 

To test this I had to make sure that I wasn't in any other AD groups or roles.  I only had to add myself and the service account that runs the SharePoint 2013 PerformancePoint content (a.k.a. Proxy) to the ReadUserName and the ReadCustomData role.  We also have a ReadUnrestricted Role.  I had to make sure I wasn't in that role.

 

Here is another link, that describes how to get this working with PerformancePoint 2010:  How to configure dynamic security in Analysis Services and Performance Point 2010 without Kerberos.

 

Here is a link that describes various methods for testing if the security is working correctly: 

Unit Testing Role Security in Analysis Services.

The series of posts below are wonderful!  I won't repeat what is said there; but, I will say I was relieved to see that this person also came to the conclusion that two roles would be needed. 

 

http://dbaexperience.com/2013/01/24/sharepoint-2010-sql-server-2012-bi-dynamic-security-part-1/

http://dbaexperience.com/2013/01/31/sharepoint-2010-sql-server-2012-bi-dynamic-security-part-2/

http://dbaexperience.com/2013/03/12/sharepoint-2010-sql-server-2012-bi-dynamic-security-part-3/

 

Here is where things got interesting... No, really.

 

This post:  Using EffectiveUserName To Impersonate in SSAS mentions that you have to set up Kerberos on your SSAS server to get this to work with SSRS MDX based reports.  I can tell you we got it to work without that.  I did set up the data source in SSRS as this person describes: "Credentials stored securely in the report server, use as windows credentials, Impersonate the authenticated user after a connection has been made to the data source."

 

I got a little lucky (ok, maybe a lot lucky) in that my account had certain permissions that no one else in my active directory group had.  If it weren't for that, I don't know if I would have ever gotten any of this working.

Our initial goal was to set these permissions on either the SharePoint proxy account or the domain service account that process the cube.  That worked for the SSRS MDX reports; but, the Excel Services dashboard pages quit working.  Currently we are faced with the possibility that these permissions will need to be set for all the dashboard users and we are investigating what that actually means.  (http://windowsitpro.com/active-directory/hiding-data-active-directory-part-3-enabling-list-object-mode-forest)

 

image

 

We think the List contents is the one that made the difference.  My Active Directory sent me these links that he felt describe what is going on pretty well.  Here is what he said: 

'The link below explains the end result as the Account authenticating for the SSRS reports needs Read access to the TokenGroupGlobalAndUniversal attribute. This is done by adding the required account to the Windows Authorization Access  group in Active Directory.

Originally it was thought the SharePointProxy account was the account being used. I did add it to the group last Saturday and it did not resolve the issue. But today it was identified that the service account that connects to the data warehouse for cube processing was the account needing to verify the users group memberships to present the reports based on this information to the clients.

I also confirmed this to be the attribute by allowing Read access to all the User Object attributes and removing them until this single attribute was remaining. Removing this last attribute denied the reports with the Unknown username or password error.'

http://sqlinsight.net/blog/ssrs/ssrs-subscriptions-not-working/

http://blogs.msdn.com/b/richin/archive/2008/08/27/ssrs-subscription-tggau-rights-authzinitializecontextfromsid-exceptions.aspx

 

The other piece that seemed to make a difference was that the SSAS service is running under the NT AUTHORITY\NetworkService account.

EDIT:  We couldn't get the cube to process with this setting.  We ended up changing it so the service ran under a domain service account.

 

Post in the comments if you have questions.

 

more info...

Hiding Data in Active Directory, Part 3: Enabling List Object Mode in a Forest

Could not obtain information about Windows NT group/user 'DOMAIN\user', error code 0x5

Print
Categories: Blogs
Tags:
Rate this article:
No rating

Mike MilliganMike Milligan

Other posts by Mike Milligan

Please login or register to post comments.