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
STEP 2: COPY THE SQL SSAS BINARIES
STEP 3: CREATE AN APPLICATION POOL
Open Internet Information Services (IIS) Manager console (IIS Manager) by navigating to it or by selecting Run, and then type inetmgr.
Click the Application Pools node
- Right Click and choose Add Application Pool…
Name the application pool OLAP, using .NET Framework v2.0.50727, with Managed pipeline mode set to Classic.
Right click the OLAP Application Pool and select Advanced Settings.
Change the Process Model > Identity to a domain account
- Domain\username, by default it uses Network Service or ApplicationPoolIdentity
- 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
Create IIS Website for the OLAP services
- In IIS Manager expand the Sites
- Right click Sites and select Add Web Site…
- Type DataSources as the Site name:
- Provide the Physical path: to <drive>:\inetpub\wwwroot\OLAP "Previously created"
- Edit the Binding as appropriate to point to the previously created domain URL
Click Test Settings… and verify that the results pass. If you see any authorization issues you must resolve these before moving forward.
You can navigate to the web address created for the new site to test.
- 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
Right click the DataSources website from within the Sites in IIS Manager and select Add Virtual Application…
- In Alias:, type OLAP.
In Physical path:, click the browse button and navigate to C:\inetpub\wwwroot\OLAP.
- Click OK.
Right-click the OLAP virtual directory just created, and select Convert to Application.
- 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.
- Click OK twice to accept the changes, and to convert the application.
- 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.
- In IIS Manager expand Sites > DataSources > and select the OLAP virtual directory.
Double click Authentication in the IIS Features main page.
- Click Windows Authentication and select Disable under the right pane Actions.
- Click Anonymous Authentication and select Disable under the right pane Actions.
Click Basic Authentication and select Enable under the right pane Actions.
- 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
Click Basic Authentication and click Edit under the Action pane.
- Add the Default domain: Domain.com
Add the Realm: Domain
- 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.
Click the OLAP virtual directory to open the IIS Features main page.
Double click Handler Mappings.
Right-click anywhere on the page and select Add Script Map...
- In the Add Script Map dialog box Request path: type *.dll
- In the Executable: path type :\inetpub\wwwroot\OLAP\msmdpump.dll
- In the Name: box type OLAP.
Click Request Restrictions.
- Select the Verbs tab and verify that All verbs is selected.
- 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.
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.
On the IIS server navigate to the msmdpump.ini file located at C:\inetpub\wwwroot\OLAP and open it using Notepad.
The syntax in the file should look similar to the following:
<ConfigurationSettings>
<ServerName>Server Name\INSTANCE</ServerName>
<SessionTimeout>3600</SessionTimeout>
<ConnectionPoolSize>100</ConnectionPoolSize>
</ConfigurationSettings>
- 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.
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