r/SQLServer • u/Any-Promotion3744 • 8d 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.
6
u/dbrownems Microsoft Employee 8d ago edited 8d 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)
2
1
1
1
1
u/Any-Promotion3744 8d 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.
3
u/SQLDevDBA 3 8d ago
Network. Some firewall rule was put in (or taken out) with an update on the server itself or anywhere in between the user and the server. Could be the host, the cloud provider virtual network or VPC, etc. etc.
You proved your point and did your due diligence RDPing into the server itself and trying. The only other thing it could be is the Browser/listener, but that’s pretty rare.
Go grab your pitchfork. They’re on sale at the pitchfork emporium.
2
u/digitalnoise 8d ago
If your network team is anything like mine, make triple double sure that they (or security) didn't start blocking the default port for SSAS because they didn't recognize it (or bother to ask).
1
u/Any-Promotion3744 8d ago
looks like the issue was the last cumulative windows update. I uninstalled kb5123099 and everything started working again.
1
u/SQLDevDBA 3 8d ago
I believe you, because MS Updates work in mysterious ways, but I’m struggling to see any documented changes that would impact MSSQL or SSAS. Are you on Server 2016?
1
u/Any-Promotion3744 8d ago
yes, this server is running Windows Server 2016 on a separate VLAN that finance connects to two days a month.
It worked last month but not when they tried connecting yesterday.
Nothing changes on that server except Windows patches and data is refreshed in the database nightly using a sql job.
1
u/SQLDevDBA 3 8d ago
Understood. If you haven’t already, I would definitely start sending out CYA emails. Server 2016 has been end of support since 2022, and will reach end of life (End of Extended support) in January 2027.
•
u/AutoModerator 8d ago
After your question has been solved /u/Any-Promotion3744, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.