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 SQL Server 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 how to solve this.

As the SQL Server 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 TCP 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 SQL server then you must use the port instead of the name

So vm-sql has instances sql01,sql02,sql03

then use vm-sql\sql01 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 SQL\instance.

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