Issue with Spotfire 14 OAuth2 for SQL server data source (OAuth2/OpenID Connect authentication for Information Services)

[Andrey Belinskiy]

Hello, we’re experimenting with implementing the identity provider authentication for information services (Identity provider (OAuth2/OpenID Connect) authentication for Information Services). We’re trying to authenticate Spotfire towards an SQL server database in Azure that has “Microsoft Entra authentication only” option enabled. We have created a custom data source template based on the existing SQL Server template where we’ve added the necessary parameters listed in the Identity Provider topic linked above (such as issuer, scope, etc.). We have added the redirect URI for Spotfire to our App reg in Azure. We have also registered the App reg as IdP in Spotfire Server configuration. The data source template config and more information about the process and the errors we get can be found in the attached document.

In short, when we try to connect using our custom data source template, we get the following error:

Error message: Could not get contents of 'Sandbox SQL Server Test OAuth' from the server.
The data source reported a failure.

InformationModelException at Spotfire.Dxp.Data:
Error retrieving metadata: Login failed for user '<token-identified principal>'. ClientConnectionId:58c62949-0eb9-45a2-a3c7-2ec677d65b0d (HRESULT: 80131500)

We've also tried different parameters for the connection string, and as a result we get another error:

The server has no permissions credentials for doing the operation on the 'oauthJorntest3' data source.

InformationModelException at Spotfire.Dxp.Data:
No credentials for data source(s) that requires user authentication. (HRESULT: 80131500)

At the moment we are wondering about if the data source template that we have created is correct, or we miss some additional parameters in regards to OAuth in there. In particular, according to the documentation page (Identity provider (OAuth2/OpenID Connect) authentication for Information Services) there are several additional parameters available, but we are not sure which values should be used there, and if these parameters are required at all in our case. More specifically, should we use the following parameters “metadata_url_property_name” and “token_endpoint_url_property_name” in the data source template, and if so, which values should we use?

We will appreciate any input on this topic 🙂
 

spotfire oauth2 issue.pdf

[Andrey Belinskiy]

Short update - after analyzing the audit logs for the SQL Server database, we found out that the failed logins from Spotfire server look like this:

event_time_t [UTC] - 2024-02-22T08:11:05.566Z
succeeded_s - false
affected_rows_d - 0
client_ip_s - 10.73.214.12 (this is the address of the VM that hosts Spotfire Server)
application_name_s - Microsoft JDBC Driver for SQL Server
additional_information_s - <login_information><error_code>18456</error_code><error_state>132</error_state></login_information>

So the error code reported by the database is 18456 and the state is 132, which points at Azure Active Directory login failure. We will keep looking for more info.