Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance

Analysis Services always listens on all IPs unless it's a clustered instance or running in a VM. So you can't disable the browser service in this configuration.

If you can live with IIS and HTTP access you can configure multiple IIS websites running instances of the Analysis Services Data Pump.

Introduction

After reading through various online articles and configuring multiple Analysis Services on a multi-instance SQL Server I can provide the following possible solutions for people in the same situation. They all involve more or less configuration and/or firewall settings. They are as near as you can get to an instance-specific IP:PORT and/or CNAME:PORT configuration.

Reference Material

Following a list of articles I pored over while trying to configure Analysis Services to run on a specific IP:port configuration:

• Connect from client applications (Analysis Services) (Microsoft | Docs | SQL Server 2014)

• How to determine and change the port of an SSAS Instance (Microsoft Support)

• Configure the Windows Firewall to Allow Analysis Services Access (Microsoft | Docs | SQL Server 2014)

• SQL Server Browser Service (Database Engine and SSAS) (Microsoft | Docs | SQL Server 2014)

• SQL Server 2005 Analysis Services (SSAS) Server Properties (Microsoft Technet)

• List of TCP and UDP port numbers (Wikipedia)

  ^{still valid for SQL Server AS 2005 through 2017}

Sorry, you can't configure SQL Server Analysis Service to run on a specific IP address and a specific port number. However, you can configure SQL Server Analysis Service to run on a specific port, but the Analysis Service will listen on all IP addresses on the server it is installed on.
Running netstat -a | find ":238" says it all:

TCP 0.0.0.0:2382 SERVERNAME:0 LISTENING
TCP 0.0.0.0:2383 SERVERNAME:0 LISTENING
TCP [::]:2382 SERVERNAME:0 LISTENING
TCP [::]:2383 SERVERNAME:0 LISTENING

---

Solution Without SQL Server Browser Running

If you really are security conscious and don't want people "browsing" your SQL Server for available instances, then you don't want the SQL Server Browser service in a running state. The starting point for this configuration is the following statement from SQL Server Browser Service (Database Engine and SSAS):

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

But you lose the following abilities:

_{(emphasis mine; just want I wanted)}

• Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

• Any component that generates or passes server\instance information that could later be used by other components to reconnect.

• Connecting to a named instance without providing the port number or pipe.

• DAC to a named instance or the default instance if not using TCP/IP port 1433.

• The OLAP redirector service.

• Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.
  If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

  • You must update and maintain client application code to ensure it is connecting to the proper port.
  • The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.

After installing the SQL Server Analysis Service you will have to make the following changes to the SSAS configuration/settings.

---

Configure SSAS Instance to Listen on a Specific Port

Modify the specific instance's (e.g. Instance01) SSAS configuration to listen on a specific port. Each instance (e.g. Instance01, Instance02, ...) requires its own dedicated port. You can't configure all instances to use the same port (e.g. 2383)

... With SSMS

• Connect to the Analysis Server (e.g. servername\instance01)
• Open the Analysis Server Properties.
• Switch to the General tab
• Scroll down to the Port setting; it should be 2383 for the default (MSSQLSERVER) instance and 0 for a named instance which means the port is dynamically set.
• Enter a default port; don't use 2382 as this can mess up the SQL Server Browser service which is still listening on port 2382 for any incoming connections to the nine other SSAS instances currently running. You might want to consider using a port number in the region of 49152–65535, which are available for private use (List of TCP and UDP port numbers / wikipedia).
• Press OK.
• Restart the SQL Server Analysis Services (Instance01) service.

... With Notepad

• Locate the msmdsrv.ini file which is normally to be found in the directory C:\Program Files\Microsoft SQL Server\MSAS12.INSTANCE01\OLAP\Config directory.
• Open the file in Notepad.
• Find the <Port>0</Port> setting.
• Enter a default port; don't use 2382 as this can mess up the SQL Server Browser service which is still listening on port 2382 for any incoming connections to the nine other SSAS instances currently running. You might want to consider using a port number in the region of 49152–65535, which are available for private use (List of TCP and UDP port numbers / wikipedia).
• Save the changes.
• Restart the SQL Server Analysis Services (Instance01) service.

---

Configure Clients to Connect to Specific Instance

In order for the clients to be able to connect to the Analysis Service you will have to supply the Fully Qualified SQL Server Analysis Server Name (FQSSASN) with port like this:

servername-ins01:52383

This is the CNAME of the servername\instance01 SQL Server, but because the SQL Server Browser service will be turned off in the end, you can't use servername\instance01:52383 to connect to the server. This doesn't actually matter, because the SSAS instance will pick up on any IP.

Firewall Settings

Because the SSAS instance is running on a dedicated port you can configure the firewall to let connections through to the IP:port of the named instance (e.g. 10.2.1.1:52383 as per the question).

Important The above procedure has to be repeated for each SSAS instance running on the dedicated server, incrementing the port number along the way. After all Analysis Services instances have been reconfigured to listen on a dedicated port, the SQL Server Browser service can then be turned off.

Verifying Analysis Services Ports

This can be verified by running netstat -abno and looking for the individual [msmdsrv.exe] entries:

  TCP    0.0.0.0:52382          0.0.0.0:0              LISTENING         6884
 [msmdsrv.exe]
  TCP    0.0.0.0:52383          0.0.0.0:0              LISTENING         7716
 [msmdsrv.exe]
  TCP    0.0.0.0:52384          0.0.0.0:0              LISTENING         7248
 [msmdsrv.exe]

  TCP    [::]:52382             [::]:0                 LISTENING         6884
 [msmdsrv.exe]
  TCP    [::]:52383             [::]:0                 LISTENING         7716
 [msmdsrv.exe]
  TCP    [::]:52384             [::]:0                 LISTENING         7248
 [msmdsrv.exe]

The number at the end of the line is the process number of the service. You can look this up in the Windows Task Manager (PID) to verify that the Analysis Service for the specific instance is running on the specified port.

SQL Server Browser Service

Once you have configured all your SSAS instances to listen on individual ports and your existing SQL Servers are all running on dedicated IPs and ports (as in the question) , you can then stop the SQL Server Browser service.

Each individual Analysis Service will be reachable via the CNAME:PORT syntax:

servername-ins01:52383
servername-ins02:52384
servername-ins03:52385
servername-ins04:52386