ODBC connection string to Snowflake for Access Pass Thru Query

Question

I am trying to create a connection string to get to Snowflake data from Access 2010 and above. I can create a database DSN and link to the tables I use, but I need to build DSN-less connection strings for distributed applications. Here's what I have so far, it fails with the message "ODBC connection to xxxx failed". Here's what I have so far:

ODBC;Driver={SnowflakeDSIIDriver}; Server=https://server name; Role=role name;Warehouse=warehouse name;Database=db name;Schema=schema name;UID=snowflake ID; PWD=snowflake password;

Server -- I think you may need to remove the "https://" piece. You can also test with a regular DSN first before making the connection string to make sure all the entries work. — Suzy Lockwood
Thanks for the reply, like I said, I can connect with a DSN - defined in Access in External Data - ODBC Database, I can attach to and query Snowflake tables. I don't want to distribute applications that require users to maintain DSN passwords, etc. I have tried without https:// in the server name, same error. It is trying to connect, it churns for 30 seconds or so. I have a feeling it might be how the Snowflake authentication is specified in the parameters. — BrianHtx
Understood. Hard to say what the issue is then; if the DSN is working then the same setup via connection string should behave similarly. This connection string should work (it looks right to me) with the default Snowflake authenticator, unless Access somehow does not support this. You could potentially test with another authenticator or another application. Most Driver Managers have tracing as well you can enable (or you can try looking at the logs generated from the ODBC driver for clues as well). community.snowflake.com/s/article/… — Suzy Lockwood

Steven Segal Steven Segal · Accepted Answer · 2020-07-18T05:02:13

I think you are on the right track. I have the same thing and it works.

ODBC;
driver={SnowflakeDSIIDriver};
server=accountname.snowflakecomputing.com;database=dbname;
schema=public;
warehouse=whname;
role=rlname;
Uid=userid;
Pwd=password;

Very odd that the DSN one works and your doesn't.

ODBC connection string to Snowflake for Access Pass Thru Query

1 Answers