How to Cooperate between Office 365 and Cloud-based Workflows

Hi, There!

I have once tried to summarize “the situation of federation between Office 365 and cloud-based workflow” to the extent I could comprehend at the time. However, I was not possible to deeply investigate about invoking of Office 365 APIs then.
* Incidentally, as for the authentication collaboration with Azure AD (single sign-on), I have been able to deal with since before, as described in another article.

In this article, I am going to describe the contents that I examined whether it is possible to correspond with API practically, focusing on cooperation with SharePoint Online (such as file output from Questetra to SharePoint Online etc.) (Since the authentication part is processed with Azure AD, it is supposed to be applied also to services to be authenticated by Azure AD, such as Dynamics 365 or Office 365 besides SharePoint Online.)
By the way, I spent a hard time to reach the material I needed since there were too many documents with regard to the Office 365 API and AzureAD, and could not find out the materials summarized in sub-theme, like this article.

Authentication for calling the API of Office 365 (SharePoint Online), you will use one of the following.

New app registration: at Application Registration Portal (apps.dev.microsoft.com)
Authorization Endpoint: https://login.microsoftonline.com/{tenant}.onmicrosoft.com/oauth2/v2.0/authorize
Token Endpoint：https://login.microsoftonline.com/{tenant}.onmicrosoft.com/oauth2/v2.0/token

New app registration: at AzureAD
Authorization Endpoint:：https://login.microsoftonline.com/{tenant}.onmicrosoft.com/oauth2/authorize
Token Endpoint：https://login.microsoftonline.com/{tenant}.onmicrosoft.com/oauth2/token

New app registration:https://{tenant}.sharepoint.com/_layouts/15/appregnew.aspx
Authorization Endpoint：https://{tenant}.sharepoint.com/_layouts/15/OAuthAuthorize.aspx
Token Endpoint：https://accounts.accesscontrol.windows.net/{tenant}.sharepoint.com/tokens/OAuth/2

The registration method of the application is different depending on each method, and the usable API is also different. In addition, it seems that it corresponds to four grant_type (Authorization Code/Client Credentials/Password/Implicit) of OAuth 2, respectively.

Incidentally, ACS seems to have been prepared for SharePoint before AzureAD first appeared. Since it is an old method, I suppose that it is better not to use it if possible. Moreover, although AADv2 is the newest method, it seems that it is in the middle of proceeding of correspondence currently, and there seems to be quite a lot of API processes that can not be executed unless it is not AADv1 at the moment…

There are some that can be authenticated via Questetra, and others that can not be done due to specification limitations.

In case where grant_type is Authorization Code, it can be dealt with “OAuth 2.0 setting” setting only, but it is restricted only when never-expiring access tokens can be issued. Incidentally, there is no problem since Azure AD is capable of issuing never-expiring access tokens.

In case where grant_type is Client Credentials/Password, it can be corresponded with using “Script task”. However, the available APIs are limited in the case of Client Credentials, since it is an authentication that is not associated with UserContext. In the case of Password, since the password is held at API accessing side, which is Questetra, I believe that there is no problem in this case. (In a mean that, normally, it is a problem that passwords are passed to the API accessing side in grant_type of Password, but this point won’t be a problem.)

The following is a summary of available and not.

• AADv2、Authorization Code
  Possible to get an access token with “OAuth 2.0 setting”, and calling APIs as well. However, some APIs require specification of values in the http header, so it is not possible to call APIs those which conflict in Questetra’s constraints (currently, those begin with “X-“. “BoxApi”, “Dropbox-API-Arg” are only allowed). In such cases, it is able to call the APIs by interposing an intermediate program.
• AADv1、Authorization Code
  Since the type of expire_in that is returned from Azure AD is inconsistent to RFC, access token can not be acquired with “OAuth 2.0 setting”. It is able to call the APIs by interposing an intermediate program.
• AADv1、Client Credentialns/Password
  It is possible to get an access token with “Script Task”. However, cannot call API using that token, since Questetra has a limit on the number of characters that can be set in the http header (currently up to 1000 characters), and most of access tokens exceeds 1000 characters. It is able to call the APIs by interposing an intermediate program.
• ACS、Authorization Code
  Cannot get the access token with “OAuth 2.0 setting since it is not possible to specify resource parameter in the token end point URL. It is able to call the APIs by interposing an intermediate program.
• ACS、Client Credentialns/Password
  Although I do not try, I suppose it conflicts with the limit of the number of characters in the http header.

From the above results, there are cases for limited purposes where it is possible to process directly from Questetra alone with AADv2, but depending on the application, support by intermediate program is necessary.

As an intermediate program, EAI tools/data linkage tools such as DataSpider Cloud etc. are capable, as well as Microsoft Flow, Logic Apps, Azure Functions, AWS Lambda plus Amazon API Gateway etc. are also considerable.
* This time I tried with DataSpider Servista, the on-premise version which is the origin of DataSpider Cloud. I could confirm the insertion of data into Excel Online, etc., in addition to cooperating with SharePoint Online.

There are also possibility that future improvements of Questetra will broaden the range that can be achieved without an intermediate program.

If you have any questions, please feel free to Contact us.

Reference

• AADv2 Official Document