topic Re: Setting the default role when using Snowflake on GCP as a target in Qlik Replicate

Setting the default role when using Snowflake on GCP as a target

strandafir — Mon, 16 Sep 2024 11:03:00 GMT

Hello Community,

Is there any option to set a custom role to the Endpoint Connection for Snowflake?

All I can find in the documentation is that it uses SYSADMIN: https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/Snowflake_Google_Target/google_permissions_snow.htm

There is a need for using Snowflake external OAuth with OKTA.

To use this, a token is generated by mentioning the scope, which maps to the role.

However, in this particular scenario, role will not be defined in OKTA, and instead a generic scope is defined: SESSION:ROLE-ANY.

This allows the connection to be made to Snowflake with a valid token, but the user needs to manually change the role after connecting.

For Azure and AWS it seems that there is way to do this: https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/Snowflake%20Target/snowflake_permissions.htm?id=802#Required%20permissions

But for Google, the "Additional ODBC connection properties" setting is not available in the UI:

Thank you

Re: Setting the default role when using Snowflake on GCP as a target

john_wang — Mon, 16 Sep 2024 12:50:08 GMT

Hello @strandafir ,

Thanks for reaching out to Qlik Community!

There are several options you may have a try:

1- Add internal parameter additionalConnectionProperties and then input the properties settings there;

2- If the above option does not work and you might want to manually change the role after connecting, you may add internal parameter afterConnectScript and put the alter session command there.

Hope this helps.

John.

Re: Setting the default role when using Snowflake on GCP as a target

strandafir — Thu, 26 Sep 2024 08:23:43 GMT

Thank you@john_wang ! I have tested out with afterConnectScript and it works!

Re: Setting the default role when using Snowflake on GCP as a target

john_wang — Thu, 26 Sep 2024 09:03:52 GMT

Thank you so much for your excellent support @strandafir ! if you can share sample command then it's much appreciated, it's valuable for all of us.

Best Regards,

John.

Re: Setting the default role when using Snowflake on GCP as a target

strandafir — Thu, 26 Sep 2024 09:08:14 GMT

Hey! Sure, here is how it was achieved:

Re: Setting the default role when using Snowflake on GCP as a target

john_wang — Thu, 26 Sep 2024 09:19:06 GMT

Thank you so much for your information in resolving the technical issue. Your support made a big difference, and I truly appreciate it! @strandafir