How to connect Azure Synapse with Sharepoint server

Hello Pushkal Nandan (A), Welcome and thanks for posting your query on Microsoft QnA!

Coming to your ask, Azure Synapse does not provide a built-in, single-click Linked Service specifically labelled “SharePoint Server.” Instead, you must leverage Azure Data Factory’s (ADF) SharePoint Online List connector or the generic HTTP/OData connector together with a Self-Hosted Integration Runtime (for on-premises SharePoint) to bridge the gap. This indirection is why customers cannot simply pick “SharePoint” from the Synapse Linked Service gallery without additional configuration.

Steps you can follow to create the Linked list:

From Synapse Portal

1. If your SharePoint server is on-premises, deploy and register a Self-Hosted Integration Runtime (SHIR):

• In Synapse Studio, select Manage > Integration runtimes > + New > Self-hosted > Download.
• Install the IR agent on a machine in your network, then copy the auto-generated authentication key back into Synapse to register

2. Create the Linked Service for SharePoint:

• In Synapse Studio, select Manage > Linked services > + New.
• Search for “SharePoint Online List” if using SharePoint Online; otherwise select “HTTP” or “OData.”

Enter the following details:

• Name: LS_SharePoint
• Authentication type: Service Principal (recommended) or Basic (username/password)
• Tenant ID, Client ID, Client Secret (for SP Online), or Base URL and credentials for on-prem.

Under Connectivity:

• If on-prem, select the SHIR you registered.
• If online, set URL to https://<your-tenant>.sharepoint.com/_api/web/lists.
• Test connection and Create.

3. Create the Linked Service for Azure Synapse SQL Pool:

• In Manage > Linked services > + New > Azure Synapse Analytics.
• Supply workspace name, database name, authentication (SQL Auth or Managed Identity).
• Test and Create.

4. Build a pipeline:

• In Integrate > + New pipeline > Add Copy Data activity.
• Source: select your LS_SharePoint, then choose list name.
• Sink: select your Synapse SQL Pool LS.
• Map columns, schedule or trigger as needed.

 5. Publish and run the pipeline. Monitor under Monitor > Pipeline runs.

Best practice:

• Store credentials in Azure Key Vault linked to your Synapse workspace.
• For large SharePoint lists consider incremental loads using filterQuery or pagination.

References:

• https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory
• https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory

Also, please let me know the following further answers for providing better guidance to you. The above provided response is totally generic, that mean how you can proceed with initial setup. For advance guidance, please answer the following questions.

1. Is your SharePoint environment SharePoint Online (part of Microsoft 365) or an on-premises SharePoint Server (e.g., SharePoint 2016/2019)? This determines whether to use the SharePoint Online List connector or a custom HTTP/OData setup with Self-Hosted Integration Runtime (SHIR).
2. Do you have access to register an application in Microsoft Entra ID for service principal authentication? The SharePoint Online connector requires service principal (app-based) authentication.
3. If using on-premises SharePoint, is the server accessible from a machine within your corporate network where you can install the Self-Hosted Integration Runtime?
4. Are there any firewall, proxy, or TLS/SSL restrictions in your network that might affect communication between the SHIR and SharePoint or between SHIR and Azure Synapse?
5. What type of data are you looking to extract — list items, document libraries, or files? The SharePoint Online List connector supports list data. For files (e.g., documents in libraries), you may need a different approach using the Web activity + HTTP connector.

Please let me know if you have further questions. Kindly "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks, and Happy to Help!

Pratyush