Sqitch does not find DBD::ODBC after proper (?) install - DBD::ODBC 1.59 required to manage Snowflake

[dlawrences]

Hey @theory

I know #26 talks about this as well, but I wanted to create a new thread because I believe we should come up with an in-depth list of steps of properly configuring all dependencies & sqitch to work. I am looking at using sqitch instead of Flyway/snowchange for my work and my teams's work on Snowflake, but I am having difficulties in setting this up properly.

1. OS: Mac OS X
2. Using: Docker container as detailed here

That is pushed to a bin directory that is available in the $PATH.

3. Have installed & configured properly ODBC for Snowflake as detailed here

I have also installed libiodbc via homebrew to allow for the header and/or config files to be identified by DBD::ODBC.

4. Installing DBI & DBD::ODBC

This was the trickest part in a way, although the commands are fairly simple:

(base) laurentiudiaconu@Laurentius-MacBook-Pro flipr % perl -MCPAN -e shell
Terminal does not support AddHistory.

To fix that, maybe try>  install Term::ReadLine::Perl

cpan shell -- CPAN exploration and modules installation (v2.27)
Enter 'h' for help.

cpan[1]> install DBI
...
DBI is up to date (1.643).
cpan[2]> install MJEVANS/DBD-ODBC-1.59.tar.gz
...
**********
    Remember to actually *READ* the README file!
    And re-read it if you have any problems.

**********

OSNAME: darwin
LANG: 
ODBCHOME: 
LD_LIBRARY_PATH: 
DBROOT: 
WINDIR: 
II_SYSTEM: 
DBD_ODBC_UNICODE: 
Perl: 5.032001
ExtUtils::MakeMaker: 7.44
Command line options:
  e! = undef
  w! = undef
  x! = undef
  g! = 0
  u! = undef
  o=s = 

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 : nowhere
Looking for odbc_config in (PATH) ...
  odbc_config not found
Looking for iodbc-config in PATH ...
  Found iodbc-config (via iodbc-config) version 3.52.14

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

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

  iodbc-config reports --prefix=/usr/local/Cellar/libiodbc/3.52.14
  ODBC INC dir set to /usr/local/Cellar/libiodbc/3.52.14/include from iodbc-config
  ODBC LIB dir set to /usr/local/Cellar/libiodbc/3.52.14/lib from iodbc_config/lib
Using ODBCHOME /usr/local/Cellar/libiodbc/3.52.14

This looks like a iodbc type of driver manager.
Warning: LD_LIBRARY_PATH doesn't include /usr/local/Cellar/libiodbc/3.52.14/lib
Adding iodbc_config --cflags "-I/usr/local/Cellar/libiodbc/3.52.14/include" to CC line

Checking if your kit is complete...
Looks good
...
/usr/bin/make install  -- OK

I have removed local explicit paths in the output, but all looks good from an installation perspective.

5. Running a sqitch deploy

e.g.

sqitch deploy 'db:snowflake://myLoginName@mySnowflakeAccount/myDatabase?Driver=Snowflake'
DBD::ODBC 1.59 required to manage Snowflake

Even by installing the latest DBD::ODBC version through:

install DBD::ODBC

This still doesn't work. Any ideas why?

---

A related question is with regards to sqitch-authentication. The documentation says:

Snowflake does not support password-less authentication, but does support key-pair authentication.

Does this mean that sqitch does not support a connection with an authenticator of type externalbrowser (e.g. via Azure AD)?

I would really appreciate your thoughts on this one. I believe sqitch is a great tool, at least as per what I have been able to see from docs and tutorials, and I am really looking forward to having all the team use this on a day by day basis.

Thank you

[dlawrences]

In the meantime, I have been able to apparently circumvent this issue by installing sqitch via homebrew i.e.

brew install sqitch --with-snowflake-support --without-libiodbc

I have also been able to pass the authenticator based parameters to the sqitch deploy command i.e.

sqitch deploy 'db:snowflake://mySnowflakeAccount/myDatabase?Driver=Snowflake;warehouse=myWarehouse;authenticator=externalbrowser;uid=myLoginName'
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...
Adding registry tables to db:snowflake://mySnowflakeAccount/myDatabase?Driver=Snowflake;warehouse=myWarehouse;authenticator=externalbrowser;uid=myLoginName
251006 (n/a): Password is empty
"/Applications/SnowSQL.app/Contents/MacOS/snowsql" unexpectedly returned exit value 5

I am still facing the last issue in the terminal and will continue debugging and logging here.

[dlawrences]

I have been able to circumvent the authenticator based authentication by using Private Keys as detailed here.

SnowSQL works just fine connecting via the private key i.e.

snowsql -a $ACCOUNT_NAME -u $USERNAME -d $DATABASE_NAME -w $WAREHOUSE_NAME -r $DEFAULT_ROLE --private-key-path path/to/private_key.p8

However, sqitch still has an issue with the same arguments:

sqitch deploy 'db:snowflake://mySnowflakeAccount/myDatabase?Driver=Snowflake;warehouse=myWarehouse;authenticator=SNOWFLAKE_JWT;uid=myUsername;priv_key_file=path/to/private_key.p8;priv_key_file_pwd=privateKeyPwd'
...
251006 (n/a): Password is empty
"/Applications/SnowSQL.app/Contents/MacOS/snowsql" unexpectedly returned exit value 5

Setting some arbitrary password in ~/.snowsql/config doesn't help as well:

250001 (08001): Failed to connect to DB: HJ27726.eu-central-1.snowflakecomputing
"/Applications/SnowSQL.app/Contents/MacOS/snowsql" unexpectedly returned exit value 3

[dlawrences]

It seems externalbrowser authentication actually works, you just have to duplicate the settings in the SnowSQL config i.e.

[connections]
accountname = accountName
region = accountRegion
username = loginName
dbname = databaseName
warehousename = defaultWarehouseName
authenticator = externalbrowser

And the deployment command now works as:

sqitch deploy 'db:snowflake://accountName.accountRegion.snowflakecomputing.com/databaseName?Driver=Snowflake;authenticator=externalbrowser;uid=loginName'

What's weird is the sqitch status targetName command does not seems to read the same SnowSQL variables:

sqitch status targetName
# On database targetName
[Snowflake][DSI] (20032) Required setting 'PWD' is not present in the connection settings. (SQL-28000)

[dlawrences]

Updates - I know realised the image available on Docker Hub is not built for Snowflake and one has to build separately. Apologies :)