Answered

Understanding SQL Server Connector Security

(0) Share

Report

Posted on by Community Power Pla...

Please help me understand this clearly. I've read dozens of articles and pages, but it's still not clear to me. My main question is about connecting to the database (SQL Server) using Azure Active Directory. Because I don't know well this way of accessing SQL Server.

If I use Azure Active Directory in my connector, does that mean I have to provide access to users in my database directly? This seems to me even more absurd than using an implicit connection with SQL credentials.

Is my summary correct?

SQL Server Authentication (implicit): User can use the shared connection to create their own Apps and manipulate data in the database.

Azure AD integrated or Windows (Explicit): It is necessary to configure the database giving direct access to the user. Which can also connect and manipulate data directly in the database.

Result: I can't restrict data access only by my application.

That's right? And if I create the app in an environment that users don't have access to, is the connection available to them in their environment too?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

tommytong 4 on at

Like (1)

Report

Running into something similar and that's mainly my understanding as well.

From what i can tell the idea is that if you have your own environment, the connector shared isn't accessible except only by the makers of that environment. The problem is that there is still the authentication piece, so you would still have to make the table/db accessible to an AAD group or subset of users (don't think you can make it available to all). From there you would have to apply RLS/Data Masking to the underlying dataset.

I'd love to see other views on this.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

"I'd love to see other views on this"
@tommytong me to.

The exposure of information, which can be manipulated outside the application, is the biggest problem with this platform.
I even miss the old MS Access with VBA.

Was this reply helpful? Yes No
Verified answer

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (2)

Report

@Anonymous , you are correct in that you can't restrict the SQL connector to only be used by the app. The best you can do is create a dedicated environment where your app and SQL connector will exist and only give a limited number of people access to create in that environment. You can have users use the apps created in that environment but if they can't create then they won't be able to use the SQL connector in manner it wasn't intended.

I am not a fan of this approach because you have to create and manage an environment for every SQL app that you want to be secured but it is the only option at this time. Here is a link to an Idea that someone posted awhile back about this issue. I just went in and voted for it and if you think it would be helpful I would recommend voting for it.

Making SQL Connector Secure - Power Platform Community (microsoft.com)

Was this reply helpful? Yes No