cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
westerdaled
Continued Contributor
Continued Contributor

Manual Solution Imports with SQL Connection reference woes across multiple environments

 

 

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!

 

westerdaled_0-1658838457417.png

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 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
eleung83
Resolver II
Resolver II

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

5 REPLIES 5
eleung83
Resolver II
Resolver II

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
Continued Contributor
Continued Contributor

@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
Resolver II
Resolver II

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
Continued Contributor
Continued Contributor

@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
Resolver II
Resolver II

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.

Helpful resources

Announcements

Super User of the Month | Drew Poggemann

As part of a new monthly feature in the Community, we are excited to share that Drew Poggemann is our featured Super User for the month of February 2024. If you've been in the Community for a while, we're sure Drew's name is familiar to you, as he is one of our most active contributors--he's been a Super User for five consecutive seasons!   Since authoring his first reply 5 years ago to his 514th solution authored, Drew has helped countless Community members with his insights and expertise. In addition to being a Super User, Drew is also a User Group leader and a Microsoft MVP. His contributions to our Super User sessions and to the new SUIT program are always welcome--as well as his sense of humor and fun-loving way of sharing what he knows with others.   When Drew is not solving problems and authoring solutions, he's busy overseeing the Solution Architecture team at HBS, specializing in application architecture and business solution strategy--something he's been doing for over 30 years. We are grateful for Drew and the amazing way he has used his talent and skills to help so many others in the Community. If you are part of the SUIT program, you got to hear some great tips from Drew at the first SUIT session--and we know he still has much more to share!You can find him in the Community and on LinkedIn. Thank you for all you do, Drew!

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Our team will be reviewing posts using the new "Copilot Studio" label to ensure we highlight and amplify the most relevant and recent content, so you're assured of high-quality content every time you visit. If you share a post that gets featured in the curated gallery, you'll get a PM in the Community to let you know!The curated gallery is ready for you to experience now, so visit the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community. We can't wait to see what you "cook" up!      

Celebrating a New Season of Super Users with Charles Lamanna, CVP Microsoft Business Applications

February 8 was the kickoff to the 2024 Season One Super User program for Power Platform Communities, and we are thrilled to welcome back so many returning Super Users--as well as so many brand new Super Users who started their journey last fall. Our Community Super Users are the true heroes, answering questions, providing solutions, filtering spam, and so much more. The impact they make on the Communities each day is significant, and we wanted to do something special to welcome them at our first kickoff meeting of the year.   Charles Lamanna, Microsoft CVP of Business Applications, has stressed frequently how valuable our Community is to the growth and potential of Power Platform, and we are honored to share this message from him to our 2024 Season One Super Users--as well as anyone who might be interested in joining this elite group of Community members.     If you want to know more about Super Users, check out these posts for more information today:    Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

February 2024 User Group Update: Welcoming New Groups and Highlighting Upcoming Events

It's a new month and a brand-new year, which means another opportunity to celebrate our amazing User Groups!Each month, we highlight the new User Groups that have joined the community. It's been a busy season for new groups, because we are thrilled to welcome 15 New User Groups! Take a look at the list below, shared by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you!   We love our User Groups and the difference they make in the lives of our Community! Thank you to all the new User Groups, new User Group leaders--we look forward to hearing about your successes and the impact you will leave!   In addition to our monthly New User Group spotlight, it's a great time to share some of the latest events happening in our User Group community! Take a look at the list below to find one that fits your schedule and need! There's a great combination of in-person and virtual events to choose from. It's a great time of year to connect and engage with User Groups both locally and online. Please Welcome Our NEW User Groups   Power Platform: Heathcare Power Platform User Group Power Platform Connect Hub Power Platform Usergroup Denmark Mexico Norte- Power Platform User Group Pune Power User Group Sudbury Power Platform User GroupMicrosoft User Group GhanaMPPBLR - Microsoft Power Platform Bengaluru User Group Power Apps:   Myrtle Beach Power Platform User GroupAnanseTechWB PowerApps Copilot Studio: Pathfinders Power Platform Community Dynamics365: Cairo, Egypt MSD 365 Business Central/NAV/F&O User GruopMS Dynamics 365 Business Central LatamCincinnati OH D365 F&O User Group February User Group Events February 2024 Cleveland Power Platform User GroupPortallunsj - Februar 2024Indiana D365/AX February User Group MeetingQ1 2024 KC Power Platform and Dynamics 365 CRM Users Group 

Super Users 2024 Season One is Here!

   We are excited to announce the first season of our 2024 Super Users is here! Our kickoff to the new year welcomes many returning Super Users and several new faces, and it's always exciting to see the impact these incredible individuals will have on the Community in 2024! We are so grateful for the daily difference they make in the Community already and know they will keep staying engaged and excited for all that will happen this year.   How to Spot a Super User in the Community:Have you ever written a post or asked for help in the Community and had it answered by a user with the Super User icon next to their name? It means you have found the actual, real-life superheroes of the Power Platform Community! Super Users are our heroes because of the way they consistently make a difference in the Community. Our amazing Super Users help keep the Community a safe place by flagging spam and letting the Community Managers know about issues. They also make the Community a great place to find answers, because they are often the first to offer solutions and get clarity on questions. Finally, Super Users share valuable insights on ways to keep the Community growing, engaging, and looking ahead!We are honored to reveal the new badges for this season of Super Users! Congratulations to all the new and returning Super Users!     To better answer the question "What is a Super User?" please check out this article: Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

Did You Attend the Microsoft Power Platform Conference in 2022 or 2023? Claim Your Badge Today!

If you were one of the thousands of people who joined us at the first #MPPC Microsoft Power Platform Conference in 2022 in Orlando--or attended the second-annual conference in Las Vegas in 2023--we are excited to honor you with a special community badge! Show your support for #MPPC Microsoft Power Platform Conference this year by claiming your badge!         Just follow this link to claim your badge for attending #MPPC in 2022 and/or 2023: MPPCBadgeRequest    Want to earn your badge for 2024? Just keep watching our News & Announcements for the latest updates on #MPPC24.

Users online (7,704)