SSAS / Excel 2010 – An error was encountered in the transport layer

Connecting to SQL Server Analysis Services from a remote domain / login

When connecting to SSAS from a different remote domain / login using Excel 2010 where you have set up a windows user at the SSAS server, provided adequate permissions at the SSAS server to read the cube etc. but still cannot connect from a local Excel the issue is probably on account of the different domains / logins and a lack of trust between the servers.

All of your settings are correct, its probably just the windows being over cautious.

I found two workarounds for this

  • Create a local user with the same login details. Login as that user and then connect (not ideal)
  • Connect using the runas command and include the /netonly switch

A little more info on the second method….

  1. Navigate to excel in Windows explorer (“C:\Program Files (x86)\Microsoft Office\Office14″)
  2. hold down the right shift key then right click the explorer window and choose Open command window here
  3. now paste this in to the window “runas /netonly /user:REMOTEDOMAIN\USERNAME EXCEL"
  4. You will be prompted for a password. Enter it and press the ENTER key
  5. Excel will load.

You will now be able to connect to the Analysis Services on the remote machine with the same details as your excel session will be trusted.

If this has fixed your problem you might like to create a shortcut.

Simply create a standard shortcut to EXCEL and then edit the command to this

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\USER "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

 

NOTE: This also works for other programs exhibiting the same behaviour.
The reason this works is that you are allowing the remote server to trust your connection. Obviously you will still need all of the appropriate permissions and ports open etc. so this article has assumed that you have done this already.

About these ads

About davidbridge

I run David Bridge Technology Limited and specialise in Database design and development including administration and web application development using .net
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , . Bookmark the permalink.

3 Responses to SSAS / Excel 2010 – An error was encountered in the transport layer

  1. Frenk says:

    Worked like a charm! Thanks so much for this tip.

  2. simo says:

    So usefull! Thanks a lot!

  3. Shawn says:

    I was having a similar issue connecting with excel to analysis services. Your post got me thinking in the right way. It looks like the computer is automatically trying to authenticate with the current domain. to bypass that just specify the correct domain (domain\) or no domain (.\) This has been troubling me for 2 weeks, so I hope this helps someone else avoid that frustration.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s