10

TL;DR

I would like to add SQL Server 2014 Analysis Services to a specific instance of SQL Server and to configure it in such a way, that the Analysis Services (SSAS) and future Reporting Services (SSRS) will only pick up requests on the instance-specific IP, in order to allow the SQL Server Browser Service to remain in the state stopped.

Prerequisites

I have the following prerequisites regarding a single Windows Server running multiple SQL Server 2014 instances:

Basic Information

-  2 x NICs
- 12 x IP Addresses
- 10 x SQL Server Server Service Instances        (running)
- 10 x SQL Server Agent Server Service Instances  (running)
-  1 x SQL Server Browser Service                 (not running)

NIC Configuration

The Windows Server NICs are configured as follows:

- 1 x NIC (Backup Network) 
    - 10.2.0.1 
- 1 x NIC (LAN)
    - 10.2.0.2  (Windows Server)
    - 10.2.1.1  (SQL Server "Instance01"/ CNAME: servername-ins01)
    - 10.2.1.2  (SQL Server "Instance02"/ CNAME: servername-ins02)
    - 10.2.1.3  (SQL Server "Instance03"/ CNAME: servername-ins03)
    - 10.2.1.4  (SQL Server "Instance04"/ CNAME: servername-ins04)
    - 10.2.1.5  (SQL Server "Instance05"/ CNAME: servername-ins05)
    - 10.2.1.6  (SQL Server "Instance06"/ CNAME: servername-ins06)
    - 10.2.1.7  (SQL Server "Instance07"/ CNAME: servername-ins07)
    - 10.2.1.8  (SQL Server "Instance08"/ CNAME: servername-ins08)
    - 10.2.1.9  (SQL Server "Instance09"/ CNAME: servername-ins09)
    - 10.2.1.10 (SQL Server "Instance10"/ CNAME: servername-ins10)

SQL Server Configuration Manager

The SQL Server Configuration Manager is configured as follows for each "Instance nm" of SQL Server:

- Protocol for 'Instance01' 
    - Shared Memory : Enabled
    - Named Pipes   : Disabled
    - TCP/IP        : Enabled

TCP / IP Properties 'Instance01' - Protocol Tab

- Protocol 
    - Enabled    : Yes
    - Keep Alive : 30000
    - Listen All : No

With the above settings each 'Instance nm' will have to be configured individually to respond to IP-specific calls on the configured port. (Note: As an added benefit, each instance will be able to listen on the default SQL Server port 1433, which in turn will greatly simplify firewall configuration, because the you will be able to use the port rule sql-server. Just saying.)

TCP / IP Properties 'Instance01' - IP Addresses Tab

For the IPn entry which corresponds to the IP address of the specific instance, the settings are as follows:

- IPn
    - Active            : Yes
    - Enabled           : Yes
    - IP Address        : 10.2.1.1
    - TCP Dynamic Ports : 
    - TCP Port          : 1433
- IPAll
    - TCP Dynamic Ports : 
    - TCP Port          :

Any other IPn entry not related to the specific IP address of the instance will configured as follows:

- IPn
    - Active            : Yes
    - Enabled           : No
    - IP Address        : 10.2.1.xx
    - TCP Dynamic Ports : 
    - TCP Port          :

Quick Summary

With the above settings and configuration, each SQL Server instance will run on/with a dedicated IP address on the standard SQL Server port 1433. Each instance has its own CNAME, which allows the users to connect to the SQL Servers:

  • servername-ins01
  • servername-ins02
  • servername-ins03
  • servername-ins04
  • servername-ins05
  • servername-ins06
  • ...

...without having to specify the port or the instance-name. Another plus is that the SQL Server Browser Service doesn't have to be running to distribute calls to SQL Server instances, because each call is directly routed to the instance specific SQL Server (service).

Add Analysis Services to an Instance

The software vendor would now like to increase the user's experience and provide the ability to run some analytical queries against the existing data. The software vendor requires us to install SQL Server Analysis Services. This is an instance-specific feature and during the installation process you have to select the instance you will be adding the feature to.

Firewall Configuration

To allow connections to the Analysis Services you have to configure the following rule in the firewall:

10.2.0.2:2382 (Browser Service)

If I would have used the default instance (MSSQLSERVER) then I could have configured the following rule in the firewall:

10.2.1.1:2383 (Default Instance)

This is according to the following documentation:

SQL Server 2005 Analysis Services (SSAS) Server Properties (SQL Server 2005, but apparently still valid for current versions)

Connecting to Instance-Specific Analysis Services

After the installation has been finalised you connect to the instance-specific Analysis Services with the following syntax:

servername\Instance01

SQL Server Browser Service

After configuring the SQL Server instance-specific Analysis Service, I noticed that the SQL Server Browser service was now in the running state.

Oops, this means that requests to the Analysis Services are being routed through the Window Server's IP address (10.2.0.2) to the SQL Server specific Analysis Service instance.

If I stop the SQL Server Browser service, then I can no longer connect to the SQL Server Analysis Services via servername\Instance01.

Security Considerations

According to the above mentioned documentation:

The SQL Server Browser service is shared between the SQL Server 2005 Database engine and the Analysis Services engine. For the Database engine, for security purposes, it is recommended to turn the SQL Server Browser service off. This can create a dilemma because you may need it for the Analysis Services engine. It is a recommended practice to turn the service on if you need it, but otherwise leave it off.

Wow.

Findings

Installing the SQL Server Analysis Services has started the SQL Server Browser service, creating a security consideration.

Questions

  1. Can I configure the SQL Server Analysis Services of the Instance01 with the CNAME servername-ins01, to be bound to the IP address 10.2.1.1?

  2. Can I configure SQL Server Analysis Services of my named instance in such a way, so that I can then stop the SQL Server Browser service again?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
4
  • The funny thing is, I have a named Analysis Service running on a multi-SQL-Server-instance Windows Server, without the SQL Server Browser service started. It is accessible from another server in the network. I have fixed the port to <Port>2383</Port> and equally fixed the <servername>. I'm just curious if I just got lucky whilst modifying the mdmnsrv.ini file of the Analysis Service on the multi-instance SQL Server.
    – John K. N.
    Commented Oct 25, 2018 at 13:56
  • 1
    The instances are all accessable from other servers without the browser service, each AS instance listens on a different port across all IP addresses.
    – David Browne - Microsoft
    Commented Oct 25, 2018 at 14:03
  • Thank you for that input. I will perform additional installations of Analysis Services on a multi-instance server and see what happens. Just to be sure I understand the feedback: Are you implying that the SQL Server Browser service isn't required, but will be started as soon as Analysis Services is installed? Or is that just because I fixed the port?
    – John K. N.
    Commented Oct 25, 2018 at 14:16
  • 1
    The browser service isn't required for connectivity, but without it the clients will have to be configured with the correct ports. And each AS instance will be reachable on every IP address.
    – David Browne - Microsoft
    Commented Oct 25, 2018 at 14:20
3

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:

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.

Task Manager - MSMDSRV.exe processes

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.