CODEWITHSUNDEEP
ssisazure-active-directorydynamics-crmsql-server-data-toolspower-platform
Vic
Vic

Reputation: 472

SSIS connection to Dynamics CRM Online via the TDS endpoint

The Dynamics CRM (online) instance has the TDS endpoint enabled (both port 1433 and 5558), I can connect to it and execute SQL commands from SSMS.

Now I'm trying to connect to it from SSIS following the instructions from Kingswaysoft. I get this error when creating an ADO.Net connection from SSIS:

Test connection failed because of an error in initializing provider. One or more errors occurred.

I noticed that when connecting succesfully from SSMS I'm using Azure Active Directory - Universal with MFA authentication, but in SSIS only Azure Active Directory - Password is available. When trying to connect with AAD-Password in SSMS I get:

Cannot connect to xyz.crm3.dynamics.com. ADSTS53001: Device is not in required device state: domain_joined. Conditional Access policy requires a domain joined device, and the device is not domain joined.

My understanding is that the AAD-Universal authentication requires interactive login and AAD-Password is non-interactive. So it makes sense that SSIS only offers AAD-Password, as the packages are mostly executed unattended.

All our users are currently set for MFA. Is there any way to still use the TDS endpoint with SSIS?

Upvotes: 2

Views: 971

Answers (1)

JayakrishnaGunnam-MT
JayakrishnaGunnam-MT

Reputation: 1886

There is no other way available. As per the error, there is a conditional access policy which is allowing the connection form domain joined machine with MFA enabled. May be you can check with admin to give permissions for one user to connect without MFA or conditional access policy and see if that works(AAD-Password).

Upvotes: 1

Related Questions