cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidForlen
Advocate II
Advocate II

Azure SQL with AAD authentification doesn't work


Hi,

 

I'm trying to set up a connexion to my Azure SQL Server via Azure Active Directory.

 

But when I try to get the tables in PowerApps, I get this error :

 

{
  "error": {
    "code": 502,
    "source": "europe-002.azure-apim.net",
    "clientRequestId": "98373092-88b0-4ac0-9fce-933013ba05a9",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": "Unexpected Exception : Newtonsoft.Json.JsonReaderException: Unexpected character encountered while parsing value: h. Path '', line 0, position 0.\r\n   at Newtonsoft.Json.JsonTextReader.ParseValue()\r\n   at Newtonsoft.Json.JsonReader.ReadForType(JsonContract contract, Boolean hasConverter)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)\r\n   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)\r\n   at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)\r\n   at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)\r\n   at Microsoft.Azure.Connectors.Common.Profile.TokenHeaderInformation.GetProperty[T](String key) in X:\\bt\\1010746\\repo\\src\\source\\common\\Profile\\TokenHeaderInformation.cs:line 81\r\n     inner exception: Unexpected character encountered while parsing value: h. Path '', line 0, position 0.\r\nclientRequestId: 98373092-88b0-4ac0-9fce-933013ba05a9",
      "error": {
        "message": "Unexpected Exception : Newtonsoft.Json.JsonReaderException: Unexpected character encountered while parsing value: h. Path '', line 0, position 0.\r\n   at Newtonsoft.Json.JsonTextReader.ParseValue()\r\n   at Newtonsoft.Json.JsonReader.ReadForType(JsonContract contract, Boolean hasConverter)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)\r\n   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)\r\n   at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)\r\n   at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)\r\n   at Microsoft.Azure.Connectors.Common.Profile.TokenHeaderInformation.GetProperty[T](String key) in X:\\bt\\1010746\\repo\\src\\source\\common\\Profile\\TokenHeaderInformation.cs:line 81\r\n     inner exception: Unexpected character encountered while parsing value: h. Path '', line 0, position 0."
      },
      "source": "sql-we.azconn-we.p.azurewebsites.net"
    }
  }
}

ID de session : 6b93a41f-11c7-4f2c-a952-989a7f33a4ee

 

 

In Power Automate, the same connexion return the following error when using an action involving SQL Server : 

 

Could not retrieve values. The dynamic invocation request failed with error: {
  "status": 400,
  "message": "Invalid connection settings\r\n     inner exception: Key 'token' in connection profile is not valid.\r\n         inner exception: Unexpected character encountered while parsing value: h. Path '', line 0, position 0.\r\nclientRequestId: 404cb90d-070a-458e-99eb-7b2f-6365ad10",
  "error": {
    "message": "Invalid connection settings\r\n     inner exception: Key 'token' in connection profile is not valid.\r\n         inner exception: Unexpected character encountered while parsing value: h. Path '', line 0, position 0."
  },
  "source": "sql-we.azconn-we.p.azurewebsites.net"
}.

 

 

I can access the server with SSMS with AAD connexion with the same account used for PowerApps and Power Automate. There isn't any problem there.

 

I tried to whitelist IPs of PowerApps and PowerAutomate as suggested here : https://powerusers.microsoft.com/t5/Connecting-To-Data/Unable-to-connect-to-SQL-server/m-p/571403 and here : https://powerusers.microsoft.com/t5/Building-Power-Apps/Unable-to-connect-to-Azure-SQL-database-BadG.... But it didn't change anything.

 

Any idea ?

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
rimatos
Community Support
Community Support

Hi @DavidForlen 

 

There was an ongoing issue that occurred in our side recently with Azure SQL connections resulting in 502 error messages, but the fix should have been deployed this Monday.

 

Can you confirm me you if applied any workaround on your side such as this?

1. Remove all the Database connectors from your app.
2. Go to File -> Settings -> Advanced Settings -> Disable enhanced Microsoft SQL Server Connector.
3. Add all your Database connectors back again.

 

Additionally, could you show me the connection string you are using in PowerApps and Flow connections?

 

Regards,

Ricardo

View solution in original post

4 REPLIES 4
rimatos
Community Support
Community Support

Hi @DavidForlen 

 

There was an ongoing issue that occurred in our side recently with Azure SQL connections resulting in 502 error messages, but the fix should have been deployed this Monday.

 

Can you confirm me you if applied any workaround on your side such as this?

1. Remove all the Database connectors from your app.
2. Go to File -> Settings -> Advanced Settings -> Disable enhanced Microsoft SQL Server Connector.
3. Add all your Database connectors back again.

 

Additionally, could you show me the connection string you are using in PowerApps and Flow connections?

 

Regards,

Ricardo

View solution in original post

Thank you for the answer,

 

If I try your workarround, I can't select the SQL connexion that uses AAD (as the parameter "enhanced Microsoft SQL Server Connector" is precisly here to allow it).

 

I tried again to use the SQL connexion with AAD (to see if the fix you talk about changed anything) : no changes, I'm facing the same error in PowerApps and Power Automate.

 

How can I see the connexion string used ?

The connection string would be relative to the server name and database name you are trying to access. The issues we were seeing were relative to some characters in here not being correctly transformed on our end.

 

rimatos_0-1600184119637.png

If you believe providing this information would contain sensitive data, you could alternatively open a service request through: https://admin.powerplatform.microsoft.com/support and we would check it on our end in the ticket.

 

Regards,

Ricardo

I got it to work by creating a new SQL connexion (with AAD).

 

The connexion created last week still doesn't work, so the fix you talked about must have fixed something in creation process.

 

Anyway, it works now.

 

Thanks a lot !

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,377)