Connecting to Microsoft Dynamics 365 CRM via SQL

You can use SQL to connect to Microsoft Dynamics 365 CRM in the CData Virtuality Studio or the Code Editor in the Web UI.

Pre-requisites

You need the Client Id.

How to Connect

Run the following statements in the CData Virtuality Studio or the Web UI (remember to enter your credentials):

CODE

CALL SYSADMIN.createConnection (
    name => 'dynamics_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'EndPoint=https://{endpoint}/api/data/v<version>,ClientId=&#60;ClientId&#62;,ClientSecret=&#60;ClientSecret&#62;,RefreshToken=&#60;RefreshToken>,AccessTokenEndpoint=https://login.microsoftonline.com/common/oauth2/token'
);;

CALL SYSADMIN.createDataSource (
    name => 'dynamics_ws',
    ,translator => 'dynamics'
    ,modelProperties => 'TenantId=<TenantId>,OnlyEndpointsCSV=<OnlyEndpointsCSV>,OnlyEndpointsFile=<OnlyEndpointsFile>,ExcludeEndpointsCSV=<ExcludeEndpointsCSV>,ExcludeEndpointsFile=<ExcludeEndpointsFile>,networkTargetTable=<networkTargetTable>,noInitMetadataExceptions=<noInitMetadataExceptions>'
    ,translatorProperties => ''
);;

Replace <ClientId>, <ClientSecret> and <RefreshToken> with your values:

<AccessTokenEndpoint> is typically https://login.microsoftonline.com/common/oauth2/token
<Endpoint> coincides with the host of the dynamics web interface that was received for cloud version of Microsoft Dynamics 365 on registration and may look like <your_company>.crm.dynamics.com or <your_company>.crm4.dynamics.com.