Share PowerApp connected to Azure SQL Database without sharing credentials

0
votes

We have an Azure SQL DB and are currently using MSAccess as a user interface. I am investigating using PowerApps instead for better portability and have created a test PowerApp connected to an Azure SQL Database.

When I share the App the other users are not being asked to provide credentials and instead are connecting using my security rights (my SQL Server connector).

I added the PowerApps connector to SQL SERVER - Connect Directly (cloud services), enter our server mytestserver.database.windows.net, 1433, database name, user name and password.

Built a quick form then shared it with another user who does not have any rights to access the Azure SQL Database.

Is it possible to force a user to login and if so how do I implement it?

EDIT As of 2019-08-01 Microsoft has announce that Azure AD-based authentication for SQL Server databases is planned for PowerApps and Microsoft Flow. Public preview: October 2019, General availability December 2019.

Following is a link to the post.

https://docs.microsoft.com/en-us/power-platform-release-plan/2019wave2/cdm-data-integration/better-enterprise-connectivity-sql-server

1
sql-serverazureazure-sql-databasepowerapps

1 Answers

1
votes

This is a known issue with PowerApps and db credentials. You really don't have a choice here if your users are savvy. As soon as you share an app that has SQL Credentials with them, they can create their own PowerApp and reuse the ConnecTION with whatever permissions the SQL User for the ConnecTION has.

One thing I've seen people do is:

  1. Add the Active Directory Connector to your PowerApp
  2. Create your own login screen
  3. Check to see if user is part of an AD Security Group via your login screen before they can access the SQL area of the app.

I don't particularly like this method because the AD Connector doesn't have any way to authenticate users. User just passes it an email address.

This does not mitigate the shared ConnecTION issue noted above, but may give you some piece of mind.

Another option that comes to mind is to only give the PowerAppsSql user Read access to the database. Force the user write to a Sharepoint list instead, then use Flow under your creds to Write to the DB (Crazy work around, I know, but I'm trying to help here :) ).

Hope some of this helps!