I’m currently evaluating SQL Server Analysis Services and various methods for connecting to, and consuming the data. Excel has built in support for not only a “direct” connection to SSAS, but also supports an HTTP connection to msmdpump.dll hosted in IIS (see this article for how to set that up). In order to support a custom authentication store for the project I am working on (i.e. don’t rely on Windows Integrated authentication for the clients), I wrote a HTTP module that works with Basic Authentication and queries a database to validate a user.

While this worked on my local development machine, it was failing on my virtual machine which I use for testing (has no development software, as clean as can be install of Windows, etc). I was getting the error “Errors in the OLE DB provider. An error occurred while loading the connection dialog box component for prompting”. I also tried a couple of other machines that covered a wide range of conditions (Excel 2007 vs 2010, Visual Studio installed vs not installed). After searching the web for awhile and finding bits and pieces of information, I came to the conclusion that the reason that it worked on my local dev machine is that I had SQL server installed.

I found some references to updating to the OLE DB 9 provider, but that update was from 2006 I believe, and so it should have been bundled with Windows 7. Also, whenever I found a download for that, it always sounded like it was something you were supposed to install on your SQL server. Well I finally found an entry on Microsoft Connect which provided a solution. You need to install the ASOLEDB10 provider from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1b2bd555-cb5b-47b9-88c7-3f89f3b43779. As with the OLE DB 9 provider downloads I found, this one still sounds like it’s more for SQL server, but installing it on my Windows 7 test machine (running Excel 2007) fixed the issue.