A few days ago, we had issues to connect to an Analysis Services 2008 cube from Excel 2007.

Imagine that you are in a Windows domain or workgroup and that you must connect to an Analysis Services on a remote server/server farm with its own workgroup or domain.

In our case, we are building something like software as a service (SAAS) platform for a client where the concerned cube is just one small part of all the services & software we've to build. To access the cube, we are currently using a VPN connection.

The first thing is that Analysis Services is relying on a Windows authentication to work, this is the only option we have:

http://msdn.microsoft.com/en-us/library/ms143708.aspx

It's not possible for us to create a trust between the remote environment and the consumer one. And one or two other data consumers may connect from other environments.

When trying to access the cube with XL 2007, we can thus forget the "Use Windows Authentication" as the user we are logged on with is not known by the remote environment.

But we can choose to enter the user name and password of a windows user on the remote platform:

For the user name, we tried the forms "User", "RemoteDomain\User", "User@RemoteDomain" when working with a domain, "RemoteServer\User", "User@RemoteServer", or with the IP replacing the remote server name when working in a workgroup.

The problem was always the same, XL shows this window :

Then, after choosing a cube or perspective, it takes very long time before showing the message "Initialization of the data source failed" :

No choice, you click the ok button and then a new dialog window (Multidimensional Connection 9.0) appears:

And only then you are ready to work.

This is of course really annoying.

So, after unsuccessfully playing with and without domain users, what did we do?

1. We saved the connected workbook to reopen it later. We still had the very long waiting time and the "Initialization of the data source failed".
2. We saved the XL connection to reuse it. We still had the very long waiting time and the "Initialization of the data source failed".
3. We configure the HTTP Access to SSAS (http://msdn.microsoft.com/en-us/library/cc917711.aspx). This time, we didn't have to wait a long time to receive the "Initialization of the data source failed".
4. To avoid the multiple windows and warning messages, we added the user and password to the Windows Vault of the Credential Manager of Windows 7 : http://windows.microsoft.com/en-us/windows7/What-is-Credential-Manager

About the Credential Manager, it's easy to find it in Windows 7 and Vista. In Windows XP, you can type the command: control userpasswords2

With the credentials managed by the credential manager, the user only has to type the URL of the SSAS and check the Windows authentication option.

Perhaps there are other ways to deal with these issues but we're happy to find this quick setup answer to deal with these.