Why does Excel require less information for querying a SQL Server than Power Apps?

Question

In Excel 365 desktop, I can:

1. Open a blank workbook
2. Click on the 'Data' ribbon
3. Click 'Get Data'
4. Click 'From Database'
5. Click 'From SQL Server Database'
6. Fill in the 'Server' field
7. Click OK

and that's all that I need to query my SQL server. Conversely, in the web version of Power Apps, it appears that I absolutely must set up something called a "gateway" (or sometimes, an "on-premises data gateway"). This appears to be non-trivial and looks like it may even cost money.

Is there any technical reason for this restriction? I find it very surprising that Excel appears to be more powerful than Power Apps. Am I profoundly ignorant in some way?

Answer 1

To answer your last question: Yes, but that can be changed.

PowerApps is a cloud service. It is hosted on Microsoft servers. You can query all kinds of data, but you need so-called "connectors" to do that.

If the data source is on your company's internal network, then you need a way to connect to that internal data securely and safely. You wouldn't want to expose your company's SQL Server data for all the world to see.

To create that secure connection from a cloud-hosted service like PowerApps (or Power BI, or Power Automate), you install the data gateway on a machine in your internal network. That gateway is then the, ehm... , gateway from the cloud-hosted system into your company's SQL Server or other on-premises data.

If your SQL Server database is hosted in the cloud, for e.g. in Azure, then you would not need the gateway and could use a different connector in PowerApps that targets Azure hosted SQL.