Connecting Dynamics 365 v9.0 from Power BI

Though connecting to Dynamics 365 from Power BI Desktop is same old process , the As-Is Dynamics 365 9.0 Web API Url would not let you connect. A small change to the D365 API Url needs to be done when attempting a connection from Power BI. The 9.0 in the endpoint Url needs to be replaced with 8.1

Original API :-  https://organizationname.api.crm.dynamics.com/api/data/v9.0/ 

Url to be used :- https://organizationname.api.crm.dynamics.com/api/data/v8.1/ 

This would help you to prevent the error

Unable to connect

We encountered an error while trying to connect.

Details:"Odata: The feed's metadata document appears to be invalid."

The same is applicable when connecting using Power Query , Excel etc.

Note:  This approach has been tested on Power BI Desktop version -2.54.4970.961 64-bit (January 2018) & Dynamics CRM Version -1612 (9.0.1.510) (DB 9.0.1.510) online.

Implementing Authorization in Odata V4 Source API Using Query String Parameters for D365 Virtual Entities

With increasing adoption of Dynamics 365 v 9.0 , Virtual Entity is on of the feature that users would want to leverage for addressing their Integration requirements.

"Odata V4 Data Source" is the out of box data source available that can be tied to the D365 Virtual Entities and these data sources need an Odata V4 Web API.  The key question that arises for the developers while using this feature is - Authentication.  Though D365 does not give us flexibility to handle various authentication mechanisms it provides us the ability to handle Authorization using Query String Parameters.

Prerequisite:

You need to have an existing Odata Web API and a Configured Virtual Entity on your Dynamics 365 9.0 System.

If you need help in Odata Web API you can download sample code here and the detailed steps for creating and configuring Virtual entity are available here.

Steps:

1. Go to your Odata V4 Web API Source code and add a new class "CustomAuthorization.cs" with definition as below. The value of the Key ( i.e. Token ) can be stored in Azure key Vault .

The more complex the authorization rules the more secure the API would be. An additional call to CRM could be made to validate any parameters from a custom configuration Entity.

//Sample Code Snippet
using System.Linq;
using System.Net.Http;
using System.Web.Http;
using System.Web.Http.Controllers;

//This namespace to be referenced in the using section of the controller.
namespace POC.Authorization
{
    //Give any desired name to your class instead of CustomAuthorization to be later used as a header attribute for your controller.
    public class CustomAuthorization: AuthorizeAttribute
    {
        public override void OnAuthorization(HttpActionContext actionContext)
        {
            
            var queryString = actionContext.Request.GetQueryNameValuePairs().ToDictionary(x => x.Key, x => x.Value);
            try
            {
                //This value corresponding to the "token" key cane be stored in Azure Key Vault and retrieved in runtime.
                if (queryString["token"].ToString() != "816e49a0-b3f1-4754-b659-e9fe3f34f505")
                {

                    //Write your custom code here ex:- Trace Logs , Throwing exceptions etc.
                    actionContext.Response =  new HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
                }

            }
            catch
            {
                //Write your custom code here ex:- Logs , Exception handling etc
                actionContext.Response = new HttpResponseMessage(System.Net.HttpStatusCode.Forbidden);
            }

        }

    }
}

2. Reference this namespace in your controller class and add [CustomAuthorization] i.e. the Class name created above as header to your controller class.

using POC.Authorization;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Web.Http;
using System.Web.OData;

namespace POC.Controllers
{
    [CustomAuthorization]
    public class EntitiesController : ODataController
.....

3. Build and Run this project to your local and Verify the data from Browser by navigating to your controller.

Ex:- http://localhost:32097/odata/<<Your Entity Name>> ?token=816e49a0-b3f1-4754-b659-e9fe3f34f505

4. Publish your project to your website.

5. Update Query String Parameters on your Odata V4 Source in D365

6. Go to Advance Find and query your Virtual entity and you should be able to get the desired result.

Note: D365 Also gives us the flexibility to send the Request Parameters through header , Clicking on the Parameter Type would change the type to Header from Query String. And D365 allows us to add up to 10 Request Parameters.

Sample code for creating Odata V4 Source API for D365 Virtual Entities

The Sample source code for creating an Odata V4 Web API can be downloaded from the below link -  Download Sample Code

How to Enable up Microsoft . Live id Authentication in Dynamics CRM / Dynamics 365 Portals

Typically the portal users are either external users i.e. either Partners or customers , One of the most popular external authentication that users prefer is Live Id Authentication

1. Navigate to Portals => Site Settings.

2. Update the Site Settings for the Microsoft Client id - Authentication/OpenAuth/Microsoft/ClientId and Secret Key - Authentication/OpenAuth/Microsoft/ClientSecret  as well as 'Authentication/Registration/ExternalLoginEnabled' parameter to true.

                                                

Below are the steps to get the Client id and Secret key.

1. Navigate to https://apps.dev.microsoft.com/?deeplink=/appList. This is a new app registration portal where you can manage your Microsoft apps.

2. Sign in with either a personal or work or school Microsoft account. If you don't have either, sign up for a new personal account.

3. Click Add an app, and give it a name. The portal will assign your app a globally unique Application Id - This will be the Client Id for the Site settings

                                   

4. Create an Application Secret here as well that you would update in the site settings.

                                     

5. Next, add the Platforms that your app will use. Choose the app type as Web and Redirect Url post fixed with /singin-microsoft ex:-https://xxxxxxxxxxxx.microsoftcrmportals.com/signin-microsoft
and update the Home Page Url as https://xxxxxxxxxxxx.microsoftcrmportals.com

The Urls are given just for illustration purpose but you need to give your actual application Urls.

                                    

6. Use the Save button to Save the changes. Go back to the portal and Refresh. Read More

Note: When you Login for the first time you would get an additional screen after authenticating . You should say 'Yes' on that page and you will be redirected to your portal.

If the redirect Url is not give correctly, You would get the error message as below.

"https://login.live.com/err.srf?lc=1033#error=invalid_request&error_description=The+provided+value+for+the+input+parameter+'redirect_uri'+is+not+valid.+The+expected+value+is+'https://login.live.com/oauth20_desktop.srf'+or+a+URL+which+matches+the+redirect+URI+registered+for+this+client+application.&state=i6oTIsozNoxHE1Z4ohHbVWMmtTBDL-5u_OkJsTRdSAUKqM2GiXAffMRW7wMBgCb1dD5QQKHrLJyHbFVpmD8UhhQiGKp6qZCldxuREWt1WsSPy36ck7ZRTzj0HPTy5i3Fdr82yvbFppzXjTLwFYH20LQc-YppgL4qDMg7fFtOJmFtu-XR0V7SA-hsbMJFOqlvUZ6Jt_R_18Q0-qrt-AEp-lU1DG2OOvPFYITK8s39BfdArKjqgvxSgXwGObeWFmCfKyWS1JUkR5NdVaSuDs3boiWVRouxnFCme5pmbgJ1Dkw"

"We are unable to complete your request. Microsoft account is experiencing technical problems.Please try again later"

Creating a Simple JavaScript application to use Dynamics CRM Online Web API

This post is in extension to the msdn blog that gives a walk through on Registering and configuring simple single page application with adal.js.

This referenced blog has been written with reference to old Azure portal ( manage.windowsazure.com ) and there has been a change on the Azure side ( portal.azure.com ). This post explains it with respect to the new azure portal.

Prerequisites

• Microsoft Dynamics CRM Online 2016 Update
• You must have a Microsoft Dynamics 365 (online) system user account with administrator role for the Microsoft Office 365.
• A Microsoft Azure subscription for application registration. A trial account will also work.
• Microsoft Visual Studio 2015

Sample Code

The sample code is available in the original blog as well as here ( with minor tweaks). Note that the clientid to be passed in the code will be the Application ID that you would get in the Registration processs below.

Register the application

1.  Sign in to Azure - https://ms.portal.azure.com , You must use an account in the same Office 365 subscription (tenant) as you intend to register the app with. You can also access the Microsoft Azure portal through the Office 365 admin center by expanding the ADMIN item in the left navigation pane and selecting Azure AD.

 

2. Click on New Application registration , Provide any Name , Choose the Application Type as Web App/Web API and Sign-on URL that later translates to Home Page Url also called as Redirect Url is the URL which the user should be redirected to after they sign in and this field can be changed at later point of time.

3. Once created, Click on Manifest and change the property from false to true "oauth2AllowImplicitFlow": true and Save the Manifest file , Else it would throw the error as below

"adal error occurred: aadsts70005: response_type 'token' is not enabled for the application"

4. Add permissions to the Dynamics CRM Online Web API as below, Else it would throw the below error.

"ADAL error occurred: AADSTS65001: The user or administrator has not consented to use the application with ID 'ce2b1c70-06e3-430f-8672-41ef6d03da2d' named 'CRMJavascript'. Send an interactive authorization request for this user and resource. Trace ID: 7b81cedf-dec2-4e68-a91f-ecdb192a0800 Correlation ID: a559a661-4909-4c2f-a629-5edef06e3df8 Timestamp: 2018-02-07 02:41:21Z"

Below screen gives is the information on Grant Permissions screen.

Sample code for creating a Single Page JavaScript application to Fetch Data from CRM Online using Web API

Create a web application project

1. Using Microsoft Visual Studio 2015, create a new ASP.NET Web Application project and use the Empty template. You can name the project whatever you like.

   You should be able to use earlier versions of Microsoft Visual Studio as well, but these steps will describe using Visual Studio 2015.
2. Add a new HTML page named SimpleSPA.html to the project and paste in the following code:

   HTML

   <!DOCTYPE html>
   <html>
   <head>
    <title>Simple SPA</title>
    <meta charset="utf-8" />
    <script src="https://secure.aadcdn.microsoftonline-p.com/lib/1.0.0/js/adal.min.js"></script>
    <script type="text/javascript">
     "use strict";
   
     //Set these variables to match your environment
     var organizationURI = "https:// [organization name].crm.dynamics.com"; //The URL to connect to CRM (online)
     var tenant = "[xxx.onmicrosoft.com]"; //The name of the Azure AD organization you use i.e. Active directory tenant. 
     var clientId = "[client id]"; //The applicationid you got when you registered the application
     var pageUrl = "http://localhost: [PORT #]/SimpleSPA.html"; //The URL of this page in your development environment when debugging.
   

Sample Code for creating D365 Custom Data Provider

The below code helps in Writing the Retrieve Multiple plugin for a Custom Data Provider that fetches the data from SQL via a Web API


Sample Code :

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Query;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Threading.Tasks;