Before setting up the Snowflake connector, gather the following information:
The connector requires network access to Snowflake's services. These endpoints vary by account and region, so you need to retrieve your specific list.
Run this command in your Snowflake environment:
SELECT SYSTEM$ALLOWLIST();
This returns a JSON structure similar to:
[
{"host":"zg59224.us-east-2.aws.snowflakecomputing.com","port":443,"type":"SNOWFLAKE_DEPLOYMENT"},
{"host":"talactz-ds69451.snowflakecomputing.com","port":443,"type":"SNOWFLAKE_DEPLOYMENT_REGIONLESS"},
{"host":"sfc-repo.snowflakecomputing.com","port":443,"type":"SNOWSQL_REPO"},
{"host":"ocsp.rootg2.amazontrust.com","port":80,"type":"OCSP_RESPONDER"}
]
From the JSON response, extract the host and port values and format them as: <host>:<port>:TCP
Example:
{"host":"zg59224.us-east-2.aws.snowflakecomputing.com","port":443,"type":"SNOWFLAKE_DEPLOYMENT"}zg59224.us-east-2.aws.snowflakecomputing.com:443:TCPsnowflakecomputing.com:443:TCPsnowflakecomputing.com:443:TCP.
Use the following JSON structure for your connector configuration. Replace the highlighted placeholders with your specific values:
[
{
"auto.create": "true",
"auto.evolve": "true",
"auto.offset.reset": "earliest",
"batch.size": "500",
"confluent.custom.schema.registry.auto": "true",
"connection.password": "<your snowflake account password (4)>",
"connection.url": "<the snowflake JDBC(3)>",
"connection.user": "<your snowflake username (4)>",
"delete.enabled": "true",
"insert.mode": "upsert",
"key.converter": "io.confluent.connect.json.JsonSchemaConverter",
"offset.flush.interval.ms": "10000",
"pk.mode": "record_key",
"table.name.format": "${topic}",
"topics": "<the name of the topic (2)>",
"value.converter": "io.confluent.connect.json.JsonSchemaConverter"
}
]
Replace <the name of the topic (2)> with your actual topic name:
"topics": "<the name of the topic (2)>""topics": "shipping_info"Apply the same process to all other highlighted placeholders using the corresponding values from your prerequisites list.
Your Snowflake connector is now running and sending data from your topic to Snowflake tables.
For this configuration (apart from what you already have) you need:
To generate an unencrypted version, use the following command:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
This command will create a file called rsa_key.p8 in your chosen folder. The commands generate a private key in PEM format.
From the same directory where you created the private key, run:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
-----BEGIN PUBLIC KEY----- and footer -----END PUBLIC KEY-----ALTER USER <your_username> SET RSA_PUBLIC_KEY='<your_public_key_single_line>';
Check that the key was properly configured:
DESCRIBE USER <your_username>;
Look for the RSA_PUBLIC_KEY property in the results.
Modify your connector JSON to use key-pair authentication instead of password:
[
{
"auto.create": "true",
"auto.evolve": "true",
"auto.offset.reset": "earliest",
"batch.size": "500",
"confluent.custom.schema.registry.auto": "true",
"connection.url": "<the snowflake JDBC(3)>",
"connection.user": "<your snowflake username (4)>",
"connection.private_key": "<content_of_rsa_key.p8_file>",
"delete.enabled": "true",
"insert.mode": "upsert",
"key.converter": "io.confluent.connect.json.JsonSchemaConverter",
"offset.flush.interval.ms": "10000",
"pk.mode": "record_key",
"table.name.format": "${topic}",
"topics": "<the name of the topic (2)>",
"value.converter": "io.confluent.connect.json.JsonSchemaConverter"
}
]
connection.password with connection.private_key and paste the entire content of your rsa_key.p8 file (including the BEGIN and END lines).
You can test if the private key authentication is working by connecting to Snowflake using the Snowflake CLI:
snow sql --accountname <your_account> --username <your_username> --authenticator SNOWFLAKE_JWT --private-key-path rsa_key.p8 --query "SELECT current_user();"
If successful, this will return your username, confirming that the key-pair authentication is properly configured.