I am trying to allow Excel 2010 to connect to a SSAS 2008 cube via IIS 7.5. I have setup the msmdpump in IIS 7.5 and can successfully access the cube through excel with 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 custom authentication since we will have potentially hundreds of users that will need access and their credentials will be stored in a SQL 2008 table.
I have tried creating a custom authentication http module however it isn't called by Excel. Is it possible to create an http module or something else that will allow me to authenticate users against a custom data store when a user tries to access a cube in Excel?
P.S. After I authenticate the user, I would use impersonation to connect to SSAS with a Windows Account.
I was able to get this to work with an http module. The code is very similar to that of the Custom Basic Authentication Module on Code Plex. The key was that I had to set the Application Pool to Integrated Mode. I set the application pool to run under a windows account. If the user credentials were ok against the SQL table, I inserted a value into the customdata property in the xmla so I could limit access to what the user could see.