Azure SQL with Azure AD authentication dose not work
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?
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 -
CREATE ROLE [standardUser];
ALTER ROLE [db_datareader] ADD MEMBER [standardUser];
ALTER ROLE [db_datawriter] ADD MEMBER [standardUser];
CREATE USER [email@example.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [standardUser] ADD MEMBER [firstname.lastname@example.org];
The Above SQL Query Creates the defined role with read and write permissions to the database 'management' it then assigns azure ad user email@example.com <-- 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.