Credentials for AWS Athena ODBC connection

2
votes

I want to access AWS Athena in Power BI with ODBC. I used the ODBC driver(1.0.3) that Amazon provides: https://docs.aws.amazon.com/de_de/athena/latest/ug/connect-with-odbc.html To access the AWS-Service I use the user=YYY and the password=XXX. To access the relevant data our administrator created a role “ExternalAthenaAccessRole@99999”. 99999 is the ID of the account where Athena runs.

To use the ODVC-driver in Power BI I created the following connection string:

Driver=Simba Athena ODBC Driver;AwsRegion=eu-central-1;S3OutputLocation=s3://query-results-bucket/testfolder;AuthenticationType=IAM Credentials;

But when I enter the User XXX with the password YYY It get the message “We couldn’t authenticate with the credentials provided. Please try again.”.

Normally I would think that I must include the role “ExternalAthenaAccessRole@99999” in the connection string, but I couldn’t find a parameter for it in the documentation. https://s3.amazonaws.com/athena-downloads/drivers/ODBC/SimbaAthenaODBC_1.0.3/Simba+Athena+ODBC+Install+and+Configuration+Guide.pdf

Can anybody help me how I can change the connection string so that I can access the data with the ODBC driver in Power BI?

1
odbcpowerbiamazon-athena
When you select AuthenticationType=IAM Credentials you need to pass Access key ID as username and secret access key as password.These keys has to be generated for role ExternalAthenaAccessRole@99999 and to be passed in connection string.If you don't know how to generate them check out this link aws.amazo n.com/premiumsupport/knowledge-center/create-access-key/ - Prabhakar Reddy
Thank you! Our administrator said, that the keys can just be created for user (yyy) and not for the role (ExternalAthenaAccessRole@99999). When I use the keys for the user (yyy) I have no access: User: arn:aws:iam::88888888:user/yyy is not authorized. I tried to use the parameter “Preferred_Role=(ExternalAthenaAccessRole@99999” and “Preferred_Role=arn:aws:iam::99999:role/ExternalAthenaAccessRole” but it also did not work, and I got the same message than before. When I use the role direct in the Athena-Service than everything works fine. Do you you have any ideas? - HEWI

1 Answers

3
votes

TL;DR; When using Secret Keys, do not specify "User / password", but instead always click on "default credentials" in Power Bi, to force it to use the Local AWS Configuration (e.g. C:/...$USER_HOME/.aws/credentials)

Summarized Guide for newbies:

Prerequisites: AWSCli installed locally, on your laptop. If you don’t have this, just download the MSI installer from here: https://docs.aws.amazon.com/cli/latest/userguide/install-windows.html

Note: this quick guide is just to configure the connection using AWS Access Keys, and not federating the credentials through any other Security layer.

  1. Configure locally your AWS credentials.
    • From the Windows command prompt (cmd), execute: aws configure
    • Enter your AWS Access Key ID, Secret Access Key and default region; for example "eu-west-1" for Ireland.
    • You can get these Keys from the AWS console, IAM service, Users, select your user, Security, Create/Download Access Keys.
    • You should never share these keys, and it’s highly recommended to rotate these, for example, every month.

  2. Download Athena ODBC Driver:

    • https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html Important: If you have Power Bi 64 bits, download the same (32 or 64) for the ODBC.
    • Install it on your laptop, where you have Power Bi.
    • Open Windows ODBCs, add a User DSN and select Simba-Athena as the Driver.
    • Use always "Default credentials" and not user/password, since it will use our local keys from Step 1.
    • Configure an S3 bucket, for the temporary results. You can use something like: s3://aws-athena-query-results-eu-west-1-power-bi

  3. On the Power Bi app, click on Get Data and Type ODBC.

  4. Choose Credentials "default", to use the local AWS keys (from step 1) and, optionally, enter a "select" query.
  5. Click on Load the data.
    • Important concern: I’m afraid Power Bi will load all the results from the query into our local memory. So if, for example. we're bringing 3 months of data and that is equivalent to 3 GB, then we will consume this in our local laptop.

Another important concern: - For security reasons, you'll need to implement a KMS Encryption keys. Otherwise, the data is being transmitted in clear text, instead of being encrypted.

Relevant reference (as listed above), where you can find the steps for this entire configuration process, but more in detail: - https://s3.amazonaws.com/athena-downloads/drivers/ODBC/Simba+Athena+ODBC+Install+and+Configuration+Guide.pdf

Carlos.