Tuesday, June 4, 2013

An Extra Mile: Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0

Through this post I'll try to explain how to connect MS Office Excel to Analysis Services Cube but through HTTP access

 The idea of configuring such approach is that normally, you need to be connected to the domain containing your SQL servers to be able to connect your excel file with the external OLAP source (Cube). while on the other hand what if I need users to be able to do ad-hoc reporting from home or mainly outside the domain ? ... So the solution for this is to Configure HTTP Access to Analysis Services ;)

 These instructions I'm going to explain are valid for any supported version or edition of:

  1. Analysis Services instance that interfaces with IIS 7.0,
  2. SQL Server 2005 or later versions (SQL Server 2012, SQL Server 2008 R2, SQL Server 2008)
  3. HTTP access is supported for both tabular mode and multidimensional mode servers.

Important Note (Study this table from Microsoft MSDN before applying the below steps):
Scenario
Configuration
IIS and Analysis Services on the same computer
This is the simplest configuration because it allows you to use the default configuration (where the server name is localhost), the local Analysis Services OLE DB provider, and Windows integrated security with NTLM. Assuming that the client is also in the same domain, user authentication is transparent to the user, with no additional work on your part.
IIS and Analysis Services on different computers
For this topology, you must install the Analysis Services OLE DB provider on the web server.
Provider (.msi) file is located in [Deployment Folder]\Release\Release_1.1\Prerequisites\SQL2008R2_SSAS_OLEDB_Provider
You must also edit the msmdpump.ini file to specify the location of Analysis Services instance on the remote computer.
This topology adds a double-hop authentication step, where credentials must flow from the client to the web server, and on to the backend Analysis Services server. If you are using Windows credentials and NTLM, you will get an error because NTLM does not allow delegation of client credentials to a second server. The most common solution is to use Basic authentication with Secure Sockets Layer (SSL), but this will require users to provide a user name and password when accessing the MSMDPUMP virtual directory. A more straightforward approach might be to enable Kerberos and configure Analysis Services constrained delegation so that users can access Analysis Services in a transparent manner.
Consider which ports to unblock in Windows Firewall. You will need to unblock ports on both servers to allow access to the web application on IIS, and to Analysis Services on a remote server.
What I'm going to apply/explain in this article is the "IIS and Analysis Services on different computers" approach.

"Enough with the boring readings already :D ........ NOW Let's Go"

The following steps explain how to set up HTTP access to an Analysis Services default instance using IIS 7.0:

  1. Copy the MSMDPUMP.dll to a folder on the Web server:
    • On each WFE server, create the an OLAP folder under: C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP
    • Copy the contents of the ISAPI folder on the Analysis Services computer to the C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP folder you just created.(Note: The ISAPI folder can be found at <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi. It contains the following files and folder: MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources folder that contain language resource files.)
    •  Verify that the “\inetpub\wwwroot\wss\VirtualDirectories\OLAP\” folder on your web server contains the following:
      • MSMDPUMP.DLL
      • MSMDPUMP.INI
      • Resources folder


  2. Create an application pool and virtual directory in IIS:
    • Start IIS Manager > open Sites
    • Under "Default Web Site" > Navigate to Find your OLAP Folder
      (Note: You can create a SharePoint Application and add the OLAP application beneath it as well)
    • On the OLAP Folder:
      • Right-click the OLAP virtual directory, and then click Convert to Application

      • In Alias, type OLAP
      • In Physical Path, click the browse button and navigate to C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP. Click OK

      • Click OK twice to accept the changes, and to convert the application


  3. Configure IIS authentication and add the extension:
    • Select the OLAP virtual directory.
    • Double-click Authentication in the IIS section of the main page
    • Disable all the Authentication methods, and enable only Basic Authentication
    • Click the OLAP virtual directory to open the main page. Double-click Handler Mappings.
    • Right-click anywhere on the page and then select Add Script Map.
    • In the Add Script Map dialog box, specify
      • *.dll as the request path
      • C:\inetpub\wwwroot\VirtualDirectories\OLAP\msmdpump.dll as the executable
      • Type OLAP as the name
    • Click Request Restrictions
    • On the Verbs tab, verify that “all verbs” is selected. Click OK, and then click OK again to finish adding the script mapping
    • When prompted to allow the ISAPI extension, click yes



  4. Edit the MSMDPUMP.INI file to set the target server:
    • Open the msmdpump.ini file located in folder C:\inetpub\wwwroot\VirtualDirectories\OLAP and take a look at the contents of this file. It should look like the following:
    • If the Analysis Services instance for which you are configuring HTTP access is a default instance, you will either leave the (*.ini) file as it is or specify the server name (for example, <ServerName>MyServer</ServerName>).
    • But if the analysis services Instance is a named instance, then be sure to append the instance name (In my Case Its named: <ServerName>lms-sql-vs01</ServerName>)



  5. Give Access to users will be logging on to the OLAP application:
    • On each WFE, Navigate to "C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP"
    • On the OLAP folder > Right Click > Properties > Security Tab
      • Edit and Add the groups/users that you need them to access the targeted OLAP Cube with default permissions.




  6. Configure SSL Certificate:
    • If you are to publish your website/SharePoint application with HTTPS, then:
      • Install/Request a Valid SSL Certificate to publish your website using HTTPs.
      • Make Sure that your default web site/your SharePoint application is published and successfully accessed externally.



  7. Test your Configuration:
    • [Approach 1: Internal Testing]:
      • Inside your SQL Server, on the desktop> right click > create new document
      • Rename the document to "TestHTTPAccess" and replace the extension from ".TXT" to ".UDL"
      • Open the UDL file, on the "Provider" tab, select the provider "Microsoft OLEDB Provider for Analysis Services 10.0"
      • On the "Connection" tab, write the following:
        • Data Source: http://<your website name>/OLAP/msmdpump.dll (Replace <your website name> with your internal SharePoint/WebSite URL)
        • User Name: <domain-name>\<user-name>
          (Write the username of the user(s) you previously added to the OLAP folder permissions)
        • Password: <Your Password>
      • Click on Test Configuration, if it Succeeds, then continue with approach 2

    • [Approach 2: External Testing]:
      • Open a new MS Office Excel sheet (2007, 2010 or 2013)
      • From the Data tab > Choose from Other Sources > from Analysis Services
      • In the Data connection wizard, Write your SharePoint application/WebSite URL (http(s)://<your website name>/OLAP/msmdpump.dll) and the user name and password of the user with access permissions on your cube
      • If connection is successful, you should be able to see your Cube, and start working with your pivot table(To know how to integrate these Excel Reports with SharePoint, kindly follow my post on using Kerberos authentication with SP 2010 excel services >  http://ahmedmedhat141084.blogspot.com/2013/06/using-sharepoint-2010-with-excel.html)

That's it :D
Now you can start building your Pivot Table and Pivot Chart normally using excel.

References (Read especially if you need to know how to configure Firewall access):

Posted by at No comments:

Monday, June 3, 2013

Using SharePoint 2010 with Excel Services to access SQL analysis services cube (external data) using Kerberos Authentication

In this article, I'll try to explain how to work with Excel Services in-order to visualize data/reports from SQL analysis services cube (or external data) using kerberos authentication.

 To more elaborate on the topic, What I want to achieve is:

 1- Create a Cube on SQL Analysis Services (my external data i'm trying to access)

 2- Create Specific Roles on that Cube (each user see something different from the other)

 3- Create an Excel File, connect it to the Cube to generate a Pivot Table and Charts to visualize the data from cube

 4- Upload the Excel File to my SharePoint Site

 5- Access the SharePoint Site with Different Roles and Open the uploaded excel file using excel services passing the user permissions to the Cube to retrieve user specific data.

 Such implementation is mainly used if you need to create a BI portal viewing live reports and all are OOB SharePoint. Also used if you are planning that users authenticating your portal wishes to display only data related to their accounts. (My Example: Cube containing information about multiple k-12 schools, but each school principal will only see the excel report rendering information about his/her school only)

 Now, to be able to achieve this, first you need to know study your environment and know your current authentication mechanism as it really differs on which approach you will use.

 Here I'm going to explain how to configure Kerberos authentication to link your excel services to SQL external data (cube)
The reason for using Kerberos is quite simple: you need Kerberos to work around double or multi-hop authentication scenarios. For example in 2007, the user authenticated to the web front-end (1 hop), the web front-end contacted ECS running under the SSP (2 hops) which in turn went back to fetch data from the SQL Server or Analysis Services (3 hops).

 The only way to eliminate using Kerberos authentication is if you have all in one machine (SP, SQL & AD all on the same server), then you can just work with excel services directly with your NTLM authentication, no change.

 But in any normal SharePoint topology, you have multiple web front ends, clustered SQL servers and AD servers. So in that case you are obliged to use kerberos authentication in order to succeed at this.

 So, enough talking...........let's get into business :D (Start Reading Carefully)


[I] I'll suppose, you have your cube ready and you added it to your SQL analysis services component.

I'll post an article soon on how to do that exactly, and replace this text with a link to the post :)



[II] Create an Excel File connecting to your cube and formulating a report:

  1. Open a new MS Office Excel sheet (2007, 2010 or 2013)
  2. From the Data tab > choose from Other Sources > from Analysis Services
  3. In the Data connection wizard, Write your SQL Server instance name (or IP) and the user name and password of the user with access permissions on your cube

  4. After that, Select your Cube, and click next
  5. Name your Connection file (in my case: "MyConnection"), then make sure to open the Authentication Settings and that "Windows Authentication" is Selected, then click Finish
  6. Now, you are ready to build your Report, You can choose either "PivotTable Report" or "PivotChart and PivotTable Report" and click ok.
  7. Finally, your report should look like the screenshot below. Save file and close Excel.

    (Note: You might need to follow my other article about enable HTTP access to Analysis Services, if you want to enable ad-hoc reporting for your cube data: http://ahmedmedhat141084.blogspot.com/2013/06/an-extra-mile-configure-http-access-to.html)


[III] Configure your SharePoint Web Application:

  1. Open your SharePoint Web Application and Change its Authentication Providers as below:
    • Open Central Administration > Application Management > Manage Web Applications > MyPortal
    • Authentication Providers > Default Zone
  2. The authentication providers dialog will list all the zones for the selected web application along with the authentication provider for each zone:
  3. By selecting the zone, you will see the authentication options for that zone:
  4. Verify that Kerberos authentication is enabled:
    • Open IIS manager.
    • Select the IIS web site to verify.
    • In Features View, under IIS, double click Authentication.
    • Select Windows Authentication which should be enabled.
    • On the right hand side under Actions, select Providers. Verify Negotiate is at the top of the list.
  5. Verify that Kernel mode authentication is disabled
    • Open IIS manager
    • Select the IIS web site to verify
    • In Features View, under IIS, double click Authentication.
    • Select Windows Authentication, which should be enabled.
    • Click Advanced Settings.
    • Verify both EAP and Kernel Mode Authentication are disabled.

[IV] Active Directory Configurations:

  1. Open your AD, Create these 2 Service accounts:
    • svcExcel
    • svcC2WTS
  2. For each service account, configure a set of service principal names (SPNs) that map to the DNS host names assigned to each web application.
    • Run the following commands in CMD:SetSPN -S HTTP/myportal.mydomain.com mydomain\sp_defaultwebapp
    • where, "sp_defaultwebapp" is my app pool user identity controlling my portal.
  3. Open AD, find sp_defaultwebapp service user > right click > properties > delegation tab > select Trust this user for delegation to specified services only, then select Use any authentication protocol.
  4. Click the Add button to add the services the user (service account) will be allowed to delegate to. To select a SPN, you will look up the object the SPN is applied to. In our instance, we are trying to delegate to a HTTP service which means we search for the service account of the IIS application pool that the SPN was assigned to in the previous step. On the Select Users or Computers dialog box, click Users and Computers, search for the IIS application pool service accounts (in our case mydomain\sp_defaultwebapp” and then click OK:
  5. You will then be prompted to select the services assigned to the objects by service principal name.
  6. On the Add Services dialog box, click Select All then click OK. Note that when you return to the delegation dialog you do not actually see all the SPNs selected. To see all SPNs, check the Expanded check box in the lower left hand corner.

[V] Kerberos authentication for SQL Server Analysis Services:

  1. Configure Analysis Service instances in the SQL Server 2008 R2 cluster to use Kerberos authentication
  2. Verify that the client can authenticate with the cluster by using Kerberos authentication
    • on SQL server, Open CMD and run the following commands:SetSPN -S MSOLAPSvc.3/lms-sql-vs01.mydomain.com mydomain\sp_defaultwebapp
      SetSPN -S MSOLAPSvc.3/lms-sql-vs01 mydomain\sp_defaultwebapp
    • Verify by running:SetSPN -L mydomain\sp_defaultwebapp

[VI] Configure Kerberos for Excel Service:

Kerberos constrained delegation must be configured if Excel Services is going to delegate the client’s identity to a back end data source. In this example Excel services will query data from a SQL transactional database, therefore Kerberos delegation is required.
The Active Directory Users and Computers MMC snap-in is typically used to configure Kerberos delegation. To configure the delegation settings within the snap-in, the Active Directory object being configured must have a service principal name applied; otherwise the delegation tab for the object will not be visible in the object’s properties dialog. Although Excel Services does not require a SPN to function, we will configure one for this purpose.

On the command line, run the following command:

SETSPN -S SP/SAPExcelServiceApp mydomain\svcExcel
After that, open AD, find “svcExcel” user to configure constrained delegation:
  1. Open the Active Directory Object’s properties in Active Directory Users and Computers.
  2. Navigate to the Delegation tab
  3. Select Trust this user for delegation to specified services only.
  4. Select Use any authentication protocol. This enables protocol transition and is required for the service account to use the C2WTS.
  5. Click the add button to select the service principal allowed to delegate to.
  6. Select User and Computers and choose “sp_defaultwebapp”.
  7. Select the service account running the service you wish to delegate to. In this example it is the service account for the SQL service. (The service account selected must have a SPN applied to it. In our example the SPN for this account was configured in a previous scenario.)
  8. Click OK. You will then be asked to select the SPNs you would like to delegate to in the following window, select MSOLAPSvc.3/lms-sql-vs01.mydomain.com
  9. Select the services for the SQL cluster and click OK
  10. You should now see the selected SPNS in the services to which this account can presented delegated credentials list
  11. Repeat the same above steps for user: “svcC2WTS” starting with the command line:SETSPN -S SP/C2WTS secedu\svcC2WTS

Next, configure the required local server permissions that the C2WTS requires. You will need to configure these permissions on each server the C2WTS runs on. Log onto the server and give the C2WTS the following permissions:
  1. Add the service account to the local Administrators Groups.
  2. In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:
    • Act as part of the operating system
    • Impersonate a client after authentication
    • Log on as a service
Finally, apply these configurations on SharePoint central administration:
  1. Under Security > Configure Managed Service Accounts, Register the C2WTS service account as a managed account.
  2. Under services, select Manage services on server.
  3. In the server selection box in the upper right hand corner select the server(s) running excel services.
  4. Find the Claims to Windows Token Service and start it
  5. Go to Security > Manage Service Accounts. Change the identity of the C2WTS to the new managed account > svcC2WTS
    (Note: If the C2WTS was already running before configuring the dedicated service account, or if you need to changes the permissions of the service account after the C2WTS is running you must restart the C2WTS from the services console.)
  6. Again, Go to Security > Manage Service Accounts. Change the identity of the Excel Services to the new managed acount > svcExcel
  7. In addition, if you experience issues with the C2WTS after restarting the service it may also be required to reset the IIS application pools that communicate with the C2WTS

[VII] Add Startup dependencies the WIF C2WTS service:

There is a known issue with the C2WTS where it may not automatically startup successfully on system reboot. A workaround to the issue is to configure a service dependency on the Cryptographic Services service:

In addition, if you experience issues with the C2WTS after restarting the service it may also be required to reset the IIS application pools that communicate with the C2WTS.
  1. Open the Command Prompt window.
  2. Type: sc config "c2wts" depend= CryptSvc
  3. Find the Claims to Windows Token Service in the services console.
  4. Open the properties for the service.
  6. Check the Dependencies tab. Make sure Cryptographic Services is listed.
  7. Click OK.



[VIII] Grant the Excel Services service account permissions on the web application content database:

A required step in configuring  SharePoint Server 2010 Office Web Applications is allowing the web application’s service account access to the content databases for a given web application. In this example, we will grant the Excel Services service account access to the “portal” web application’s content database by using Windows PowerShell.

Run the following command from the SharePoint 2010 Management Shell:

$w = Get-SPWebApplication -Identity http://portal

$w.GrantAccessToProcessIdentity("vmlab\svcExcel")



[IX] Start the Excel Services service instance on the Excel Services server:

Before creating an Excel Services service application, start the excel services serve service on the designated Farm servers.
  1. Open Central Administration
  2. Under services, select Manage services on server
  3. In the server selection box in the upper right hand corner select the server(s) running excel services
  4. Start the Excel Calculation Services service if not started.



[X] Configure Excel services trusted file location and authentication settings

Once the Excel Services application is created, configure the properties on the new service application to specify a trusted host location and authentication settings.

  1. Open Central Administration.
  2. Select Manage Service Applications under Application Management
  3. Click the link for the new Service Application  > Excel Services in this example.
  4. In the Excel Services management page, click "Trusted File Locations".
  5. Add 2 new trusted file location.
    • http://myportal.mydomain.com
    • https://myportal.mydomain.com
  6. Specify the location to your test library.
  7. In External Data Select trusted data connection libraries and embedded.



[XI] The Big Restart (v. important)

You may need to first IISreset the server running the (excel services & c2wts), if nothing changes then you may need to restart the server itself and IISreset all other WFEs


WooOOooOOOoooW, Now that was something :D

Now for our final preview, we will upload the Excel file to the portal (in any document library).:
  1. Log-in with the desired role school user
  2. And now click on the excel file to view it in the browser to display the report with security injected to the cube seamlessly and now the user can see only info about his school.
    ( HHHuuurrrrrraaaaaaaayyyyyy  :D )

I hope you'all have enjoyed this, I really spent a lot of time collecting this info from several sources and a couples of hair pulling searches on the internet. And tried as much as I can to simplify the steps above and aggregating all the configurations here and there into this one article.


Posted by at 2 comments:
Subscribe to: Posts (Atom)