Troubleshooting Common SQL Server Analysis Services Connectivity Issues
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?
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
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:
- Open Task Manager on the server that is running the SQL Server Analysis Services instance.
- In the Details tab, search for the process ID of the msmdsrv.exe process. In this example, the process ID value is 4448.
- Open a command prompt window and type the following command
C:\> netstat -abo | find /i "LISTENING" | find "4448"
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
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.
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.
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.
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?
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:
- Navigate to the %ProgramFiles(x86)%\Microsoft SQL Server\90\Shared\ASConfig folder and search for the msmdredir.ini file.
- Open the msmdredir.ini file in a text editor and look for the < PortIPV6 > value inside the < Instances > tag.
- Delete the line containing the < PortIPV6 > tag and save the file.
- 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.
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.
- Read the following articles
- How to determine and change the port of an SSAS Instance.
- How to Configure the Windows Firewall to Allow Analysis Services Access and Microsoft KB article 2658571.
- Check out the other SQL Server Analysis Services Administration tips.
About the authorEdwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.
View all my tips