From Version 9.2.29 you can setup XLCubedWeb to use a SAML 2.0 compliant Identity provider service to handle user authentication.
You will need the following:
- Enterprise XLCubedWeb
- A SQL-based Repository in "Role" mode
- An identity provider service that supports SAML 2.0 (for example, Okta, PingIdentity etc.)
- Some identity providers will require that the XLCubedWeb website is running as HTTPS
- Contacted email@example.com to enable the feature
You will need to create a new application or service provider. Use the following details where:
servername = Your xlcubedweb server address, for example http://someserver/xlcubedweb, this needs to match what the users would type into a web browser of the Excel publication screen
xlcubedurl = servername/webform/auth.aspx, for example http://someserver/xlcubedweb/webform/auth.aspx
The following is a list of possible information you will need to provide:
|Single sign On URL||xlcubedurl|
|SP Entity ID||XLCubedWeb|
The NameID attribute will be used as the key for users to store user-specific information such as "My Reports", recent reports, bookmarks, workbook aspects etc. Any format should be fine.
The following optional attributes can also be included:
|DisplayName||The text to display when the user is listed||Joe Bloggs|
|The email address of the user, would be used when emailing alerts, for firstname.lastname@example.org|
|EffectiveUser||User-id for connections that support this option. See "Datasources" section for more information||cubeuser|
|EffectiveRole||Comma-delimited list of Cube Roles to use for this user. See "Datasources" section for more information||Accounts, Management|
|MemberOf||Comma-delimited list of SIDs or active-directory group paths the user belongs to, this is used to determine which XLCubed Roles the user belongs to||S-1-5-21-1085031214|
|Roles||Comma-delimited list of XLCubedWeb Roles the user belongs to, this controls which folders and reports they can see and what level of access they have||Authenticated Users, Upper Management|
|SQLContext||Text value that will be passed as a read-only session context to SQL connections. (You can use SELECT SESSION_CONTEXT(N'xlcubed_context'); to retrieve that value)||SQLValue1|
For providers that don't allow custom attributes you can use the "AuthEffectiveUserClaim" AppSetting setting in the web.config to define which attribute to use, for example to use the Email from a Microsoft Azure setup:
<add key="AuthEffectiveUserClaim" value="http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress" />
Or, depending on the setup of the Active Directory users it may be more appropriate to use:
<add key="AuthEffectiveUserClaim" value="http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name" />
This guide assumes you already have XLCubedWeb running using a SQL-based repository using the "Role" model. The authentication currently used doesn't matter at this stage.
You will first need to export the Application/Service metadata from the provider service, this includes information such as the URLs XLCubedWeb will access to authenticate as well as any signing certificates needed for the request.
Switch the authentication type to "Customer provider":
Browse and import the Provider Metadata xml file.
You then have some other options to consider:
- Redirect vs Postback - this is provider specific the most common is "Redirect".
- Redirect - Identity provider initiated. This is the most common option. When XLCubedWeb is accessed directly users will be sent to the providers login page, they can optionally access XLCubedWeb directly from the providers portal.
- Postback - Service provider initiated. XLCubedWeb must the first point of access and it will send an AuthN request to the identity provider. If encryption is required on this request you must download the signing certificate, import it and provide the password under the "Advanced" tab of the XLCubedWeb config tool.
- Custom auth procedure see the next section
- Enable scheduler access see the specific section about scheduling
- Allow provider initiated sign on available from V10.0.35, allows the sign on process to be started from the identity provider, allowing XLCubedWeb to be accessed directly from the identity provider
SQL-based user mapping
Rather then using the attributes provided by the Identity provider to determine things like the users display name or XLCubedWeb Role membership you can specify a stored procedure to use as well. Attributes will either replace or combine with the service provider ones depending on each one.
The stored procedure needs to be created in the XLCubedWeb SQL Database and XLCUBED_USER database role must have execute access to that. Here is an example procedure:
CREATE proc [dbo].[ap_ValidateUser] @identity nvarchar(500) as select 'Joe Blogs' as cDisplayName, 3 as nRoleID, 'cubeuser' as cEffectiveUser
@identity will be the value provided as the NameID from the provider NB The stored procedure can return several rows, one for each "nRoleID" needed only the first row will be used for other attributes
Every field is optional and can be the following:
|cDisplayName||The text to display for the user||Joe Blogs|
|cEmail||The email for the email@example.com|
|cEffectiveUser||This correlates to the EffectiveUser attribute||cubeuser|
|cASRole||This correlates to the EffectiveRole attribute||Accounts, Managment|
|nRoleID||The role id for the user based on the [Role].[XL3Roles] Table, one row should be returned for each role id||3|
Typically you'd be doing a lookup based on the user's identity, for example assuming you added 2 tables to the XLCubedWeb Database:
AllUsers - contains a list of all the users
- nUserID - an identity to identify the user internally
- cEmail - email of the user
- cFirstName - First name of the user
- cLastName - Last name of the user
UserToGroup - contains a list of all the groups a user belongs to - the XLCubedWeb roles match the group name
- nUserID - an identity to identify the user internally
- cGroupName - the group a user is a member of
You would then create the Roles to match the "cGroupName" and assign the permissions required using the XLCubedWeb config application
CREATE proc [dbo].[ap_ValidateUser] @identity nvarchar(500) as select U.cFirstName + ' ' + U.cLastName as cDisplayName, R.nRoleID as nRoleID from AllUsers U Join UserToGroup G on G.nUserID = U.nUserID Join [Role].[XL3Roles] R on R.cDescription = G.cGroupName where U.cEmail = @identity
The following providers integrate with SAML 2.0 and XLCubedWeb will automatically pass the SAML Assertion to the data source for user-authentication:
- SAP Hana
All other datasources set to use "Integrated" or "Windows-based" authentication will use either the "Anonymous" user account (if Asp.Net impersonation is enabled on the XLCubedWeb Website) or the Application Pool user for access.
Analysis services allows cube admins to impersonate other users, this means that you can set the service account to be a cube admin and then use the EffectiveUser or EffectiveRole attributes to ensure data-access is made in the context of the user. This makes use of the connection properties "EffectiveUser" and "Roles" when the connection is opened, please see the Microsoft documentation for more information.
For access from the XLCubed Mobile app, set the repository to "Require login" and to use the "Browser-based login".
Offline reports cannot be scheduled to update or be refreshed
To enable access from the XLCubed Scheduler, tick the "Enable scheduler access". This creates a special secret key that allows the Scheduler service to access XLCubedWeb, this is limited to only allow access from the same machine as XLCubedWeb is running.
You can't use Distribution lists based on Roles as it is not possible for the Scheduler to access the list of role members from the identity provider
If connecting to an Analysis Services backend, you can check the username context by inserting a grid, and driving a member from a range that contains the following:
This will give the current user, once published it should show the user that you just logged in with. If it doesn't then it's likely the EffectiveUser claim was empty or not found.
You can get a copy of the SAML response by adding the following setting to the web.config:
<add key="AuthServiceDebug" value="true"/>
If you have AuthServicePostBack enabled then you may need to temporarily disable that, depending on the provider requirements.
You can then (in a new browser session) go to:
Using "View Source" will give you the XML response from the identity provider.
You MUST remove the debug setting when you no longer need that.