"Error in transport layer" refreshing SSAS tabular cube results in Excel

Question

I have a user receiving the above error trying to refresh a pivot in Excel pulling data from an on-prem SSAS cube. This user was able to perform this OK as recently as last Tuesday. No changes have been made to the model or the server; I'm not sure about his workstation, however he has the same Excel version as I do (I have no issue). This is SSAS 2017. He did say today that he was able to refresh things OK when in the office, previous attempts have been over VPN. Prior to last Tuesday all attempts over VPN were successful (no change to his Cisco AnyConnect client version either).

Any thoughts? Nothing showing in Profiler against the SSAS server. I've readded his perms to the cube, and also tried to get him to connect to other servers/cubes -- all result in the error above.

Thanks,
Jason

Answer 1

The following process fixed the issue.

Log into your notebo

1. ok, but don’t start anything.
2. Connect to VPN
3. Hit CTRL-ALT-DEL and then choose Lock your workstation
4. Unlock your workstation with your regular network password
5. Restart, log in, connect to VPN, open Excel and retry refreshing your workbook

During all of this, it’s important to not open Excel or try and refresh the data until after you’ve connected via VPN.

Basically, the same local password cache refresh you'd use after updating your network password. His feedback was that after doing this he's able to refresh again -- no idea if that holds up over time, or was just a coincidence, but for now it seems to have worked.

Answer 2

This error basically means that Excel has troubles reaching SSAS at all. So there'll naturally be nothing in Profiler because SSAS is blissfully unaware that something or other has been trying to make contact. Permissions do not get to be checked either. The usual source for the problem is when a user had the pivot opened and used while on one connection between the Excel-running PC and SSAS-hosting server, and next tries to refresh/change after the connection has changed (a new VPN session could do the trick as well). The usual solution is to retry the operation - I've yet to see this error to not go away on the first refresh. The problem will manifest independently for each open file and probably each connection within, which may well explain how other servers/cubes behaved the same.

Answer 3

Hi @Jason

Please check if the answer given by AlexeiStoyanovsky is helpful to you.
Any updates, please let us know.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Alexei Stoyanovsky - I appreciate the response. I haven't seen this error before, so it's all new to me.

Due to COVID, this user has been working remotely and connecting via VPN basically constantly (this week was the first "in office" day in awhile). We've tried multiple refreshes/retries on an existing workbook, and he's also unable to create net new connections to the any cube in new workbooks (while painful because of the amount of analysis, I did try the "let's just rebuild it" option with him). All still yield the same problem. Do you have any suggestion as to where I should look next?

Regards,
Jason

Answer 5

Thanks @Alexei Stoyanovsky - I'll keep checking. This has persisted for over a week during multiple restarts, VPN reconnects and trying both existing workbooks and creating new from scratch. It's very puzzling.

J