If you’re aiming to integrate your PowerApps with a local instance of SQL Server, the On-Premises Data Gateway is an indispensable tool. This guide provides a step-by-step walkthrough of downloading, installing, and setting up this gateway to facilitate seamless connectivity.
Initiating the On-Premises Data Gateway Installation
Embarking on this journey, the foremost step involves downloading the On-Premises Data Gateway. This tool, approximately 150MB, is readily available on the official Microsoft PowerApps download page.
It’s pivotal to select an appropriate machine within your local network for the installation. While Microsoft recommends a system that’s incessantly active with robust internet connectivity for production use, for the purpose of this demonstration (development/testing), the tool will be set up on the same system where SQL Express resides. This method eases the process as installing the gateway on a different system from SQL Server necessitates configuring the Windows firewall on the SQL Server device, especially when dealing with named instances.
Running the Installer
Initiate the installation by executing the GatewayInstaller.exe file. As the installation proceeds, you’ll be prompted to choose an installation directory, accept the licensing agreement, and log in using your Microsoft Work Account. Post login, opt to register a new gateway on the system.
Configuring the Gateway
Configuration entails assigning a descriptive name to your data gateway. For context, if you’re integrating this gateway within your home network, you might label it as “Home Network Gateway”. Equally crucial is setting a recovery key; essential if a gateway reinstallation on a separate system becomes a requirement. It’s advisable to securely document this key for future reference.
Finalizing the installation presents options to adjust logging settings and execute network port tests, invaluable for troubleshooting.
Facilitating Connection from PowerApps to SQL Server
With the On-Premises Data Gateway in place, bridging PowerApps with your database becomes straightforward. Navigate through the View > Datasources menu in PowerApps, select the SQL Server connector, and establish a new connection.
Connection Settings Highlights:
- SQL Server Name: Given the gateway and SQL Server are on the identical system for this guide, “localhost” is the default server name. Note that the instance name, if applicable, is integrated.
- SQL Database Name: Ensure accuracy while inputting your database name, as this is a free text field.
- Authentication Type: Both Windows and SQL Authentication are viable. If in doubt, mixed mode authentication offers flexibility for diagnosis.
- Username: For Windows authentication, enter your username in the format .\[username], e.g., .\tim in this demonstration.
Post connection, one can seamlessly select views/tables and establish data connectivity within the app.
Conclusion
Through this guide, we’ve elucidated the essential steps for downloading and installing the On-Premises Data Gateway, enabling an efficient connection to local SQL Server databases. Should you have queries or seek further technical assistance, please feel free to contact us. Our expert team is primed to aid you in ensuring your integrations and projects operate without a hitch.
