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

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

alrezac on at

Like (0)

Report

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.

Was this reply helpful? Yes No
kijoupa 13 on at

Like (0)

Report

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

Was this reply helpful? Yes No
beneze 3 on at

Like (0)

Report

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

Was this reply helpful? Yes No
kijoupa 13 on at

Like (2)

Report

Hi Beneze,
Microsoft is still working on the case.
I keep you inform when it is solved.
Alex

Was this reply helpful? Yes No
ajpowell 4 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
kijoupa 13 on at

Like (1)

Report

Hi ajpowell,
Microsoft is still working on the case.
I keep you inform when it is solved.
Alex

Was this reply helpful? Yes No
AnilNagthane 12 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
ajpowell 4 on at

Like (0)

Report

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).

Was this reply helpful? Yes No
robgauldie on at

Like (0)

Report

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.>

Was this reply helpful? Yes No
robgauldie on at

Like (0)

Report

Hello, is there any news on this case?

Was this reply helpful? Yes No