Azure SQL with Azure AD authentication dose not work

[sandra1]

Since October it is possible to use an SQL connector with Azure AD authentication. So I created an Azure SQL database and can log in SSMS with my Azure account, but I cannot use this connection in Powerapps.
The account uses 2-factor authentication. Could this be the problem?
Or is it a problem that the database and the used Azure Active directory is created in a second Azure directory (my own directory and not the company directory)?
The error message when I try to use a flow is: Could not retrieve values. Invalid connection setting inner exception: The key "token" in the connection profile is invalid.

When I try to use the connection in Powerapps, I get the error message: Tables were not loaded correctly. With message: BadGateway state 502 and "Newtonsoft.Json.JsonReaderException: Unexpected character occurred while parsing the value".
 
I have also added the Powerapps IPs to the firewall rules.
Does anyone know about this problem and has solved it? 

[Ben-Western]

Hi @sandra1 ,

We got around this issue two ways:

First way - Create a security group in the tenant - assign the required sql administrators - assign the group to the azure ad admin portion of SQL

Second way - 

USE management;
GO
CREATE ROLE [standardUser];
GO
ALTER ROLE [db_datareader] ADD MEMBER [standardUser];
ALTER ROLE [db_datawriter] ADD MEMBER [standardUser];
GO
CREATE USER [ben@nextstepcreations.com.au] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE [standardUser] ADD MEMBER [ben@nextstepcreations.com.au];
GO

The Above SQL Query Creates the defined role with read and write permissions to the database 'management' it then assigns azure ad user ben@nextstepcreations.com.au <-- me to the above created role.

If the connector is the issue. Create the SQL Server connection in the PowerPlatform as the administrator and see how you go.

Hope this helps!

Regards,

Benjamin Western | Automation Specialist

NextStep Creations

Taking your PowerApps Skill the NextStep