Connect with the SharePoint Excel provider

This provider is made so you can connect to a file located on a SharePoint server. The normal Excel provider is also able to do it, but it requires a different setup and it can use OAuth to connect to the file.

It can only connect to one file. It can also not use OAuth to connect to the file.

As in the normal Excel provider, there are a lot of options and some are more relevant than others.

Check the guide ADO.NET Provider for SharePoint Excel for more info about the fields. Especially check Establishing a Connection and Connection String Options.

For this provider, the focus is on the Authentication, Connection, and Misc areas.

Authentication

There are many ways to connect and check the link about establishing a connection for more options.

​To connect to SharePoint use the following fields.
  1. Auth Scheme
     
    1. NTLM: Set this to use your Windows credentials for authentication. Default option.
    2. Basic: Set this to use HTTP Basic authentication.
    3. Digest: Set this to HTTP Digest authentication.
    4. Forms: Set this to use Forms authentication.
    5. Negotiate: If Auth Scheme is set to Negotiate, the provider will Negotiate an authentication mechanism with the server. Set Auth Scheme to Negotiate if you want to use Kerberos authentication.
    6. ADFS: Set this to use Single Sign-On authentication with ADFS.
    7. None: Set this to use anonymous authentication.
  2. Password: Add the password of the account you connect with.
  3. SharePoint version: To determine if it is an online or local version of SharePoint.
  4. STSURL: The URL of the security token service (STS) when using Single Sign On (SSO). You also need Active Directory Federation Services (ADFS) as the Auth Scheme setting.
  5. URL: The name of the SharePoint server you connect to. It will usually be something like https://companyname.sharepoint.com. If you are pointing at a specific site you can add it as https://companyname.sharepoint.com/Sites/mysite/.
  6. Use NTLMV1: If your server uses the old version add this check.
  7. User: The user name you want to connect to SharePoint as.

Connection

Connecting to a Workbook

Regardless of which edition of SharePoint you are using, set File to the Excel workbook. This path is relative to the following properties:

  • Library: The Shared Documents library is used by default. You can use this property to specify another document library in your organization; for example, if you want to connect to OneDrive, set this property to "Documents".
  • Folder: You can use this property to specify a path to a sub-folder in a library. The path is relative to the library name specified in Library.

Connecting to Spreadsheet Data as Tables

The provider detects the available tables based on the available objects in the underlying API.

The APIs surface different API objects; select the API based on the organization of your spreadsheets and your SharePoint version:

  • OData: The OData API allows access to tables defined from Excel table objects (not ranges or spreadsheets), which you create by clicking Insert > Table in Excel. When connecting to the OData API, the provider may not return any tables if you do not have table objects defined in your workbook. Set UseRESTAPI to true to connect to spreadsheets or ranges as tables.
  • REST: The REST API enables access to tables defined from Excel table objects, ranges, and spreadsheets. This is the default API. Requesting a large number of rows from ranges and spreadsheets is restricted by the REST API, and the provider limits the number of rows returned to 100 by default. The provider also defaults to detecting column names from the first row; set Header to disable this.

With the Define Tables option set, you can define tables based on ranges, using the Excel range syntax. A range that is too large will be restricted by the API. More is explained in the Excel guide.

See Data Model for more information on how the provider detects tables and how to query them.

Connecting to SharePoint On-Premises

Set the URL to your server's name or IP address. Additionally, set SharePointVersion and the authentication values.

To authenticate to SharePoint OnPremise, set AuthScheme to the authentication type and set User and Password, if necessary.

Note: When connecting to SharePoint On-Premises 2010, you must set UseRESTAPI to true.

  • Windows (NTLM)

    This is the most common authentication type. As such, the provider is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.

  • Authenticating with ADFS

    Set the AuthScheme to ADFS. The following connection properties need to be set:

    • User: Set this to the ADFS user.
    • Password: Set this to ADFS password for the user.
    • SSOLoginURL: Set this to the WS-trust endpoint of the ADFS server.
    The following SSOProperties are needed to authenticate to ADFS:
    • RelyingParty: The value of the relying party identifier on the ADFS server for Sharepoint.
    Below is an example connection string: 
    AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SSOLoginURL=https://<authority>/adfs/services/trust/2005/usernamemixed;SSO Properties ='RelyingParty=urn:sharepoint:sp2016;';