SQL - Caution! This is how users can hack shared SQL connections
When we create an app that connects to a SQL Server database, we can choose a number authentication methods.
If we create a connection using SQL Server or Windows Integrated authentication, Power Apps creates the connection as a shared "implicit" connection.
This means that when we share our app with other users, we must also share this implicit connection. Our shared users will connect to our database using the credentials that we specified we created the connection at design time. Power Apps will secure the connection credentials, and users cannot view the clear text username and password that are associated with the shared connection.
What exactly is the security implication of using a shared connection? In this post, we'll look at this topic in more detail and discover a way in which a user can exploit a shared connection..
Example security requirement - build an app that grants access to certain records only
Walkthrough - publishing and sharing our app
Once we complete our app, we begin the process of publishing and sharing our app. Here's a walk through of this process.
With SQL Server Azure, there is better news. With SQL Azure, we can avoid the use of implicit connections by creating a connection that uses AAD (Azure Active Directory) authentication. We'll cover this in a subsequent post.
If we create a connection using SQL Server or Windows Integrated authentication, Power Apps creates the connection as a shared "implicit" connection.
This means that when we share our app with other users, we must also share this implicit connection. Our shared users will connect to our database using the credentials that we specified we created the connection at design time. Power Apps will secure the connection credentials, and users cannot view the clear text username and password that are associated with the shared connection.
What exactly is the security implication of using a shared connection? In this post, we'll look at this topic in more detail and discover a way in which a user can exploit a shared connection..
Example security requirement - build an app that grants access to certain records only
To mimic a typical use case scenario, we'll attempt to build an app that restricts access to certain records.
This app will restrict access to records, based on the logged on user. To implement this requirement, we filter all the data access controls so that they return only the records that are assigned to the current user.
To demonstrate how we implement this, the screenshot beneath shows the gallery control on the main screen. Here, we filter the records to return only those where the 'CreateManagerEmail' field match those of the logged on user.
To demonstrate how we implement this, the screenshot beneath shows the gallery control on the main screen. Here, we filter the records to return only those where the 'CreateManagerEmail' field match those of the logged on user.
Walkthrough - publishing and sharing our app
Once we complete our app, we begin the process of publishing and sharing our app. Here's a walk through of this process. When we share our app, the designer shows a prominent warning that tells us that "shared connections can be used to access the data source outside of this app". We must confirm this message before we can continue to share our app.
Let's suppose we share our app with Sally, but we choose not to make her a co-owner of the app. Where exactly does the vulnerability lie? After all, Sally will not have sufficient permissions to edit the app and to modify the formula or the filter functions in our app.
Where is the security vulnerability?
Where is the security vulnerability?
To answer the above question, let's login as Sally. Sure enough, Sally does not have sufficient permissions to edit our app. She only has sufficient permissions to play our app, and to view the records that are associated with her
It appears at first glance that we've restricted access to data as required. However, if Sally were to create a brand new app of her own, she'll be able to select our shared connection, and access all the tables that we don't want her to see.
It appears at first glance that we've restricted access to data as required. However, if Sally were to create a brand new app of her own, she'll be able to select our shared connection, and access all the tables that we don't want her to see.
Furthermore, Sally can switch to Power Automate, create a Flow, and perform actions using our shared connector. This includes actions such as deleting rows, executing custom SQL queries, and stored procedures.
How can we secure a shared connection?
Unfortunately, there's no way to prevent users from re-using a shared connection to create new Apps or Flows.
Therefore, when we create a shared connection, it's essential that we create the connection using credentials that grant no more permissions then are absolutely necessary.
How can we secure a shared connection?
Unfortunately, there's no way to prevent users from re-using a shared connection to create new Apps or Flows.Therefore, when we create a shared connection, it's essential that we create the connection using credentials that grant no more permissions then are absolutely necessary.
With an on premise instance of SQL Server, we unfortunately have no option other than to use implicit connections because is the only connection type that SQL Server and Windows integrated authentication support.
With SQL Server Azure, there is better news. With SQL Azure, we can avoid the use of implicit connections by creating a connection that uses AAD (Azure Active Directory) authentication. We'll cover this in a subsequent post.
Conclusion
When we create and share a SQL Server app that uses an implicit connection, the end user can create a
new app or Flow that is based on the shared connection. This can enable the user to
access records and tables that we do not intend the user to see. Therefore it is important
to make sure that we properly secure access to database objects with the
user logins that we use to create shared connections with SQL Server. If we use SQL Server Azure, we should consider using AAD authentication, which does not rely on an implicit connection.
