Integrating Oracle Data Integrator (ODI) with Snowflake: Adapting to Authentication Changes

James Walsh -

Integrating Oracle Data Integrator (ODI) with Snowflake can unlock powerful data processing capabilities. However, with Snowflake's upcoming changes to service user authentication, adjustments are needed to stay ahead.

Understanding Snowflake's New User Types

Recently, Snowflake announced changes to how service users can connect to their accounts. These changes introduce three distinct user types:

  • Person: Represents a human user.
  • Service: Represents a connection from a program or service that supports OAuth or Private Key authentication.
  • Legacy Service: Represents a connection from a program or service that does not support OAuth or Private Key authentication.

You can find more details about these user types in Snowflake’s documentation.

Snowflake has also stated that in November 2025, all Legacy Service users will be migrated to the Service type. This effectively means that username and password authentication will no longer be available. For more information, check out Snowflake's announcement.

Adapting ODI to Private Key Authentication

At one of our clients, many service accounts had been created using usernames and passwords. Consequently, their user type was set to LEGACY_SERVICE. If you’re in a similar position, you might be wondering how to continue using ODI across all your database applications.

Fear not—ODI fully supports Private Key authentication. Here’s how you can make the switch.

Step 1: Generate a Private Key and Public Key

First, generate a private key and public key pair. The following Python script can help you get started:

from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives import serialization

def get_key_pair():
    private_key = rsa.generate_private_key(
        public_exponent=65537,
        key_size=4096
    )

    encrypted_pem_private_key = private_key.private_bytes(
        encoding=serialization.Encoding.PEM,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption()
    )

    pem_public_key = private_key.public_key().public_bytes(
        encoding=serialization.Encoding.PEM,
        format=serialization.PublicFormat.SubjectPublicKeyInfo
    )

    return encrypted_pem_private_key, pem_public_key

private_key, public_key = get_key_pair()

print(private_key.decode())

print(public_key.decode())

Step 2: Update Your Snowflake User

Once you have your public key, update your Snowflake user. Use the following SQL to set the public key and change the user type to SERVICE:

alter user MY_USER set RSA_PUBLIC_KEY='-----BEGIN PUBLIC KEY-----
Your public key...
-----END PUBLIC KEY-----';

alter user MY_USER set type = 'SERVICE';

Step 3: Configure ODI

Store the Private Key: Save the private key to a file on the ODI agent server.

mkdir -p /home/odiagent_user/keys/MY_SNOWFLAKE_SERVICE_USER/rsa_id.pem

Update the ODI Topology: Modify the JDBC URL for the Snowflake data server

jdbc:snowflake://account.identifier.snowflakecomputing.com/?warehouse=COMPUTE_WH&db=DATABASE&schema=DEFAULT_SCHEMA&role=DEFAULT_USER_ROLE&private_key_file=/home/odiagent_user/keys/MY_SNOWFLAKE_SERVICE_USER/rsa_id.pem

Test the Connection: Save the changes and test the connection to ensure everything is working correctly.

Conclusion

Switching ODI to Private Key authentication is straightforward and ensures compatibility with Snowflake’s upcoming changes. If you’re interested in using ODI with Snowflake, we can help you set up your topology and encapsulate data processing into knowledge modules to maximise the benefits of both tools. If you want to bullet proof your security we can show you how to implement key pair rotation. Reach out to us for assistance.