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….
- Navigate to excel in Windows explorer (“C:\Program Files (x86)\Microsoft Office\Office14”)
- hold down the right shift key then right click the explorer window and choose Open command window here
- now paste this in to the window “
runas /netonly /user:REMOTEDOMAIN\USERNAME EXCEL“
- You will be prompted for a password. Enter it and press the ENTER key
- 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.