How to Configure HTTP Access to SQL Server 2008 Analysis Services on IIS7

 

Client applications communicate with Analysis Services using XML for Analysis (XMLA) over TCP/IP or HTTP. Analysis Services provides an XMLA listener component that handles all XMLA communications between Analysis Services and its clients. The Analysis Services Session Manager controls how clients connect to an Analysis Services instance.

AS uses IIS as middleware component to enable access to data via HTTP. Communication between client and server is as follows:

1-Client sends an HTTP post request to IIS using the IIS servername and path to service component (msmdpump.dll) as url.

2-IIS receives the request, authenticates the sender, and creates a security context in which the in the pump will be executed.

3-IIS starts pump and uses ISAPI to communicate with pump.

4-The pump connects to AS via TCP/IP and sends data received from client without any change, 5-AS executes the request and sends response to pump which passes response to client.

If you run SSAS and IIS on separate box, you should pay attention to user credential delegation since OS doesn’t allow delegation as default. If you want to run IIS and SSAS on separate box, you need to configure Kerberos authentication.

You can still compress content of request and response in binary similar to TCP/IP protocol.

The default instance of Analysis Services uses port 2383, but that can be changed. Named instances of Analysis Services use dynamic ports. For more information about how to configure for the use of a specific port, see How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager). If you are running a default instance of Analysis Services only, using the SQL Server Browser service is optional. To use SQL Server Browser, you must open TCP port 2382.You must configure Windows Firewall to enable access to the relevant port for authorized users or computers, including configuring Analysis Services to use a specific TCP/IP port..

https://msdn.microsoft.com/en-us/library/ms174937.aspx

The Port server property for an instance of Analysis Services determines the port number on which the instance listens for client connections. The Port property has a default value of 0. This means that the instance uses the default port, 2383. An Analysis Services administrator can change the default port value to a specific port number or can leave the default value as is. If set to the default value, the instance tries to use the default port, 2383

https://msdn.microsoft.com/en-us/library/ms174906.aspx

Start->Run->Type IIS (if IIS is not installed, you can install it,

- Open Add or Remove Programs in Control Panel,

- Select Add/Remove Windows Components.

-Then add IIS to Windows)

-On IIS, click Application Pools -> Right Click-> Add New Application Pool and change it like below:

-Create folder called OLAP under C:\Inetpub\wwwRoot

-Copy two files (msmdpump.dll, msmdpump.ini) under installation isapi folder <Installation Folder> \Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi and paste them to C:\Inetpub\wwwRoot\OLAP folder.

-Go to IIS and Click OLAP folder under Default Web Site -> Right Click->Convert to Application-> then Click OK.

-Go to Handler Mappings option

Click Open Feature then Edit Feature Permission and make sure the directoy has Read and Script Permission.

In the top right coner of the Handler Mappings screen, click “Add Script Map…”

And add following information.

Then Click OK and when you click OK, you will get following warning message:

Then Click Yes.

After that, On IIS, click Server Name and go to ISAPI and CGI Restrictions under security

and change your extension description to OLAP like below:

Next step is to configure security.

Click OLAP virtual directory and select Authentication from menu.

Choose your authentication type you want to use.

Anonymous access

When this mode is selected, Pump (msmdpump.dll) is running with credentials; in our case, the credentials of IUSR_MACHINENAME user. Therefore, every connection to Analysis Services is opened as IUSR_MACHINENAME user. When this mode is selected, there is no distinction between users who are connecting to IIS and those who are connecting to Analysis Services. There is no way to distinguish between users.

This mode is to be used when the security infrastructure does not take advantage of the security functionality of Analysis Services. This is most likely an extremely controlled environment, where users are given or denied access to the virtual directory.

Basic authentication

This mode requires that the user enter a user name and password. The user name and password are transmitted over the HTTP connection to IIS. IIS will try to impersonate the user using the provided credentials.

Please note that it is absolutely imperative for anyone building a system where the password is transmitted to have ways of securing the communication channel. IIS provides a great set of tools for setting up and requiring that all communications be encrypted using HTTPS protocol.

The steps required for setting up HTTPS for a virtual directory are described later in this paper.

Integrated Windows authentication

This is the most secure and the recommended mode.

It requires that IIS Server be able to access user domain credentials. This could be done using Microsoft Active Directory® or another mechanism. It is beyond the scope of this paper to discuss in depth all the possible configurations.

Let’s open the msmdpump.ini file located in your folder and take a look at the contents of this file. It should have following in it:

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>100</ConnectionPoolSize>

<MinThreadPoolSize>0</MinThreadPoolSize>

<MaxThreadPoolSize>0</MaxThreadPoolSize>

<MaxThreadsPerClient>4</MaxThreadsPerClient>

</ConfigurationSettings>

The only setting you are interested in at this point is <ServerName>.

If the Analysis Services instance that you need to provide access to is located on the local machine and installed as a default instance, there is no reason to change this setting. Otherwise, you need to specify the machine name and instance name ( mymachine\inst1).

It is also possible to specify a pointer to the virtual directory on another IIS server that is set up for HTTP access to Analysis Services.

For example you can have <ServerName>https://secondmachine/olap/msmdpump.dll</ServerName>

You can connect your Analysis Services instance with following syntax from you clients:

 

Also you can find steps for SSAS 2005 below:

https://technet.microsoft.com/hi-in/library/cc917712(en-us).aspx

As conclusion, we have three ports involved here:

1- http port : port 80 for clients to access IIS

2-SSAS port: 2383 is default port for SSAS

3-Browser port: 2382 UDP for SQL Browser Service

Comments

• Anonymous
  December 05, 2011
  Hi there! This is a great article and definitely helped me to set up the "pump" in my environment.  I am actually trying to automate this for some load testing and am running into one snag... Basically what I'm doing is recording the HTTP traffic with Fiddler, then created a VisualStudio webTest from that Fiddler trace.  It all seems to work pretty well except that the requests require a SessionID.  What I'm trying to do is extract the SessionID from the response of a BeginSession request, but unfortunately the response comes back in a garbled format.  I know this is a bit of an odd question, but do you perhaps know how to handle this? Thanks!

• Anonymous
  September 02, 2012
  Hi, I'm trying to use an IIS published AS server to sync a cube between two different domains via an SSIS package, but I seem to be struggling with the authentication. If I set IIS to use WindowsIntegrated (NTLM), then it always uses the SQL Engine account (ignoring UserName/Password in the connection string). But if I'm going cross domain, I'm need to specify credentials in the correct domain. If I set IIS to use Basic, then authentication always fails. Any ideas? Thanks Craig

• Anonymous
  October 01, 2013
  Looking for help to enable HTTP Acces Hi All, I have tried and implemented all that was required to enable HTTP Access for Analysis services. However still getting 500 Internal Server error when try to access the  analysis cube through IE or through SSMS excel etc. No Firewal exists. All ports correctly configured. OLEDB properly installed. IIS Configuration proper as per page suggestion. " HTTP Error 500.0 - Internal Server Error The page cannot be displayed because an internal server error has occurred." Can anybody Please help. Thanks In Advance!

• Anonymous
  October 03, 2013
  Hey Smra, first place to look is the IIS logs to see if there is any more detail on the HTTP 500 error.  Then it's probably also worth looking at the Applicationa and System Event logs for clues. It's also worth setting up a dummy HTML file in the same web application and test accessing that with IE, that way you can test that the Auth/etc is working OK. Good luck! Later'ish Craig