XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Kerberos Delegation

Recommended Approach

(Formerly termed "Alternate Approach")

From V9.2.31, if running against Analysis Services (tabular or multi-dimensional) there are alternate configuration options that by-pass the need for Kerberos Delegation to be configured. To use this approach the server connection would be made using the Application Pool user and make use of the "EffectiveUser" connection property to impersonate the XLCubedWeb user within Analysis Services itself.

To do this you need to ensure the Application Pool is running as a domain account and that account has "Admin" access on the server. Then set the following ApplicationSettings in the XLCubedWeb web.config: 

   <add key="StopImpersonatingDuringDataAccess" value="true"/>
   <add key="ForceAnalysisServicesUser" value="true"/>

The first setting ensures the application pool user is used for the data connection and the second setting forces the "EffectiveUser" to be set to the XLCubedWeb user.

Additionally update the following file: 

\inetpub\wwwroot\xlcubedweb\bin\xlcubed.data.analysisservices.xml

Ensure the setting "enforceuseridentity" is set to false: 

<connections keepopen="False" keepsession="True" usenativecom="True" useconnectionpooling="False" enforceuseridentity="False" />

Depending on your network setup, the application pool and SQL Server Analysis service users may need to authenticate, and therefore need to be added to the Windows Authorization Access Group built-in Domain group. See this Microsoft article for more details.

Kerberos Delegation

Delegation will need to be configured if XLCubedWeb is installed on a different server to the underlying database and the Website is configured to use Windows Authentication.

If you have this type of setup and Delegation isn't configured then you will receive a message such as "Server is unavailable" for Analysis Services or a "(null) user" error for SQL Server when trying to access published reports.

Most corporations have a standard setup for Delegation and this would be handled by the Domain Administrators. The following is a guide to how to configure Delegation in the simplest case but is no means a "Best practice" guide as each domain setup and infrastructure can vary enormously.

There are also many other factors that can impact a successful implementation Delegation, such as network infrastructure, client security settings and software. The following guide will assume that all the clients will be running Internet Explorer and there is only one domain with everything located on the same physical network with no firewalls/routers that could filter Delegation requests.

Preparation

Before beginning it is worth ensuring XLCubedWeb is working correctly including loading a report (at which point it should give the server is not available error)

Ensure the XLCubed Website Application pool is running as either NetworkService or a specifically created domain user and then only "Windows Authentication" and "ASP.net Impersonation" are enabled in the Authentication settings of the website.

Check/clear any existing SPNs for the servers - if any already exist and they are invalid then the configuration will not work. The following link contains some good information about how to do that: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

Information Required

Before configuring Delegation you will need to know the following information:


  • What the networks domain name is - this is needed to get a server's Fully Qualifed Domain Name (FQDN): server.domain
  • The name and FQDN of the Web Server: Webserver
  • The name and FQDN of the Analysis Services: ASServer
  • The name and FQDN of the SQL Server (if allowing SQL connections): SQLServer
  • The port SQLServer is running under (if allowing SQL connections)
  • The User ID for the XLCubed Application Pool configured for the XLCubedWeb website: WebUser
  • The User ID the Analysis Services service is running as: ASUser
  • The User ID the SQL Server service is running as: SQLUser

User ID format

If the User ID's are domain accounts then the format needed is simply: 

 domain\user

If the service or application pool is set to run under NetworkService then use the server name: 

 server

Access Required

You will need to be a Domain Administrator to perform all of the configuration steps

Domain configuration

Enable Server Delegation

The first step is to enable delegation on ALL the machines involved in the process:

  • WebServer
  • ASServer
  • SQLServer

This is a domain level setting and is set using the "Active Directory Users and Computers" administrative tool which is part of the Microsoft Admin Tools Pack and would normally be on the Domain Controller.

ComputerDelegation.png


If there is more then one domain controller then you would need to either initiate a propagate or wait for this change to be applied to all the domain controllers.

Set SPNs

The Service Princial Names (SPNs) are set using a Command Line tool: setspn. This needs to be run in an Administrator Command Prompt with an account that has Domain Admin privileges. It needs to be run for BOTH the server name and the Fully Qualified Domain Name.

If there is more than one domain controller then you would need to wait for this change to propagate to all the servers. Note that we are using the -S switch, which is available from Windows Server 2008 (see this article for details). If you are using a previous version, use the -A switch after manually checking for duplicate SPNs defined in the domain.

Web Server

 setspn –S http/WebServer WebUser
 setspn –S http/WebServerFQDN WebUser

Example: 

 setspn -S http/webserver domain\appooluser
 setspn -S http/webserver.domain.com domain\appooluser

Analysis Services

setspn –S MSOLAPSvc.3/ASServer ASUser
setspn –S MSOLAPSvc.3/ASServerFQDN ASUser
Named instance

Where InstanceName is the name of the instance: 

setspn –S MSOLAPSvc.3/ASServer:InstanceName ASUser
setspn –S MSOLAPSvc.3/ASServerFQDN:InstanceName ASUser

You may need to also setup an SPN for the SQL Browser Service, see the following for more information: http://support.microsoft.com/kb/950599

SQL Server

setspn –S MSSQLSvc/SQLServer:1433 SQLUser
setspn –S MSSQLSvc/SQLServerDQDN:1433 SQLUser

Where 1433 would be replaced with the appropriate SQL Server port number

DNS Aliases

In the case where the server has been set up with an alias, if the alias is an ANAME alias, you should add the SPNs for the name that the users will type in. If it is a CNAME alias, then you must also add the SPNs for the machine name, as the browser may use those when requesting the Kerberos ticket.

User account delegation

Now the SPNs have been set you would need to enable delegation for the service account and application pool users, this is again done in "Active Directory Users and Computers" administrative tool.

Windows 2012 Server

If you are running on Windows 2012 server or above you will also need to run the following command in PowerShell, running as a domain administrator: 

$user1 = Get-ADUser -Identity WebUser
Set-ADUser ASUser -PrincipalsAllowedToDelegateToAccount $user1
Set-ADUser SQLUser -PrincipalsAllowedToDelegateToAccount $user1

Please note in this instance you shouldn't include the domain for WebUser, SQLUser or ASUser

IIS Configuration

After making the following changes and running the Delegation configuration run an IISReset before testing and restart Internet Explorer on the client machine.

Check the providers and order

In IIS Manager, right click on "Windows Authentication" under the Authentication section and select providers, ensure Negotiate appears before NTLM (Negotiate:Kerberos may or may not be needed depending on the network setup):

KerberosProviders.png

Advanced settings

You may need to disable "Enable Kernel-mode authentication" - this is accessible by right clicking on "Windows Authentication" under the Authentication and selecting "Advanced Settings". If Delegation doesn't work initially try toggling this setting (with an IISReset).

With "Enable Kernel-mode authentication" disabled you may encounter problems publishing from the Excel client, if so re-enable it and follow the guidance on the following page:

https://technet.microsoft.com/en-us/library/dd573004(office.13).aspx

iOS Access

If you need iPhone/iPad access either via Safari or XLCubed Mobile, you need to add a configuration profile to enable single-sign on for the device. This can optionally include a list of application ids. When you register the website in XLCubed Mobile, enter the url and then leave "Requires Logon" off, authentication will be handled automatically.

To enable SSO, create text file with an extension of ".mobileconfig". Copy the following into it and modify as needed: 

<?xml version="1.0" encoding="UTF-8"?>   
<!DOCTYPE plist PUBLIC "-//Apple/DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd%22>   
<plist version="1.0">   
<dict>   
  <key>PayloadContent</key>   
  <array>   
    <dict>   
      <key>PayloadDisplayName</key>   
      <string>SSO Settings</string>   
      <key>PayloadType</key>   
      <string>com.apple.sso</string>   
      <key>PayloadVersion</key>   
      <integer>1</integer>   
      <key>PayloadUUID</key>   
      <string>XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX</string>   
      <key>PayloadIdentifier</key>   
      <string>com.YOURCOMPANY.sso</string>   
      <key>Name</key>   
      <string>XLCubedWeb SSO</string>   
      <key>Kerberos</key>   
      <dict>     
        <key>Realm</key>   
        <string>YOURDOMAIN</string>   
        <key>URLPrefixMatches</key>   
        <array>   
          <string>HTTP://YOURSERVER</string>     
        </array>             
      </dict>   
    </dict>   
  </array>   
  <key>PayloadOrganization</key>   
  <string>YOURCOMPANY</string>   
  <key>PayloadDisplayName</key>   
  <string>Single Sign-On Profile</string>   
  <key>PayloadVersion</key>   
  <integer>1</integer>   
  <key>PayloadUUID</key>   
  <string>YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYYYYYY</string>   
  <key>PayloadIdentifier</key>   
  <string>com.YOURCOMPANY.sso.profile</string>   
  <key>PayloadDescription</key>   
  <string>Enables Kerberos Authentication</string>   
  <key>PayloadType</key>   
  <string>Configuration</string>   
</dict>   
</plist>


You should replace the following values as appropriate: 

XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX - generate a guid (https://www.guidgenerator.com/online-guid-generator.aspx)
YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYYYYYY - generate another guid
YOURCOMPANY - You company name with no spaces or punctuation
YOURDOMAIN - your Fully qualified domain name
YOURSERVER - The url as entered into the web browser/mobile app


At the same level as the "Realm" key you can optionally add the following to limit access to Safari/XLCubed Mobile: 

       <key>AppIdentifierMatches</key>   
       <array>   
         <string>com.apple.mobilesafari</string>   
         <string>com.xlcubed.mobile</string>   
       </array>

You can then email the profile to the users, they can then open and install the profile. During the installation process they need to confirm the details and enter a "realm", this would be their user-id, it can be left blank for them to be prompted for that each time.

XLCubed Configuration

There are no steps required within XLCubed - the whole Delegation process is handled between the browser and the various servers before XLCubedWeb starts to process user requests

Troubleshooting

Microsoft Kerberos testing tool

Microsoft have a tool available to test that Delegation has been correctly configured, this can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=39046


Alternate testing tool

There is also a Kerberos website that you can install - it would need to be setup to duplicate the exact configuration and can then be used to check the configuration. It will also provide options to fix any problems it finds. It can be downloaded from the following link:

http://www.xlcubed.com/downloads/kerberos.zip

Non-IE browsers

It is possible for some non-Internet Explorer browsers to use Kerberos, here are some tips that may help, but if you can't get it working then Internet Explorer would need to be the fall back or consider setting up Dual Authentication for access to remote clients.

Chrome

You would need to add all the servers involved in the process to the "White List", this is done via the shortcut for the browser: 

 "C:\Program Files\Google\Chrome\Application\chrome.exe" --auth-server-whitelist="ASServer,WebServer,SQLServer" --auth-negotiate-delegate-whitelist="ASServer,WebServer,SQLServer"

Or by updating the registry, as detailed here:

More information

This page describes the requirements for the recommended approach and EffectiveUserName:

The following pages contain some very useful information or tools regarding Delegation:

Retrieved from ‘https://help.xlcubed.com/index.php?title=Kerberos_Delegation&oldid=11207’