Blog: Mr. Mathiesen

MSCRM and OData from ConsoleApp/Script without annoying web login form

March 03, 2016.

Background

I’m preparing a talk for the upcoming Office 365 Saturday event, hosted by the Danish SharePoint User Group where I will be showcasing the interaction between MS CRM and SP online. As Microsoft Partners we have the possibility to create Demos for these kind of events where we get both CRM and SP instances but there is no Azure AD. The usually next step would be to create a new subscription and use a personal credit card (not billed but still needs to be added). The credit card thing is always a big issue for me as if I forget to change the password for some of the admin users I might get billed a shit load of money for somebody Bitcoin crunching on my behalf.

I made a few Google searches and I came across kb3133137, see References for more info, where I discovered a magick GUID (ClientID) that can be used for all kind of custom applications in order to access MS CRM OData interface. I made a simple prototype in C# and here are the results:

C# Console Application

Program.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

using System;
using System.Linq;

using Microsoft.IdentityModel.Clients.ActiveDirectory;

using CRM = ConsoleApplicationOData.Microsoft.Dynamics.CRM;

namespace ConsoleApplicationOData
{
  class Program
  {
    static void Main(string[] args)
    {
      var oauth2 = @"https://login.microsoftonline.com/common/oauth2/authorize";

      // Use clientId of Excel Power Query add-in/Microsoft Power BI 

      // MSDN - https://support.microsoft.com/en-us/kb/3133137

      var clientId = @"a672d62c-fc7b-4e81-a576-e60dc46e951d";

      var resource = @"https://ORG_GOES_HERE.api.crm4.dynamics.com";
      // Works with custom domains as well. Ex: usr@delegate.dk

      var usr = @"admin@ORG_GOES_HERE.onmicrosoft.com";
      var pwd = @"pass@word1";

      var authenticationContext = new AuthenticationContext(oauth2);

      var authenticationResult =
        authenticationContext.AcquireToken(
          resource, clientId, new UserCredential(usr, pwd));

      var token = authenticationResult.AccessToken;

      var xrm = new CRM.System(new Uri(resource + "/api/data/v8.0/"));

      xrm.SendingRequest2 += (s, e) =>
      {
        e.RequestMessage.SetHeader("Authorization", "Bearer " + token);
      };

      var query =
        from a in xrm.Accounts
        select new { a.Accountid };

      Console.WriteLine("Nr. of accounts: " + query.ToList().Count);

      if (System.Diagnostics.Debugger.IsAttached) Console.ReadLine();
    }
  }
}

Producing the following output:

Nr. of accounts: 2

Note: You will need to generate the OData client with the OData v4 Client Code Generator, see References for more info, and you will only need to add one NuGet package: Microsoft.IdentityModel.Clients.ActiveDirectory to your project

Pretty nifty right? As I have blogged about using the F# OData TypeProvider with MS CRM in the past, I was never able to get data out from the online instance though, I decided to update my initial script and now it works perfectly with the magick GUID :)

F# Script

You will need to retrieve Microsoft.IdentityModel.Clients.ActiveDirectory from NuGet:

DG.MSCRM.GetNugetAzureAD.cmd

:: Download latest Nuget from: https://dist.nuget.org/index.html
:: and ensure that it's location is stored in your User or System 
:: Environment PATH

nuget install Microsoft.IdentityModel.Clients.ActiveDirectory^
  -Version 2.23.302261847 -ExcludeVersion -OutputDirectory "packages"

pause

DG.MSCRM.OData.fsx

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

#r @"System.Data.Services.Client"
#r @"FSharp.Data.TypeProviders"

#I @"packages/Microsoft.IdentityModel.Clients.ActiveDirectory/lib/net45"
#r @"Microsoft.IdentityModel.Clients.ActiveDirectory"

open System
open System.Net
open System.Data.Services.Client

open Microsoft.FSharp.Data
open Microsoft.IdentityModel.Clients.ActiveDirectory

[<Literal>]
let oauth2 = @"https://login.microsoftonline.com/common/oauth2/authorize"

// Use clientId of Excel Power Query add-in/Microsoft Power BI 
// MSDN - https://support.microsoft.com/en-us/kb/3133137
[<Literal>]
let clientId = @"a672d62c-fc7b-4e81-a576-e60dc46e951d"

[<Literal>]
let resource = @"https://ORG_GOES_HERE.api.crm4.dynamics.com"
// Works with custom domains as well. Ex: usr@delegate.dk
[<Literal>]
let usr = @"admin@ORG_GOES_HERE.onmicrosoft.com"
[<Literal>]
let pwd = @"pass@word1"

let authenticationContext = new AuthenticationContext(oauth2)

let authenticationResult =
  authenticationContext.AcquireToken(
    resource, clientId, new UserCredential(usr, pwd))

let token = authenticationResult.AccessToken

[<Literal>]
let url = @"https://ORG_GOES_HERE.api.crm4.dynamics.com"
[<Literal>]
let odata = url + @"/XRMServices/2011/OrganizationData.svc/"
[<Literal>]
let csdl = __SOURCE_DIRECTORY__  + @"/odata/OrganizationData.csdl"

// WebAPI (OData4) is not supported by the F# OData TypeProvider
type Xrm = 
    TypeProviders.ODataService<
        ServiceUri = odata,
        LocalSchemaFile = csdl,
        ForceUpdate = false>

let ctx = Xrm.GetDataContext()

ctx.DataContext.SendingRequest.Add(
  fun e ->
    e.RequestHeaders.Add(name = "Authorization", value = "Bearer " + token))

query { for a in ctx.AccountSet do
        where (a.Name.Contains("e"))
        select (a.AccountNumber, a.AccountId)
        skip 1
        take 1 } 
|> Seq.length
|> printfn "Nr. of accounts: %i"

Producing the following output:

> 
Nr. of accounts: 1
val it : unit = ()

Note: The TypeProvider doesn’t seem to understand OData4 specifications

Conclusion

It’s actually really usefull that we are now able to create non-human interactive applications that can also gain the power of the OData interface instead of using the heavy and tradicional WSDL/SOAP interface. See this stackoverflow answer: Simple explanation about SOAP and REST using Martin Lawrence/Big Mama as data to point out the benefits:

References: