Power BI (Azure-hosted SaaS service) to AWS S3 via Athena Connectivity

[GauravSinghPBI]

Hi Team,

We are trying to connect to AWS S3 Bucket via Athena from Power BI (Azure-hosted SaaS service). In a high level we have below requirements

• We will pull the data from AWS and populate the Power BI Semantic Layer Cache and Power BI reports will use the cached data for reporting
• The connection should use federated user identity which has username and password stored in cyber ark.
• Connection should also suffice the data orchestration. Whan data is changed in AWS, API call in Power BI should trigger the data refresh

Do we have any jdbc driver which is provided by AWS or Microsoft for the same. I know that that ODBC driver is a possibility but i think there are some challenges with it.

Thanks,

Gaurav

[v-sshirivolu]

Hi @GauravSinghPBI ,
Thanks for reaching to Microsoft Fabric Community Forum.
To connect Power BI (Azure SaaS) to AWS S3 data via Athena, while meeting requirements for semantic layer caching, secure credential storage (CyberArk), and automated refresh upon data changes, consider the following approach:

Leverage the Amazon Athena ODBC driver along with the On-premises Data Gateway (Enterprise mode) to enable supported connectivity from Power BI Service to Athena.

Configure the dataset in Import mode to utilize semantic caching and enhance report performance.

Integrate CyberArk for credential management by rotating and injecting credentials into the gateway service account using CyberArk automation or plugins.

Implement automated refresh by setting up an AWS Lambda trigger on S3 events, which invokes the Power BI REST API to refresh the dataset.

Optimize Athena costs by storing partitioned and compressed data in S3 and scheduling refreshes during off-peak hours.

This solution ensures a Microsoft-supported connectivity path, integrates with enterprise credential management, and enables automated refresh workflows using S3, Lambda, and the Power BI API.

[v-sshirivolu]

Hi @GauravSinghPBI ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[GauravSinghPBI]

Thanks @v-sshirivolu for the response. Still we have few outstanding doubts.

We have Athena setup with IAM not with IDC.

The challenge with IAM - if we have a service account setup on PBI Data Gateway to connect to Athena we would need to reset the password every 90 days. Is this correct and how to manage it.

If you refer to JDBC 3.x section, https://docs.aws.amazon.com/athena/latest/ug/jdbc-v3-driver-datazone-idc.html, we need this equivalent in ODBC in order to PBI to delegate the authorisation to ASUS/DZ.

Thanks,

Gaurav

[v-sshirivolu]

Hi @GauravSinghPBI ,

When configuring the Gateway with static IAM user credentials, these typically require rotation every 90 days, depending on your organization's policy. To address this:

Consider using STS temporary credentials via IAM roles or federation. These tokens are short-lived, refresh automatically, and eliminate the need for a 90-day reset.

If your security policy mandates credential rotation, integrating AWS Secrets Manager or CyberArk with the Gateway can automate and manage credential refresh.

Regarding IDC/DataZone: the JDBC v3 driver supports this natively, while the ODBC driver currently does not. As an alternative, you can configure ODBC with federated SSO (SAML/OAuth) to allow Power BI Gateway to delegate authentication without storing static passwords.

Yes, static IAM credentials are subject to a 90-day rotation.

To resolve this, prioritize STS temporary credentials and federation, or automate rotation using Secrets Manager or CyberArk.

For IDC/DataZone delegation, ODBC is not yet at feature parity, but federation is the recommended approach for now.

[GauravSinghPBI]

Thanks @v-sshirivolu 

One doubt On - "Regarding IDC/DataZone: the JDBC v3 driver supports this natively, while the ODBC driver currently does not. As an alternative, you can configure ODBC with federated SSO (SAML/OAuth) to allow Power BI Gateway to delegate authentication without storing static passwords"

do you mean establishing trust between AD FS and AWS? 

Configure federated access to Amazon Athena for Microsoft AD FS users using an ODBC client - Amazon ...

Thanks,

Gaurav

[v-sshirivolu]

Hi @GauravSinghPBI ,

That’s correct. By federated SSO, I’m referring to configuring a trust relationship between your corporate Identity Provider (such as AD FS or Azure AD) and AWS.

• With this approach, the Power BI Gateway does not store static AWS credentials.
• Authentication is managed by delegating it: AD FS provides a SAML/OAuth token, which AWS STS then exchanges for temporary credentials, allowing access to Athena.
• This process ensures that user identity and session security are maintained throughout.

If you’re using AD FS, you would set up a relying party trust with AWS and configure IAM roles for SAML federation. For Azure AD, the process is similar but uses the AWS SAML application.

For further information, please refer to Microsoft’s official documentation:

https://learn.microsoft.com/en-us/power-query/connectors/amazon-athena

This resource explains how to connect Power BI or Power Query to Athena with the ODBC driver, including prerequisites and authentication options.

[GauravSinghPBI]

@v-sshirivolu 

We are facing challenge around the connecticity.

AWS STS tokens typically expire in 15 minutes to 1 hour. ODBC/JDBC drivers does not refresh them automatically, causing connection failures.

Our AWS resources are not public-facing. This means that the AWS endpoint requires that the client is routed via a trusted IP range i.e. appears to originate from within the Our network. the only way for Power Platform to communicate with the AWS S3 resource is via public internet and it will be blocked.  By all means try it, but unless the owner of the resource can confirm their resource is publicly available, the connection will not work.

How to handle this? Kindly help.

Gaurav

[v-sshirivolu]

Hi @GauravSinghPBI ,

The suggested approach is to deploy the On-Premises Data Gateway (Enterprise mode) on a Windows EC2 instance within your AWS VPC, preferably in the same subnet as your S3 and Athena resources. This setup allows Power BI Service to connect securely to Athena without exposing resources to the public internet. The EC2 instance should have an IAM role with permissions for Athena and S3, enabling the gateway to get refreshed temporary credentials from the instance metadata. This removes the issue of STS token expiry and avoids manual credential rotation or CyberArk integration. After installing the Athena ODBC driver and Power BI Gateway on the EC2 instance, set up the data source in Power BI Service to use the gateway and authenticate via AWS SSO or the attached IAM role. The gateway will communicate with Athena over private VPC networking, ensuring security and reliability.

Microsoft documentation for reference:
https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

[v-sshirivolu]

Hi @GauravSinghPBI ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[GauravSinghPBI]

@v-sshirivolu Many Thanks for the response. We are progressing with whitelisting AWS Athena URL or Setup firewall for IPs. I mean we are progessing and will share the outcome.

Thanks,

Gaurav

[v-sshirivolu]

Hi @GauravSinghPBI 

Thanks for the update. Good to hear you’re making progress with the whitelisting and firewall setup. Please do share the outcome once it’s tested, it’ll help confirm everything’s working smoothly.

[v-sshirivolu]

Hi @GauravSinghPBI ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @GauravSinghPBI ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

HI @GauravSinghPBI ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[AmiraBedh]

Hello !

Thank you for posting on Fabric community.

You need to use the official Amazon Athena connector (ODBC) + an on-premises data gateway. Power BI does not support JDBC directly.

https://learn.microsoft.com/en-us/power-query/connectors/amazon-athena

https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc-and-power-bi.html

The import is recommended for semantic layer cache requirement while the DirectQuery is more for live queries and higher latency or cost. https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc-and-power-bi.html

AWS provides an Athena JDBC driver for Java apps, but Power BI can’t use JDBC without a 3rd party bridge (not Microsoft/AWS). If you see JDBC in Power BI, it’s via tools like ZappySys or Progress bridges not an official path.

https://www.progress.com/tutorials/jdbc/use-your-custom-jdbc-driver-with-microsoft-power-bi

https://zappysys.com/api/integration-hub/jdbc-connector/power-bi

[GauravSinghPBI]

Thanks @AmiraBedh for the response..

We have Athena setup with IAM not with IDC.

The challenge with IAM - if we have a service account setup on PBI Data Gateway to connect to Athena we would need to reset the password every 90 days. Is this correct and how to manage it.

If you refer to JDBC 3.x section, https://docs.aws.amazon.com/athena/latest/ug/jdbc-v3-driver-datazone-idc.html, we need this equivalent in ODBC in order to PBI to delegate the authorisation to ASUS/DZ.

Thanks,

Gaurav

[GauravSinghPBI]

Kindly reply @AmiraBedh