There are a lot of use cases where it can be challenging to configure SSO to Cognos Analytics and then Pass-Through Authentication to SSAS (SQL Server Analysis Services) Cube Datasource without being prompted, all via Kerberos Authentication.
So, here I've outlined the changes I've made to successfully set this up in a lab environment and share all the configuration settings to achieve this successfully.Environment:
MSSQL/SSAS 2016 Server: WinEndorLab1.CASUPPORT.SUPPORT2016.AD.HURSLEY.IBM.COM
CA 11.1.2+ Server: CHOIRS1.CASUPPORT.SUPPORT2016.AD.HURSLEY.IBM.COM
Client - Windows 10: Strews1.CASUPPORT.SUPPORT2016.AD.HURSLEY.IBM.COM
AD Domain: CASUPPORT.SUPPORT2016.AD.HURSLEY.IBM.COM
Service Account: CASUPPORT\CAService
OS: Windows 2016
Test user: User1Part 1 - User Authentication via SSO with Kerberos
NB: From 11.1.2+ the IIS Script to configure the gateway is now found in the installation <install>\cgi-bin\templates\IIS\CA_IIS_Config
NB: When editing the script, ensure SSO is set to "True"
1. IIS configurations
Service Account running Cognos Analytics - CASupport\CAService
Gateway Server - CHOIRS1$ is the SAME Server running IIS and CA
To Configure Kerberos for SSAS Server download and install 'Microsoft® Kerberos Configuration Manager for SQL Server'
You can download it onto the same SSAS Server or another machine, in this case it was installed on the same server.
Run the tool and if its run locally (optional) then just click the "Connect" button
Though now this is configured correctly, initially there will be the option to "Fix" each of the SPNs listed. Just select "Fix" for each one.
Click on the "Delegation" tab and make sure that Trust for delegation has been granted as you can see below:
Check SPN's for WinEndorLab1 (SSAS) which would have been updated due to the above configuration tool.
DelegationPart 2 - SSAS Datasource Configuration
1. Log into CA and create a new datasource connection 'Microsoft Analysis Services 2016 (ODBO)' and select "Namespace"
2. Test the connection as the user logged in i.e. User1 and NOT as System AdministratorPart 3 - SSAS Security Settings
1. Open SSAS Management Console from the SSAS Server, navigate to the Roles and create a new Role
4. Click on "Cubes" and make sure you set the Access to "Read"
5. Go to the Datasource Section
6. The Connection String section would be containing the host/catalog/database for the SSAS cube.
The "Impersonation Info" is set to "Use the Service Account"
7. Log into the Windows 10 Client Machine (Strews1) as User1 and SSO into CA as User1,
create and run a simple crosstab