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 :)