Solved: Re: Manual Solution Imports with SQL Connection re...

westerdaled · ‎07-26-2022

I am trying to define best practices with our solutions and at the same time resolve an issue which is impacted multiple users in multiple environments😫.

Current approach

Each Power Platform ( canvas app and nn x flows) solution is assigned a service account which is the account used to import the managed solution into the test (sandbox ) environment

In the Test environment

Import Solution_A under Svc_A ( via a private browser session)

configure DB_A on Sever_A in the SQL Connection Ref with SVC_A

Import Solution_B under Svc_B ( via a private browser session)

configure DB_A on Sever_A in the SQL Connection Ref with SVC_B

Now this recreating the connection ref under the current service account is something I am starting to doubt the wisdom on.  Is the previously configured connection ref  both visible, so not require a new connection to be configured under the current service account

Import Solution_C under Svc_C ( via a private browser session)

configure DB_A on Sever_A in the SQL Connection Ref with SVC_C <- same issue as above.

I am seeing a number of issues, with all roads lead to the dialog of no progress!

In some cases you go into the connection ref in the Default solution and you see a guid value and not a server name that will definitely trigger the above access dialog.

Now here is the weird part: Yesterday evening in one of the environments , I recreated the connection ref with the service account credentials, under the Default solution. I added myself as test user to the user group shared by the Power App . When I open the app and get the access dialog. This morning I attempted to open the Power App and now I don't see the access dialog. All the other users in the same Power App user group do see the access dialog, or in other words: "What the ......!" . Try explaining that one to SLT.

In all cases the users have a Power Apps per user license via an SG license group and a valid security role in the environment

eleung83 · ‎08-08-2022

Personally, the only reasons I would see the need for separate connection references that point to the same target environment (SQL/Sharepoint/Dataverse etc) would be

1. They are using different credentials/separate connections to access the target environment

2. They are pointing to separate connections that are owned by separate users, which may or may not be using the same or different credentials. The importance more on who owns the connection

The last point is important when it comes to Flows, as someone who is attempting to turn on any flows needs access to the connections used by any connection references used in any flows (Canvas apps work differently due to the fact that the connections are implicitly shared with the user when the canvas apps are shared).

And, as there still isn't a way to share some connections with all users, a user editing or creating a Flow would have to remember to edit the Flow as the user who has access to the connection that is used by a connection reference (e.g. dataverse connections). This becomes more of an issue if you have multiple Flows owned by multiple different users. You would then have a quagmire trying to automate some process to manage all these connections/connection references

The added benefits I can see with your setup are:

1. You can change the connection used by one connection reference and it would only affect any dependent components of that connection reference

2. You may choose to configure another connection with different credentials in future and only need to change one connection reference, which also allows you to take advantage of any security configuration in the target connection system, and your dependent components also then run under a different security model

The downside that I could see would be in scaling. Would you want to have one connection reference/connection per custom Flow, or one connection reference/connection per canvas app in your environment? And, as there is not yet an automated way to configure these connection references and connections when deploying to another environment, you would have to manually configure these if you were to have one connection reference per Flow/Canvas app. Hence you would have to judge how often and how many canvas apps/Flows you would intend on creating in future, as well as if you were, in future required to change the credentials used by these connections. In short, I would say there's no right/wrong way to this, but would be more of your preference in how you wanted to maintain these disparate components

View solution in original post

eleung83 · ‎07-27-2022

I can't recall how things work with connections behind the scenes when you share an app with other users (I think it implicitly shares the connection with those users, or I vaguely recall the user has to setup their own connections the first time they run a canvas app, but I think solution based canvas apps share the connection, although it's been a while since I built a solution based canvas app).

From your example, it looks like you've setup at least 2 separate SQL connections but all using the same connection reference?

And you have setup the SQL connections using different user accounts?

Or have you attempted to setup 3 separate connection reference and 3 sql connection using the different service accounts?

If you've attempted to setup separate SQL connections using just one connection reference, then this is why you are getting "In some cases you go into the connection ref in the Default solution and you see a guid value and not a server name that will definitely trigger the above access dialog."

The reason for this is because the underlying connection that the connection reference is using is not currently shared with the user login that you are currently using to view the connection reference. To get around this issue, you need to share the underlying connection with all users who need to access your flow (and hence whomever would use that connection reference). If your flow is executed manually (instant flows), then you have the concept of run-only users which allows you to control which connections are available within a flow for a user to be able to user when executing the flow

When you gave yourself shared access to the app, this also implicitly shared the power app and any connections it depends on with you, so you were able to reuse the sql connection that the app required. Any other users with whom the app hasn't been explicitly shared with would still get that access dialog until the app has been explicitly shared with them. This runs the risk though of allowing users to reuse the connection outside of the app/flow that it was intended to be used for (especially risky if you are using a static username/password combination to access the sql resource)

Unfortunately this gets further muddied if you are using non-solution based flows and canvas apps vs solution based versions, as I don't think the non-solution based versions don't make any use of the connection references, and rely on the connections directly

The following explains some stuff about the connection/connection reference and some of the risks especially regarding SQL connections:

https://docs.microsoft.com/en-us/power-apps/maker/data-platform/create-connection-reference
https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/connections-list#security-and-types-of...

westerdaled · ‎08-03-2022

@eleung83

Firstly sorry , I took so long to reply. Ironically, since posting, this issue as been vexing a group of us devs ( and a bunch or irate business users lol) for a the best part of the week.

I have read through your comments and they do explain the issues we have having. The SQL connections are implicit , so will run under a service account.

As an interim fix , we using one service account for all SQL Connections. We now set up all connections (Dataverse -> Connections) , prior to solution import. During the manual import we select the appropriate connection, when prompted to configure each connection reference. This is now repeatable process and we don't get issues with our test users.

I would like to run by you our more strategic approach:

Import Solution_A under Svc_A ( via a private browser session)

configure DB_A on Sever_A in the SQL Connection Ref named sol_A_SQL1 with SVC_A

Import Solution_B under Svc_B ( via a private browser session)

configure DB_A on Sever_A in the SQL Connection Ref named sol_B_SQL1 with SVC_B

In this case the connection references are named appropriately in each solution: sol_A_SQL1 and sol_B_SQL1 . They both have separate underlying connections SVC_A and SVC_B, respectively, but point to the same Customer db table. This eliminates the interdependencies between solutions.

Yes we maybe duplicating some of the connection references pointing to the same table but we get a more resilient solution architecture overall.

I be interested in hearing your thoughts.

eleung83 · ‎08-08-2022

Personally, the only reasons I would see the need for separate connection references that point to the same target environment (SQL/Sharepoint/Dataverse etc) would be

1. They are using different credentials/separate connections to access the target environment

2. They are pointing to separate connections that are owned by separate users, which may or may not be using the same or different credentials. The importance more on who owns the connection

The last point is important when it comes to Flows, as someone who is attempting to turn on any flows needs access to the connections used by any connection references used in any flows (Canvas apps work differently due to the fact that the connections are implicitly shared with the user when the canvas apps are shared).

And, as there still isn't a way to share some connections with all users, a user editing or creating a Flow would have to remember to edit the Flow as the user who has access to the connection that is used by a connection reference (e.g. dataverse connections). This becomes more of an issue if you have multiple Flows owned by multiple different users. You would then have a quagmire trying to automate some process to manage all these connections/connection references

The added benefits I can see with your setup are:

1. You can change the connection used by one connection reference and it would only affect any dependent components of that connection reference

2. You may choose to configure another connection with different credentials in future and only need to change one connection reference, which also allows you to take advantage of any security configuration in the target connection system, and your dependent components also then run under a different security model

The downside that I could see would be in scaling. Would you want to have one connection reference/connection per custom Flow, or one connection reference/connection per canvas app in your environment? And, as there is not yet an automated way to configure these connection references and connections when deploying to another environment, you would have to manually configure these if you were to have one connection reference per Flow/Canvas app. Hence you would have to judge how often and how many canvas apps/Flows you would intend on creating in future, as well as if you were, in future required to change the credentials used by these connections. In short, I would say there's no right/wrong way to this, but would be more of your preference in how you wanted to maintain these disparate components

westerdaled · ‎08-12-2022

@eleung83

Thanks for your reply.. When it comes a solution with a SQL connection reference. I intend to come up with a design that allows all current, and new Canvas Apps and Flows to be able to use the connection reference. The connection reference will be authenticated with a service account credentials that is allocated to that solution only.

Another solution in the environment may also have a separate connection reference and this may even point to the same database but again, the connection reference will be authenticated with a service account credentials that is allocated to that solution only.

My thoughts are when I logon as service account user and look at Dataverse -> Connections , I will only see the connections for the connection references that have been set up under the service account.

I will need to ensure my connection references are easily identifiable and tbh and I dreading having to tell the dev team, mid sprint to look that their solution publisher id - this is a last resort lol. As long as I can name the connection refs so that they are easily associated with the host solution, I am good.

As you say there are number of advantages keeping connection references unique and not shared amongst the other solutions .

eleung83 · ‎08-20-2022

There's a couple of difficulties with your intended design:

"intend to come up with a design that allows all current, and new Canvas Apps and Flows to be able to use the connection reference" - If the user creating a new Flow doesn't have access to the connection, they can't see the connection reference. Yet when another user who edits the flow "owns" the connection, they can use the connection reference in the Flow, and then all other users can see that same connection reference in that Flow.

"the connection reference will be authenticated with a service account credentials that is allocated to that solution only." - The user who is creating a Flow/Canvas app may inadvertently setup their own connection reference (if they have access to the target of the connection reference, so the person who owns the service account connection may have to go into the canvas app or Flow to reconfigure the connection references used, or update the new connection reference to use your service account connection.

This is currently my one bug bear with the inability to share all connections with all users (or any real users in particular)

The nice thing is that you can currently rename the connection references however you wish, and you will also soon be able to rename the connections themselves.