Connecting to Dynamics 365 TDS-enabled database with OLEDB Linked Server

Question

I want to run SQL Server queries on a Dynamics 365 database.

The administrators of the Dynamics database have enabled "TDS" for me - and now I can easily connect SSMS, using "Azure Active Directory - Password" authentication, and run SQL SELECTs against it. So far, so good!

But I also wish to create a Linked Server to the Dynamics database, and for this I understand that OLEDB is needed? Is that the correct approach?

Hoping so, I downloaded the latest OLEDB provider, MSOLEDBSQL19. My test query, using OLEDB, looks like this:

SELECT c.*
FROM OPENROWSET(
    'msoledbsql19'
    , 'Server={myServer};Database={myDatabase};UID={myUserId};Pwd={myPassword};Authentication=ActiveDirectoryPassword;'
    , 'SELECT name FROM dbo.account;'
    ) c;

But this results in the following error:

Failed to authenticate the user 'myUserId' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0xCAA20002; state 10
AADSTS65002: Consent between first party application 'db465503-d247-463e-8a13-95889346b742' and first party resource '00000007-0000-0000-c000-000000000000' must be configured via preauthorization - applications owned and operated by Microsoft must get approval from the API owner before requesting tokens for that API.

A search for "AADSTS65002" brings back "A developer in your tenant may be attempting to reuse an App ID owned by Microsoft. This error prevents them from impersonating a Microsoft application to call other APIs. They must move to another app ID they register in https://portal.azure.com."

I have no knowledge of Azure AD and Dynamics, so this is all a little baffling to me - why would trying a Microsoft OLEDB driver be "reusing an App ID"?

Does a change need to be made at my end, in SSMS that is?

Or is the problem with the Azure/Dynamics configuration? If so I would need to pass details of the solution on to the people administering that.

Thanks for any assistance :)

Answer 1

Failed to authenticate the user 'myUserId' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0xCAA20002; state 10
AADSTS65002: Consent between first party application 'db465503-d247-463e-8a13-95889346b742' and first party resource '00000007-0000-0000-c000-000000000000' must be configured via preauthorization - applications owned and operated by Microsoft must get approval from the API owner before requesting tokens for that API.

Cause: External admin on SQL server is not set

Resolution: Check the external (Azure AD) admin configuration.

To complete tasks such as authentication of users through security group membership or the creation of new users, your database requires permissions to read Azure AD. You must grant the database permission to read Azure AD for this to work.

To grant your database Azure AD read permission using the Azure portal you can refer this link

OR

To create a linked server for Dynamics in SQL Server Management Studio

1. Launch Management Studio and select your SQL Server instance.
2. Expand the Server Objects node in the Object Explorer pane, right-click on Linked Servers, and then click New Linked Server.
3. Configure your linked server as follows: • In the Linked server field, enter a name for your server. • Select Other data source under Server type. • In the Provider drop-down list, select Microsoft OLE DB Provider for ODBC Drivers. • In the Data source field, type the name of your DSN, for example, Devart ODBC Driver for Dynamics. You can also enter the ODBC Driver connection string in the Provider field.

In the Object Explorer Pane, the linked server will be listed under Linked Servers. You can now run distributed queries and gain access to Dynamics.

For more information refer this document