This project has moved. For the latest updates, please go here.

Cannot use ASSP procedure when AS is clustered

Topics: User Forum
Jan 22, 2016 at 8:00 AM
I have two node Windows server failover cluster. On first node I run SQL Server RDBMS and on second node I run AS. On RDBMS I created linked server to AS. SQL Browser servis is started on both node. When I try to call ASSP stored procedure as part of openquery I got error "(No connection could be made because the target machine actively refused it". On other hand when I do the same thing on standalone server with SQL Server and AS openquery runs without any problem.

Tnx.

Tomislav
Coordinator
Jan 22, 2016 at 8:40 AM
Do standard MDX queries that don't use ASSP work over this linked server? Which particular call are you trying to use? Most of them should not have any problem running in clustered mode, but some make a second internal connection and that may be why you are seeing issues.

The SQL Browser service should be redundant in a clustered scenario as SSAS does not support named instances in clustered mode (it always connects on the default port)
Coordinator
Jan 24, 2016 at 8:28 AM
Edited Jan 24, 2016 at 8:28 AM
I suspect I have hit something similar. If you connect in SSMS and right click on the server node and choose Properties what do you see as the server name? Basically what does SSAS think is its own server name?

At one of my clients they installed SSAS as a named instance for the cluster then set the port to the default port number. So AdomdServer.Context.CurrentServerID was returning ClusterVirtualName\SSAS and unless SQL Browser was running that internal connection ASSP would open (like for Discover sprocs) would fail.

So figure out what SSAS thinks is its server name then get on the node where SSAS is currently running and see if you can open a connection to SSAS using that server name. Keep us posted.
Coordinator
Jan 24, 2016 at 8:29 AM
What's an example ASSP sproc that is failing?
Mar 27 at 12:57 PM
I have the same scenario as described. Failover Cluster with to nodes.

CALL ASSP.DMV("
SELECT DIMENSION_ORDINAL
, [DIMENSION_NAME]
, DIMENSION_CARDINALITY
, DEFAULT_HIERARCHY
FROM $SYSTEM.MDSCHEMA_DIMENSIONS
");

When I run the query (below) on secondary node it works, but after switching back to primary node I get the error...

Executing the query ...
The connection either timed out or was lost.
Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.
    An existing connection was forcibly closed by the remote host
Run complete

ASSP was added by running xmla script (1.4.2) on primary node. The dll file is located on the disk that is attached to the active node.
Coordinator
Apr 6 at 9:30 PM
This is a shot in the dark. Can you make sure that the SQL Browser service is running in services.msc on both nodes and that it's set to auto-start?

Let me explain a bit more something I've run into at a client of mine. They had a cluster with SSAS. The SSAS instance was installed as a named instance but then the port was changed to the default port 2383. So you could connect via SSASVIRTUALNAME which pointed to the active server's IP address and to the default port 2383. However, when the ASSP sproc looked up the connection info with Context.CurrentServerID it returned PRIMARYSERVER\SSAS (where SSAS was the instance name). And since SQL browser wasn't running, connecting to that failed.

See if that's the issue you're hitting. If not, please specify more details like whether it's the default instance or a named instance and what port. Also, connect Object Explorer in SSMS to SSAS. Then right click on the server node and choose Properties and then report back what the "Name" of the server is according to that dialog box that pops up.