Forum Discussion

ash_y's avatar
ash_y
Icon for Helper I rankHelper I
1 year ago
Solved

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

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 Athe...
Need Help
  • jaineshp's avatar
    jaineshp
    1 year ago

    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

  • jaineshp's avatar
    jaineshp
    1 year ago

    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

jaineshp's avatar
jaineshp
Icon for Memorable Member rankMemorable Member
1 year ago

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's avatar
ash_y
Icon for Helper I rankHelper I
1 year ago

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.