SQL Server Analysis Services connection troubleshooting

I have a server with 5 SQL Server instances installed, different versions from 2005 to 2014.

I have only one instance of SQL Server Analysis Services installed, 2012 version, which is not the default instance.

All SQL services (including SQL Browser) are running under domain user accounts and are up.

I tried to connect to Analysis Services from another server and I received the error below:

TITLE: Connect to Server

------------------------------

Cannot connect to computer_name\instance_name.

------------------------------

ADDITIONAL INFORMATION:

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 192.168.00.00:2382 (System)

------------------------------

BUTTONS:

OK

------------------------------

As I looked to solve this connection problem, I found out some interesting things and also the solution. 

Facts:

The default instance of Analysis Services listens on port TCP 2383.

The other instances (named instances) have assigned a dynamic port which changes every time SQL Server Analysis Services is restarted.

SQL Browser for Analysis Services listens on port TCP 2382.

So, in order to connect to Analysis Services from another server, Firewall inbound rules must be configured as following:

• Inbound rule for port TCP 2383 (in case of SQL Server Analysis Services default instance).

OR

• Inbound rule for port TCP 2382 (used by SQL Browser) and
• Inbound rule for port TCP used by Analysis Services named instance.

And here comes the problem.

In order to configure inbound rule for the port used by Analysis Services, a fixed port should be configured.

And we have 2 simple ways: SSMS and configuration file.

Before we start:

To see the port currently used by Analysis Services:

• Check Process ID (PID) of the service in Configuration Manager
• Open Process Explorer and look for the PID. The process name is msmdsrv.exe. Double click on the process and a window will open. You'll find the port under TCP/IP tab.

OR

• Open Command Prompt and run netstat /abo. Look for msmdsrv.exe and there you have the port.

Solution 1: SQL Server Management Studio /SSMS

It's the simplest way to set the port.

Open SSMS > connect to Analysis Services > right click on Analysis Server and click Properties > the following window will open:

In Select a page tab choose General.

In Name column look for Port  and look at the values: 0 means a dynamic port is configured.

To fix the problem choose a port and write it under the column "Value".

Press Ok and Restart Analysis Services service in Configuration Manager.

Solution 2: Configuration file

Good to know:

As I said at the beginning, my SQL services are running under domain users accounts.

When SQL Server Analysis Services 2012 is installed, a group with the following name is automatically created (look in Computer Management > Local Users and Groups > Groups):

SQLServerMSASUser$computer_name$instance_name

Group Description: Members in the group have the required access and privileges to be assigned as the logon account for the associated instance of SQL Server 2012 Analysis Services.

Members: NT SERVICE\MSOLAP$instance_name - seems to be a virtual account created during SQL Server Analysis Services installation. In Process Explorer, under msmdsrv.exe > Security, this user is owner of Analysis Services service.

We have 2 different configuration files to look in (check the path on your server):

1. C:\Program Files\Microsoft SQL Server\MSAS11.instance_name\OLAP\Config\msmdsrv.ini

Permissions:

• The group SQLServerMSASUser$computer_name$instance_name has full rights on this file.

Look for <Port>0</Port>. A value of 0 means dynamic port.

2. C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini

Permissions:

• The group SQLServerMSASUser$computer_name$instance_name has full rights on this file;
• SQL Browser service account has full rights on this file.

Look for <Port>0</Port> and <PortIPv6>0</PortIPv6>. A value of 0 means dynamic port.

To change the port used by Analysis Services, open the first file (msmdsrv.ini) and look for <Port>0</Port>:

<RequestPrioritization>

<Enabled>0</Enabled>

<StatisticsStoreSize>1024</StatisticsStoreSize>

</RequestPrioritization>

<Port>0</Port>

<InstanceVisible>1</InstanceVisible>

Change the value of 0 into the port you want (Ex. <Port>49201</Port>).

Save and close the file. Restart the service in Configuration Manager.

!!!Check the second file (msmdredir.ini). You will see that the configured port was updated here too.

<Instances>

<Instance>

<Name>instance_name</Name>

<Port>49201</Port>

<PortIPv6>49201</PortIPv6>

</Instance>

</Instances>

Conclusion:

A dynamic port means that it will change each time the service is restarted. When Analysis Services service starts, the group SQLServerMSASUser$computer_name$instance_name check the system to see which ports are available and modify the first file msmdsrv.ini, assigning an available port.

When we connect to Analysis Services (named instance) from a different server using the instance name, the application (SSMS, Visual Studio etc.) ask SQL Browser (on port TCP 2382) for the port used by Analysis Services.

SQL Browser then reads the second file msmdredir.ini to see the port.

If a fixed port for Analysis Services is configured in the first file msmdsrv.ini and Firewall inbound rules for both SQL Browser port and Analysis Services fixed port are opened, the connection should work without problems.