Getting PowerApps data into on-prem SQL server table without using the SQL Server connector

[Mr0bb0]

Hi all,

I work for a very large organisation and we are unsure as to whether we can cover the licensing fees involved with premium connectors.

I was wondering if there was anyway I can write the fields from a PowerApps form straight to an on-prem SQL server table.

Thanks in advance

[timl]

Hi @Mr0bb0 

There is no way to write fields into a on-prem SQL Server table without a premium license.

Many users here have floated ideas to workaround this licensing issue, none of which have particularly worked in practice.

Without a premium license, the best avenue to explore would be to save your data into SharePoint, or some other data source that uses standard connector, and to create some process outside of PowerApps to move that data into your SQL Server database.

[Mr0bb0]

Thanks for that timl.

I've just been looking into the patch function. This seems to write to SQL server from PowerApps, does this also need a SQL connector?

[timl]

Hi @Mr0bb0 

Yes, the patch function also accesses SQL Server through the SQL connector, and therefore also requires a premium license.

The other point to mention is that because you are using an on-prem instance of SQL Server, this requires the on premises gateway and using this will always require a premium license.

[Mr0bb0]

Thanks again timl,

Do you know if the premium connector would only be required for the developer or for all users?

[timl]

Hi @Mr0bb0 

The premium connector would be required for all users.

[Mr0bb0]

Thanks again,

Unfortunately I don't think that is going to be an option as we have several thousand staff.

Microsoft's pricing is confusing and doesn't seem best suited to catering for large companies .

[KRider]

So, I'm sure there are more topics on this but this came up in search so I will post here.

I am also at a large company and pricing is absolutely astronomical so we won't be seeing premium here for a long time.

I currently grab records via premium Automate(much more affordable as only your admin need the accounts).

This isn't the problem, very easy to do. What I am attempting to do now is to write the update back to the server.

I can't even attach a premium flow to the app without causing the premium flag to go off. My idea is now to try an automated flow that watches my sharepoint list. It then uses the Premium Automate to update the item. Now to figure it out. I will post my findings later.

Wish me luck! 

[kky1]

Hi @Mr0bb0 - I'd suggest looking into a per-user plan for power automate and power apps. The cost of a per-user plan for each of these is $15/month. I also work at a large org but our office was able to purchase this plan and it works very well with updating data in sql based on an app user's input. 

To get around the email issue from an app (since emails sent in the app are from the logged in user), I have the data patched to a SharePoint list which triggers a Power automate flow to send a confirmation email from a shared mailbox, add the data to sql, then deletes the row in the SharePoint list.  

Hopefully this is helpful!

@kky1