Connecting to Amazon Athena via ODBC and Python: Handling SSO and Temporary Credentials

Connecting to Amazon Athena from a client application or script can be straightforward — until you throw SSO authentication, cross-region access, and temporary credentials into the mix. This guide walks through how to configure your Athena ODBC connection, troubleshoot common issues, and query Athena from Python.

---

Understanding the Setup

Many organizations use AWS SSO to manage access to multiple accounts and regions. In this scenario:

• Your SSO portal may reside in one region (e.g., us-west-2).
• Athena and the associated data reside in another region (e.g., us-east-2).
• Temporary credentials are issued via SSO and expire periodically.

These details affect how you configure the Athena ODBC driver or any client tool.

---

Step 1: Choose the Right Athena Client Tool

Several SQL clients support Athena:

• DBeaver – Free, cross-platform, supports Athena via JDBC.
• PopSQL – Web-based, collaborative SQL editor.
• SQL Workbench/J – Lightweight, DBMS-independent, supports Athena via JDBC.
• Aqua Data Studio – Advanced client with visualization and reporting tools.

For Windows users, SQL Workbench/J is a common choice.

---

Step 2: Configure the Athena ODBC Driver

1. Install the 64-bit Athena ODBC driver from AWS.
2. Open ODBC Data Source Administrator (64-bit).
3. Create or edit a DSN and configure:
   • AWS Region → Region where Athena lives (us-east-2).
   • S3 Output Location → An S3 bucket your Athena role can write to.
   • Authentication Type → IAM Profile or IAM Credentials.
   • AWS Profile Name → Name of your AWS CLI profile with SSO credentials.

---

Key Notes for Windows ODBC:

• Even if the ODBC GUI shows only Username and Password, credentials are passed via the JDBC/ODBC URL or driver settings, not these fields.
• Enable logging in the driver for debugging:
  1. DSN → Configure → Advanced Options → Enable Logging, Log Level = Trace.
  2. Logs are written to %LOCALAPPDATA%\Amazon\AthenaODBC\.

---

Step 3: Handling Temporary SSO Credentials

SSO credentials are temporary and need to be refreshed periodically. To use them with Athena ODBC:

1. Run a fresh login:
2. aws sso login –profile dev
3. Use your CLI profile in the ODBC driver configuration or extract temporary credentials for IAM Credentials authentication.

Example JDBC URL using profile:

jdbc:awsathena://AwsRegion=us-east-2;

S3OutputLocation=s3://my-athena-results-bucket/;

AwsCredentialsProviderClass=com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider;

AwsProfile=dev;

Example JDBC URL using temporary credentials:

jdbc:awsathena://AwsRegion=us-east-2;

S3OutputLocation=s3://my-athena-results-bucket/;

AccessKey=ASIA…;

SecretKey=abcd…;

SessionToken=IQoJb3…;

⚠️ Make sure your SSO session is valid and your credentials haven’t expired.

---

Step 4: Troubleshooting Common Errors

Error	Cause	Fix
MissingAuthenticationTokenException	Driver didn’t send credentials	Ensure SSO session is valid, use profile or temporary credentials in URL
Connection fails to Athena endpoint	Firewall or port 444 blocked	Open outbound port 444 to athena.<region>.amazonaws.com
S3 write errors	Output bucket in wrong region	Ensure S3 bucket is in same region as Athena

Tip: Trace-level logging in the Athena ODBC driver can pinpoint whether the driver is using the correct profile, region, and credentials.

---

Step 5: Query Athena via Python using ODBC

Once the DSN is configured, you can query Athena from Python with pyodbc:

import pyodbc

 

DSN = “Athena64”  # Replace with your DSN name

QUERY = “SELECT current_date;”

 

try:

conn = pyodbc.connect(f”DSN={DSN};”, autocommit=True)

cursor = conn.cursor()

cursor.execute(QUERY)

 

# Print results

columns = [desc[0] for desc in cursor.description]

print(” | “.join(columns))

for row in cursor.fetchall():

print(” | “.join(str(x) for x in row))

 

finally:

cursor.close()

conn.close()

You don’t need to specify username/password here; credentials are handled by the ODBC driver from the DSN.

---

Step 6: Optional – Automate SSO Credential Refresh

For temporary SSO credentials, you can use a PowerShell script to refresh the session and update the ODBC DSN automatically. This avoids manual copy-pasting of credentials every hour.

---

Summary

1. Ensure your DSN uses the correct region and S3 bucket.
2. Use AWS CLI profiles or temporary credentials for authentication.
3. Enable ODBC logging for troubleshooting.
4. Use Python pyodbc or a SQL client to query Athena.
5. Refresh SSO credentials before they expire to avoid authentication errors.

With this setup, users can connect to Athena securely via ODBC, Python, or SQL clients, even in multi-account or multi-region AWS environments.