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
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" />

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

Click configure:

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:

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

Troubleshooting

Testing

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:

 MDX: UserName()

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.

Checking response

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:

 https://server/xlcubedweb/webform/auth.aspx?id=123

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.