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.
5. 
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.

Hope it helps someone out there :)


References:
http://blogs.msdn.com/b/andrasg/archive/2010/05/04/setting-up-sharepoint-2010-excel-services-to-get-external-data.aspx
http://www.microsoft.com/en-eg/download/details.aspx?id=23176