Getting Credentials for Microsoft Dynamics 365 CRM
You need Client Id, Client Secret, and Refresh Token to get access to the API.
Client Id and security settings can be set up according the official tutorial.
During registration, please enter Redirect URI as http://localhost as shown on the screenshot:
ClientId can be taken at Azure Active Directory admin center → App registrations → <Your application> → Overview as shown on the screenshot:
ClientSecret can be assigned on the page Certificates & secrets as shown on the screenshot. Click New client secret, and after you finish adding it, you can see the value of ClientSecret. Save it as it is shown only once:
Before getting authentication and refresh tokens, you need to get a temporary auth code. Open the following URL in your browser (replace <client_id> with one received earlier):
https://login.microsoftonline.com/common/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&redirect_uri=http%3A%2F%2Flocalhost&response_mode=query&scope=https%3A%2F%2Fadmin.services.crm.dynamics.com%2Fuser_impersonation+offline_access
Next, enter your credentials during authentication dialogs. At the end, you will be redirected to a page like the following:
http://localhost/?code=OAQABAAIAAACQN9QBRU3jT6bcBQLZNUj7g0wmOC24Qq_oBtBaxPnWo9u_f2RmxuRDyzCQ-6ui-QG6fCOO2GuTu6C2vly04q0zxohTmnerrymlkQhjtrc1RLcTm15WQhKwkHbrNtLp-5A7cAATpyBYitVRDEJNzvjtYtnzQHoxdaVa-JD3F1e1HiTo4raeHuuOfe3KFJY2Irk-vqS0eKRVjQt_WIE-gba8eO7tho7MCAR0i_y1-Se7D8nebJ6WOMFZAggLS_y3MMKXrU9NeDBG4Wzk0izN0jba2dDOMnWcJcA30FIs93oxg-vHjPcakkecMPRX8QAirXc9_mX7vd299bGe9z_QbjMA8QOs0xi3yXzKWO7u9AnGTR23sEfhZ-gwl0iuJU78EA7oLQXZQIy5FZQBRa-rVTLxY4e0KCPfKezUiSehE0JAATftrXWR6brIgR8SxU5HcJYabFanTPewfdpgvqoIWIyhrDJuRvG3VJ86XUyNeRn02rXM1gTSklUh431fWQQhOiLASdXx4fyXr6yg6FZh7hzfowFBnjsA8mr2Fm6Hc0UQIOS_t31IX_N8HxUyV19wjgS_k8m7dToJoF-9hqP4M1BiRtVy_8P39BnLXITvopwcORlj-6hT97u9iMJpZta7YRsklgugtH3Tm4V97cTfbxvNMylaGBk1fyhG3dF7fLDFuCAA&session_state=bded3c3a-0fed-4527-b3a8-c2ed57fea894
Copy the value of the code parameter (after code=.and before &session_state=) and create dummy connection/data source that will be used for getting your refresh token:
CALL SYSADMIN.createConnection (
name => 'www',
jbossCliTemplateName => 'ws'
);;
CALL SYSADMIN.createDatasource (
name => 'www',
translator => 'ws'
);;
Take the code from the snippet below and edit the first 3 declared variables with your actual <CLIENT_ID>, <CLIENT_SECRET>, and <AUTH_CODE>, respectively, then run it:
BEGIN
DECLARE string client_id = '<CLIENT_ID>';
DECLARE string client_secret = '<CLIENT_SECRET>';
DECLARE string code = '<AUTH_CODE>';
DECLARE string grand_type ='authorization_code';
DECLARE string scope = 'https://admin.services.crm.dynamics.com/user_impersonation offline_access';
DECLARE string request = 'client_id=' || client_id || '&redirect_uri=http%3A%2F%2Flocalhost&grant_type=' || grand_type
|| '&scope=' || URLENCODE (scope, 'UTF-8')
|| '&code=' || code
|| '&client_secret=' || URLENCODE (client_secret, 'UTF-8');
SELECT
j.expires_in,
j.ext_expires_in,
j.access_token,
j.refresh_token,
j.token_type,
j.scope,
TO_CHARS (a.result, 'UTF-8')
FROM www.invokeHttp (
action => 'POST',
endpoint => 'https://login.microsoftonline.com/common/oauth2/v2.0/token',
requestContentType => 'application/x-www-form-urlencoded',
request => request
) a,
OBJECTTABLE (LANGUAGE 'javascript' 'JSON.parse (tokens);'
PASSING
CAST (TO_CHARS (a.result, 'UTF-8') AS string) AS tokens
COLUMNS
expires_in integer 'dv_row.expires_in',
ext_expires_in integer 'dv_row.ext_expires_in',
access_token string 'dv_row.access_token',
refresh_token string 'dv_row.refresh_token',
token_type string 'dv_row.token_type',
scope string 'dv_row.scope'
) AS j;
END;;
The SQL code will return the tokens as shown on the screenshot: