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.
