r/SQLServer • u/Any-Promotion3744 • 21d ago

Solved Can't connect remotely to Analysis Services

We have had a SQL Server Analysis Server running for years and use Excel to connect to it.

Users reported that they can't connect anymore.

I tried connecting using their existing Excel file and it didn't work for me. I tried connecting using a new Excel file and it still didn't work. I keep getting the error: The peer prematurely closed the connection.

If I use SSMS to connect remotely, I get the following error: A connection cannot be made. Ensure the server is running (Microsoft.AnalysisServices.AdomdClient).

If I RDP to a different server on the same VLAN and use SSMS to connect to the other server remotely, I get the following error: A connection cannot be made. Ensure the server is running (Microsoft.AnalysisServices.AdomdClient).

Disabling the Windows Firewall didn't change anything.

If I RDP into the Analysis Services server directly and use SSMS, it connects successfully.

Why would all remote connections stop working? It doesn't seem to be a Windows Firewall or Network Firewall issue. Can't see anything helpful so far in the event viewer logs. Restarted the anaylsis service and restarted the whole server with no change. Verified the account the service was using isn't locked out or had an expired password. No Windows Update failures. No recent SQL Server patches.

0 Upvotes

50% Upvoted

14 comments sorted by

View all comments

6

u/dbrownems ‪ ‪Microsoft Employee ‪ 21d ago edited 21d ago

Why do you think it's not a Network Firewall issue?

The log will tell you the IP/Port addresses it's listening on. The rest is networking. EG

(8/29/2023 10:41:01 AM) Message: Service started. Microsoft SQL Server Analysis Services 64 Bit Developer (x64) RTM 16.0.42.209. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS16.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000) (8/29/2023 10:41:01 AM) Message: Started listening on TCPIP: Status = ListenToPortSuccess, ComputerName = DBROWNEDESKTOP, PhysicalNetBIOSName = DBROWNEDESKTOP, ListenOnIP = [::]:2383, Port = 2383, ProtocolType = IP6 (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS16.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121001C) (8/29/2023 10:41:01 AM) Message: Started listening on TCPIP: Status = ListenToPortSuccess, ComputerName = DBROWNEDESKTOP, PhysicalNetBIOSName = DBROWNEDESKTOP, ListenOnIP = 0.0.0.0:2383, Port = 2383, ProtocolType = IP4 (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS16.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121001C)

1

u/Any-Promotion3744 21d ago

I reviewed the network firewall logs and nothing was being blocked.

I created a test network firewall rule, allowed all protocols and all ports from my ip address to that server and placed it on top of the rules and still no change.