Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Configure SQList to use AppId and AppSecret authentication with SharePoint Online

This form of authentication is useful when connecting to SharePoint Online without using user credentials. Instead an application is registered in SharePoint and SQList connect to SharePoint through it.

Important: the application must be registered in SharePoint before the connection in SQList can be configured.

To configure this type of authentication, select the option AppId and AppSecret in the connection details:

Follow the steps below to configure SQList to use AppId and AppSecret to authenticate against SharePoint Online.

1. Configure the App-only principal in SharePoint Online:

Open the SharePoint site you want to replicate, and go to the URL:

In this page click the "Generate" button to generate a client id and client secret and fill the remaining information like shown in the screenshot below, then click the "Create" button.

Important: make sure that you the App Domain and Redirect URI exactly as shown below:
You will be shown a confirmation screen that looks like this:

2. Configure the app permissions

Go to the URL:

In this page fill the App Id from the previous step and click the "Lookup" button to retrieve the app's detail, then fill the "Permission Request XML" and click the "Create" button.

Important: make sure that you enter the Permission Request XML exactly as shown below; this will give SQList access only to the SharePoint site you are configuring. For different level of access, see links at the end of this article.
Image Placeholder

The "Permission Request XML" to grant read-only (recommended) to the website is:

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web"
Right="Read" />

The "Permission Request XML" to grant full access to the website is:

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web"
Right="FullControl" />


If you are exporting lists that contain multi-lookup columns to lists that have "Content approval" enabled or the "User Information List" or the "TaxonomyHiddenList", SQList will throw warning messages when creating the corresponding many-2-many tables in the SQL tables. That is because SharePoint denies "looking up" those lists via the APIs even if the "Permission Request XML" grants full access to the website that contains them - this is a SharePoint security restriction that cannot be bypassed.
In this case, the lists will still be exported successfully, but be aware that the name of the foreign ID column in the corresponding many-2-many SQL tables will not contain the name of the lookup list (default naming in SQList) but the name of the lookup-field instead.

Also, check SQList know limitations for any limitations with this type of authentication.

These limitations do no apply to version 6.1.20 onwards of SQList.

You will be prompted to trust the application, ensure that you click the "Trust it" button:

You will be then be redirected to the settings page of your site.

3. That is it!

Now you can use those AppId and AppSecret in SQList to connect to the SharePoint site - see screenshot below.

Click the Test Connection button to ensure the connection is successful.

For further details about this process, see this article:

For the details about the types of permission you can grant to the app, see this article:

For any assistance, contact us at support@axioworks.com.