Connect Snowflake via ODBC#

This page shows how to connect Reportworq to Snowflake through the generic ODBC Provider datasource tile, using a native Snowflake ODBC driver and DSN.

The default, recommended way to connect Snowflake is the guided CData SnowFlake tile, which ships with Reportworq. It lets you fill in named fields (Warehouse, URL, Auth Scheme, Database, Schema) rather than hand-building a connection string, and supports key-pair authentication natively. See Connect Snowflake for that path.

Use the ODBC route described here when you prefer a native ODBC/DSN setup, or when an environment or driver requirement makes ODBC the better fit. It is a valid, supported alternative to the guided tile, not a temporary measure.

For the general add-and-test flow and how the ODBC Provider tile works, see Connect SQL Server, OLE DB, and ODBC.

Driver prerequisites#

Set these up first. Most failed Snowflake ODBC connections come down to one of them.

• Install the 64-bit Snowflake ODBC driver on the machine that runs the Reportworq Windows service. The Reportworq service is a 64-bit process, so it needs the 64-bit driver; a 32-bit driver is invisible to it. Get the driver from Snowflake's downloads.
• The Reportworq service account must be able to load the driver. Reportworq connects under its Windows service account, not your interactive login, so that account is the one that must see the driver and the DSN.
• For key-pair authentication, the service account must be able to read the private-key (.p8) file. Place the key where the service account has read access and reference that path.
• Open the network path to Snowflake. The Reportworq server must reach *.snowflakecomputing.com on port 443, through any corporate proxy or SSL-inspection layer in between.

Recommended: build a 64-bit System DSN first#

Create and test a System DSN before pointing Reportworq at Snowflake. This proves the driver, the key file, and the credentials all work before Reportworq uses them, and it spares you hand-crafting driver keywords.

1. On the Reportworq server, open the 64-bit ODBC Data Source Administrator: run odbcad32.exe (the 64-bit build under C:\Windows\System32\odbcad32.exe).

2. Go to the System DSN tab, not User DSN. The Reportworq service account must be able to see the DSN, and a User DSN belongs to one interactive user, so it is often invisible to the service.

3. Select Add, choose the SnowflakeDSIIDriver, and fill in the account, warehouse, database, schema, and authentication details.

4. Use the driver's built-in Test to confirm the DSN connects.

5. In Reportworq, add the ODBC Provider datasource tile and give it a minimal Connection String that points at the DSN:

   DSN=<YourSnowflakeDSN>;Uid=%USERNAME%;
   

6. Enter the Snowflake username in the masked %USERNAME% Variable field (see Credential variables below), save, and select Test Connection.

DSN-less fallback (ODBC Provider tile)#

If you cannot create a DSN, put a full driver connection string directly in the ODBC Provider tile's Connection String field. Use %USERNAME% and %PASSWORD% as placeholders and enter the real values in the tile's masked variable fields (see Credential variables).

Key-pair (JWT) authentication:

Driver={SnowflakeDSIIDriver};Server=<account>.snowflakecomputing.com;Uid=%USERNAME%;Authenticator=SNOWFLAKE_JWT;PRIV_KEY_FILE=<C:\path\rsa_key.p8>;PRIV_KEY_FILE_PWD=%PASSWORD%;Warehouse=<wh>;Database=<db>;Schema=<schema>;

Here %PASSWORD% holds the private-key file's password (the passphrase that protects the .p8 file), and PRIV_KEY_FILE is the path to that file on the Reportworq server. The service account must be able to read it.

Username and password authentication:

Driver={SnowflakeDSIIDriver};Server=<account>.snowflakecomputing.com;Uid=%USERNAME%;Pwd=%PASSWORD%;Warehouse=<wh>;Database=<db>;Schema=<schema>;

Credential variables#

The ODBC Provider tile keeps secrets out of the visible connection string. Put the placeholders %USERNAME% and %PASSWORD% in the connection string, then enter the real values in the tile's masked %USERNAME% Variable and %PASSWORD% Variable fields. Reportworq substitutes them at connect time and stores them encrypted, so the secrets never appear in the saved string. In the key-pair string above, %PASSWORD% maps to the private-key file's password.

Confirm these per environment#

Two values change from one Snowflake account to another and are the usual reason a copied connection string fails. Check both against a client that already connects successfully.

• The exact Server host. Azure-hosted Snowflake accounts often need a region segment, for example <account>.<region>.azure.snowflakecomputing.com. Use the host from your account's connection details, not a guess.
• The exact Driver={...} name. SnowflakeDSIIDriver is the typical name, but confirm it on the Drivers tab of odbcad32.exe and use the name exactly as listed there.

When to use the ODBC route#

The guided CData SnowFlake field editor is the default, recommended path: it ships with Reportworq, supports key-pair auth without hand-built connection strings, and needs no separate ODBC driver or DSN. See Connect Snowflake.

Choose the ODBC route on this page when a native ODBC/DSN setup suits your environment better, for example to reuse a Snowflake ODBC driver and System DSN you already standardize on, or when a driver or connectivity requirement makes ODBC the right fit. Both paths are supported and stay supported; pick the one that suits your environment.

Notes and limits#

• Only administrators can add or configure datasource connectors.
• The driver, the DSN, and the key file all live on the Reportworq server, under the service account, not on your workstation.
• Once the connection tests, query Snowflake in a data model through table or SQL mode, exactly as for any ODBC source. See Connect SQL Server, OLE DB, and ODBC.