How to Connect SQL Server 2019 to Power Apps Online
Question details
The user needs to connect an on-premises SQL Server 2019 database to Power Apps Online without exporting or moving the database to a local Excel or Access file.
- Product
- Power Apps
- Device & OS
- not provided
- Scenario
- Accessing and integrating an on-premises database with cloud-based Microsoft 365 applications.
- Observed behavior
- Power Apps cannot natively access on-premises SQL Server databases directly from the cloud without a dedicated connectivity component acting as a bridge.
Ensure you have administrator access to the server hosting your SQL Server 2019 database (or a designated gateway server) and confirm your Power Apps account has the necessary premium licensing to use the SQL Server connector.
Install and Configure the On-premises Data Gateway
Use the Microsoft On-premises Data Gateway to create a secure bridge between your local SQL Server and Power Apps Online.
The On-premises Data Gateway acts as a secure bridge that provides quick and encrypted data transfer between on-premises data sources (like SQL Server) and Microsoft cloud services. Installing this gateway prevents you from having to migrate your database to the cloud or local files.
Download the standard On-premises Data Gateway installer from the official Microsoft download center to the server where SQL Server 2019 is hosted, or to a machine on the same local network.
Launch the installer, accept the terms of service, and follow the on-screen prompts to complete the installation on your local server.
Open the Gateway application and sign in using the same Microsoft 365 admin or Power Apps credentials you use online. Select 'Register a new gateway on this computer', provide a distinct name, and configure a recovery key.
Log in to the Power Apps maker portal, navigate to the left sidebar, click on 'Data', and select 'Gateways'. Verify that your newly registered gateway is listed and its status is online.
In Power Apps, go to 'Data Sources', click 'Add data', and search for 'SQL Server'. Select the SQL Server premium connector, choose your gateway from the dropdown, and enter your SQL Server 2019 credentials to establish the connection.
Need a Lightweight Office Suite for Daily Tasks? Try WPS Office
While Power Apps handles your custom business applications and SQL database integrations, you still need a reliable office suite for data reporting, documentation, and daily tasks. WPS Office is a powerful, free alternative to Microsoft Office that easily handles all your document needs.
- 1. Download WPS Office: Navigate to the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the simple setup instructions to install the suite on your computer.
- 3. Open Your Documents: Launch WPS Office and instantly open your existing Word, Excel, and PowerPoint files without format distortion.
Frequently Asked Questions
Can I install the On-premises Data Gateway on a different server than SQL Server 2019?
Yes. The gateway does not need to be on the exact same machine as your SQL Server 2019 instance. It can be installed on any server within the same local network, provided it has continuous network access to the database.
Does connecting Power Apps to SQL Server require a special license?
Yes. Connecting to an on-premises SQL Server utilizes a premium connector. Users interacting with the app will require a standalone Power Apps license rather than the standard license included with Microsoft 365.
Why shouldn't I just export my SQL data to a local Excel file for Power Apps?
Exporting to a local Excel file requires manual updates and is not suitable for multi-user, real-time access. Power Apps cannot connect directly to local Excel files; they must be hosted in the cloud (like OneDrive or SharePoint) to function properly.
