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 davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , . Bookmark the permalink.

7 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.

  4. Hi bro…your blog is very helpful…it worked in my case…so thank you so much for sharing this knowledge…

  5. hirmando says:

    This approach is great however it appears to not work when you have other instances of Excel open that are not using the RUNAS credentials. I wonder if you’ve come across this or can confirm this limitation. Thanks for a helpful post.

  6. kevin says:

    Go to C:\ProgramData\Microsoft\Windows\Start Menu\Programs , make a copy of the Excel 2016 shortcut , call it Excel 2016 Cube ,
    replace following
    “C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE”
    C:\Windows\System32\runas.exe /netonly /user:DOMAIN\USER “C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE”

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s