Use CRM Dynamics Online odata endpoints in SSIS

Your reporting team is today retrieving CRM entities directly from On premise SQL server through SSIS packages. Quick & efficient.
Your company is about to move to CRM Dynamics Online

Disclaimer

This blog post was possible with help of Basile (SSIS) & Clément (CRM / C# / SSIS)

uh-oh, no more direct SQL access in the cloud version. No problem, your SSIS colleagues told you that they've seen an odata component which could be setup for CRM Dynamics online. Plug & Play they said.

Be prepared for a long journey including C#, Azure Active Directory, SSIS, XML Transformation and pain (we are avoiding a certificate part for being nice).
With confidence, you check with your colleague how is working the odata component, and then you get a nice error

For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method. (System.Xml)

Googling the error message brings nothing good ¹ ² ³ ⁴: no solution, except buying a third party product. Not the plug & play solution we were expecting.

The main reason is that you need a special authentication with your claims/ADFS services for getting a Bearer token and being able to call the odata endpoint. Unfortunately the SSIS odata component isn't handling this authentication scenario.

Get the Bearer token from CRM Dynamics Online

After some research, we understood that we have to start looking at Azure Active Directory in order to get this token. A look into Azure-Example ⁵ github bring a lot of interesting examples, especially when you're not used to claims authentication. And especially this example ⁶

First of all, let's find this clientId value by browsing to Azure Portal (at the post time, still in old portal)

then Applications looking for having the clientId of CRM Dynamics application.
suddenly you realize there is no configure tab for getting the clientId ...

The correct approach is you need to create a new application which will have delegate permissions on CRM Dynamics Online.
In main Applications screen

Add
Add an application my organization is developing
Chose Native Client Application type as we are planning calling it from SSIS
Fill in Redirect Url with your CRM Online instance address
Configure screen brings you a clientId you were looking for
And finally you need to give this appplication permission on CRM Online

You're done for Azure Active Directory Service, before doing anything in SSIS, let's check if we can get in C# the Bearer token

        static void Main(string[] args)
        {
            string clientId = "clientId";
            string redirectUrl = "https://XXXXXXX.crm4.dynamics.com";

            // Authenticate the registered application with Azure Active Directory.
            AuthenticationContext authContext =
                new AuthenticationContext("https://login.windows.net/common", false);

            var userCredential = new UserCredential("email address of one user", "password");
            var result = authContext
                .AcquireTokenAsync(redirectUrl, clientId, userCredential)
                .Result;

            using (HttpClient httpClient = new HttpClient())
            {
                httpClient.Timeout = new TimeSpan(0, 2, 0);  // 2 minutes
                httpClient.DefaultRequestHeaders.Authorization =
                    new AuthenticationHeaderValue("Bearer", result.AccessToken);

                var response = httpClient.GetAsync("https://xxxxxxxxx.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/CompetitorSet").Result;
                Console.WriteLine(response.Content.ReadAsStringAsync().Result);

            }

        }

Part 2 ⁷ will address on how using this XML in SSIS