Support for Oauth for Snowflake

[RajanBabuS]

Hi Team,

For a custom requirement using sqitch & Snowflake, we need to fallback on OAuth as an authentication method.

When I tried individually through Snowsql the OAuth worked fine.

Hoped Sqitch will work on the same means. But when I tried to run sqitch deploy, it didn't recognized and raised an error related to "Token".

Is there an option to connect with Snowflake from Sqitch using the OAuth mechanism ?

[theory]

I think so, yes. See sqitch-authentication; search the page for SNOWFLAKE_JWT. I have not used it myself, but a number of people have on the mail list and contributed these instructions.

[RajanBabuS]

Hi David,

Thanks for the feedback. Went through the mail list and documentation.

When we do say SNOWFLAKE_JWT it is referring to private key authentication with Snowflake.

What we were trying is to use a JWT bearer token set up from Azure AD for password-less entry mechanism.

Reference: https://community.snowflake.com/s/article/Create-External-OAuth-Token-Using-Azure-AD-For-The-OAuth-Client-Itself

Since snowsql supports bearer token/password-less entry, our assumption is Sqitch to have a way around to resolve this issue.

Meanwhile as suggested, I have sent a mail in the Sqitchers group also for the same.

Regards,
Rajan Babu S

[RajanBabuS]

Hi David,

Just to add the need of the OAuth(password less entry) for Snowflake.

1. We are planning to use Sqitch for CICD.
2. Number of people who is going to access the CICD pipeline is > 60 members. So maintenance of the keys and association of keys with the developer profiles becomes a overhead
3. Also, the CICD set up is happening in a Kubernetes environment, so we can't store the .pem files and adds to management overhead.

[RajanBabuS]

Hi David @theory ,

Any help here.

[theory]

I'm sorry I know nothing about Snowflake's OAuth functionality. Maybe there's a way to get it working by setting the authenticator type to oauth in the ~/.snowsql/config file? Internally Sqitch uses ODBC to connect to Snowflake, so perhaps try adding parameters to the target URL as described here. Looks like you can pass authenticator and token there — assuming you've generated an OAuth token. Something like

db:snowflake://xxxx.snowflakecomputing.com/mydb?authenticator=oauth&token=OAuthToken

But I'm just guessing. You're going to have to do some experimentation to figure out what you need in the environment and/or ~/.snowsql/config so that SnowSQL can connect and the ODBC params in your target URI so that the ODBC connection works.

[RajanBabuS]

Hi @theory David,

We validated OAuth with Snowflake ODBC driver. Worked fine after a small change by making it to look at iodbc rather than unixodbc

From Sqitch perspective,

Can you tell us whether Sqitch by default looks at unixodbc ? Can we override/change to look for iodbc ?

**Current error message **
connection string too long
/usr/lib64/perl5/vendor_perl/DBD/ODBC.pm line 157

[theory]

If I understand correctly, you're saying:

• OAuth works with the Snowflake ODBC driver when used with iodbc and not unixODBC
• The issue with unixODBC is that the connection string is longer than it supports
• You want to know how to have Sqitch use iodbc instead of unixODBC

Correct? If so, I think the answer to that third point is to uninstall iodbc then re-compile DBD::ODBC to use iodbc. If I recall correctly, it's a compile-time dependency. How did you install Sqitch?

[shivam7805]

Hi @theory

Followed your iODBC recommendation and was going through below documentation (mapping iODBC) and unfortunately we were getting below error while executing "env ODBCHOME=/opt/openlink/odbcsdk perl Makefile.PL" command:
Doc Link: http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/IODBCPerlHOWTO#Compiling%20the%20Perl%20Modules%20for%20ODBC%20support

Do you recommend any comments to resolve this issue?
I am new to perl programming.

---------------------------------------------------------------------------------------------------
[root@xyz DBD-ODBC-1.61-0]# env ODBCHOME=/opt/openlink/odbcsdk perl Makefile.PL

**********
        Remember to actually *READ* the README file!
        And re-read it if you have any problems.

**********

OSNAME: linux
LANG: en_US.UTF-8
ODBCHOME: /opt/openlink/odbcsdk
LD_LIBRARY_PATH:
DBROOT:
WINDIR:
II_SYSTEM:
DBD_ODBC_UNICODE:
Perl: 5.026003
ExtUtils::MakeMaker: 7.34
Command line options:
  g! = 0
  x! = undef
  e! = undef
  w! = undef
  u! = undef
  o=s =


Your LANG environment variable is set to "en_US.UTF-8"
This is known to cause problems in some perl installations - even stopping
this Makefile.PL from running without errors. If you have problems please
try re-running with LANG unset or with the utf part of LANG removed.

You are using a Perl configured with threading enabled.
Please read the warnings in DBI about this.

You should also be aware that on non-Windows platforms ODBC drivers come
in two forms, thread-safe and non-thread-safe drivers and you may need
to make sure you are using the right one.

Press return to continue...
Looking for odbc_config in : /opt/openlink/odbcsdk
Looking for odbc_config at /opt/openlink/odbcsdk/bin/odbc_config
Looking for odbc_config in (PATH) /sbin:/bin:/usr/sbin:/usr/bin
  odbc_config not found
Looking for iodbc-config in PATH /sbin:/bin:/usr/sbin:/usr/bin
  Found iodbc-config (via iodbc-config) version 3.52.13

  ODBC data sources should be added to /etc/odbc.ini

  ODBC drivers should be added to /etc/odbcinst.ini

  iodbc-config reports --prefix=/usr
  but cannot find header files sql.h,sqlext.h,sqltypes.h in that path so ignoring
NOTE: Have you installed the libiodbc-dev package.
odbc_config not found - ok, there are other things I can do
Still trying to guess ODBCHOME - looking for headers now
  trying /opt/openlink/odbcsdk/include
  trying /usr/include
  trying /usr/local/include
  trying /usr/pkg/include
  trying /usr/local/easysoft/unixODBC/include

I cannot find driver manager header files. Perhaps you need to install the
unixodbc-dev package or the iodbc-dev package


The DBD::ODBC module needs to link with an ODBC 'Driver Manager'.
(The Driver Manager, in turn, needs one or more database specific ODBC
drivers. The DBD::ODBC module does _not_ include any ODBC drivers!)

You need to indicate where your ODBC Driver Manager is installed.
You can do this by:

o setting the ODBCHOME environment variable
o running 'perl Makefile.PL -o odbcdir'
o adding path to odbc_config/iodbc_config to PATH

If you do not have an ODBC Driver Manager you should try to get hold of
the unixODBC packages for your system or build it from source (see
http://www.unixodbc.org). If you install driver manager packages you
need to include the "XXX-dev" package which includes the C header files.

Thanks & Regards,
Shivam Rathod

[theory]

This bit I think is key:

NOTE: Have you installed the libiodbc-dev package.

[shivam7805]

Hi @theory

[abc@xyz ~]$ sudo yum install libiodbc-devel
Updating Subscription Management repositories.
RP_ME_RH_8_microsoft 46 kB/s | 2.1 kB 00:00
Red Hat Enterprise Linux 8 for x86_64 - AppStream (RPMs) 63 kB/s | 2.8 kB 00:00
Red Hat Satellite Tools 6.8 for RHEL 8 x86_64 (RPMs) 50 kB/s | 2.1 kB 00:00
Red Hat Enterprise Linux 8 for x86_64 - BaseOS (RPMs) 59 kB/s | 2.4 kB 00:00
Package libiodbc-devel-3.52.13-1.el8.x86_64 is already installed.

I have already installed libiodbc-dev package.
Also, I have odbc.ini and odbcinst.ini files available in the /etc/ directory which is correctly configured but still getting same error.

Below is the logs post execution of perl Makefile.PL

_**********
Remember to actually READ the README file!
And re-read it if you have any problems.

---

OSNAME: linux
LANG: en_US.UTF-8
ODBCHOME: /usr/bin
LD_LIBRARY_PATH:
DBROOT:
WINDIR:
II_SYSTEM:
DBD_ODBC_UNICODE:
Perl: 5.026003
ExtUtils::MakeMaker: 7.34
Command line options:
w! = undef
u! = undef
e! = undef
x! = undef
g! = 0
o=s =

Your LANG environment variable is set to "en_US.UTF-8"
This is known to cause problems in some perl installations - even stopping
this Makefile.PL from running without errors. If you have problems please
try re-running with LANG unset or with the utf part of LANG removed.

You are using a Perl configured with threading enabled.
Please read the warnings in DBI about this.

You should also be aware that on non-Windows platforms ODBC drivers come
in two forms, thread-safe and non-thread-safe drivers and you may need
to make sure you are using the right one.

Press return to continue...
Looking for odbc_config in : /usr/bin
Looking for odbc_config at /usr/bin/bin/odbc_config
Looking for odbc_config in (PATH) /sbin:/bin:/usr/sbin:/usr/bin
odbc_config not found
Looking for iodbc-config in PATH /sbin:/bin:/usr/sbin:/usr/bin
Found iodbc-config (via iodbc-config) version 3.52.13

ODBC data sources should be added to /etc/odbc.ini

ODBC drivers should be added to /etc/odbcinst.ini

iodbc-config reports --prefix=/usr
but cannot find header files sql.h,sqlext.h,sqltypes.h in that path so ignoring
NOTE: Have you installed the libiodbc-dev package.
odbc_config not found - ok, there are other things I can do
Still trying to guess ODBCHOME - looking for headers now
trying /usr/bin/include
trying /usr/include
trying /usr/local/include
trying /usr/pkg/include
trying /usr/local/easysoft/unixODBC/include

I cannot find driver manager header files. Perhaps you need to install the
unixodbc-dev package or the iodbc-dev package

The DBD::ODBC module needs to link with an ODBC 'Driver Manager'.
(The Driver Manager, in turn, needs one or more database specific ODBC
drivers. The DBD::ODBC module does not include any ODBC drivers!)

You need to indicate where your ODBC Driver Manager is installed.
You can do this by:

o setting the ODBCHOME environment variable
o running 'perl Makefile.PL -o odbcdir'
o adding path to odbc_config/iodbc_config to PATH

If you do not have an ODBC Driver Manager you should try to get hold of
the unixODBC packages for your system or build it from source (see
http://www.unixodbc.org). If you install driver manager packages you
need to include the "XXX-dev" package which includes the C header files._

[theory]

Did you try setting the ODBCHOME environment variable or passing the -o option as described toward the end there? It's not finding the headers it needs.