Allow connections to Microsoft Dataverse using the TDS endpoint

[JurgenPostelmans]

Hi,

I'm trying to connect to Microsoft Dataverse but this always returns the following error:

Login failed: The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Bearer authorization_uri=https://login.microsoftonline.com/c13f6e78-545e-4878-a61a-4887d80c3f28/oauth2/authorize, resource_id=https://XXXX.crm4.dynamics.com/'. RequestId: TDS;d8f1f194-a7ca-404f-871c-468ddde01d89;1 Time: 2021-01-08T12:33:30.1133716Z

The issue might be that when you request a token to connect to Dataverse, you should not use https://database.windows.net/ as the resource id, but https://XXXX.crm4.dynamics.com. If you use the wrong resource ID in a custom .NET app, you get exactly the above error when you try to connect.

Note that opening these kind of connections in the SSMS just works fine.

Jurgen

[mhammons]

Is this still an open bug? I am getting the same error when trying to use ADS to connect to a Dataverse using SQL.

Login failed: The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Bearer authorization_uri=https://login.microsoftonline.com/043207df-e689-4bf6-9020-01038f11f0b1/oauth2/authorize, resource_id=https://xxx.crm.dynamics.com/'.

ADS v 1.25.3

[kenvanhyning]

moving out to May for investigation and consideration.

[DynamicTouch]

Any updates? Also Logic apps is not able to connect to the tds endpoint using the SQL connector. It returns the same error

[kmalyszka]

I have the same problem. When can we expect a fix?

[jacr777]

Hello, I'm having the same issue... has anybody found a solution?

Thank you

[araishaftab5]

Hi, I am facing the same issue while trying to open a connection to TDS endpoint of CDS. Does anybody know about a fix yet?

[JurgenPostelmans]

Hi, I’m the original submitter of this issue. The issue has not been resolved yet. If original description is correct, It would take any descent developer 5 minutes of work to fix it…

[MaddyTP]

Same error here trying to connect to Dynamics from Azure Data Factory.

[mscottsewell]

I'm unable to connect to Dynamics via Azure Data Factory - or SSMS v19 Release 2. (I have no trouble with SSMS v18.)

[rajyraman]

Does not work for me in Azure Data Studio 1.38.0 as well. I am able to use SqlConnection with C# and connect to the TDS endpoint using Service Principal and secret, but ADS does not work at all.

[MaddyTP]

Any update on this issue?

It’s beginning to seem like maybe they don’t want to solve so as to force use of Azure Synapse Link for Dataverse…

[Yannlagaf]

hello, I'm having the same problem. with Azure Data Studio.

[ralphwest1]

I'm running into the same authentication issue. Can successfully connect from the legacy SSMS but not from Azure Data Studio. Any update on a resolution or work around?

[mathewrosssmith]

Same issue here. Data Studio connection to Dynamics365 data source (xxxxx.crm.dynamics.com) fails with the error described by others. Using the latest insiders build made no difference as of today. SSMS 15 works fine with the connection. Opening Azure Data Studio directly from the active connection in the object explorer changes nothing. Using Active Directory with MFA. Would love to be a guinea pig if it helped solve this issue or determine a work around.

[jessaskey]

Still an issue as of today, devs.. can you please fix this? Same error as OP and Im connecting to XXXX.crm.dynamics.com. SQL Server Management Studio (SSMS) works fine but Azure Data Studio errors.

[MarkMpn]

You can now connect to Dataverse/Dynamics 365 instances using the SQL 4 CDS extension. Get it from the extensions panel, then create a new connection. Change the Connection Type option to SQL 4 CDS and fill in the rest of the details to connect. You can then run queries using the TDS Endpoint or they can be translated to FetchXML. You can also run UPDATE, INSERT and DELETE statements which will be translated to the corresponding API requests.

[seanmcne]

@MarkMpn Appreciate you letting us know there are ISV options as well. The intent of the request here is for using an SPN w/ ADF (natively within ADF) using the TDS endpoint as opposed to translating it to fetch.

@kenvanhyning & @cheenamalhotra do you have an update as we approach March 2023? I noticed it was sized as a Large update - given SSMS can easily and natively connect to the TDS endpoint (for queryonly/readonly operations) I would hope this isn't a huge level of effort to enable within ADF - I think the request here is to enable an SPN/Identity to connect to a SQL/TDS endpoint for the purpose of running a sql query and read that data. For reference: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query

Thanks!

[cheenamalhotra]

Hi @seanmcne

It's one of our top priorities, and our plan stays intact for March release. I'm aware of the changes (being ex-SqlClient) that we need to use SqlAuthenticationProvider implementation to integrate with SqlClient's FedAuth flow.

As of now, Azure Authentication flow in both tools (ADS and VSCode's MSSQL extension) is governed with SqlConnection's 'AccessToken' property , and the entire token lifecycle had been driven by NodeJS layer (including refreshing token) using ADAL/MSAL NodeJs packages. ADS isn't designed similar to SSMS when it comes to Azure authentication, and that's why we have this huge design gap. To be clear, this is not just a Dataverse specific issue, but is a design issue in both tools (as they share the same backend service SqlToolsService).

The changes we are making will apply to all Azure SQL connections and are planned to be released together in both tools. And as we enable SqlClient integration with MSAL.NET, there's a greater chance things will be fragile (with regards to access tokens and persistence caching) with the way features and extensions are built and depend on access tokens and auth flows from NodeJS layer. We're trying our best here, but there's significant amount of testing needed across the product to ensure tool features and extensions' stability with the authentication layer shift, as it will affect every Azure SQL connection and related features.

Due to this reason, we will introduce this support in preview in March, so customers can enable the feature on-demand, and we can get feedback on experience and make incremental improvements along the way.

Hope that clarifies :)

[cheenamalhotra]

Since I read ADF in a few comments above, want to clarify we're home to Azure Data Studio and not Azure Data Factory.

Thanks!

[mgcox2]

Hi @seanmcne

It's one of our top priorities, and our plan stays intact for March release. I'm aware of the changes (being ex-SqlClient) that we need to use SqlAuthenticationProvider implementation to integrate with SqlClient's FedAuth flow.

As of now, Azure Authentication flow in both tools (ADS and VSCode's MSSQL extension) is governed with SqlConnection's 'AccessToken' property , and the entire token lifecycle had been driven by NodeJS layer (including refreshing token) using ADAL/MSAL NodeJs packages. ADS isn't designed similar to SSMS when it comes to Azure authentication, and that's why we have this huge design gap. To be clear, this is not just a Dataverse specific issue, but is a design issue in both tools (as they share the same backend service SqlToolsService).

The changes we are making will apply to all Azure SQL connections and are planned to be released together in both tools. And as we enable SqlClient integration with MSAL.NET, there's a greater chance things will be fragile (with regards to access tokens and persistence caching) with the way features and extensions are built and depend on access tokens and auth flows from NodeJS layer. We're trying our best here, but there's significant amount of testing needed across the product to ensure tool features and extensions' stability with the authentication layer shift, as it will affect every Azure SQL connection and related features.

Due to this reason, we will introduce this support in preview in March, so customers can enable the feature on-demand, and we can get feedback on experience and make incremental improvements along the way.

Hope that clarifies :)

Will this work for Mac Azure Data Studio installations as well? Thanks for your attention to this!

[cheenamalhotra]

Will this work for Mac Azure Data Studio installations as well? Thanks for your attention to this!

Yes, we have no platform specific reservations for adding this support.

[DynamicTouch]

For my understanding... A service principal can use the tds endpoint of PowerApps for crud actions form March/April?

[cheenamalhotra]

Authentication mode will still be MFA, so an Azure AD user account is needed. But we'll be able to connect with the dynamics endpoint instead of the default 'https://database.windows.net' when requesting access token.

I'll provide update here when this will be available in insiders, so we can have feedback from community before we release :)

[cheenamalhotra]

Hi everyone,

This feature support is now available in ADS insiders (download link) and can be enabled with below setting:

    "mssql.enableSqlAuthenticationProvider": true

It will be available in the ADS March 2023 release and is currently NOT enabled by default. I will proceed to close this issue as resolved, please open a new GitHub issue if you face any issues.

Thanks!

[mscottsewell]

This is still non-functional. I have the 'Enable Sql Authentication Provider' option checked. - I've added my MSFT account as a linked account - but when I try to connect to Dataverse using this account, It appears to be looking for my email address instead of my domain name in the MSAL cache. - I get the following error and it fails. "User account (my email address) not found in MSAL cache, please add linked account or refresh account credentials."

[cheenamalhotra]

Update: Syncing offline to investigate proper way of fixing username being used.

[MohammadShalaby]

I assume I will be the first person to put a comment here saying it worked :). I managed to execute the first SELECT statement after getting successfully connected to the CRM Dynamics 365 DB using Azure Data Studio after couple of failed attempts to authenticate.

Client Machine ........ Macbook Apple Silicon based - M1 Max MacOS .................... MS Ventura 13.2.1 Azure Data Studio ... https://azuredatastudio-update.azurewebsites.net/latest/darwin-arm64/insider

Here is a screenshot of the output

Now the two main issues I experience are:

1. I can't expand tables under the Database > same way I can on Microsoft SQL Management Studio
2. Columns names/tables names doesn't appear when I start writing their names

[cheenamalhotra]

Hi @MohammadShalaby

Thanks for testing out, if the issues with expanding look related to https://github.com/microsoft/azuredatastudio/issues/22272, I'd suggest adding details there or upvoting it. Or if it's a different issue, please open a new issue to get the right attention.

[MohammadShalaby]

@cheenamalhotra .. It is exactly #22272 .. Thx a million. I will upload my comment there :)