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,

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

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.