Reporting using data from Microsoft Dynamics 365 Online may be challenging. With Querona, you can use any BI tools to achieve that goal.

Connecting to Microsoft Dynamics 365 Online

Dynamics 365 services require OAuth authentication protocol use as described at Microsoft IT-Pro home page. The additional requirement is to register an application in Azure Active Directory and allow this app to authenticate against a Dynamics 365 instance to get data.

Register an Azure Application

Follow the steps below to obtain the OAuth values for your application, the OAuthClientId, OAuthClientSecret, and CallbackURL.

Step 1. Log in to Azure Portal

Log in to portal.azure.com.

Step 2. Add a new application to Azure Active Directory

In the left-hand navigation pane, select Azure Active Directory then App Registrations and click the New application registration button.

Step 3. Fill in the properties of the custom app

Name = The name of your custom application, for example, MyDynamics365app Application type = Web app / API Sign-in URL = A valid URL, for example, http://localhost:12345 Click the Create button. After creating the app, ensure that in the Properties section, the Multi-tenanted option is set to Yes and Reply URL is set to the URL you chose as Callback URL. Keep the values of the Application Id, and Homepage (also known as Sign-on URL) displayed.

Step 4. Create an application key

Go to the Keys section and create a new key for your app. You can select the option to create a key with a limited duration or one that never expires. After the key is saved, a value for the key is displayed once. Save this value for future use. Select Required Permissions and then click Add. Under Select an API, select the appropriate application like Microsoft Dynamics ERP or Microsoft Dynamics CRM and grant permissions as shown. Save your changes. The tricky part is to extract the OAuth access token and RefreshToken. These are required to persistently authenticate against the Dynamics 365 to execute queries and download data. To help you we have implemented two utility stored procedures: qua_get_oauth_authorization_url_d365 which Gets an authorization URL from the data source. The authorization URL can be used to generate a verifier required to obtain the OAuth token.

Step 5. Create in Querona a technical connection with basic setup

Create a connection. This connection is technical and is not to be used for production. Its only purpose is to allow for the utility stored procedure to work. Choose a descriptive name, for example, MyDynamics365SalesTechnicalConnection and fill connection properties as described below: OrganizationUrl = Set this to the organization URL of you Dynamics 365 system. OAuthClientId = Set this to the client Id / application Id displayed for the app you created in Azure. OAuthClientSecret = Set this to the value of the key you created in Azure. Save the changes and keep the connection name for future use.

Step 6. Get the authorization URL

In Querona go to Query and execute the qua-get-oauth-authorization-url-d365 stored procedure supplied with the proper parameters: 
EXEC [qua_get_oauth_authorization_url_d365] 'MyDynamics365SalesTechnicalConnection'
 , 'http://localhost:12345'
If the technical connection was set up correctly, the procedure returns the authorization URL. Save it for use in the next step.

Step 7. Navigate to the authorization URL in a web browser and save response URL

Use a web browser connected to the Internet and navigate to authorization URL you got in the previous step. Login to Azure if necessary and when prompted accept the application permissions presented. From the response URL, extract the value of code parameter and ignore the “session_state” parameter. Save the value from code for use in the next step.

Step 8. Get the OAuth access token and refresh token

In Querona go to Query and execute the qua_get_oauth_access_token_d365 stored procedure. 
EXEC [qua_get_oauth_access_token_d365] 'MyDynamics365SalesTechnicalConnection'
 , 'http://localhost:12345' 
 , '<paste here the value of _code_ parameter from previous step>'
The stored procedure returns a result set from which you need to copy the values of OAuthAccessToken and OAuthRefreshToken.

Step 9. Create and configure a new connection to Dynamics 365

Create a connection to selected Dynamics 365 service using the dedicated provider and set the following properties: OrganizationUrl = [ServerName]. Set this to the organization URL of you Dynamics 365 system. OAuthClientId = [Username]. Set this to the client Id/application Id displayed for the app you created in Azure. OAuthClientSecret = [Password]. Set this to the value of the key you created in Azure. OAuthAccessToken = Set this to the value of the **OAuthAccessToken** obtained in the previous step. OAuthRefreshToken = Set this to the value of the **OAuthRefreshToken** obtained in the previous step. InitiateOAuth = REFRESH

Step 10. Create a virtual database over Dynamics 365

Create a virtual database over the newly created connection and create a report using your favorite tool. Import metadata from Dynamics 365: Create a report in your favorite tool: That concludes this article. You may be also interested in reading: Keep virtualizing!