AWS Athena ODBC Authentication

Overview

Connecting Validatar to Athena involves two decisions, and they are easy to conflate:

1. Which host runs the connection — because that is where the ODBC driver must be installed.
2. How that host authenticates to AWS — which depends on whether it sits inside AWS.

Get the first one settled before the second. The rest of this article covers both, starting with the host.

Where the connection runs

The Athena ODBC driver executes on a specific machine, and that machine is where the driver has to be installed and where AWS credentials are resolved. It is not where Validatar itself is hosted, and it is not your browser.

Athena is reachable over the public internet, so any of these hosts can reach it given network access.

Validatar Cloud cannot run an Athena connection directly. The Cloud runtime does not package the Amazon Athena ODBC driver, and because it is a managed multi-tenant service you cannot install it there. If you are on Validatar Cloud, an Athena connection must go through a Data Agent, and the driver is installed on that agent host.

Validatar Server has both options. Because it is self-hosted, you can install the ODBC driver on the Validatar Server machine and connect straight from it — no agent required. Or you can keep using a Data Agent, which is the better choice when the agent already sits closer to your network boundary, when you want Athena traffic originating from a specific subnet or NAT address, or when the Validatar Server host is locked down against additional software.

Neither option changes the SQL, the template, or the connection string — only which machine needs the driver and the IAM permissions.

Note: A Data Agent is also the only way to run the connection from inside a specific AWS account or VPC, which is what makes the credential-free authentication in Approach 1 possible. If your goal is "no stored AWS keys", put the agent on EC2 or ECS.

Choosing an authentication approach

Once you know which host runs the connection, the authentication choice follows from one question:

Does that host live inside AWS?

Prefer the highest row you can satisfy. Every row down adds a long-lived secret that somebody has to rotate.

Installing the Athena ODBC driver

Download the current driver from AWS: Amazon Athena ODBC 2.x driver downloads. Packages are published for Windows, Linux and macOS; install it on whichever host you identified above.

On every platform the driver registers under the name Amazon Athena ODBC (x64), and that string must match the DRIVER= value in the connection string exactly.

Install the 2.x driver, not the older Simba-branded Athena 1.x connector. The connection parameters in this article are 2.x parameters; several were renamed between the two.

Windows

1. Download the AmazonAthenaODBC-2.x.y.z-windows-amd64.msi installer.
2. Run it with local administrator rights and accept the defaults.
3. Confirm it registered — open ODBC Data Sources (64-bit) and check the Drivers tab for Amazon Athena ODBC (x64).

Requires 64-bit Windows 10/11 or Windows Server 2016 or later, and the Microsoft Visual C++ Redistributable for Visual Studio 2022.

To verify from PowerShell without opening the GUI:

Get-OdbcDriver -Platform 64-bit | Where-Object { $_.Name -like "*Athena*" }

Installation requires elevation. A silent install (msiexec /i ... /qn) run from a non-elevated shell fails with Error 1925 — You do not have sufficient privileges, so run it from an elevated prompt or via your software-deployment tooling.

Upgrading from an earlier 2.x release? Starting at version 2.2.0.0 the Windows installer changed — uninstall the previous version first.

Linux

AWS ships the Linux driver as an RPM only, and officially supports:

• Amazon Linux 2023
• Red Hat Enterprise Linux 9 or later

It also requires unixODBC 2.3.9 or later and glibc 2.34 or later. There is no Debian or Ubuntu package — if your Validatar Server or Data Agent host runs Ubuntu, the practical options are to place the agent on a supported distribution or run it in a container built from one.

Install:

sudo rpm -Uvh AmazonAthenaODBC-2.x.y.z-x86_64.rpm

Verify:

rpm -qa | grep amazon-athena-odbc-driver

AWS also publishes a public key and signature so you can verify the RPM before installing, which is worth doing on a server host:

openssl dgst -sha256 -verify public_key.pem -signature signature.bin AmazonAthenaODBC-2.x.y.z-x86_64.rpm

The driver installs to /opt/amazon/athena-odbc/, with the shared library at /opt/amazon/athena-odbc/lib/libathenaodbc.so. Register it with unixODBC by adding it to odbcinst.ini — template files are provided under /opt/amazon/athena-odbc/share/athena-odbc/:

[ODBC Drivers]
Amazon Athena ODBC (x64)=Installed

[Amazon Athena ODBC (x64)]
Driver=/opt/amazon/athena-odbc/lib/libathenaodbc.so
Setup=/opt/amazon/athena-odbc/lib/libathenaodbc.so

If your odbcinst.ini is not in the default location, point unixODBC at it:

export ODBCSYSINI=/opt/amazon/athena-odbc/etc/athena-odbc

Confirm the driver manager sees it:

odbcinst -j

Make the registration visible to the right account. The driver must be registered for the account that runs the Validatar Server or Data Agent service, not just for your interactive login. If you set ODBCSYSINI in your own shell profile, the service will not inherit it — set it in the service unit or install odbcinst.ini at the system default path instead.

Approach 1: Instance Profile — the connection runs inside AWS

This is the right answer for production. Attach the IAM policy to the EC2 instance profile, ECS task role, or EKS service account that the host runs under — whether that host is a Data Agent or a Validatar Server instance deployed in AWS. The driver calls the instance metadata service directly. No key is ever created, stored, or rotated, and credentials expire automatically.

DRIVER={Amazon Athena ODBC (x64)};
AwsRegion=us-east-1;
S3OutputLocation=s3://my-bucket/athena-results/;
Catalog=AwsDataCatalog;
Schema=my_database;
Workgroup=my_workgroup;
AuthenticationType=Instance Profile

Note there are no credential fields at all. That is the entire point.

Approach 2: IAM Profile — a named AWS profile on the host

The middle option. The driver reads credentials from the host's AWS configuration files under a named profile, so nothing secret is stored in Validatar. It works outside AWS, which Instance Profile cannot.

DRIVER={Amazon Athena ODBC (x64)};
AwsRegion=us-east-1;
S3OutputLocation=s3://my-bucket/athena-results/;
Catalog=AwsDataCatalog;
Schema=my_database;
Workgroup=my_workgroup;
AuthenticationType=IAM Profile;
AWSProfile=my-profile-name

The profile itself decides where credentials actually come from, via credential_source in ~/.aws/config:

• Ec2InstanceMetadata — the EC2 instance role
• EcsContainer — the ECS task role endpoint
• Environment — AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY / AWS_SESSION_TOKEN

You can also chain to an assumed role with preferred_role (a role ARN) and duration (session seconds, default 900).

Important — service accounts. The driver runs inside a service, not your login session, so the profile has to be readable by the service account.

On Windows the Data Agent runs as LocalSystem by default, and Validatar Server likewise runs under a service account. LocalSystem does not see the interactive user's %USERPROFILE%\.aws, so a profile you created at a command prompt is invisible to the driver and you get No AWS profile found. Either place the profile where the service account reads it — C:\Windows\System32\config\systemprofile\.aws\ — or run the service as a dedicated user whose profile you control.

On Linux, put the profile in the HOME of the service user and confirm the unit actually sets that HOME. A systemd service with no HOME will not find ~/.aws at all.

Be aware of the trade-off in the LocalSystem workaround: credentials placed in the system profile are readable by anything running as SYSTEM on that machine, which erodes the "no stored secret" benefit this approach exists for. Running the service as a dedicated account is the cleaner answer.

Approach 3: IAM Credentials — an explicit access key

Use this when the host runs outside AWS and you cannot control the service account. The key and secret go into the Validatar connection configuration, where they are stored encrypted. This is the most common starting point for Validatar Cloud customers, since the Data Agent typically sits on-premises.

DRIVER={Amazon Athena ODBC (x64)};
AwsRegion=us-east-1;
S3OutputLocation=s3://my-bucket/athena-results/;
Catalog=AwsDataCatalog;
Schema=my_database;
Workgroup=my_workgroup;
AuthenticationType=IAM Credentials;
UID=AKIAIOSFODNN7EXAMPLE;
PWD=your-secret-access-key

For temporary credentials, add SessionToken=.... Create a dedicated IAM user for this — never reuse an administrator's key. The policy below is the complete set of permissions required.

Connection parameters

IAM policy

This is the complete runtime policy. Substitute your region, account, workgroup, database, and bucket.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AthenaWorkgroupQueryExecution",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:StopQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults",
        "athena:GetQueryResultsStream",
        "athena:GetWorkGroup"
      ],
      "Resource": "arn:aws:athena:REGION:ACCOUNT:workgroup/WORKGROUP"
    },
    {
      "Sid": "AthenaCatalogMetadataRead",
      "Effect": "Allow",
      "Action": [
        "athena:GetDataCatalog",
        "athena:GetDatabase",
        "athena:ListDatabases",
        "athena:GetTableMetadata",
        "athena:ListTableMetadata"
      ],
      "Resource": "arn:aws:athena:REGION:ACCOUNT:datacatalog/AwsDataCatalog"
    },
    {
      "Sid": "AthenaListingRequiresWildcard",
      "Effect": "Allow",
      "Action": ["athena:ListWorkGroups", "athena:ListDataCatalogs"],
      "Resource": "*"
    },
    {
      "Sid": "GlueCatalogRead",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase", "glue:GetDatabases",
        "glue:GetTable", "glue:GetTables",
        "glue:GetPartition", "glue:GetPartitions"
      ],
      "Resource": [
        "arn:aws:glue:REGION:ACCOUNT:catalog",
        "arn:aws:glue:REGION:ACCOUNT:database/DATABASE",
        "arn:aws:glue:REGION:ACCOUNT:table/DATABASE/*"
      ]
    },
    {
      "Sid": "S3ReadSourceData",
      "Effect": "Allow",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::BUCKET/data/*"
    },
    {
      "Sid": "S3WriteQueryResults",
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:PutObject", "s3:AbortMultipartUpload"],
      "Resource": "arn:aws:s3:::BUCKET/athena-results/*"
    },
    {
      "Sid": "S3BucketLevel",
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetBucketLocation", "s3:ListBucketMultipartUploads"],
      "Resource": "arn:aws:s3:::BUCKET"
    }
  ]
}

athena:GetQueryResultsStream

This action is required and is easy to miss. It has no corresponding public API call, so it does not appear in the Athena API reference and is absent from most example policies — it exists solely for the ODBC and JDBC drivers, which stream results rather than paging them.

Omit it and the failure is genuinely confusing: the connection test passes, metadata queries succeed, and then result retrieval fails with an opaque error that does not mention permissions.

Use a dedicated workgroup

The policy above scopes query execution to one workgroup rather than primary. That is worth doing:

• The identity cannot run queries under any other workgroup.
• You can set BytesScannedCutoffPerQuery on the workgroup. Athena bills per terabyte scanned, and a per-query scan cap is the cheapest possible protection against a SELECT * against a large partitioned table.
• You can enforce the result location at workgroup level, so a misconfigured client cannot write results somewhere unexpected.

Network

Outbound TCP port 444 must be open from the host running the driver. Athena streams query results over port 444, separately from the HTTPS API on 443. If you connect through a PrivateLink endpoint, the endpoint's security group needs inbound 444.

Because the connection test only calls GetWorkGroup over HTTPS, a blocked port 444 produces a connection that tests successfully and then hangs or times out on the first real query. On a corporate network this is the most common failure.

Troubleshooting

Note: The driver's connection test calls GetWorkGroup. It is free, and it writes nothing to your results bucket — but for the same reason it validates far less than a real query does. A passing test means credentials and region resolved, not that the connection works end to end.

Related

• AWS Athena Data Source Template

External:

• Amazon Athena ODBC 2.x driver downloads — Windows, Linux and macOS packages
• Windows installation and DSN configuration
• Linux installation and unixODBC configuration
• Athena ODBC 2.x connection parameters