topic Snowflake database connection using OAuth 2.0 authentification in Talend Studio

Snowflake database connection using OAuth 2.0 authentification

AdelineA — Mon, 01 Jul 2024 12:55:14 GMT

Hello,

I'm trying to connect to a Snowflake database using OAuth 2.0 authentification.
I have a Snowflake account with the following caracteristics :
CREATE SECURITY INTEGRATION MY_SNOWSQL_CLIENT2
TYPE = API_AUTHENTICATION
AUTH_TYPE = OAUTH2
ENABLED = TRUE
OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
OAUTH_CLIENT_ID = 'xxxxxxxxxxxxx'
OAUTH_CLIENT_SECRET = 'xxxxxxxxxx'
OAUTH_TOKEN_ENDPOINT = 'https://xxxxxxx.snowflakecomputing.com/oauth/token-request'
OAUTH_ACCESS_TOKEN_VALIDITY = 86400
OAUTH_REFRESH_TOKEN_VALIDITY = 86400
OAUTH_GRANT = 'CLIENT_CREDENTIALS'
;

When I try to connect to my database with Talend, I have this error message :

'java.lang.NullPointerException: Cannot invoke "com.fasterxml.jackson.databind.JsonNode.asText()" because the return value of "java.util.Optional.orElse(Object)" is null'

Did anyone experienced the same issue ?
Do you know if there is a particular library to install to connect to Snowflake with OAuth 2.0 authentification ?

Best regards,

Adeline

Re: Snowflake database connection using OAuth 2.0 authentification

Jareds — Wed, 31 Jul 2024 17:44:54 GMT

I am having trouble finding documentation for it but, with regard to your Snowflake security integration, what did you use for your redirect URI?

Re: Snowflake database connection using OAuth 2.0 authentification

Joni1 — Mon, 05 Aug 2024 15:45:15 GMT

We were having this same issue. The problem is that Talend only uses external OAuth. This means you must have an identify provider. When you create the security integration in Snowflake, the type will be external_oauth. You will need to provide snowflake with information from your identify provider.

This is the link to Snowflake's documentation on setting up an External Oauth Security Integration: https://docs.snowflake.com/en/user-guide/oauth-ext-overview

We found some of the information on the identify providers set ups to be a bit out of date. We had to use our identify provider's documentation to get the application set up correctly.

What helped was adding the tLibraryLoad component to the integration during testing so we could see the exact error message and know what to change. It took a few iterations to get it right.

Hope this helps!

Re: Snowflake database connection using OAuth 2.0 authentification

m_garg — Thu, 07 Nov 2024 14:09:11 GMT

Did anyone try this and was successful, if yes pls help by providing detailed steps as in how three different components External OAuth security integration in Snowflake, Custom authorization server and Talend were configured.