Due to recent requirement I had to go through several Data cubes which needs to be accessed via a web browser. In the client end I used Telerik pivot grid which provides a comprehensive way of processing the cube. I’ll explain the Telerik part later, lets focus on how to enable SSAS access via HTTP.
SSAS accessibility provides following capabilities,
- Client access over internet/intranet
- Client access from un-trusted domain via proper authentication
- Accessibility via HTTP only gateways
- Authentication – Ah ! some points to remember according to the Microsoft “Authentication methods other than Windows integrated security are required” Specifically, you can use Anonymous connections and Basic authentication when configuring Analysis Services for HTTP access. Digest, Forms, and ASP.NET authentication are not supported.
We are going to create a ISAPI extension (MSMDPUMP that’s what they call it) which loads with IIS, which will eventually gives us the capability to access Analysis Services.
This Service instance can be on
- Same computer
- Remote Computer with in same domain
Find MSMDPUMP files
Simply there are two file you need to find from following location from your local machine, <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi
Copy all the files inside the directory to new folder which needs be created as <drive>:\inetpub\wwwroot\OLAP, It is not mandatory to create this OLAP folder inside the inetpub place it where you normally place web site files.
Find and open Internet Information Services Manager by typing IIS in to search,
Right Click on application pools and select add new application pool,
there you must fill details as follows. Managed pipe line needs to be in classic mode.
Right click on the created OLAP application pool and choose advanced settings, will show the following screen.
There you have to concentrate on two settings, Enable 32-bit Applications if the MSMDUMP files comes from a 64-bit machine set this to false other wise set to true. Identity should be set to either Network Service or to Application Pool Identity depending on the windows version that you are using.
Now create a web site and map MSMDUMP file location to it.
Make sure to use same application pool you created previously to this web site.
Go to the Authentication module of the site and disable anonymous authentication and enable windows authentication.
Click on the OLAP site and Double-Click Handler mappings,
Click Add Module Map on right side pan, And fill the details as follows.
Click on Request Restrictions Button and Select verbs Tab, Make sure that All Verbs are selected.
Click Ok and then Click ‘Yes’ To allow ISAPI extension.
Open MSMDUMP file folder and open MSMDPUMP.INI file, If you have SSAS installed same machine where IIS is hosted then it should looks like follows. for example this sever name can be (<ServerName>TEST-SRV01:55555</ServerName>)
To granting permissions use SQL Server Management Studio. Under the Database | Roles folder, you can create roles, specify database permissions, assign membership to Windows user or group accounts, and then grant read or write permissions on specific objects.
Anonymous
Add to the Membership list the account specified in Edit Anonymous Authentication Credentials in IIS. For more information, see Anonymous Authentication,
Windows authentication
Add to the Membership list the Windows user or group accounts requesting Analysis Services data via impersonation or delegation.
Basic authentication
Add to the Membership list the Windows user or group accounts that will be passed on the connection string.
Testing Accessibility of SSAS Service via Excel.
Go to excel > Data > From Other sources > SSAS Service
in server name text box type your hosted address as follows,
http://localhost:8088/msmdpump.dll
and for the Authentication mode,
choose Use Windows Authentication if you are using Windows integrated security or NTLM, or Anonymous user.
For Basic authentication, choose Use the following User Name and Password, and then specify the credentials used to sign on. The credentials you provide will be passed on the connection string to Analysis Services.
If you are succeeded, you will see following screen with available Cubes.
