Secure the SQL server connection so no one can use it to access all SQL server data

[johnjohn123]

I want to build a Power App which uses SQL Server . now i have experience in those technical approaches:-

1) Using Power Apps with SharePoint

2) Using ASP.NET Core MVC with SQL server

But i am unable to figure out how SQL Server connector works in Power Apps compared to the above approaches.

1) As in SharePoint case, if we build the connector using Office 365 admin user, then any user who try to use that connector for building new Power Apps, will not be able to misuse this connector (as the user can not see anything through the SP connector their username can not see). as the SharePoint connector on Design mode and on Run mode works under the login user and not under the account which created the connector. and SharePoint items and documents are secured inside SharePoint, no one can view anything not shared with them.

2) As in the .NET web application, we secure the connection with the SQL Server, using a service account username and password inside web.config file, and no end user can access this web.config file.. so user can only view the database items inside the application, so we can apply custom permission inside our .NET code.

But in Power App + SQL Server case, we have implicit and explicit connectors, where none of them worked in a similar way to the above 2 approaches. so how i can secure the connection with SQL Server which get created using Office 365 admin + allow users to only view items based on the Power Apps formulas and filters.. is this possible? as if we use implicit connector that got created using Office 365 admin, then this can be misused by end users, also if we want to use explicit connector, then we need to define the users' permission inside SQL ,, which i never did during my 10 years of experience in .NET development... so can anyone advice on these confusing points? where we have different SQL tables , and users should only view certain records inside those table (we have record/item level permission)..

Thanks

[Pstork1]

The Implicit SQL connection in Power Apps works like a Connection account in .NET.  The user credentials you add to the connection determines what SQL data Power Apps can see.  The main difference in this case is that Implict connections use SQL accounts instead of Windows Domain accounts.  But the concept of a service account is the same in both.  The SQL credentials you embed in the connection determine the user's access to the data, not the Office 365 admin's credentials.

[johnjohn123]

@Pstork1  but i do not think that implicit sql connector works the same as web.config connection string. for the following main reasons:-

1) web.config is secured from been read. unless you ave access to the hosting server, so if a user access the .NET web application, this does not mean that they can reuse the web.config to build their own app and read all the data.

2) Also .NET is a server-side application, so if we implement custom permission through the code then this will be highly secure, unlike power apps , where defining a formula to filter the items does not prevent users for seeing all the data ,, since they can reuse the connector which is implicit and get all the data inside the database by developing a  new power app...

am i correct?

so returning back to my question, How i can secure the implicit connection with SQL Server which get created using Office 365 admin + allow users to only view items based on the Power Apps formulas and filters.. is this possible? 

[Pstork1]

1) Somewhat true.  But users cannot see or change the credential in the connection either.  And if someone gives you the contents of the Web.config connection string any .net developer can connect to the database.  I would not depend too much on the web.config not being readable.  That's why most web sites don't use connection strings stored directly in the WEb.config.  They use an encrypted copy that is read from somewhere else.

2) Yes, .Net is server side code, but Javascript uses the same SQL connection strings.  So again, you can't really depend on that alone for Security.

But yes, I agree that .Net has more security features available than Power Apps.  But to get back to your original question.  You can't secure the connector so other users can't use it.  You can make sure that the account used only has access to the tables needed for the Power App.  But that is the limit. It is one of the limitations of a low-code environment. You'll have to live with it or find another platform.  There is no workaround other than what I've already stated.

[johnjohn123]

@Pstork1  but the issue is that we not only want to secure certain tables, we want to secure certain rows in the same table... so not sure how SQL can help us with this, when we use SharePoint each item can have its own permission settings + the SharePoint connector will run under the login user (always explicit).. now we are moving away from SharePoint because weare developing a large application which need to have a true-relational database,,, but now we are facing another problem of how we can define row level permission for the SQL server data !! as we need to use explicit sql connector.. implicit will cause a real security problem in our case..

[Pstork1]

If you want Row level security in SQL then you have to have the individual users defined in SQL security.  Otherwise you will get whatever permissions the connection account has.  That's true in .Net as well.  So the solution would be the same in .NET as it is in Power Apps.  You must define user level security in the database, not in the application.

[johnjohn123]

@Pstork1 ok understood...

First Question, but i did not find any resources which talks about building Power Apps with SQL server and define Row Level security.. seems it is not a widely used approach?

Second Question, again in .net no one can reuse the connection string, while in power apps you can reuse SQL server connection which uses implicit authentication.. connection string can be encrypted and also can not be accessed unless you access the hosting server,, so the situation is totally different from SQL Connection inside Power Apps .. also Microsoft already mentioned the security implication of using Implicit SQL connection, while no one will say that storing sensitive data inside web.config might poses a security issue, as it is secure by nature

[Pstork1]

1) Dataverse is Microsoft's focus now and since SQL is a Premium connector there isn't as much focus on it as there is on Dataverse and SharePoint.  Also, Power Apps has nothing to do with row level security in SQL.  If you define row level security in SQL and use an explicit connection you'll have row level security.  But the design and maintenance is all on the SQL side, not Power Apps.  So there isn't much of anything to cover.

2) I disagree.  In .Net people normally can't see the connection string.  But if someone gives it to me then I can re-use it.  Power Apps is designed around the idea of giving user's access to the data they already have access to.  Its not and wasn't designed to be a security layer.  Yes the situation is different.  Power Apps wasn't designed to be a replacement for .Net, which is a professional developers tool.  It was designed to let Citizen Developers create apps using data that they have access to.  As I've said before, if that design limitation doesn't meet your needs then continue to use .Net and don't use Power Apps.