Connecting Excel to SSAS from the Internet with Basic Authentication


  • I know there's been lots of posts about this in the past, but I'm hoping that with IIS 7 in particular, things have changed.  I have an idea of how this might work, but I'd like some feedback on whether or not my approach is possible.

    Right now we're using the Dundas OLAP Services control to provide all of our Clients (all Internet Clients) to interact with our cubes.  We would like to move to Excel as our Client tool, and Sharepoint seems like it would be overkill as it's expensive and the only thing we'd need from SharePoint is the ability to dynamically interact with our cubes over the Internet.  Currently our ASP.NET web app restricts which users can access the Dundas control and the Dundas control connects to our SSAS database using an impersonated account.  We may require a few different Windows accounts with different SSAS roles/privileges in the future but we will always be mapping a large number of ASP.NET users to a handful of Windows accounts with SSAS access.

    We're currently running Windows Server 2008 Standard for our web server (IIS 7) and separate SSAS server.  SSAS is SQL Server 2008 Enterprise.  We'll probably be moving to R2 of SQL Server and maybe Windows Server as well in the near future.  Here's what I'm hoping I can do:

    • Install the SSAS datapump in IIS and configure IIS to secure the datapump with Basic Authentication
    • Distribute Excel Workbooks that reference the datapump in my PivotTable connection strings (with no username or password) using something like https://myservername/olap/msmdpump.dll (I'm hoping this means that Excel can/will use HTTPS)?
    • Since there's no username/password in the Excel connection string I assume Excel will pop up a Windows dialog asking the user for their UserName and Password
    • I will implement a custom HttpModule on IIS with a custom basic authentication provider using as a guideline
    • The custom authentication provider will check the credentials of the user against the database of my web app users
    • The custom authentication provider will also assign the HttpContext.Current.User to be a GenericPrincipal for the Windows user I want to impersonate when connecting to SSAS (my customer auth provider will need to map the credentials passed by the end user to the small number of Windows accounts used for SSAS)
    • The HTTP Request with the assigned Principal will finally hit the datapump and the use will be interacting with the SASS as if they were logged in with one of the Windows accounts


    From what I've read, this should be possible but there are a few things I'm unsure of:

    • It seems that prior to IIS 7, custom HttpModules could only be applied to ASP.NET and Web Services.  Am I correct in thinking that with IIS 7, I can now use a custom HttpModule to control anything that comes in through IIS, including the SSAS data pump?
    • Can is use the GenericPrincipal of HttpContext.Current.User as described to impersonate a different particular Windows user when accesing the SSAS data pump?  In other words, can the HttpContext.Current.User be distinct from the using info provided in HttpContext.Current.Request.Headers["Authorization"], and am I correct in thinking that the datapump will function in the context of the user specified by HttpContext.Current.User?
    • Can/will Excel use https when connecting to the data pump as long as https (rather than http) is used in my PivotTable connection strings?
    • Moved by Alex Feng (SQL)Moderator Thursday, November 11, 2010 6:12 AM move to an appropriate forum (From:SQL Server Security)
    Wednesday, November 10, 2010 9:16 PM