Can PowerApps Connect to SQL Server
Can PowerApps connect to SQL Server? You will be pleased to know that yes, it is possible. SQL Server is an important data source so it is only expected that Microsoft would make it available.
Like any system, you should set up your table structure before creating the app. Create the field names and assign the correct data type. When you are satisfied that the table is correct, you will be in a position to let PowerApps access it. If you are using the on-premises version of SQL Server then things are not so straightforward.
Can PowerApps Connect to SQL Server Using a Gateway?
If you are thinking Can PowerApps connect to SQL Server as a data source, in other words can PowerApps connect to SQL Server on premise, then first a connection needs to be created. This will be how to get data from On Premise SQL Server database in PowerApps.
In order to connect PowerApps to the on-premises SQL Server, you will need to use what is known as the on-premises data gateway. This enables PowerApps to connect to SharePoint and SQL Server networks that are inside internal company networks. You may want to consult with your database administrator about this.
As soon as you have an on-premises Gateway connection created, you will be able to use it to create an app from data from
PowerApps. Alternatively you will be able to create a blank app and import the on-premises connection as a data source for your
application.
When setting up the Gateway, it is possible to assign permissions to users. If you are able to manage your Gateway then you can administer the permissions yourself, otherwise the database
team may be able to assist here.
To connect to the Gateway from within your PowerApps application, do the following steps:
Go to the File menu
Select the Connections option
Choose SQL Server
Click ‘New connection’
Select SQL Server
A pop up will appear with options for authentication types such as Azure, SQL Server and Windows.
Select SQL Server and you will be asked to enter:
SQL server name
SQL database name
Username
Password
Gateway
If your Gateway has been set up then it should appear as one of the choices in the Gateway dropdown. Select your Gateway if it is there and then click the ‘Create’ button.
If you do not see your Gateway listed then it is worth checking with the database team to see if it has been created.
Can PowerApps connect to SQL Server and give you access to objects such as tables and store procedures?
PowerApps and SQL
Once connected with the Gateway, you will see the tables in the database listed in your PowerApps application. What if you want access to PowerApps SQL stored procedure objects?
What you cannot do is connect to stored procedures directly at the moment. There is no direct way to call a stored procedure in PowerApps.
If you do want to use stored procedures then the best way is to create a flow and run them from there.
You can use PowerApps to call the flow and pass in the relevant parameters which in turn can be fed into the stored procedure.
Once you have created your on-premises connection, it will be shown under your list of connections in your PowerApps application.
Also Check Access Database to PowerApps
