Adopt application passwords for Excel live data feature to support JAS SAML authentication

Most rich clients in the ELM suite of products have now adopted JAS/SAML application passwords, since this is a very popular way for customers to authenticate in ELM since 7.0.2. Customers would like to refresh Excel data in Excel live reports, but tweaking has not yet been performed to make this possible.

As a form of rich client, there needs to be an adoption of application passwords as per the below wiki for this to work correctly:

https://jazz.net/wiki/bin/view/Main/ApplicationPasswordsAdoption

The /rs application uses OIDC SDK to connect to ELM, so this seems to apply:

"Other clients that can connect to ELM applications with OIDC authentication must be changed to support application passwords."

For the Excel live report feature see the below:

Exporting reports to different formats

https://www.ibm.com/docs/en/elm/7.0.2?topic=builder-exporting-reports-different-formats

For JAS/SAML and application passwords see the below:

https://jazz.net/wiki/bin/view/Deployment/JASUserRegistryConfig

https://jazz.net/wiki/bin/view/Deployment/EnableJASAppPasswords

[Environment]

Report Builder 7.0.2 iFix007 (JRS_7.0.2-I20210421-1303)

MS Excel 365

Windows client

[Reproduction Steps]

1. Create a new report in report builder that accesses data from a ETM project area.

2. Click the [Export] > [Export the report to Microsoft Excel] option.

3. Click the link "Download a spreadsheet with live data." and download the xlsm file

4. Open the xlsm and enable editing and content, as required.

5. Click the button "Make the results dynamic" to create a data source for the live query.

6. In the popup authentication dialog specify your user ID and application password. --> Authentication fails.

What is the expected output?

A dialog from the SAML iDp is displayed prompting for user id and password. After entering your user ID and application password the data refreshes.

Note: It should NOT be possible to authenticate WITHOUT the application password.

What do you see instead? Please provide screen shots if possible.

Excel displays a Windows security dialog prompting for user id and password, but after entering your user ID and application password, it fails to refresh the data.

Please provide any additional relevant information below (server logs, custom project area template, etc...).

This issue is similar to the issue described here.. except that in that case the refresh did not work for smart card authentication.

RS could not authenticate /dataservice URL on smart card environment.

https://jazz.net/jazz/web/projects/Jazz%20Reporting%20Service#action=com.ibm.team.workitem.viewWorkItem&id=462179