3

I have a specific problem to which I couldn't find any answer online.

The situation is the following: We created a Canvas app that connects to the Azure SQL database. We set the connection type to be AAD integrated.

Users that are members of the AD can see the data in the app, but guest users, even though we gave them all the rights and PowerApps plan, cannot see the data. they recieve the same authorization window as members, but when they click on allow, the app starts but no data is being pulled from the SQL database.

enter image description here

When we try to connect directly to the Azure SQL database with the guest user email and credentials (via SQL server management studio), everything works as expected and the guest can see all the tables.

When we use implicitly shared connection (with SQL server authentification), guests can see the data, but we need to use AAD integrated due to its security.

So far we tried:

  • Changing PA environment from sandbox to production
  • Adding special permissions in SQL database like database owner etc
  • Trying out different AAD guest user settings, eq- setting that guest users have the same privileges as members (picture below).

enter image description here

Nothing seems to work. I would be more than happy if you could tell me how to make this work or even push me in the right direction.

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
  • What happens if you grant guest users the role of administrator? – Carl Zhao Mar 12 '21 at 09:43
  • Nothing really, It still doesn't allow the communication between PA and SQL server, although no error messages occur ... – Kresimir L. Mar 12 '21 at 16:52
  • 1
    What happens if you enable diagnostics on the Azure SQL Server and database level? From there, you could get an idea of why is something failing. In what kind of a group did you place those guests? What access level does that group have on the server/database? I have too many questions ;-) – milivojeviCH Mar 14 '21 at 21:30
  • 1
    Hi @milivojeviCH, We've checked diagnostics on SQL, no query is even accessing the database. It looks like PA is unable to send requests when using AAD integrated guest account. We tried putting a guest in Security and Office365 groups, none is working. We tried giving the group and user all possible privilages that we can in Azure, still no luck. Now we wonder if it is even possible. so if anyone has succeeded in allowing guest users access from PowerApps to a SQL using AAD, it would be great if they could share how, or even acknowledge if it is possible, so that we know it is worth trying. – Kresimir L. Mar 15 '21 at 11:15

3 Answers3

1

I've reproduced your problem in my side. Here's my experience.

After assigning powerapp license(I use O365 E5 here) to guest account and share the app to this account, I can visit the app but can't see the data in the table. I assume that it resulted from the connector hasn't been shared, but it's true that this connector can't be shared because of no 'share button'.

Then I tried to add access policy to my guest account with these two sql:

CREATE USER [tiny-wa_outlook.com#EXT#@xx.onmicrosoft.com] FROM EXTERNAL PROVIDER;
GRANT SELECT ON dbo.tinyTest TO [tiny-wa_outlook.com#EXT#@xx.onmicrosoft.com];

Pls note here, I used the User Principal Name here(can find the principal name in azure ad->users), I also tested to use 'tiny-wa@outlook.com' in the sql but after executing successfully, it didn't work.

So I think you can try to use the sql above to add your guests accounts and make them can access the powerapp.

Here's some related docs:

create contained users mapped to azure ad identities

Share app resources

add table permission to user

==========================UPDATE==========================

Share my experience of this troubleshot.

First I need to create a power app but I found that after creating the connector with sql server azure ad authentication, it can't connect to the sql server, the error message is like 'Credentials are missing or not valid. inner exception: The credentials provided for the SQL source are invalid', solution is add my admin account as sql server instance Active Directory Admin.

Then I choose a table and successfully create a sample app. With the creating account, I can visit the app but other accounts can't. Here needs to share the app and it's related connectors to other users. But other accounts still can't reach the app because of no license. Because sql server connector is premium connector, so I assign O365 E3 license here. I met an error when assign license, the user's 'Usage location' can't be null or it can't assign license in M365 admin center.

Then I met similar error with Op, the difference is that both member account and guest account can't see the data in app. I try to find the way to share the connector to these uses but failed, I haven't made sure if those connectors without share button can be shared to others. So I have no options to study if this kind of connectors are authenticated in other ways so they don't need to be shared at all.

Next actions is using the account which used to create the sql server and database to sign in database and execute the sqls above. enter image description here

Then the member account can see data in the power app while the guest account can't see. The reason is I used xx@outlook.com as the parameter in the sql, when I used xx_outlook.com#EXT#@xx.onmicrosoft.com, it worked finally.

Hope this can also help you.

===================================================

For creating my demo app: First, I'm sure my environment isn't a sandbox(the environment in the screenshot below). And I think it's easy to create a demo app, and my app is simple, just choose to create an app from data and then select sql server as the connector, next I choose auzre ad auth and click the connector, enter server name and database name then choose a table, after that my app has created. That table has one row of data so when I signed in the app with the creating account, I see it in the screen while other accounts(member or guests) can't.

My sql server instance and database are created long time ago, but I'm sure I followed this tutorial to create them.

enter image description here enter image description here

Tiny Wang
  • 10,423
  • 1
  • 11
  • 29
  • Hi @Tiny-wa , thank you for your comprehensive answer! Tried everything as you stated in your reply. Still, the guest user cannot see the data in App. Could it be due to the PowerApps environment? I'm using a sandbox environment, not the default one. If you could share more info about your PowerApps or SQL setup that could be relevant in making this work? For my AD server there is a master Admin with who I've created the database. Only later have I added AD Integrated Admin so that I could create an external user. – Kresimir L. Mar 17 '21 at 10:17
  • Did you try to create an AAD group, add the internal user and the guest into the group and authorise the group itself to be a reader on the SQL Server side? – milivojeviCH Mar 17 '21 at 14:47
  • 1
    @milivojeviCH I haven't try that yet, it sounds like to be a good idea :) – Tiny Wang Mar 17 '21 at 15:21
  • 1
    @KresimirL. I'm not an expert in powerapp indeed, I'll share my creating details. – Tiny Wang Mar 17 '21 at 15:34
  • @milivojeviCH that was my first intention to use AAD group, but again, guest users couldn't see the data, even though they had direct access to SQL. – Kresimir L. Mar 18 '21 at 08:34
  • @milivojeviCH I've tried to create a group and add users into it, then set this group as the admin of sql server instance, then the users in it can all access the table, even without executing create/grant sql in the database, but this suitable for member accounts and guest account which is a personal account. – Tiny Wang Mar 18 '21 at 09:15
  • @KresimirL. Excuse me sir, do the guest accounts are work accounts? I found that my guest which could see the data is a personal account, but when I test with a work account , it can see the data. I'm now trying to find a solution. – Tiny Wang Mar 18 '21 at 09:18
  • @Tiny-wa, My accounts are all work accounts (Office 365). – Kresimir L. Mar 18 '21 at 16:08
0

This appears to still be a limitation to access to Azure SQL via PA connector for guest users:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Azure-SQL-to-PowerApps-Connector-AAD-doesn-t-work-for-guest/idi-p/1637817

Rob Gauldie
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 25 '22 at 12:25
0

If the "guest" does NOT have a PowerApps Per-App/Per-User plan, they cannot use your PowerApps with SQL data source (Note: SQL is a premium connector).

enter image description here

Determine exactly which type of license the guest has. Then, either your organization or the guest (or guest's org) must purchase one of these licenses. $5/$10/$20 per month depending on your use case.

REF

SeaDude
  • 3,725
  • 6
  • 31
  • 68