Connecting to a SQL Server database on AWS from PowerApps

[xx]

I have a virtual machine on Amazon cloud (AWS) with a SQL Server.  When I try to connec to a database on it using PowerApps web editor I get a message saying the SQL Server is not found.

I'm sure this is related to the inbound rules on the virtual machine, but does anyone know what inbound rules I need to allow PowerApps to connect to a database on AWS? 

thanks

[cherie · Accepted solution]

Hi,

I've not used AWS with SQL server before, but I'm wondering if this article might help you out?

https://powerapps.microsoft.com/en-us/tutorials/limits-and-config/

Thanks

Cherie

[xx]

Yes that's exactly what I needed. Thank you.

[giot]

Dear XX,

I saw that this problem was solved.

I am very new to PowerApps and would need to connect to MySql (on AWS).

You used a custom connector, I'm sure.

Could you share more about it please?

Thank you!

Giovanna

[xx]

I don't think these are related.  I'm using SQL Server.

As far as I know, MySQL connector currently does not support cloud but supported only when using On-premises data gateway. So you cannot get direct connection on cloud services such as Amazon.  I think your choises are 1) install an on-prem gateway, 2) change to a data source that PowerApps connects via cloud (try Azure SQL), 3)  create a web service to do the read write operations and connect your app to it directly.  The web service route is quite involved, but if you are stuck with MySQL I can't think of any other way.

[giot]

Thank you. Option 2 seems the most viable option indeed.

thank you for taking the time to answer!

[hayden]

Very soon I'll be working on a project with has a Powerapp connecting to SQL Server on AWS. Is there a specific data connector to use? Or did you have to create a custom connector? Any lesson learned info you have would be appreciated.

[xx]

You can use the SQL Server connector, but you need to makesure you have configured the inbound rules.  Take a look at the source IPs for the PowerApp servers on the link in the answer post.  You just need to allow the Powerpp servers to see your DB server.  In the end I went with Azure SQL, because the architecture is much simpler and cheaper as I don't need to configure and manage VMs.

However, PowerApps has some serious limitations when working with SQL Server directly (instead of your own API).  If your database has triggers for example then PowerApps won't read/write to that.  It really needs a simple database.

There is a record limit, so if you are pulling a lot of data to show as a list, check what your requirements are.  If you want to pull more than 500 rows then you need to look into delegable data sources.  It is worth looking at this link.

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

[giot]

thank you! I'll check it out!