Power BI Service Auto Refresh with AWS EC2 using Athena ODBC and Gateway

[ash_y]

Hello,

I’m building a dashboard using Power BI Desktop on an AWS EC2 instance and planning to publish it to the Power BI Service. The data source is a CSV file stored in S3, accessed via Amazon Athena using the ODBC driver.

I would like to schedule automatic data refresh, so I’m considering installing an On-Premises Data Gateway on the EC2 instance. The EC2 instance is configured to automatically start and stop every day.

I have two questions regarding this setup:

1. If I configure the Athena ODBC connection to use the EC2's Instance Profile, will the scheduled refresh in Power BI Service still work properly, given that the EC2 instance automatically stops and starts daily?

2. If I configure the Athena ODBC connection using IAM credentials (Access ID and Secret Access Key) via the ODBC Data Source Administrator, will that configuration persist across EC2 restarts? In other words, will Power BI Service still be able to perform scheduled refreshes after the EC2 instance is restarted?

Based on my research, I couldn't find any definitive information regarding Option ①, which uses an Instance Profile (IAM role) for the Athena ODBC connection.

However, with Option ②, where IAM credentials (Access Key and Secret Access Key) are used, the ODBC configuration is stored in the Windows registry, so it persists after the EC2 instance is restarted. Therefore, scheduled refresh in Power BI Service should work without issues.

That said, Option ① using an Instance Profile is ideal, as it eliminates the need to issue and manage Access Keys. It allows authentication via temporary credentials and is therefore more secure and aligned with best practices.

I’d appreciate any insights or experiences you could share regarding this setup.

Thank you in advance!

[jaineshp · Accepted solution]

Hey @ash_y,

Power BI Auto Refresh with EC2

Option 1: Go with this

Yes, it works fine with daily EC2 restarts. The Instance Profile automatically reassigns when EC2 starts up, so your Gateway stays connected.

Quick setup:

• Configure ODBC driver to use "Default Credentials Chain"
• Install Gateway as Windows service with auto-start
• Set service recovery to restart if it fails
• Add 2-3 minute startup delay

Option 2:

You're right - ODBC settings in registry persist after restarts. Works but not ideal for security.

From experience: Instance Profile is the way to go. I've run similar setups and the credentials refresh seamlessly when EC2 restarts. Just make sure your Gateway service starts automatically with Windows.

Test tip: Do a manual restart first to verify everything reconnects properly before setting up your scheduled refreshes.

The daily start/stop schedule won't break this - Instance Profiles are designed for exactly this kind of scenario.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ash_y]

@jaineshp 

Thank you very much for your detailed explanation.

We plan to try out the solution you suggested.

Due to personal environment constraints, it may not function as expected. In that case, we may ask you additional questions.

Thank you for your continued support.

[jaineshp]

@ash_y 

Thank you for the kind recognition - always happy to contribute to our community's success!

Best Regards,
Jainesh Poojara | Power BI Developer

[jaineshp · Accepted solution]

Hey @ash_y,

Power BI Auto Refresh with EC2

Option 1: Go with this

Yes, it works fine with daily EC2 restarts. The Instance Profile automatically reassigns when EC2 starts up, so your Gateway stays connected.

Quick setup:

• Configure ODBC driver to use "Default Credentials Chain"
• Install Gateway as Windows service with auto-start
• Set service recovery to restart if it fails
• Add 2-3 minute startup delay

Option 2:

You're right - ODBC settings in registry persist after restarts. Works but not ideal for security.

From experience: Instance Profile is the way to go. I've run similar setups and the credentials refresh seamlessly when EC2 restarts. Just make sure your Gateway service starts automatically with Windows.

Test tip: Do a manual restart first to verify everything reconnects properly before setting up your scheduled refreshes.

The daily start/stop schedule won't break this - Instance Profiles are designed for exactly this kind of scenario.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ash_y]

Hi @jaineshp 

Thank you for your previous response. I now understand that using an Instance Profile is possible.

I have three follow-up questions:

1. What does the “Default Credentials Chain” mean in this context?
   My understanding is that we are using the Instance Profile to authenticate the Athena ODBC connection. Is that correct?

2. Regarding the on-premises gateway installed on the EC2 instance, is the setting to automatically start the gateway when Windows boots available only during installation, or can it also be configured/modified after installation?

3. I vaguely remember reading that a gateway connection in Power BI Service can be configured using anonymous authentication. Is that actually correct?

Thank you in advance for your help.

[jaineshp · Accepted solution]

Hey @ash_y,

Great questions! Let me clarify each point:

1. Default Credentials Chain explained:

• Yes, exactly right - Instance Profile authenticates your Athena ODBC connection
• "Default Credentials Chain" means ODBC driver automatically looks for credentials in this order:
  • Environment variables → Instance Profile → Other AWS credential sources
• Since you're on EC2 with Instance Profile, it picks that up automatically
• No need to hardcode any credentials in ODBC settings

2. Gateway auto-start configuration:

• Good news: You can modify this anytime, not just during installation
• Go to Windows Services → Find "On-premises data gateway service"
• Right-click → Properties → Startup type → Set to "Automatic"
• Also check Recovery tab → Set all failure actions to "Restart the Service"
• Pro tip: I always add 2-3 min delay in Recovery settings to let Windows fully boot first

3. Anonymous authentication in Power BI Service:

• Yes, that's correct - you can set gateway connection to Anonymous
• This works because authentication happens at the ODBC level (via Instance Profile)
• Power BI Service → Gateway → Data source → Authentication method → Anonymous
• Gateway passes through the ODBC authentication without additional Power BI credentials

Quick verification steps:

1. Set up ODBC with Default Credentials Chain
2. Configure gateway service for auto-start
3. Test manual EC2 restart
4. Verify gateway reconnects and data refreshes work

This setup is solid for daily EC2 cycling. The Instance Profile handles all the heavy lifting.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ash_y]

Hi @jaineshp 

Thank you very much for your detailed explanation.
Currently, we are in the security risk review phase within our company before installing the gateway.
Once the review is completed and the installation is done, we plan to try out the solutions you suggested.

Due to some company-specific environmental constraints, things may not work as expected. In that case, I may reach out again with additional questions.

Thank you for your continued support.