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||
<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:
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:
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
UserToGroup - contains a list of all the groups a user belongs to - the XLCubedWeb roles match the group name
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:
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.
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.