page static_html_caption.xsl b2008_12_29

Dec. 12, 2008

Options for managing cube access security when using SSAS XMLA provider

Summary

“XML for Analysis (XMLA) is a Simple Object Access Protocol (SOAP)-based XML protocol, designed specifically for universal data access to any standard multidimensional data source residing on the Web.” That is the definition which starts the Books Online article at http://msdn.microsoft.com/en-us/library/ms187178(SQL.90).aspx.

This articles discusses the options and issues regarding cube access security in the context of XMLA. Since the Microsoft XMLA provider uses SOAP HTTP, the articles could have alternatively been titled “The intersections of SSAS and HTTP from the perspective of security.” That is because the only security architectures available to a developer are those which are supported both by SSAS and by HTTP.

SSAS security can be summarized as follows. Access control is granted or denied to SSAS Roles and Roles can have one or more NT Security Principals (users, groups) as members. So security to resources involves a SSAS-specific thing (Roles) with an NT-specific thing (Security Principals). But it is a fairly simple, one-size-fits-all model. I will refer to this concept as “security trimming” (borrowing the term used on the web tier).

Security on the Microsoft web server platform is a complex topic which involves many components and has many permutations. A summary is therefore difficult. But a summary of the contextual constraints is as follows. The context is web site built upon IIS and ASP.NET. Managed code running on this site makes XMLA calls via HTTP. There is credential associated with the HTTP request.

HTTP Credentials

When the HttpWebClient class makes a request, there is an associated credential. This credential can be either i) that of a specified username/password, ii) that of the IIS service account, iii) that of the user who is accessing the web site. The first two options are available without any “heavy lifting” from a network infrastructure standpoint. The third option does require an infrastructure component – Kerberos must be deployed across all tiers of the application, from the browser up to SQL Server.

Kerberos Option

If you already have or can have Kerberos as part of you intranet web application deployment, then you have an easy option. This is because the end user (desktop) credential can follow the request from the browser to SSAS – it can “hop” across tiers. The Role member which SSAS sees is that of the user. The web server is a non-entity from a security perspective and security trimming can be administrated in SSAS. Your application can still make use of web tier security tweaks, but they are optional.

The problem is that it's a lot more complex to handle authentication with Kerberos. You'll also have to give the web server the "Trust machine for delegation" privilege in AD. You'll need to ask a domain admin to do this and normally they don't really like to accept this type of request.

Web-tier Credential Options

So what if Kerberos is not happening for you? They key thing to understand is that SSAS is not going to know who is calling. Don’t fight it – accept it – and continue reading.

Without Kerberos, SSAS must be told by the web server who is the user. This is accomplished by specifying a credential for the XMLA SOAP request. As was mentioned earlier, this credential can be either that of a specified username/password or that of the IIS service account. These are discusses separately below.

Single Credential Option

Using either an IIS configuration (identity of application pool service account) or an ASP.NET configuration (identity of impersonation), you can specify what Windows Principal will make the SOAP call, which will also be the principal which SSAS sees as making the request. If your SSAS web application does not need to differentiate between users, then solution path is pretty clear: add an SSAS role which has unrestricted access to the cubes and add this Principal as a member. But if this is where you leave it, there will be no security trimming in SSAS.

What if you need security trimming? While you cannot in this scenario have SSAS trim by identity, you still have the option to trim by role. Why would you do this? And how? First the why. Security trimming by role may not be intuitive because it is flipping around the way you normally approach SSAS security trimming, which is to have (from an request perspective) static roles and a dynamic member. To flip it round and trim by role, you will have in the request a static member identity and dynamic roles.

This is accomplished as follows. In SSAS, setup security trimming roles as normal. But add the single web credential as a member to all of the roles. That is the easy part. Now in the XMLA request, we can take advantage of a lesser-known XMLA request property called Roles. We use this property to pass to SSAS a list of roles. And security trimming will pay attention to those roles.

First a warning. If you have in the request a role which has not been configured in SSAS, then the request will fail with a security violation error. So we need to be careful in using the XMLA Roles property this way.

So now we have to tackle the problem of where to get these roles. If the goal is to have security configurations be made outside of the web application, then we need to have these roles stored in AD. Here in brief is my solution.

1. Setup the web tier to use Windows integrated authentication.
2. In AD, add SSAS security trimming roles (as AD Groups) to users using names which begin with “XMLA_”.
3. In the web application, use the AuthorizationStoreRoleProvider with the RolePrincipal class to iterate the users role membership. Add to the XMLA Roles property those role names which begin with “XMLA_”.

This architecture allows SSAS security trimming to be configured in AD without the need to deploy Kerberos.

Specified Credential Option

The web tier can make an XMLA request using a specified credential. SSAS will see the request as from this credential and will perform security trimming accordingly. So another security implementation involves using an XMLA request credential which depends upon the original user.

If the web site is setup to use Forms Authentication, then the web application would need to maintain a table to map authenticated user IDs to an XMLA credential. If the Membership Provider is being used, then the XMLA credential can be stored in Profile. If custom authentication logic is used, then that logic would also have to provide an NT credential for the XMLA call. If security is a concern, then this identity to credential mapping should be stored in an encrypted manner – perhaps in web.config if it is fairly static. Either of these would be appropriate for an internet application which accesses SSAS on the back end.

If this is an intranet application and the goal is to use Windows integrated authentication and manage SSAS security trimming outside of the web application, then the web application will have to ask AD for an NT username and password (and possibly domain) from which to construct the XMLA credential. Or you could use a similar approach to the “Single Credential Option” discussed above, and specify in AD a role for this user (for example “XMLA_cred_A”). The web application can then iterate the roles, looking for the one with the “XMLA_” prefix, and use an encrypted section in web.config to lookup the NT password for that named credential. The credentials in the web application should be fairly static and coarse-granular if you wish to avoid managing security trimming by modifying this list. But since the SSAS roles define the trimming, this should indeed be the case.

Combining XMLA Roles and Credential

There is nothing preventing one from combining both of these approaches. You could store in AD both a named credential and a list of roles, and then make the XMLA call with the named credential and passing the roles in the Roles property. This combined approach adds expressiveness to the security trimming. You could, for example, use the credential to trim cubes, and then use the roles to trim within cubes.

In AD, make credential groups and role groups. Again, the web app code would have to parse the groups, get the group which represents the XMLA credential (for example, begins with “XMLA_cred_”), get the password from an encrypted web.config section, and create the credential object for the XMLA call. And while parsing the roles, it would also created the XMLA Roles property (a comma separated list of those roles which began with “XMLA_role_”).

There is some jumping through hoops here because we are using AD Groups to represent a credential and also SSAS roles. A more direct approach would be to have named AD properties for these things and use the System.DirectoryServices namespace to get a user then get the values of a couple named properties, perhaps “XMLA_user, XMLA_pwd, and XMLA_roles”. If the XMLA password were stored in AD, then there would not be an need to maintain a password lookup table in web.config.

References

1. How to configure an ASP.NET application for a delegation scenario [http://support.microsoft.com/default.aspx?scid=kb;en-us;810572]
2. How to use the ASP.NET utility to encrypt credentials and session state connection strings [http://support.microsoft.com/default.aspx?scid=kb;en-us;329290]
3. Howto: (Almost) Everything In Active Directory via C# [http://www.codeproject.com/KB/system/everythingInAD.aspx]
4. http://books.google.com/books?id=bATOzjQmhIgC“Applied Microsoft Analysis Services 2005" by Teo Lachev (Best resource on SSAS role based security)
5. http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1563.entry"Dimension Security Tips, Tricks and Problems" (also discusses work-around for calculated member errors)
6. http://www.mosha.com/msolap/articles/kerberos_delegation.htm"Enabling Kerberos and Delegation in Analysis Services 2005" (step-by-step instructions)
7. http://www.robkerr.com/post/2008/05/Microsoft-BI-with-Constrained-Kerberos-Delegation.aspx"Microsoft BI with Constrained Kerberos Delegation” - step-by-step with screenshots
8. http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.olap/2006-11/msg00188.html"Problem with HTTP access (msmdpump.dll) to SSAS on a machine different than the one where Analysis Services is installed."