- 1 Introduction
- 2 Preparation
- 3 Information Required
- 4 Access Required
- 5 Domain configuration
- 6 Windows 2012 Server
- 7 IIS Configuration
- 8 iOS Access
- 9 XLCubed Configuration
- 10 Troubleshooting
- 11 More information
- 12 Alternate approach
Please note the recommend setup is to now use the Kerberos Delegation#Alternate approach as this is significantly less complex and supports all browsers
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.
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
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:
If the service or application pool is set to run under NetworkService then use the server name:
You will need to be a Domain Administrator to perform all of the configuration steps
Enable Server Delegation
The first step is to enable delegation on ALL the machines involved in the process:
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.
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.
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.
setspn –S http/WebServer WebUser setspn –S http/WebServerFQDN WebUser
setspn -S http/webserver domain\appooluser setspn -S http/webserver.domain.com domain\appooluser
setspn –S MSOLAPSvc.3/ASServer ASUser setspn –S MSOLAPSvc.3/ASServerFQDN ASUser
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
setspn –S MSSQLSvc/SQLServer:1433 SQLUser setspn –S MSSQLSvc/SQLServerDQDN:1433 SQLUser
Where 1433 would be replaced with the appropriate SQL Server port number
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
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):
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:
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.
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
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:
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.
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:
- https://chromeenterprise.google/policies/?policy=AuthNegotiateDelegateWhitelist (up to Chrome 100)
- https://chromeenterprise.google/policies/?policy=AuthNegotiateDelegateAllowlist (from Chrome 101)
The following pages contain some very useful information or tools regarding Delegation:
- Kerberos Explained
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 in the cubes being connected to. 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:
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.