SQL Server analysis service SSAS Can’t connect Remote An existing connection was forcibly closed by the remote host #SQL #SQLServer #Azure #SQLHelp

For some time I build/rebuild many SQL sites and often migrate the “old” servers to Azure. Faced many challenges. Clustering FCI and Always On availability groups seen and build many configurations. But lately I did not so much SQL and now in my new lab setup I had an old issue. Looking at the problem many Google hits came along, good advice but not fixing the issue. The setup is a multiple region Azure virtual wan with secure hub Running Application's in North Europe but some need to connect to West Europe also. But Failed to connect remotely. As many Security components are between this, I had to trim down the issue, to region to vnet. Oh and I used marketplace SQL servers handy and quick, and went against my own advise the next next finish approach never do that.

Step 1 the issue Failed to connect remotely to the analysis service SSAS, got two instances running one tabular and one multidimensional

===================================

Cannot connect to VM-WEU-SQL-p-01:2399.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)


Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()

Nice Error but no clue on the actual issue or solve this.

As the analysis service uses an Ini file for the configuration it is easy to adjust it and test it.

Checking the port and removed IPv6

the good old netstat -a -p shows me all the ports

and yes the port 54321 is used for the SQL.

Next checking the access rights also common on the internet solutions

Non of these fixes helped my problem. But the thing is if you have multiple instances running on the then you must use the port instead of the name

So -sql has instances sql01,sql02,sql03

then use vm-sqlsql01 when you are running this local but access this remote you must use vm-sql:portnumer vm-sql:12345

So in my case vm-weu-sql-p-01:54321

Then I noticed something when you are using and sql server with the default instance mssqlserver and a SQL Server analysis service SSAS, with a multidimensional instances. you can connect remotely on name SQLinstance.

With the broker services running you can connect on name, did not see this in any document. So keep this in mind running SSAS in a multiple instance config always use portnumbers.

Also keep in mind you application must support portnumbers!! If not use a default instance to avoid these issues, or use a better application that does support port numbers. Hope it helps you to find the right solution for you, let me know if you have or face this issue.

ope it was helpfull thanks for visiting my blog.

Follow Me on Twitter @ClusterMVP

Follow My blog https://robertsmit.wordpress.com

Linkedin Profile Http://nl.linkedin.com/in/robertsmit

Author: Robert Smit [MVP]

Robert Smit is Senior Technical Evangelist and is a current Microsoft MVP in Clustering as of 2009.
Robert has over 20 years experience in IT with experience in the educational, health-care and finance industries.
Robert's past IT experience in the trenches of IT gives him the knowledge and insight that allows him to communicate effectively with IT professionals
who are trying to address real concerns around business continuity, disaster and regulatory compliance issues. Robert holds the following certifications:
MCT – Microsoft Certified Trainer, MCTS – Virtualization, MCSE, MCSA and MCPS. He is an active participant in the Microsoft newsgroup community and is currently focused on , Failover Clustering, SQL Server, Azure and all things related to Cloud Computing and Infrastructure Optimalization.
Follow Robert on Twitter @ClusterMVP
Or follow his blog https://robertsmit.wordpress.com
Linkedin Profile Http://nl.linkedin.com/in/robertsmit

Robert is also capable of transferring his knowledge to others which is a rare feature in the field of IT. He makes a point of not only solving issues but also of giving on the job training of his colleagues.

A customer says ” Robert has been a big influence on our technical staff and I have to come to know him as a brilliant specialist concerning Microsoft Products. He was Capable with his in-depth knowledge of Microsoft products to troubleshoot problems and develop our infrastructure to a higher level. I would certainly hire him again in the future. ”

Details of the Recommendation: “I have been coordinating with Robert implementing a very complex system. Although he was primarily a Microsoft infrastructure specialist; he was able to understand and debug .Net based complext Windows applications and websites. His input to improve performance of applications proved very helpful for the success of our project

 

This article was originally published by The Windows Server HA Blog. You can find the original article here.