With the recent announcement of SOAP basic authentication deprecation, it will be required for users to switch and use an authentication option that supports SharePoint modern authentication. In our latest release (v22.1), we added support for REST OAuth in our SharePoint connection manager to help achieve this goal. This blog post will demonstrate the process involved in order to start using the OAuth authentication type in your integration. Before that, we would like to highlight a few advantages of using OAuth over basic authentication.
- OAuth is a standard "Open authorization" infrastructure that provides some better security in protecting your data access when it comes to data integration or migration.
- When setting up an OAuth connection properly, it can support SharePoint instances that use Multi-Factor Authentication (MFA) enabled. MFA is a commonly used application security mechanism to improve the protection of your application data.
- There are two OAuth methods available in our first release of OAuth support - Authorization Code and Certificate, and either one can be opted to be used, as they have their own prominence.
However, setting up an OAuth connection is quite an involved process; switching to OAuth authentication involves a number of steps. In this blog post, we will walk you through the process with some great details to help you understand what is required and how OAuth works in our SharePoint connections. We hope that this complements our OAuth online documentation page, and that this provides you some real-world configurations of how it can be properly achieved.
Now, let's get started.
To use OAuth, you need to make sure you have registered an Azure App and you need to get the Client App ID and Client Secret from the Azure Portal.
Registering an App in Azure portal
If you do not already have an Azure App, then to create a new Azure App please follow these steps (Note, if you do already have an Azure App, then after step 1, please skip to step 4):
- Log in to Azure Portal and navigate to Azure Active Directory in the same tenant as your SharePoint instance.
- In the left pane, click on App registrations, then select “New registration”.
- On the Registration page, please ensure that you enter a Redirect URI. For the Redirect URI, you can supply any valid https URL. Note that the URL doesn't have to actually exist, it will be the URL that will receive the authorization code when an authentication process completes.
- After you click Register, you should now see your app in the App registrations page. You can click and open the App where you get your App (Client) ID. Then click on your App to continue adding permissions.
- In the left Navigation Pane, click on “API Permissions”. Click on “Add a permission” and select the permissions shown below from the window that opens. (Please note that steps 5 and 6 are optional, as our component automatically authorizes it from the connection manager. However, in case there are some constraints and if you face permission issues, you could perform the below two steps to overcome those.)
- Next, click on “Grant admin consent” to see the status change to Granted for the permissions.
- To create your Client Secret, go to “Certifications & secrets” in the left Navigation Pane, then click on “New client secret”. Add a “Description” and “Expires” timeframe, then click “Add”.
Please note that Client Secret is available to view and copy only at the time when it is created. If you forget to copy it at the time of creation, there is no way to reveal the client's secret afterward. In such cases, all you can do is create a new client secret and you may remove the unrecoverable client secret if you know it is not used elsewhere.
Your App is now created and registered with the required permissions and Client Secret.
Configure the SharePoint Connection Manager in SSDT
Once you have completed the above steps, you may now head to your SSIS development environment to complete the connection setup. If it is a package with an existing connection, you may simply switch your SharePoint connection to use REST Service Endpoint and then choose the authentication type as OAuth. Otherwise, if your package does not currently have any connection, you may simply create one. Enter your SharePoint Server URL (which would be till Site and subsite if any).
When you choose OAuth type as Authorization Code, you get to click on the "Get New Token" button, which will bring up the below OAuth token generator.
Enter the details that were previously generated in the Azure portal, and click on OK, which would take you to your SharePoint authorization portal via your default browser, to enter the SharePoint user and password details.
Once authorized and provided with the required permissions, you would see that the browser URL redirects to the redirect URI that was specified in the Azure portal app registration. And appended to it, would be the code. Copy the whole URL, and paste it on the window that had opened up in SSDT.
Navigate through the prompts to save the token file and encrypt it using an alphanumeric password. And you have successfully created your SharePoint OAuth token. Click on Test connection to confirm the connection actually works.
Closing Notes
In this blog post, we only covered the Client Credentials option. However, the OAuth authentication in the SharePoint connection manager supports additional options like Certificate, which we didn't cover in this blog post. This uses a certificate key pair, out of which the public key would be uploaded to your Azure AD App; the private key is installed on the machine or your server to generate a thumbprint to be used in the connection manager.
In short, OAuth is a recommended authentication option for your SharePoint integration purpose. If you had an existing SharePoint connection previously created using the SOAP service endpoint, you could easily switch to the new OAuth option by following the above steps without losing any functionalities. Once done, you are better protected with the new authentication options.