Are you suffering from random AdomdConnectionException thrown when trying to connect to a named instance?

Article
11/14/2007

This is fixed starting with CU6. More info in this post .

I recently analyzed an issue reported by one customer who had an SSIS package which was repeatedly opening connections against an existing and working named instance of Analysis Services 2005, and from time to time, the Open method of the AdomdConnection class was throwing an AdomdConnectionException ("The 'YourInstanceName' instance was not found on the 'YourServerName' server").

Default instances of Analysis Services 2005 listen on a well known TCP port which is 2383, but for named instances it can be any TCP port. So, clients must dynamically discover on which port a given instance is listening in order to actually establish a session with it.

On the client side, it is an XmlaClient function (Microsoft.AnalysisServices.AdomdClient!Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetInstancePort) the one responsible for determining on which port a given instance is listening on. If the connection string didn't include an instance name, then it returns port 2383, but if an instance name is provided, the Discover method is invoked with a RequestType of DISCOVER_INSTANCES and the particular instance name as the only Restriction. Internally, this Discover method sends the discovery request to SQL Server Browser who is listening on TCP port 2382 (Analysis Services redirector is implemented in msmdredir.dll, loaded by SQL Server Browser, and it is responsible for responding to clients who want to dynamically discover named instances installed on that server).

On the server side, the AS redirector component accesses the msmdredir.ini file to extract the most recent information about all named instances installed and available on the server, with the information found under the ConfigurationSettings\Instances element in that file, it builds the DiscoverResponse with the information it will return to the client which called the Discover method.

A typical response looks similar to this one:

<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">

...

</xsd:schema>

<row>

<INSTANCE_NAME>YUKON</INSTANCE_NAME>

<INSTANCE_PORT_NUMBER>1075</INSTANCE_PORT_NUMBER>

<INSTANCE_STATE>4</INSTANCE_STATE>

</row>

</root>

</return>
</DiscoverResponse>

But it happens that if the file msmdredir.ini is being accessed exclusively by another process when SQL Server Browser is trying to read that information from it, the AS redirector silently fails and the DiscoverResponse returns no rows, as if the instance wouldn't exist.

When the client parses that empty response, it throws the AdomdConnectionException exception we weren't expecting to be thrown.

Now the question is: And what other process could be exclusively accessing the msmdredir.ini file while SQL Server Browser tried to read from it, to satisfy my client application dynamic discovery request?

Well, in my customer case it was an antivirus, but it could even be one of the named instances of Analysis Services running on that server, since they all update that msmdredir.ini file every 30 seconds to persist in it their most recent whereabouts. The more named instances you have concurrently running on a given server, the higher the chances to experience this issue are.

So the bottom line is: 1) Catch those AdomdConnectionException eventually thrown when opening a connection against a named instance and simply retry. Probability is too low to hit this issue twice from two subsequent connection open attempts. 2) Use resources in the most efficient way, therefore reuse your already opened connections until you actually don't need them anymore and then close them. But don't follow the use-once-and-throw-away approach.

Hope you enjoyed this new story!

Are you suffering from random AdomdConnectionException thrown when trying to connect to a named instance?

Additional resources