This form of authentication is useful when connecting to SharePoint Online without using user credentials. Instead an application is registered in Azure AD and SQList connect to SharePoint through it.
Beware! The application permissions configured in this guide will give SQList access to all SharePoint sites and lists in the tenant. Ensure that you do not share the application's details and certificate with any unauthorised parties.
Note: it is possible to grant permissions to specific Site collections only by selecting the option "Site.Selected" in Azure AD Application Permissions (see further below); note that this option requires additional manual configuration via POST to specific endpoints via scripting or third party tools. For more details see this article:
Important: a self-signed certificate must be created and the application must be registered in Azure AD before the connection in SQList can be configured.
To configure this type of authentication, select the option Azure AD Application in the connection details:
The parameters to fill are:
- Client ID: the Client ID of the application in Azure AD.
- Tenant ID: the Tenant ID of the application in Azure AD.
- Certificate Path: the path to the PFX file of the self-signed certificate.
- Certificate password: the certificate password.
Follow the steps below to configure SQList to use Azure AD Application permissions to authenticate against SharePoint Online.
1) Create and configure a self-signed X.509 certificate
Firstly, download the PowerShell script Create-SelfSignedCertificate.ps1 from the URL below and save it into a local folder:
To create the certificate, open a PowerShell command prompt and run the following command:
.\Create-SelfSignedCertificate.ps1 -CommonName "MyCompanyName" -StartDate 2017-10-01 -EndDate 2019-10-01
You will be asked to give a password to encrypt your private key, and both the .PFX file and .CER file will be exported to the current folder.
Keep the password and the path to the PFX file handy as you will need them later on.
2) Register the Azure AD application in the Azure Active Directory tenant that is linked to your Office 365 tenant
Click on the Azure Active Directory option:
From the left-hand menu, select the option App registrations:
Click the New registration button in the upper left part of the blade. Next, provide a name for your application, select Accounts in this organisational directory only, and click the Register button.
Now click on API permissions in the left menu bar, and click on the Add a permission button.
From the choice of applications, select SharePoint:
Next, select Application Permissions and grant access to Sites.Read.All:
Then click Add permissions.
Next, Click the Grant admin consent button followed by Yes, to grant consent to the application.
it is possible to grant permissions to specific Site collections only
by selecting the option "Site.Selected" in Azure AD Application Permissions
(see further below); note that this option requires additional manual configuration via POST to specific endpoints via scripting or third party tools. For more details see this article:
The next step is to pair the self-signed certificate with the application.
On the left menu, select Certificates & secrets, followed by Upload certificate:
Select the .CER file you generated earlier and click on "Add" to upload it.
Lastly, select the Overview option and keep this page handy as you will need to copy and paste the Client ID and Tenant ID later on.
3) Configure the connection in SQList Manager
Open SQList Manager, select SharePoint Connections,then New SharePoint connection.
In the Connection details section, select Azure AD Application and fill the following fields:
- Client ID: copy and paste the Client ID from the application's Overview (see above);
- Certificate password: enter the password you used earlier to create the certificate;
- Tenant ID: copy and paste the Tenant ID from the application's Overview (see above);
- Certificate path: enter the path to the .PFX file that was generated when you created the certificate.
Click the Test Connection button to ensure the connection is successful.
For further details about this type of authentication see this article: