Vidas Matelis Analysis Services Blog

Last week I answered question in MSDN Analysis Services forum thread that at first appeared to be very simple. Somebody asked how do you control access to SSAS cubes if information about users and groups is inside SQL Server table and not in the Active Directory. I answered that you cannot do that. This was my understanding from reading Books Online and various articles. I was sure that Microsoft SQL Server Analysis Services 2005 works just with integrated security.

For clients that do not use Active Directory you have an option to setup OLAP HTTP access and then control security to the folder where http dlls are. This does work, but you compromise security, as all people have the same access rights to database. So you have to implement additional security methods in the front end and in the firewalls (example access to http folder is allowed just for the specific front end, etc).

But I was wrong with my answer. Mosha Pasumansky noticed that thread and replied that it is possible to do this if you have middle tier. And he actually describe 2 different methods on how you can do this. All you need to do is add additional parameters into connection string and then setup matching SSAS role security.

Method 1:

User connects to middle tier application, lets say IIS. IIS is setup to connect to SSAS as some named user with certain assigned rights. Then you middle tier application dynamically builds SSAS connection string with additional parameter”Roles=Role1,Role2,Role3″. This parameter specifies what additional roles have to be assigned for that specific user.

Method 2:

Sames as Method 1, but this time into connection string you pass parameter: “CustomData=appuser1”. Here appuser1 is any string that you use to identify specific connection. Then in SSAS role definitions you can use function CustomData() in the similar way as you would use function UserName() and retrieve value you pass from connection string.

Note: You can find example on how to use UserName() to setup dynamic security in this paper by Carrie Williams.

Both of these methods are not very well documented. I have not tested them, as I just found about them now. But it is good to know that such security options exists.

 Vidas Matelis