cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
versdev1
Frequent Visitor

Sharing access to users - backend Azure SQL server

Hi,

 

Is there any easier way to connect Azure Active Directory users to Azure SQL Database? I am asking in regards to enabling non SQL proficient Azure users to assign the permissions to database. I have noticed there is "Set Admin" functionality in Azure SQL Server to assign group. But the problem is, this is dbo access which could produce security issue where these users can then connect to the database using SSMS and change DDL schema as PowerApps needs only CRUD (create, read, update and delete). It is also on the server level. I would like to restrict users to particular database not all of them.

 

Thanks!

10 REPLIES 10
Pstork1
Dual Super User
Dual Super User

The Power Apps connector to SQL uses a connection account to access the database.  Users have no direct access to the database.  Their permissions in the database are also not applied.  They have access to any data that the connection account has access to in the database.  Making sure that the connection account you use only has access to a particular database will also keep the users from using the connection to access other databases/tables on SQL.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

When sharing frontend app to users there is a message to ensure that users have access to Azure SQL database.

The other thing is if backend access on the database level is not assigned to users, PowerApps users will not be able to view any data in the drop downs, galleries etc.

The message is a generic one for apps that applies to all data sources.  In the case of SQL the connection is an implicitly shared connection and the users do not need specific SQL access.  You can read more about implicit shared connections here:

Share resources used in your canvas app - Power Apps | Microsoft Docs

Are you saying you are seeing issues using the shared connection?  Can you provide screenshots of some of the messages and issues you are seeing?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
versdev1
Frequent Visitor

Hi Dual Super,

 

Thank you very much!

versdev1
Frequent Visitor

There is no "Share" option for Azure SQL Server connection.

 

versdev1_0-1620775754981.png

 

 

You don't need to share the connection.  It is implicitly shared automatically.  Read the specific section about implicitly shared connections.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
moutinhoabreu
Advocate IV
Advocate IV

Hi Guys,

 

In my case i shared the app with some users and they are prompted to create SQL connection with their AD username.

But when they finally open the app, no data is shown from SQL azure database.

 

How can solve this issue.

It is happening with all users. Only my user can see the data

When you create your SQL connection what form of authentication are you specifying?  It sounds like you selected Windows Authentication (Non Shared).  If you use the SQL or Windows Authentication it will be a shared connection and user's won't be prompted to create their own.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

My understanding is that the implicit shared connection is with on-premise SQL not with Azure SQL, am I right?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,722)