Connecting to Snowflake using Key-Pair Authentication in Azure Data Factory

[Guest sgsshankar]

Azure Data Factory is one of the widely used Data Integration tools for Snowflake and is used to copy and transform data. It integrates with Snowflake natively as a Source and as a Sink. As of this date, Data Factory supports secure authentication using a Snowflake account through passwords. Azure Key Vault can be utilized for managing the passwords in Data Factory.

 

 

 

This article describes the workaround method for connecting to Snowflake from Azure Data Factory using Key-pair Authentication. It uses Snowflake's ODBC driver with self-hosted integration runtime to connect via Key-pair authentication.

 

 

 

• Setting up Self-hosted Integration Runtime. The ODBC connection can be setup only with Self-hosted integration Runtime. It is recommended to install the integration runtime in the region closest to Snowflake or use private link for better performance. For installing self-hosted integration runtime, please follow Create and configure a self-hosted integration runtime by Microsoft.
  
• Setting up Key Pair for Snowflake User Account. The Snowflake user account should have a key pair authentication setup. The private key is required for authentication into Snowflake via the ODBC driver. For setting up Key pair authentication, please refer to Snowflake Documentation Key Pair Authentication & Key Pair Rotation.
  
• Setting up Snowflake ODBC Driver. The Snowflake ODBC driver needs to be installed on the Integration Runtime machine. If there are multiple nodes, then it must be installed and configured on all the nodes. The latest 64-bit version of Snowflake ODBC driver for Windows can be downloaded from Snowflake Repo. The installation instructions can be found at Installing and Configuring the ODBC Driver for Windows.
  
• Setting up ODBC Driver for Key Pair Authentication. The ODBC driver should be configured with Key pair authentication. The DSN need to be created and the Snowflake details must be configured in the DSN. The Authenticator should be set as SNOWFLAKE_JWT for the Keypair authentication support
  

 

 

 

 

The additional connection parameters PRIV_KEY_FILE and PRIV_KEY_FILE_PWD for the ODBC driver needs to be updated in the registry under the path HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN_NAME> for system DSN.

 

For more information on setting the Parameters in Windows, please refer to the Setting Parameters in Windows from Snowflake User Guide. For additional information on ODBC connection Parameters, please refer to Snowflake’s User Guide.

 

 

 

The ODBC Linked Services can be created with the System DSN and can be utilized by the Datasets. For more information on Snowflake with Azure Data Factory, please refer to Copy and transform data in Snowflake using Azure Data Factory or Azure Synapse Analytics.

 

 

 

The ODBC connector does not support all the features of the native Snowflake connector. The recommendation is to leverage native Snowflake connector in Data Factory.

 

Continue reading...