cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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? 
 
1 REPLY 1
Helper II
Helper II

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,674)