Single sign-on

From Version 9.2.29 you can setup XLCubedWeb to use a SAML 2.0 compliant Identity provider service to handle user authentication.

Requirements

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 support@xlcubed.com to enable the feature

Setup

Provider setup

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:

Field Value
Single sign On URL xlcubedurl
Recipient URL xlcubedurl
Destination URL xlcubedurl
SP Entity ID XLCubedWeb
Audience URI XLCubedWeb
Audience Restriction XLCubedWeb

Attributes

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:

Attribute Description Example
DisplayName The text to display when the user is listed Joe Bloggs
Email The email address of the user, would be used when emailing alerts, for example joe.bloggs@xlcubed.com
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

XLCubedWeb Setup

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.

Provider metadata

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.

XLCubedWeb Config

Switch the authentication type to "Customer provider":

Auth1.png

Click configure:

Auth2.png

Browser 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

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 used 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:

Field name Description Example
cDisplayName The text to display for the user Joe Blogs
cEmail The email for the user joe.bloggs@xlcubed.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

Data-source access

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.

Mobile access

For access from the XLCubed Mobile app, set the repository to "Require login" and to use the "Browser-based login".

Limitations

Offline reports cannot be scheduled to update or be refreshed

Scheduling

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.

Limitations

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