Excel Connectivity with SSAS - An error was encoun

Question

I have a SSAS Cube in SQL 2012 to which I can connect from Excel 2010 without an issue using a windows user credentials. However whenever I restart the server hosting the cube and I try to refresh the pivot in the excel (in a client machine) it throws and an error saying "An error was encountered in the transport layer". I am then prompted to re-enter the password for the windows account I am using to connect to the data source.

The problem is that if I have couple of pivots open in different sheets in the same excel book per each pivot I am prompted to do this (even if I do a Refresh and not a Refresh All) which is bit of a trouble.

Is there a way to fix this or at least to make sure I have to enter the password only once and it will not seek for each and every pivot being refreshed?

Answer 1

If you are trying to connect to the cube under credentials which are not the credentials you used to log into your laptop, then the proper way of accomplishing this is the following. Create a new connection from scratch. (You can't modify an existing connection.) Do the following:

1. Data tab... From Other Sources... From Analysis Services...
2. Type in a server name, username, and password... click next
3. choose your database and click next
4. Check "Always attempt to use this file to refresh data". And check "Save password in file". Click Yes when the prompt pops up.
5. Click finish.

That will save your username and password (in clear text) into the odc file. (Be aware of this for security purposes.)

Based upon your description of the symptoms I suspect you didn't have these checkboxes checked when you created the connection. Thus it would continue to prompt for username/password when it needed to reconnect.