Guest user "external Azure AD Account" don't get data from azure database

[kijoupa]

Dear all,

I am having issues with the following settings in my app:

Back-end: Azure SQL database with Row Level Security based on Security Group on Azure

Connection: Azure AD Integrated

User Permissions: The user permissions are base on Azure AD Security groups membership

I wondered whether someone has an idea why I am having this issue.

The app work perfectly for all the following user type, Properties of a B2B guest user - Azure Active Directory | Microsoft Docs:
1. Member: OK

2. Microsoft Account (State 2): OK

3. Invited user ( as described in AAD): OK.

But for the External Azure AD account, the app load well but it seems that it can not read the database.

In order to check, whether the guest user cannot access the database, I tried to connect to the database via ssms with the credentials of the guest user with the property "External Azure Active Directory" and I can access the database and see only the table with the specific record based on the RLS. Everything looks fine with ssms.

After that, I expected that the power apps connection with Azure AD Integrated was having trouble.

Could anyone help me on this matter? Please tell me if you need more specific informations.

Alex

[alrez]

Hi,

Looking at this, if you are able to hit the database with the guest users credentials then I would assume that doing the same calls from the PowerApp should yield the same results. Without any logs or errors to look at this is a very hard thing to troubleshoot. I would doublecheck the settings and confirm that the external user can reach the resource naturally like the other users can. If you are still having issues, I would recommend reaching out to the Microsoft help desk on this. I'll include a link below if you would like to go down this route.

If you would like to create a ticket with Microsoft Customer Support here is a link on how to do so: https://docs.microsoft.com/en-us/power-platform/admin/get-help-support

Regards,

Alex

-------

Community Support Team _ Alex Rezac
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kijoupa]

Hi Alex,

Thank you for your answer. I will contact the Microsoft Customer Support. 

My company is also register as Microsoft Partner, is there also a link for Microsoft partners?

Thank you very much.

Regards,

Alexandre

[beneze]

Hi Alex,

I seem to have the same problem:

AAD User from PowerApp AAD => PowerApp => Azure SQL Database =>  able to read data / everything as expected

B2B Guest User from other AAD => SSMS => Azure SQL Database => able to read data / everything as expected

B2B Guest User from other AAD => PowerApp => Azure SQL Database => not showing any data

Azure SQL Database audit log shows:

Event time (UTC): 5/14/2021 7:54:12 PM

Event type: DATABASE AUTHENTICATION FAILED

Server name: xxxxxxxxxx

Database name: xxxxxxxxx

Application name: Mashup Engine

Client IP: xxx.xxx.xxx.xxx

Status: Failed

Additional information: "<login_information><error_code>18456</error_code><error_state>132</error_state></login_information>"

Do you have any solution / any advice by now?

Thanks!
Benedikt

[kijoupa]

Hi Beneze,

Microsoft is still working on the case.

I keep you inform when it is solved.

Alex

[ajpowell]

I'm essentially trying to do the same thing, getting the same results. I see that when running in monitor, the GetRows action is throwing a login error. 

My initial thoughts are that when creating the Azure AD authentication, it only created a User in the Database, not a login to the Server itself. Below is the error I am getting. 

"error": {

"message": "Login failed for user '<token-identified principal>'."

Any thoughts on this?

[kijoupa]

Hi ajpowell,

Microsoft is still working on the case.

I keep you inform when it is solved.

Alex

[AnilNagthane]

I am also stuck and unable to build app using Azure SQL Database for my customer as I am added as AAD guest users (External users) in Azure SQL Database.

Microsoft is not consistent throughout the power platform. This is very much possible in Power BI. Is there any other way? Can anybody help?

[ajpowell]

After putting in a ticket with the Azure SQL Support, and PowerApps support, I think I finally at least got an answer on this. Looks like (currently as of July 2021) the PowerApps connector for SQL Server is not supported for guest access.

Share a canvas app with guest users - Power Apps | Microsoft Docs

However, you could do some dynamic filtering within the app if you need those guest users/tenants to share the same data source but want them to see only their information. Row Level Security takes enough to set up in SQL itself, so it is kind of a pain to have to do it within PowerApps too, but until it is a supported connector, that's what I have been able to make work (minus migrating to Dataverse). 

[robgauldie]

Is there any news on when this will be resolved?    It is a very problematic constraint to anyone looking to make applications available across different organisations.

<Due to current authentication pipeline limitations, AAD guest users aren't supported when using AAD type connections to SQL Server. As a workaround, use SQL/Windows auth type connections.>