In this article, we’re going to look at how to connect Power Apps to SQL Server. This provides a slightly deeper dive than was covered in our previous Quick Start Guide to Power Apps.
Power Apps is the low-code/no-code platform from Microsoft. It aims to make it quick and easy for people in organizations to create simple apps to help improve productivity. As a Microsoft product, Power Apps is tightly integrated with data sources such as SQL Server and Excel. However, connecting Power Apps to SQL Server is not quite as straightforward as you might think. We’ll cover the main steps required and highlight some key considerations.
Before you start: key considerations
Before you connect Power Apps to SQL Server, there are a couple of things to consider and information to gather.
Location: on-premise database or Azure SQL Database?
The first thing to consider is the location of your data. Is it in an on-premise database or in an Azure SQL Database?
If you are connecting to an on-premise database, then you’ll need to install an on-premise data gateway. The gateway acts as a bridge to provide quick and secure data transfer between on-premises data and cloud-based services like Power Apps. The procedure for installing an on-premise data gateway is explained here. Once the gateway is installed, it can be used by Power Apps and other apps in the Power Platform, such as Power BI.
Data preparation
The database that you want to connect to should contain at least one table with a primary key.
You may need to also prepare the data for use in Power Apps. Typically, this could involve creating a SQL View to be used in the app.
When preparing data, it’s also important to consider the 2,000 record limitation that Power Apps has when retrieving data from a source. In most cases, this probably isn’t an issue. However, you may need to get creative with the way you write your SQL views or use some other tricks in Power Apps itself to get around the limit.
Security considerations
When you publish an app that connects to SQL Server, both the app and the connection are deployed to your users. This means that both the app and the connection are visible to users that the app is shared with.
It is therefore important that you consider the authentication method for these connections – they can be shared explicitly or implicitly. The table below shows the different authentication methods:
| Authentication Type | Power Apps connection method |
|---|---|
| Azure AD Integrated | Explicit |
| SQL Server Authentication | Implicit |
| Windows Authentication | Implicit |
| Windows Authentication (non-shared) | Explicit |
- An explicitly shared connection means that the end-user of the application must authenticate to SQL Server with their own explicit credentials. Usually, this authentication happens behind the scenes as part of Azure Active Directory or Windows authentication handshake. The user doesn’t even notice when the authentication takes place.
- An implicitly shared connection means that the user implicitly uses the credentials of the account that the app maker used to connect and authenticate to the data source while creating the app. The end user’s credentials are not used to authenticate. Each time the end-user runs the app, they’re using the credentials the author created the app with.
It is best practice to use explicit connections for your Power Apps, but there are also use cases for implicit connections. There is a good article here that explains the differences and implications in more detail.
Gather information for the database
Gather the following information, as you’ll be prompted for it when creating the connection to SQL Server in Power Apps:
- the name of the database
- the name of the server on which the database is hosted
- a valid user name and password to connect to the database
- the type of authentication needed to connect to the database – based on the security consideration noted above
- the name of the on-premise gateway (if applicable)
Steps to Connect Power Apps to SQL Server
To create a connection to SQL Server from Power Apps, first click on Data > Connections, and then click on + New connection and select SQL Server from the available options.
From the dialog box that is displayed, you can select the authentication type from the following options:
- Azure AD Integrated
- SQL Server Authentication
- Windows Authentication
- Windows Authentication (non-shared)
For this example, let’s assume that we have an on-premise database, and we’re going to select Windows Authentication (non-shared).
In the next dialog box, you enter the Username and Password for the Windows account. For the Gateway, you can select from a list of available gateways, or install a new one. You’ll notice that we haven’t been prompted to enter the database or server – this is because we chose the Windows authentication (non-shared) option, and we’ll be prompted for this when we create the app.
When you click Create, the connection is added to the list of available connections. Note that for the Windows Authentication (non-shared) option, a default name is created – you can change this by clicking on the ellipses (…) and then Edit.
We can now go ahead and create a Power App. The easiest way is to go to the Home page and use the “Start from” feature to select SQL Server.
We can now select the connection that we created earlier and enter the database and server details.
Once the connection has been made, you’ll be able to select the table or view that you want to use in the App. The table will then be loaded into Power Apps and a default 2 screen app will be created in the Canvas for you to start customizing.
Power Apps and SQL: Use Cases & Alternatives
In this article, we’ve looked at how to connect Power Apps to SQL Server, and specifically some of the things you’ll need to consider.
Use Cases
Let’s look now at some of the likely use cases for creating a mobile or desktop app that connects to SQL Server:
- A simple app for general users to view and edit data – this could be something like a product price list
- A basic app that includes some sort of simple workflow – this could be something like a leave application app where there is an approval process
- A more complex app that includes some business logic – this could be a pricing app or inventory management
Alternatives
When it comes to no code/low code alternatives to Power Apps for building an app, there are numerous options to look at, such as Appsheet, Quickbase, Bubble, Airtable, and Zoho Creator. We’ll take a look at some of these in an upcoming post.
In the meantime, if your needs are like the first use case, then going through the process of building an app in Power Apps (or another platform) may be overkill. If you need a way for general users to manage data in a SQL environment, then a solution like SQL Spreads would work well and be simpler to implement and use. SQL Spreads is an add-in for Excel that allows users to connect to SQL Server and view and update data from within Excel. It has the following features:
- Insert, update and delete data in tables in SQL Server through Excel
- Designer and Data Editor roles
- Automatic validation for data quality
- Change tracking
- Drop-down lists with key lookups for data consistency
- Conflict detection
The main benefit compared to Power Apps is that it provides general non-technical users with the ability to update and manage centralized SQL Server data from the familiar environment of Excel.
You can download a free trial of the SQL Spreads Add-In for Excel here.
Hello
An interesting article and, sadly, it may have confirmed my suspicions that you can’t use Azure AD Integrated security for power apps through a gateway. I am guessing this as you discuss gateways but then move to direct connection to SQL server. Also, when you choose AAD auth you do not get the choice of using a gateway, not even a specific server. Do you happen to have a definitive answer on that or know where I might find one?
Thanks in advance
Alex Stennett
Hi Alex,
I’m guessing that you want to use a gateway to connect to Azure SQL because of a firewall or VPN? I’m no expert in this particular area, but I fear that you are correct in saying that you can’t use an Azure AD account for gateway credentials (https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem-faq)
Regards
Andy
After the connection is created, how can we see the username specified on that connection. We may have multiple connections to the same server/database, but with different credentials (to apply security as needed). Is there a back-end way to query for that?
Hi Mitch,
There are a couple of scripts that you could use to get this information.
This first one shows the number of users connected to each database.
SELECT @@ServerName AS server
,NAME AS DatabaseName
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS Query_Run_Time
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE NAME not in (‘master’,’model’,’msdb’,’tempdb’)
GROUP BY NAME
This one shows the usernames/machines connected:
SELECT @@ServerName AS SERVER
,NAME
,login_time
,STATUS
,hostname
,program_name
,nt_username
,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE NAME not in (‘master’,’model’,’msdb’,’tempdb’)
AND loginame IS NOT NULL order by 2
Hope this helps.