Migrating Snowflake Authentication from Password to Key Pair in AWS Glue

Secure authentication is a critical aspect of data integration workflows. When connecting AWS Glue to Snowflake, moving from password-based authentication to key pair authentication significantly enhances security by eliminating the need to store passwords in configuration files or environment variables.

Key pair authentication addresses these concerns by using public-private key cryptography, providing a more secure and manageable authentication method.

Step-by-Step Migration Process

1. Create a Key Pair for Authentication

First, generate the RSA key pair using OpenSSL:

# Generate private key in PKCS8 format
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Generate corresponding public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Please note that AWS Glue supports only unencrypted base64-encoded private key string. [+]

Please use below command to generate unencrypted private key from your encrypted private key if using encrypted private key:

# Generate unencrypted private key (rsa_key.p8) from encrypted private key (rsa_key_encrypted)
openssl rsa -in rsa_key_encrypted.p8 -out rsa_key.p8

2. Store Keys in AWS Secret Manager

The private key needs to be properly formatted for storage in Secret Manager:

# Convert private key to one line without header/footer
cat rsa_key.p8 | sed '1d;$d' | tr -d '\n' > rsa_key_oneline.p8

The output will be a single line string like:

MIIEvQIBADANBgkqhkiG9w0BAQE.................U=

Store this value in AWS Secret Manager with the following key structure:

• username/sfUser: example_user
• pem_private_key: MIIEvQIBADANBgkqhkiG9w0BAQE.................U=

3. Configure Snowflake for Key Pair Authentication

To enable key pair authentication in Snowflake, you need appropriate privileges:

• MODIFY PROGRAMMATIC AUTHENTICATION METHODS privilege
• OWNERSHIP privilege on the user
• SECURITYADMIN role or higher

Execute the following SQL command in Snowflake:

ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Note: Ensure the public key content is formatted as a single line without BEGIN/END headers.

4. Verify the Key Pair Configuration

To verify the configuration is correct, compare the key fingerprints:

In Snowflake, run:

DESC USER example_user;

SELECT SUBSTR(
  (SELECT "value" FROM $1 WHERE "property" = 'RSA_PUBLIC_KEY_FP'),
  LEN('SHA256:') + 1) AS key;

On your local machine, run:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

If both outputs match, the public key is correctly configured.

5. Create a Snowflake Data Connection in AWS Glue

Finally, create a connection in AWS Glue that uses the key pair authentication method:

1. Navigate to AWS Glue Console
2. Go to Data Connections and create a new Snowflake connection
3. Select "Key Pair Authentication" as the authentication method
4. Reference the Secret Manager secret containing your credentials
5. Create Connection, The system will verify the connection's validity during the setup process.

6. Creating a Glue Job to Read and Write in Snowflake Using Glue Snowflake Connection

In the following script, I extract data from the MENU table in Snowflake, then drop any existing MENU2 table and create a new one with the retrieved data.

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue import DynamicFrame

args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Script generated for node reading from Snowflake
Snowflake_node1757522758484 = glueContext.create_dynamic_frame.from_options(connection_type="snowflake", connection_options={"autopushdown": "on", "dbtable": "MENU", "connectionName": "Snowflake connection", "sfDatabase": "SNOWFLAKE_LEARNING_DB", "sfSchema": "LOAD_SAMPLE_DATA_FROM_S3"}, transformation_ctx="Snowflake_node1757522758484")

# Script generated for node writing to Snowflake
Snowflake_node1757522917853 = glueContext.write_dynamic_frame.from_options(frame=Snowflake_node1757522758484, connection_type="snowflake", connection_options={"autopushdown": "on", "dbtable": "MENU2", "connectionName": "Snowflake connection", "preactions": "DROP TABLE IF EXISTS LOAD_SAMPLE_DATA_FROM_S3.MENU2; CREATE TABLE IF NOT EXISTS LOAD_SAMPLE_DATA_FROM_S3.MENU2 (MENU_ID decimal, MENU_TYPE_ID decimal, MENU_TYPE string, TRUCK_BRAND_NAME string, MENU_ITEM_ID decimal, MENU_ITEM_NAME string, ITEM_CATEGORY string, ITEM_SUBCATEGORY string, COST_OF_GOODS_USD decimal, SALE_PRICE_USD decimal, MENU_ITEM_HEALTH_METRICS_OBJ string);", "sfDatabase": "SNOWFLAKE_LEARNING_DB", "sfSchema": "LOAD_SAMPLE_DATA_FROM_S3"}, transformation_ctx="Snowflake_node1757522917853")

job.commit()

Handling of data and target table

• APPEND (insert) to target table : AWS Glue will append data to existing columns of the table and discard any extra columns.
• MERGE data into target table : AWS Glue will either update or append data to the table based on a set of conditions.
• TRUNCATE target table : Same as Append, except AWS Glue will first clear the contents of the table.
• DROP and recreate target table : AWS Glue will delete and recreate the table with the schema from the source data.

Conclusion

By following the steps outlined in this article, you can enhance the security posture of your data integration workflows while maintaining seamless connectivity between AWS Glue and Snowflake. This migration represents a best practice for securing enterprise data pipelines and should be considered as part of your overall cloud security strategy.