May 13
Setting Up Access to a SQL Server Analysis Services Cube Using IIS

SCENERIO

A non-Microsoft application requires a connection to SQL Server Analysis Services cube.

The client (Tomcat) sends an HTTPS post request to IIS using the IIS server name and path to service component which is defined in the msmdpump.dll as the URL (https://dev.website.domain). IIS then receives the request, authenticates the sender with the provided domain credentials, and creates a security context in which the in the pump will be executed.  IIS then starts pump and uses ISAPI to communicate with pump at which point the pump connects to SQL SSAS via TCP/IP and sends data received from client without any change, SSAS executes the request and sends response to pump which passes response to client.

The approach is to enable the Java application a connection to SQL SSAS using IIS8.

SQL SSAS uses IIS as middleware to enable access to data via HTTP communications between the client (Tomcat) and the IIS server to SQL SSAS as follows.

STEP 1: SERVER AND SERVICES PROVISIONING

Install IIS on the Windows 2012 server.

Install and download the Analysis Services OLE DB provider on the IIS Windows web server.

Consider which ports to unblock in the Windows IIS Firewall. You will need to unblock ports on the IIS and SQL Analysis Servers to allow access to the web application on IIS.

  • Open the inbound firewall ports of the IIS server for 80/443 TCP for HHTP(s) and Port 2382 TCP for the default Analysis Services instance.

Create a domain for the website to be used, in this case we used https://dev.website.domain.

Create a Domain account to be used for the IIS application services.

Configure Analysis Services for Kerberos Constrained Delegation

  • See Step 7 for more details

STEP 2: COPY THE SQL SSAS BINARIES

  • On the SQL SSAS server navigate to the <drive>:\Program Files\Microsoft SQL Server\<instance"MSAS11.xxx>\OLAP\bin\isapi directory and copy the msmdpump.dll, msmdpump.ini, and the Resources folder.
  • Place these files in a new folder named OLAP on the web server at <drive>:\inetpub\wwwroot\OLAP and verify that the path to the folder created does not contain any spaces.
  • Create an empty folder named DataSources at <drive>:\inetpub\wwwroot\DataSources
    • Create a file named Default.htm using Notepad and add the following text:
      • Data source web access is working properly...
        Test data source for OLAP connections to BI SSAS services.
    • Save and close it, this will be used later for verifying connectivity.

STEP 3: CREATE AN APPLICATION POOL

  1. Open Internet Information Services (IIS) Manager console (IIS Manager) by navigating to it or by selecting Run, and then type inetmgr.

  2. Click the Application Pools node
    1. Right Click and choose Add Application Pool…

  3. Name the application pool OLAP, using .NET Framework v2.0.50727, with Managed pipeline mode set to Classic.

  4. Right click the OLAP Application Pool and select Advanced Settings.
    1. Change the Process Model > Identity to a domain account
      1. Domain\username, by default it uses Network Service or ApplicationPoolIdentity
    2. Verify that (General) > Enable 32-Bit Applications is set to False. This is because we copied the OLAP binaries from a 64bit enabled SSAS server.

STEP 4: CREATE A DEFINED IIS WEBSITE

  1. Create IIS Website for the OLAP services
    1. In IIS Manager expand the Sites
    2. Right click Sites and select Add Web Site…
    3. Type DataSources as the Site name:
    4. Provide the Physical path: to <drive>:\inetpub\wwwroot\OLAP "Previously created"
    5. Edit the Binding as appropriate to point to the previously created domain URL

    1. Click Test Settings… and verify that the results pass. If you see any authorization issues you must resolve these before moving forward.
      1. You can navigate to the web address created for the new site to test.
        1. For example navigate to https://dev.website.domain and you should see a page that displays the text added to the Default.htm file created previously if the site is working properly

STEP 5: CREATE THE OLAP VIRTUAL DIRECTORY IN IIS

  1. Right click the DataSources website from within the Sites in IIS Manager and select Add Virtual Application…
    1. In Alias:, type OLAP.
    2. In Physical path:, click the browse button and navigate to C:\inetpub\wwwroot\OLAP.
      1. Click OK.

  2. Right-click the OLAP virtual directory just created, and select Convert to Application.
    1. In the Add Application dialog box, next to Application Pool, click Select… and select the OLAP Application pool from the drop down list which was created previously.
  3. Click OK twice to accept the changes, and to convert the application.
  4. Once this step is completed there will be a site available at https://dev.website.domain/OLAP/ and you should expect to see an HTTP Error 403.14 – Forbidden until security has been properly setup, see next steps.

STEP 6: CONFIGURE IIS AUTHENTICATION AND HANDLER MAPPINGS

In these steps we will define the authentication method for an enterprise supported authentication method for Analysis Services over HTTPs.

In this case we will use Constrained Delegation which is the security mechanism which enables a seamless credentials flow from the IIS web server to the SQL server which is where the backend Analysis Services data will be sourced buy the client. Enabling this delegation avoids a double-hop scenario which will cause multiple prompts for credentials and otherwise cause the process to fail to the application needing access to the cube. This process leverages existing infrastructure for networks that use Active Directory. This is a straightforward approach so that users can access Analysis Services in a transparent manner.

The client which may not be a Windows client will access the SQL data by access the web URL at https://dev.website.domain:2385/OLAP/msmdpump.dll.

  • The most common approach is to use Basic authentication with Secure Sockets Layer (SSL) and will require the client to provide a user name and password when accessing the MSMDPUMP virtual directory. Use this approach when Windows identities are from non-trusted domains or non-Windows environment. Basic authentication lets you specify a user identity and password on a connection string used to connect to the SQL Analysis Services data in this case.
  1. In IIS Manager expand Sites > DataSources > and select the OLAP virtual directory.
  2. Double click Authentication in the IIS Features main page.
    1. Click Windows Authentication and select Disable under the right pane Actions.
    2. Click Anonymous Authentication and select Disable under the right pane Actions.
    3. Click Basic Authentication and select Enable under the right pane Actions.
      1. If you do not see Basic Authentication you will need to add it using these steps - http://www.iis.net/configreference/system.webserver/security/authentication/basicauthentication
      2. Click Basic Authentication and click Edit under the Action pane.
        1. Add the Default domain: Domain.com
        2. Add the Realm: Domain
          1. Doing this will automatically append the Realm to any user authenticating to the site. Typically you would sign in as domain\username, Do not add domain\username once this is edited.

  3. Click the OLAP virtual directory to open the IIS Features main page.
    1. Double click Handler Mappings.
      1. Right-click anywhere on the page and select Add Script Map...
        1. In the Add Script Map dialog box Request path: type *.dll
        2. In the Executable: path type :\inetpub\wwwroot\OLAP\msmdpump.dll
        3. In the Name: box type OLAP.

    1. Click Request Restrictions.
      1. Select the Verbs tab and verify that All verbs is selected.
      2. Click OK 2 times to finish adding the script mapping and select Yes to the Add Script Map dialogue popup to allow the ISAPI extension.

STEP 7: CONFIGURE ANALYSIS SERVICES FOR KERBEROS CONSTRAINED DELEGATION

A domain administrator will be required to set the following values and delegations to the web server.

  • Create and setup the SPN's and constrained delegation (This is for SQL SSAS, make certain to use the SQL DB Engine SPN's if doing so)
    • setspn -s HTTP/https://dev.website.domain domain\username
      setspn -s HTTP/FQDN.domain domain\username
    • setspn -s MSOLAPSvc.3/SQL Server.domain domain\username
    • setspn -s MSOLAPSvc.3/SQL Server.domain:Instance domain\username
    • setspn -s MSOLAPDisco.3/SQL Server domain\username
    • setspn -s MSOLAPDisco.3/SQL Server.domain domain\username
      • The username is the service acocunt that that particular service uses to run the SQL service or IIS application pool.
      • Also notice that you have to register the NETBIOS name and the FQDN
  • Setting up constrained delegation for IIS to SSAS to URL - https://dev.website.domain
    • Add each of the above resulting SPN's to the Service Type for the web server as shown below which will enable Kerberos between the IIS host and the database. 

  • http://technet.microsoft.com/en-us/library/cc720385(v=ws.10).aspx

STEP 8: EDIT THE MSMDPUMP.INI FILE TO SET THE TARGET SERVER

In this step will modify the msmdpump.ini file which specifies the Analysis Services instance that the msmdpump.dll connects to.

  1. On the IIS server navigate to the msmdpump.ini file located at C:\inetpub\wwwroot\OLAP and open it using Notepad.
    1. The syntax in the file should look similar to the following:
      <ConfigurationSettings>
      <ServerName>Server Name\INSTANCE</ServerName>
      <SessionTimeout>3600</SessionTimeout>
      <ConnectionPoolSize>100</ConnectionPoolSize>
      </ConfigurationSettings>
      1. In many other whitepapers it states that if you defined a fixed port, you must add the port number to the server name ServerName:Port, not necessary!

STEP 9: GRANT SSAS ACCESS PERMISSIONS

The Analysis Services database object will have roles defined that provide a given level of permissions read or read/write. Add the domain user or groups memberships to the Role defines in SQL SSAS.

  • To set permissions open SQL Server Management Studio.
    • Locate the SSAS DB > Roles, reuse or create a Role named DB_Reader
      • Double click the new Role and select Memberships and add the domain user or group.
        • Select Locations… and choose Entire Directory and click OK.
        • Select Object Types… and select all Object types: and click OK.
        • Click OK.
      • Select the Cubes.
        • Set the Access column to Read and set the Local Cube/Drillthrou… to Drillthrough and Local…
        • Click OK.

STEP 10: TEST THE CONFIGURATION

To quickly test the connection, you can open new test file and change the extention to a .udl file.

  • Click Test Connection, That's it!
  • An example connection string illustrating the syntax for HTTP(S) access using Basic authentication:
    Data Source=https://<servername>/olap/msmdpump.dll; Initial Catalog=CubeName; Integrated Security=Basic; User ID=XXXX; Password=XXXXX;

RESOURCES
http://msdn.microsoft.com/en-us/library/gg492140.aspx

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools