Forum Discussion

cbailiss's avatar
cbailiss
Advocate II
10 years ago

Power BI and SQL Server in the AWS

Hopefully simple questions if someone has used Power BI with SQL Server in AWS...

 

Amazon RDS offers SQL Server in AWS.  Connections to the SQL Server instance use a server name along the lines of:

sqlsvr-pdz.abcd12340.us-west-2.rds.amazonaws.com,1433

Reference:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html

 

Will the Power BI service be able to refresh (data import, not direct query) from a SQL DB in AWS?

 

Since this isn't on-prem, I assume an Enterprise Gateway isn't needed?

But how does the Power BI service differentiate between an on-prem server and a cloud one?

Has anyone used Power BI with a database in AWS - were there any issues? (e.g. do firewall/ports in AWS need to be opened to allow the Power BI service to connect?).

 

Thanks

 

Chris

Need Help

5 Replies

  • cbailiss's avatar
    cbailiss
    Advocate II
    10 years ago

    I have tested this and recording the answers for the benefit of others:

     

    Power BI Desktop was able to load from a SQL Server in the cloud (as expected, that's the simple bit).

     

    Power BI Online was NOT able to refresh without using a Gateway.  It is currently unable to differentiate it seems between an on-prem and cloud database (with the exception of Azure SQL Database, etc).

     

    I had to set up an a data source in an instance of the Enterprise Gateway pointing at the AWS SQL instance.  A shame.

     

    Further, since our firewall blocks outbound connections on 1433, that instance of the Gateway had to be running in a VM in the cloud.

    • someuser999's avatar
      someuser999
      New Member
      8 years ago
      How do you get an AWS ec2 SSAS instance to recognize the UPN passed from PBI service to the gateway? My ec2 is domain joined but I can't find the o365 user account in the directory.
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Glad you found the issue.  For your new question. I have no idea.  Its not something i've personally had to tackle.  Might be worth creating a fresh thread about that specific issue.

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    cbailiss since yesterday with power bi desktop july update you have default connector to amazon redshift check this out. I haeven't used this before but my guess is yes you should be able to refresh since it is cloud data source and no gateway required.

  • Jannat's avatar
    Jannat
    Frequent Visitor
    8 years ago

    Hi, 

     

    I am currently having the same problem and I wonder if there is anything new about it...? 

     

    Thanks,