Ssas standard authentication
I am trying to allow Excel 2010 to connect to an SSAS 2008 cube via IIS 7.5. I installed msmdpump in IIS 7.5 and was able to successfully access the cube via excel from the url http://localhost/Excel/msmdpump.dll
. This works for both Windows Authentication and Basic Authentication. So far, so good. However, I need this to work with user authentication, as we will potentially have hundreds of users who need access, and their credentials will be stored in a SQL 2008 table.
I tried to create a custom HTTP validation module but it is not called by Excel. Is it possible to create an http module or something else that will allow me to authenticate users to a custom data store when a user tries to access a cube in Excel?
PS After I authenticate the user, I would use impersonation to connect to SSAS with a Windows account.
source to share
I managed to get this to work with the http module. The code is very similar to the Custom Basic Authentication Module code on Code Plex. The key was that I had to set the application pool to integrated mode. I have set up the application pool to run under a Windows account. If the user's credentials were ok with the SQL table, then I pasted the value into the customdata property in xmla so that I could restrict access to what the user could see.
source to share