Microsoft Graph is a RESTful web API that enables you to access various Microsoft Cloud service resources. In this blog post, we will look at working with Microsoft Graph API using OData Connection Manager and Premium OData components offered in our SSIS Productivity Pack product. This post will demonstrate how to query the SharePoint data with Microsoft Graph API using the Premium OData Source component.
Configure OData Connection Manager
To connect to Microsoft Graph through the OData web service, the first step is to configure the OData Connection Manager to establish the connection to Microsoft Graph.
The Base URL for Microsoft Graph should have an address in the following format:
- https://graph.microsoft.com/{version}
Microsoft Graph currently supports two versions: v1.0 and beta. The v1.0 service endpoint includes APIs that are generally available, while the beta one includes APIs that are under preview. It’s always recommended to use the generally available API for production use.
Once the Base URL is specified, the next step is to configure the authentication method to connect to Microsoft Graph. This can be done in the Authentication tab. Authenticating with the Microsoft Graph API should be done using an OAuth2 bearer token option. There are multiple types of OAuth2 authentication supported in Microsoft Graph API, the authorization code grant (access on behalf of a user) and client credentials (access as the app and not on behalf of a user) grant are supported.
Authenticating on behalf of a user
You would use the OAuth2 authentication method. it is required to register an Azure Active Directory application in the Azure portal so that your app can be integrated with the Microsoft identity platform and make Microsoft Graph API service calls. Once the app has been created, you would need to grant the app the necessary API permissions to have access to the Graph data. We are accessing SharePoint sites data in this blog post, so we need to make sure the Graph delegated permission “Sites.Manage.All” is added to the app.
Once you have the Microsoft AAD app registered and configured, you can come back to the connection manager, and set the authentication mode to OAuth 2 in OData Connection Manager, from where you should be able to launch the OAuth Token Generator that is shipped within the SSIS Productivity Pack software. You should already have all the required information to fill in the OAuth Token Generator form. As we have just mentioned, the OAuth Token Generator is a tool shipped along with all other components within the SSIS Productivity Pack product, and it can be launched from the OData or HTTP connection manager provided by KingswaySoft. Based on relevant Microsoft documentation on Graph API, the following should be what it looks like in the OAuth Token Generator after everything has been filled:
|
Parameter |
Value |
|
Sign In URL |
https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/authorize |
|
Client Id |
The application ID of the AAD app. |
|
Client Secret |
The application secret in the AAD app. |
|
Scope |
offline_access Sites.Manage.All |
|
Redirect URI |
The redirect_uri configured in the AAD app |
Click the Next button to go to the next page and click either the Authorize In App… or Authorize In Browser… button to launch the authorization page. From there you would log in and provide your consent to the permissions the app is requesting. Once done, you will be redirected to the redirect_uri web page that you have set up in the Microsoft AAD app. The URL will be appended with an authorization code in its code query string parameter. You would now copy the entire URL back to the pop-up dialog in OAuth Token Generator if you are using the Authorize In Browser mode, and then you will be navigating to the Request Tokens page from where you can complete the process of obtaining a token for connection purposes.
In the Request Tokens page, Request Token URL should be “https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/token” for Microsoft OAuth authentications. Fill in the Request Tokens URL and click the Get Tokens button to request an access token. If everything goes well, you should now have received the OAuth access_token along with a refresh_token. Now it is a good time to save the entire token as a file to your local file system, which can be used for your connection manager later. When doing so, you will be prompted to provide a password to protect the sensitive fields in the token file.
Once the OAuth2 token file has been successfully generated and specified in the OData connection manager, the connection manager will automatically manage the lifecycle of the token, including the automatic refresh of the token when the current access token has expired. This provides you the maximum convenience for an uninterrupted integration process when it is used for unattended SSIS job executions. You can request the token file from one computer system, but upload it to another server for runtime execution.
Authenticating without a user
Microsoft Graph supports a different authentication flow, which is the OAuth2 client credential. When doing so, our software will try to obtain the access token from Azure AD using the client credentials provided in the connection manager, which would then make Microsoft Graph service calls as the app identity. When using the client credential authentication flow, it’s required to add application permissions in the AAD app. The screenshot below shows the permission configuration that is required in order to access SharePoint sites data. Essentially, you will need the “Sites.Manage.All” application permission.
Once the app permission has been properly configured, you can now configure the OData Connection Manager in the way that it should request an access token through OAuth 2.0 client credentials authentication flow using the Azure OAuth authentication mode.
The Azure OAuth authentication mode is designed to work with various Azure services, including the support of making a POST service request to the /token identity platform endpoint to acquire an access token using client_credential grant type with a shared secret or a certificate. As illustrated above, you would simply provide the Azure tenant id, client id, and client secret of the AAD App, with a scope value of "https://graph.microsoft.com/.default" to access the Graph resource data.
After the authentication has been properly configured, we would navigate to the OData Settings tab and choose “Generic – ‘Next Link Paging’” service implementation because Microsoft Graph uses the ‘@odata.nextLink’ value in response for pagination.
Finally, if everything has been set up correctly, click the Test Connection button at the bottom left of the editor to make sure that the connection works. If everything goes well, you should receive a “Test connection succeeded” message as shown below.
Configuring OData Source to read Graph data
After successfully creating and testing our OData connection, we are now ready to actually interact with the API to facilitate reading and writing Microsoft Graph data. This can be achieved using the Premium OData Source component which is offered in the SSIS Productivity Pack product. The Premium OData Source component is an enhanced OData Source component that supports working with entity, collection of entities, bound functions and unbound functions. It does a lot more than alternative solutions that might be available.
Get SharePoint Site Id in Graph API
According to Microsoft Graph API documentation, the SharePoint siteId format is a composite of the <HostName,SPSite.ID,SPWeb.ID>. It’s easy to work with the root site as the “root” identifier can be used as the siteId to reference the root site. To get the siteId value for other sites if you don’t have the SPSite.ID and SPWeb.ID values but you know the site name, you can use the Search endpoint in Premium OData Source component to find sites that match with the provided search term. The Premium OData Source component configuration below searches across a SharePoint tenant for sites that match with the provided keyword “TestSite”.
Get SharePoint Site Lists in Graph API
Once the SharePoint siteId is retrieved, we can easily get the SharePoint lists in the given site in the Graph API. OData supports getting related entities for a particular relationship. The screenshot below shows the example of retrieving all SharePoint lists for a particular site.
Get SharePoint Site List Items in Graph API
Similar to get SharePoint lists on a site, we can also get the related items of a given list by configuring the Premium OData Source component like the following screenshot:
As shown in the screenshot above, we are also expanding the ‘fields’ resource to retrieve the related list item metadata for a list item.
Conclusion
In this blog post, we have demonstrated how to consume SharePoint site data with Microsoft Graph API using the Premium OData Source component available in SSIS Productivity Pack. Microsoft Graph API is the programmatic way to retrieve the tremendous amount of data that resides in various Microsoft services, including SharePoint. Using the same strategy, you can similarly use our Premium OData components to work with other Graph data such as Calendar, Planner, etc.