ODBC Driver for Snowflake

Build 20.0.7606

macOS DSN Configuration

This section shows how to set up ODBC connectivity and configure DSNs on macOS.

Minimum macOS Version

The CData ODBC Driver for Snowflake driver requires macOS Sierra (10.12) or above.

Licensing the Driver

In a terminal, run the following commands to license the driver. To activate a trial, omit the <key> input.

cd "/Applications/CData ODBC Driver for Snowflake/bin"
sudo ./install-license <key>

You'll be prompted for a name and password. These refer to your name and your machine's password.

Connecting through a Driver Manager

On macOS, the CData ODBC Driver for Snowflake is preconfigured for use with the iODBC driver manager, as are many other products like Filemaker Pro, Microsoft Excel, and Tableau. You can find the latest version of iODBC on the iODBC site.

The driver installation registers the driver with iODBC and creates a system DSN, which you can use in any tools or applications that support ODBC connectivity.

The driver manager loads the driver and passes function calls from the application to the driver. The driver must be registered with the driver manager and DSNs are defined in the driver manager's configuration files.

Configuring DSNs

To configure a DSN, you can use the iODBC Administrator 64-bit, the GUI installed with iODBC. Note that the ODBC Manager must match the bitness of the ODBC driver. The most recent version of the CData ODBC Driver for Snowflake is 64-bit only. Alternatively, you can edit the iODBC configuration files.

You can configure User or System DSNs. User data sources are restricted to a user account. System data sources can be accessed by all users.

Configuring a DSN with the iODBC Administrator

You can create user DSNs by opening the iODBC Administrator 64-bit from Launchpad.

To modify the system DSN installed by the driver or create a system DSN, open the iODBC Administrator 64-bit with elevated permissions. To do so, enter the following command into a terminal:

sudo /Applications/iODBC/iODBC\ Administrator64.app/Contents/MacOS/iODBC\ Administrator64
After opening the iODBC Administrator 64-bit, you will see the CData Snowflake Source listed under the System tab. Select the DSN and click the Configure button to set connection properties as name-value pairs.

To create your own DSN, instead click Add on the User or System tab and then select the CData ODBC Driver for Snowflake option.

Connecting to Snowflake

In addition to providing authentication (see below) set the following properties to connect to a Snowflake database:

  • Url: Both AWS and Azure instances are supported. For example:
    • AWS: https://myaccount.region.snowflakecomputing.com
    • Azure: https://myaccount.region.azure.snowflakecomputing.com
  • Warehouse

Account is only required if your Url does not conform to the usual syntax containing the account name at the beginning. Snowflake provides the Account name needed in this case.

Optionally, you can set Database and Schema to restrict the tables and views returned by the driver.

Authenticating to Snowflake

The driver supports Snowflake user authentication, federated authentication, and SSL client authentication. To authenticate, set User and Password, and select the authentication method in the AuthScheme property.

Using Snowflake Password Authentication

Set User and Password to a Snowflake user and set AuthScheme to PASSWORD.

Using Snowflake Key Pair Authentication

The driver allows you to authenticate using key pair authentication by creating a secure token with the private key defined for your user account. To connect with this method, set AuthScheme to PRIVATEKEY and set the following values:

  • User: The user account to authenticate as.
  • PrivateKey: The private key used for the user such as the path to the .pem file containing the private key.
  • PrivateKeyType: The type of key store containing the private key such as PEMKEY_FILE, PFXFILE, etc.
  • PrivateKeyPassword: The password for the specified private key.

Using Federated Authentication

To use federated authentication, set the User and Password that you need to authenticate to your SSO identity provider and set the following properties to configure the authentication scheme.

Set AuthScheme based on your IdP (currently, the driver supports Okta only).

  • OKTA: Set AuthScheme to OKTA and set SSOIDPDomain to the Okta SAML endpoint. For example: https://cdata-okta.okta.com.
  • AZUREAD: Set AuthScheme to AZUREAD and set User to your AD user. When connecting, your browser will open allowing you to login to Azure AD to complete the authentication.

Using SSL Client Authentication

To authenticate with an SSL client certificate, set SSLClientCert, SSLClientCertPassword, SSLClientCertSubject, SSLClientCertType, and SSLServerCert.

Configuring Access Control

If the authenticating user maps to a system-defined role, specify it in the RoleName property.

Configuring a DSN in the iODBC INI Files

Configure DSNs in odbc.ini. Register ODBC drivers in odbcinst.ini.

odbc.ini

Define ODBC data sources in sections in the odbc.ini file. User data sources can only be accessed by the user account whose home folder the odbc.ini is located in. System data sources can be accessed by all users.

PrivilegesPath
User/Users/myuser/Library/ODBC/odbc.ini
System/Library/ODBC/odbc.ini

Modifying iODBC's system-wide settings requires elevated permissions; to do so, you can use the sudo command to open a text editor from the terminal. For example:

sudo nano /Library/ODBC/odbc.ini

In addition to the connection properties required to connect to your data source, the Driver property specifies either a driver definition in the odbcinst.ini file or the path to the driver library.

[CData Snowflake Source]
Driver = CData ODBC Driver for Snowflake
url=https://myaccount.region.snowflakecomputing.com
user=Admin
password=test123
Database=Northwind
Warehouse=TestWarehouse
Account=Tester1

Additionally, in the ODBC Data Sources section, the DSN must be set to a driver defined in the odbcinst.ini file. For example, below is the entry for the DSN created during the driver install:

[ODBC Data Sources]
CData Snowflake Source = CData ODBC Driver for Snowflake

odbcinst.ini

You may need to modify the installed driver definition if you change the path to the driver library.

To register an ODBC driver, modify the odbcinst.ini file. With iODBC, drivers can be available to only one user account or drivers can be available system wide.

PrivilegesPath
User/Users/myuser/Library/ODBC/odbcinst.ini
System/Library/ODBC/odbcinst.ini

Drivers are defined in sections in the odbcinst.ini file. The section name specifies the name of the driver. In this section, the Driver property specifies the path to the driver library. The driver library is the .dylib file located in the lib subfolder of the installation directory, by default in /Applications/CData ODBC Driver for Snowflake.

[CData ODBC Driver for Snowflake]
Driver = /Applications/CData ODBC Driver for Snowflake/lib/libsnowflakeodbc.dylib

The ODBC Drivers section must also contain a property with the driver name, set to "Installed". For example:

[ODBC Drivers]
CData ODBC Driver for Snowflake = Installed

Testing the Connection

You can use the iODBC Demo, available in most iODBC installations, to connect to Snowflake and execute SQL queries.

iODBC Demo

Complete the following steps to connect from the iODBC Demo:

  • Open Launchpad and search for "iODBC".
  • If you need to connect to Snowflake from an application that can use only the ANSI ODBC API, click iODBC Demo Ansi. Otherwise, click iODBC Demo Unicode.
  • In the Environment menu, click Open Connection.
  • Select the DSN on the corresponding tab and test the connection.
You can now execute SQL statements to Snowflake by clicking Execute SQL in the SQL menu.

Set the Driver Encoding

The ODBC drivers need to specify which encoding to use with the ODBC Driver Manager. By default, the CData ODBC Drivers for Mac are configured to use UTF-32 which is compatible with iODBC, but other Driver Managers may require alternative encoding.

Alternatively, if you are using the ODBC driver from an application that uses the ANSI ODBC API it may be necessary to set the ANSI code page. For example, to import Japanese characters in an ANSI application, you can specify the code page in the config file '/Applications/CData ODBC Driver for Snowflake/lib/cdata.odbc.snowflake.ini':

[Driver]
AnsiCodePage = 932

Uninstalling the Driver

The easiest way to uninstall the driver is to open a terminal and run the included uninstall.sh script, located in the installation directory. For example:

cd "/Applications/CData ODBC Driver for Snowflake"
sudo ./uninstall.sh

Note: The script needs to be run from the installation directory.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7606