Connecting to Analysis Services in a remote computer - Microsoft SQL Server 2008 R2

Recently I had a requirement to connect to a Microsoft SQL Server 2008 R2 Analysis Services which was running in a remote computer. And it's Analysis Services authentication was configured for Windows Authentication. Connecting and retrieving data from an Analysis Services located in a remote computer seems to be a real puzzle, so thought to write this post.

So my requirement was to query a cube which was deployed in the remote Analysis Services. Of course, I can't connect through SSMS (SQL Server Management Studio) through my windows login, because when I choose Analysis Services as Connect to Server type, authentication section is disabled. My first target was to create a successful connection with the remote Analysis Services.

One of my colleagues told me, we can create a successful connection this way.

1. In the remote server, create a user account and permit access to Analysis Services.
2. In my machine, create a user account, same user name and password as in created remote server user account.
3. Now, from my windows login try creating the connection with the credentials of created user account using some tool like BIDS (Business Intelligence Development Studio), Microsoft SQL Server 2008 R2 Report Builder 3 etc.

Well I thought of using BIDS, and I tried for around an hour. It was this error I was getting and then I had enough. 

Error

Then I thought to go forward with Report Builder.

Using Build selected a Server Name

Using the Build button, I have typed my server name and I did not change anything and tested the connection. Again I got this error "A connection cannot be made. Ensure that the server is running.".

Error

Then I went to Credentials tab and entered my created user account details.

Created account credentials

Came back and tested the connection. It said "Non-Windows user credentials were supplied for a non-http connection to Analysis Services."

Error

Then I went back to the credentials tab and ticked "Use as Windows credentials.".

Use as Windows credentials

Came back and again tested the connection and this time I got a message "Connection created successfully."

Connection created successfully.

So since the connection got created successfully, I thought to select a database. For that I clicked the Build button and clicked on the "Connect to a database" drop down box. Application got freezed for sometime and then there were no databases. I am pretty sure my remote Analysis Service has Adventure Works 2008R2 cube and I did a test connection from that window. Here is what I got.

Error

Isn't this weird? I was having a terrible time with this and then I logged in to my machine using created account. I opened SSMS, selected Analysis Services as Connect to Server type and clicked on Connect. I successfully got connected to remote Analysis Services. With BIDS and Report Builder, it was same as easy as SSMS. 

What I can't understand is, to access data in a remote Analysis Services, do we really have to create a same account as in remote machine and every time we need to access, do we have to switch our windows logins? I did a lot of testing in my test environment, but non of them worked. Please I am desperately looking for an answer to this. I really appreciate your valuable feedback.

Update - Tuesday, March 13, 2012

If you are using Report Builder, you can connect to remote Analysis Services using OLE DB as Connection type instead of Microsoft SQL Server Analysis Services.  

Select OLE DB

Click on Build button. On the next window, click on the drop down where "SQL Server Native Client 10.0" is highlighted and select "Microsoft OLE DB Provider for Analysis Services 10.0".

Select OLE DB Provider

Microsoft OLE DB Provider for Analysis Services 10.0 

Provide credentials of the created user which has access to remote  Analysis Services.

Provide credentials

Test Connection Succeeded.

You will see that, your test connection gets succeeded and you will receive all the database names in your remote Analysis Services in the Initial Catalog drop down box and you can select one as Initial Catalog.

Happy Coding.

Regards,

Jaliya