Troubleshooting Common SQL Server Analysis Services Connectivity Issues

By:   |   Comments (4)   |   Related: > Analysis Services Administration


Problem

After deploying a SQL Server Analysis Services instance, I wanted to test application connectivity to make sure remote connections are allowed. However, I am having issues connecting to it from a remote machine. How do I troubleshoot connectivity issues to a SQL Server Analysis Services instance?

Solution

There are several reasons why we cannot connect to a SQL Server Analysis Services instance remotely. Each of those issues may happen at different layers of the OSI model. While the troubleshooting process outlined below is not intended to make you a network engineer, it would help you understand how to isolate the issue for better resolution. The process also applies to any service or application hosted on a remote machine or on the Internet.

Start with TCP/IP connectivity

Because SQL Server Analysis Services rely on TCP/IP for remote connectivity, it uses a combination of IP address and port number for both identification and connectivity. The IP address identifies the server in the network running the SQL Server Analysis Services instance while the port number identifies a logical connection to the specific application or service (there are many applications and services running on a server; a port number is a way to identify how to connect to those specific services).

To test TCP/IP connectivity, we can use the PING and TELNET utilities (the TELNET client isn't enabled by default on modern Windows operating systems and has to be added by following the steps outlined in this Microsoft TechNet article). You can test TCP/IP connectivity using the PING command by passing either the server name or the IP address. You can start with the IP address to verify if there is indeed connectivity, followed by the server name to verify IP address-to-server name resolution. A response from the server means that we can connect to the server. 

C:\> PING SQLASCLUS

Ping Test

If you don't get a response from the server, there could be other issues such as:

  • No physical network connectivity
  • Firewall blocking connections from client to server
  • ICMP echo response is prohibited for security reasons
  • Other reasons that only the network engineers know about

Enlist the help of your network engineers when this test fails so that you don't waste your time trying to identify other reasons when the real cause is happening on the TCP/IP stack.

Next, we'll use the TELNET utility to verify connectivity to the Analysis Services instance. But before we do so, we need to know what port number the Analysis Services instance is listening on. While the default port number for a default instance of SQL Server Analysis Services is 2383, it's better to validate if it is the correct port number. To do so:

  1. Open Task Manager on the server that is running the SQL Server Analysis Services instance.
  2. In the Details tab, search for the process ID of the msmdsrv.exe process. In this example, the process ID value is 4448.


    3. Windows Task Manager

  3. Open a command prompt window and type the following command


    4. C:\> netstat -abo | find /i "LISTENING" | find "4448"


    NetStat

    The NETSTAT command will list all of the active TCP connections and ports that the server is listening on. We will then use the FIND command to search the result of the NETSTAT command for a specific string. In this example, we will search for results where the TCP connection has a State value of listening and a Process ID value of 4448. In the results, the port number used by the SQL Server Analysis Services instance is 2383. We will then use this port number to test for connectivity to Analysis Services. A successful connection will result in a blank screen, like the one shown below.

    C:\> TELNET SQLASCLUS 2383

    Successful TELNET Test

    However, if you get a "Could not open connection to the host, on port" error message, it could mean that the Windows Firewall is blocking access to this port number.

    Failed TELNET Test

    Make sure that you allow traffic on the Windows Firewall for this specific port number.

Proceed with application connectivity

Once you've tested and verified TCP/IP connectivity to the SQL Server Analysis Services instance, you can now proceed to perform application connectivity. Make sure that you do not proceed with application connectivity until you've successfully passed the TCP/IP connectivity.

We will use the instance name in SQL Server Management Studio when we connect to the SQL Server Analysis Services instance. If SQL Server Management Studio could not connect to the Analysis Services instance, check the error message reported.

SSAS Connectivity Failure from SSMS

Check if the SQL Browser service is started

The error message tells us that the SQL Browser service is probably not started. It also tells us that a different port number - port 2382 instead of 2383 - is being used. That's because the SQL Browser service listens for client application requests on port 2382. It, then, redirects the connection to the port used by the SQL Server Analysis Services instance. And, because Analysis Services is dependent on the SQL Browser service, we need to make sure that it is running on the server hosting the SQL Server Analysis Services instance. If Analysis Services is running on a Windows Server Failover Cluster (WSFC), the SQL Browser service has to be started on all of the nodes of the WSFC.

SQL Browser Service Started

Configure connection to IPv4

In the previous error message, SQL Server Management Studio explicitly tells us that the SQL Browser service might not be running. But what do we do if it doesn't?


SQL Server Management Studio Unable to Connect to Analysis Services

Notice that, in the error message, a different port number is used - port 50328. We know for a fact that the Analysis Services instance is listening on port 2383. Why are we seeing a different port number this time? This could mean that, because Windows Server 2008 and higher supports IPv6, the installation of Analysis Services has configured a separate port number for IPv6. If you only want to connect to the Analysis Services instance using IPv4, we need to remove the IPv6 configuration from the Analysis Services instance. To do this:

  1. Navigate to the %ProgramFiles(x86)%\Microsoft SQL Server\90\Shared\ASConfig folder and search for the msmdredir.ini file.
  2. Open the msmdredir.ini file in a text editor and look for the < PortIPV6 > value inside the < Instances > tag.


    3. msmdredir.ini

  3. Delete the line containing the < PortIPV6 > tag and save the file.
  4. Restart the SQL Server Analysis Services service.

If you are dealing with a SQL Server Analysis Services instance running on a WSFC, be sure to modify this file on all of the WSFC nodes. Otherwise, your client applications may not be able to connect if a failover of the resource group occurs. You can start modifying the msmdredir.ini file on the non-active nodes first and, then, failover the Analysis Services resource group to any of the changed nodes to minimize downtime.

Summary

These are just some of the common issues when testing client application connectivity with SQL Server Analysis Services. Be sure to go thru the troubleshooting process before trying something else or you might end up wasting your time trying to solve an issue that has nothing to do with the root cause.

Next Steps

Comments For This Article




Friday, May 17, 2019 - 7:25:43 AM - Krishna Yadav Back To Top (80113)

Can't delete the port number msmdredir.ini give me error that I don't have permission to delete. Though I'm the owner on this machine

Tuesday, August 1, 2017 - 7:37:58 AM - Kevin Cabral Back To Top (63666)

 

Great Post, Well structured, with a well-organized sequence of information. The way the explanation was given, helped me to abstract and come to a conclusion of what was in my power to connect to the service analysis.

It had to do the inbound and outbound port of the SQL Server Browser.

 

Tuesday, March 7, 2017 - 4:31:47 AM - robin Back To Top (47458)

 

 Thank you so much for this post . it saved lot of time.

Tuesday, August 16, 2016 - 2:44:48 AM - Fatemeh Rad Back To Top (43125)

Hi,

My SQL Srver Analysis Service is on a remote server that I connect to in via VMWARE.

I want to write an application with AMO programming.

I use Visual Studio 2015 For programming.

what value I must set for connection string to SSAS DB?

Because it use Windows Authentication I dont know how connect to SSAS on Server.

 

Best,

Fatemeh

 














get free sql tips
agree to terms