Yet Another Post About Analysis Services HTTP Connectivity

With risk to iterate the obvious, here is a lesson learned from the trenches:

Scenario: You want to let external users browse an Analysis Services cube over the Internet.

Setup: You have followed the steps in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 article by Edward Melomed to set up Analysis Services for HTTP connectivity.

Issue: Windows integrated security doesn’t work.

After a long battle where countless options have been tried, I have to admit a defeat. In our case, we’ve set up identical Windows local accounts on the SSAS server. This worked fine when connecting Report Builder 2.0 to the cube from our home machines. However, it appeared that the firewalls that our clients use make a minced meat of Windows integrated security. Strangely, Excel connects without a problem to the cube using Windows integrated security. However, Report Builder 2.0 chokes big time. I guess this has to do with differences between OLE DB (which Excel uses) and ADOMD.NET (used by Report Builder 2.0).

So, we had to give up on Windows integrated security over Internet. Instead, we went for Basic security with SSL, as follows:

1. Configure the msmdpump virtual folder for Basic security (this should be the only available security option).
2. Install the SSL certificate on the IIS server.
3. In Report Builder 2.0, set up a new data source that uses the Microsoft SQL Server Analysis Services. Click the Edit button on the Data Source Properties dialog box.

1. In the Connection Properties dialog, enter the URL to the msmdpump.dll, such as https://adventure-works/olap/msmdpump.dll.
2. Enter the user credentials. Click the Save My Password to avoid being asked to retype the password.
3. Here is an important step if you want to populate the Connect to a Database drop-down list. Click the Advanced button and locate the Integrated Security setting. Select and clear the SSPI default setting. Click OK.
4. Back to the Connection Properties dialog, expand the Connect to a Database drop-down list. You should see the list of the SSAS databases the user has rights to access. Select a database.
5. Click the Test Connection dropdown. The connection should succeed.

The data source is set up now. You can proceed with setting up a dataset with the MDX Query Designer.