Power BI and SQL Server in the AWS

[cbailiss]

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

[Jannat]

Hi, 

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

Thanks, 

[cbailiss]

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]

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]

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]

@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.