Skip to Content

Microsoft SharePoint Server Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8690 of the connector.

Get Started

Microsoft SharePoint Version Support

The connector supports all versions of Microsoft SharePoint that support the SOAP API. This includes: Windows SharePoint Services 3.0, SharePoint Server 2007+ (2010, 2013, etc.), and SharePoint Online. The connector models the custom lists of your SharePoint site as bidirectional tables; when you connect, the connector retrieves the metadata for these tables by calling SharePoint Web services. Supported authentication schemes are NTLM, Basic, Digest, Forms, Kerberos, SSO, STS (security token services), and SharePoint authentication cookies.

Establish a Connection

Connect to Microsoft SharePoint

Regardless of whether you will connect online or on-premesis, what architecture will be used, and which Lists and Documents will be accessed, connecting to Microsoft SharePoint requires exactly two things:

  • Set the URL connection property.
  • Set the appropriate authentication properties for your working environment.

Setting URL:

Microsoft SharePoint works with all Lists and Documents in the global Microsoft Sharepoint site, or all Lists and Documents at individual sites.

To work with all Lists and Documents in the global Microsoft Sharepoint site, set the URL connection property to your Site Collection URL. For example:

https://teams.contoso.com

To work with all Lists and Documents at an individual site, set the URL connection property to your individual site URL. For example:

https://teams.contoso.com/TeamA

The following sections describe how to set the appropriate authentication properties for your working environment. For information about how to create a custom OAuth application (required for use with AzureAD in a Web application; optional for AzureAD access via a Desktop application or a Headless Server), see Creating a Custom OAuth Application.

Microsoft SharePoint Online

Set SharePointEdition to "SharePoint Online" and set the User and Password to the credentials you use to log onto SharePoint, for example, the credentials to your Microsoft Online Services account.

Microsoft SharePoint online supports a number of cloud-based architectures, each of which supports a different set of authentication schemes:

  • AzureAD
  • Single sign-on (SSO) via the ADFS, Okta, OneLogin, or PingFederate SSO identity provider
  • Azure MSI
  • Azure Password
  • OAuthJWT
  • SharePointOAuth

If the user account domain is different from the domain configured with the identity provider, set SSODomain to the latter. This property may be required for any SSO.

AzureAD

Azure Active Directory (AzureAD) is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with Microsoft SharePoint using an internet browser. The driver facilitates this in several ways as described below. Set your AuthScheme to AzureAD. The AzureAD flows described below assume that you have done so.

Your organization may require Admin Consent when authorizing a new AzureAD application for your Azure Tenant. In all AzureAD flows, any initial installation and use of an AzureAD application requires that an administrator approve the application for their Azure Tenant. For details, see Creating a Custom OAuth Application.

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom AzureAD application. See Creating a Custom OAuth Application for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom AzureAD applications.

After setting the following connection properties, you are ready to connect:

  • InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • CallbackURL: The Redirect URL in your application settings.
  • Custom applications only:
    • OAuthClientId: The client ID in your application settings.
    • OAuthClientSecret: The client secret in your application settings.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. Saves OAuth values in OAuthSettingsLocation. These stored values persist across connections.
Single Sign-On Identity Providers

ADFS

Set the AuthScheme to ADFS. You must set the following connection properties:

  • User: The ADFS user.
  • Password: The user's ADFS password.
  • SSODomain (optional): The domain configured with the ADFS identity provider.

Example connection string:

AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;URL='http://sharepointserver/mysite';

Okta

Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:

  • User: The Okta user.
  • Password: The user's Okta password.
  • SSODomain (optional): The domain configured with the OKTA identity provider.

Example connection string:

AuthScheme=Okta;User=oktaUserName;Password=oktaPassword;URL='http://sharepointserver/mysite';

OneLogin

Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:

  • User: The OneLogin user.
  • Password: The user's OneLogin password.
  • SSODomain (optional): The domain configured with the OneLogin identity provider.

Example connection string:

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;URL='http://sharepointserver/mysite';

PingFederate

Set the AuthScheme to PingFederate. The following connection properties are used to connect to PingFederate:

  • User: The PingFederate user.
  • Password: PingFederate password for the user.
  • SSODomain (optional): The domain configured with the PingFederate identity provider.

Example connection string:

AuthScheme=PingFederate;User=PingFederateUserName;Password=PingFederatePassword;URL='http://sharepointserver/mysite';
Azure MSI

If you are running Microsoft SharePoint on an Azure VM, you can leverage Azure Managed Service Identity (MSI) credentials to connect:

  • AuthScheme: AzureMSI.

The MSI credentials are automatically obtained for authentication.

Azure Password

To connect using your Azure dredentials directly, specify the following connection properties:

  • AuthScheme: AzurePassword
  • User: The user account used to connect to Azure
  • Password: The password used to connect to Azure
  • AzureTenant: Directory (tenant) ID, found on the Overview page of the OAuth application used to authenticate to Microsoft SharePoint on Azure.
OAuthJWT Certificate

Set the AuthScheme to OAUTHJWT. The following connection properties are used to connect to Microsoft SharePoint:

  • OAuthGrantType: CLIENT.
  • AzureTenant: The tenant you wish to connect to.
  • OAuthJWTCert: The JWT Certificate store.
  • OAuthJWTIssuer: The OAuth client Id.
SharePointOAuth

Set the AuthScheme to SharePointOAuth. The following connection properties are used to connect to SharePointOAuth:

  • Schema: REST.
  • InitiateOAuth: GETANDREFRESH.
  • OAuthClientId: The application's identity/Client Id.
  • OAuthClientSecret: The application's Client Secret.

Example connection string:

SharePointEdition='SharepointOnline';URL=https://rssbuscrm.sharepoint.com;Schema=REST;AuthScheme=SharepointOAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=11111111-1111-1111-1111-111111111111;OAuthClientSecret=1111111111111/11111111111111/11111111111111=;

This AuthScheme works with the custom OAuth application. To generate credentials from a custom OAuth application, see Creating a Custom OAuth Application.

Microsoft SharePoint On-Premises

Microsoft SharePoint On-Premises supports a number of premise-based architectures:

  • Windows (NTLM)
  • Kerberos
  • ADFS
  • Anonymous Access

Set SharePointEdition to "SharePoint On-Premises" to use the following authentication types.

Windows (NTLM)

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

Kerberos

Set the AuthScheme to NEGOTIATE, and then set the following Kerberos connection properties:

  • KerberosKDC: The host name or IP Address of your Kerberos KDC machine.
  • KerberosRealm: The realm of the Microsoft SharePoint Kerberos principal. This is the value after the '@' symbol (for instance, EXAMPLE.COM) of the principal value (for instance, MyService/MyHost@EXAMPLE.COM).
  • KerberosSPN: The service and host of the Microsoft SharePoint Kerberos Principal. This is the value prior to the '@' symbol (for instance, MyService/MyHost) of the principal value (for instance, MyService/MyHost@EXAMPLE.COM).

For details on how to authenticate with Kerberos, see Using Kerberos.

ADFS

Set the AuthScheme to ADFS, and then set the following connection properties:

  • User: The ADFS user.
  • Password: ADFS password for the user.
  • SSOLoginURL: The WS-trust endpoint of the ADFS server.

You also need the to set SSOProperties to authenticate to ADFS. Specify the value of the RelyingParty parameter; it is located on the ADFS server for Sharepoint. Example connection string:

AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SSOLoginURL='https://<authority>/adfs/services/trust/2005/usernamemixed';SSO Properties ='RelyingParty=urn:sharepoint:sp2016;';
Anonymous Access

Set the AuthScheme to NONE along with the URL.

Create a Custom OAuth Application

Create a Custom OAuth Application

embeds OAuth Application Credentials with branding that can be used when using Azure to connect via either a Desktop Application or a Headless Machine. However, in all cases, connecting to Azure via a Web application requires creating a custom OAuth application.

You might also want to create a custom OAuth application to:

  • control branding of the Authentication Dialog,
  • control the redirect URI that the application redirects the user to after the user authenticates, or
  • customize the permissions that you are requesting from the user.

The following sections describe how to create a custom OAuth application using either the Azure Portal or Sharepoint App.

Azure Portal

To obtain OAuth values for your app, the OAuthClientId and OAuthClientSecret, and register a custom OAuth application:

  1. Log in to the Azure Portal.

  2. In the left-hand navigation pane, navigate to Azure Active Directory > App Registrations.

  3. Click Add.

  4. Enter an application name.

  5. Select Any Azure AD Directory - Multi Tenant.

  6. Set the redirect URL to http://localhost:33333 (the connector's default) or set a different port of your choice.

  7. Set CallbackURL to the exact reply URL you defined.

    The Portal creates the new application.

  8. Navigate to the "Certificates & Secrets" section.

  9. Create a client secret for the application, and select a duration.

  10. After you save the key, the key value is displayed once. Immediately, set OAuthClientSecret to the displayed value. Set OAuthClientId to the Application Id.

  11. Select API Permissions.

  12. Click Add. If your application will only connect within a user context, specify Delegated Permissions.

  13. In the API Permissions section, click on Add a permission and select SharePoint. Choose the permissions you want your app to have. To view and edit lists, you have to select (at least) the AllSites.Manage permission.

  14. Save your changes.

Note

If you have selected to use permissions that require admin consent, you can grant them from the current tenant on the API Permissions page. Otherwise, see "OAuth: Admin Consent", below.

Admin consent refers to when the Admin for an Azure Active Directory tenant grants permissions to an application that requires an administrator in your organization to consent to the use case.

The embedded application within the Jitterbit Connector for Microsoft SharePoint, contains no permissions that require administrator consent. Therefore, this information applies only to custom applications.

When creating a new OAuth application in the Azure Portal, you must specify which permissions the application requires. Some permissions may be marked with "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your application requires admin consent, there are two ways you can do this.

The easiest way to grant admin consent is to have an administrator log into the Azure Portal and navigate to the application you have created in App Registrations. Under API Permissions, click Grant Consent, which grants permissions on the tenant under which it was created.

If your organization has multiple tenants or you need to grant application permissions for other tenants outside your organization, use the GetAdminConsentURL stored procedure* to generate the Admin Authorization URL. After the OAuth application is successfully authorized, it returns a Boolean indicating that permissions have been granted.

After the administrator has approved the OAuth Application, you can continue to authenticate.

Sharepoint App

To create a custom OAuth app and obtain the connection properties for the OAuth authentication using Sharepoint App, you must first register the add-in, then grant permissions to that add-in.

Register the Add-In
  1. Navigate to the Register Add-In page by entering the URL as : https://{sitename}.SharePoint.com/_layouts/15/appregnew.aspx.
  2. In the "App Information" section, click Generate, which is located next to the Client ID and Client Secret textboxes to generate the respective values.
  3. Set Title, App Domain, and Redirect URI by filling in their respective text boxes.
  4. Click Create.

The add-in is registered, and the Sharepoint App displays the created information.

Grant Permissions to the Add-In
  1. Navigate to the SharePoint site.

  2. Enter the URL: https://{sitename}-admin.sharepoint.com/_layouts/15/appinv.aspx in the browser. This redirects to the Grant Permission page.

  3. At App Id, enter the Client ID which you generated earlier, and click Lookup.

    This populates the values in the other text boxes (Title, App Domain and Redirect URL).

  4. Enter the following permission request in XML format:

        <AppPermissionRequests AllowAppOnlyPolicy="true">
         <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl"/>
    </AppPermissionRequests>
    
  5. Click Create. Sharepoint App displays a permission consent dialog. To grant the permissions, click Trust It.

Connect to REST API

Microsoft SharePoint REST API is supported both on Sharepoint OnPremise and on Sharepoint Online. To connect using the REST API set Schema to REST.

The property SharePointEdition may be used to define the edition of Sharepoint.

Sharepoint Online

SharePoint Online uses OAuth standard to authenticate. Follow the steps under "Authenticating to SharePoint Online" in Establishing a Connection for more information.

Sharepoint OnPremise

Follow the steps under "Authenticating to SharePoint On Premises" in Establishing a Connection for more information.

Use Kerberos

Kerberos

Authenticating to Microsoft SharePoint via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Set Authentication Properties

To authenticate to Microsoft SharePoint using Kerberos, set these properties:

  • hive.server2.authentication: Kerberos.
  • AuthScheme: NEGOTIATE.
  • KerberosKDC: The host name or IP Address of your Kerberos KDC machine.
  • KerberosRealm: The realm of the Microsoft SharePoint Kerberos principal. Find this value immediately after the '@' symbol of the principal value.
  • KerberosSPN: The service and host of the Microsoft SharePoint Kerberos Principal. Find this value just before the '@' symbol of the principal value.
Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log

in at their local computer or enter kinit USER at the command prompt.

The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

  1. Ensure that the KRB5CCNAME variable is present in your environment.
  2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
  3. To obtain a ticket:

    1. Open the MIT Kerberos Ticket Manager application.
    2. Click Get Ticket.
    3. Enter your principal name and password.
    4. Click OK.

    If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The connector uses the cache file to obtain the Kerberos ticket to connect to Microsoft SharePoint.

Note

If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the connector uses the specified cache file to obtain the Kerberos ticket to connect to Microsoft SharePoint.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with Microsoft SharePoint.

Enable Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Fine-Tuning Data Access

Fine Tuning the Microsoft SharePoint Connection

To make it easier to access data in advanced integrations, use the following connection properties to control column name identifiers and other aspects of data access:

  • UseDisplayNames: Set this to true to return column names that match field names in the underlying API
    . By default, the connector uses column names that match the field names defined in SharePoint.
  • UseSimpleNames: Set this to true to perform substitutions on special characters in column names that SharePoint allows but that many databases typically do not.
  • ShowPredefinedColumns: Set this to false to exclude fields derived from fields in the list; for example, Author and CreatedAt
    . This setting excludes the predefined fields from being returned in SELECT * statements and schema discovery.
  • ShowHiddenColumns: When true, columns marked as hidden in SharePoint will be displayed by the connector.

Advanced Features

This section details a selection of advanced features of the Microsoft SharePoint connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft SharePoint and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Jitterbit Connector for Microsoft SharePoint allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Calendar WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customizing the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Client SSL Certificates

The Microsoft SharePoint connector also supports setting client certificates. Set the following to connect using a client certificate.

  • SSLClientCert: The name of the certificate store for the client certificate.
  • SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
  • SSLClientCertPassword: The password for the TLS/SSL client certificate.
  • SSLClientCertSubject: The subject of the TLS/SSL client certificate.

SOAP Data Model

The Jitterbit Connector for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.

Customizing the Data Model

The connector sets defaults to facilitate the maximum number of integrations; however, the following connection properties allow a greater granularity of customization useful in advanced integrations:

  • CalculatedDataType: The data type to be used for calculated fields.
  • CreateIDColumns: Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.
  • FolderOption: An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
  • PseudoColumns: Indicates whether or not to report pseudo columns as columns in the table metadata.

Tables

Tables describes the available tables.

The connector can expose custom lists from Microsoft SharePoint that are not mentioned in the Tables. The data model illustrates a sample of what your SharePoint site might look like. The actual data model will be obtained dynamically based on your user credentials and SharePoint site.

Views

Typically, entities that cannot be modified are represented as Views, or read-only tables. You can also access custom views of a list as relational views.

To get data from a custom view of a list, you can set the ViewID pseudo column in the WHERE clause.

SELECT * FROM ListName WHERE ViewID='ID of the view'

You can get the ID of the view from the Views list. You must specify the List pseudo column to get a list of views for that list. For instance:

SELECT * FROM Views WHERE List ='ListName'

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They surface additional capabilities of the Microsoft SharePoint API such as searching, updating, and modifying information.

Tables

The connector models the data in Microsoft SharePoint into a list of tables that can be queried using standard SQL statements.

Generally, querying Microsoft SharePoint tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for Microsoft SharePoint Tables

Name Description
Attachments Read or delete Attachments for the specified item on the specified list.
Groups Create, update, delete, and query Groups from SharePoint.
Roles Create, update, delete, and query Roles from SharePoint.
Users Update, delete, and query Users from SharePoint.
Views Create, update, delete, and query the available lists in SharePoint.

Attachments

Read or delete Attachments for the specified item on the specified list.

Table Specific Information
Select

List and ItemId are required to return Attachments.

Insert

Call the AddAttachments stored procedure* to add new attachments to a list item.

Columns
Name Type ReadOnly Description
Url [KEY] String True Description of the term set.
List String True The internal name of the list to retrieve attachments from.
ListDisplayName String True The display name of the list to retrieve attachments from.
ItemID String True The ID of the item on the list to retrieve attachments from.
Name String True The name of the attachment on the item.

Groups

Create, update, delete, and query Groups from SharePoint.

Table Specific Information
Insert

The Name, DefaultLogin, and OwnerLogin columns are required to insert to this table.

To use the UserName pseudo column, you must set the value to the LoginName of the user. You can obtain the LoginName by querying the Users table.

Columns
Name Type ReadOnly Description
Name# [KEY] String False The name of the group.
Description# String False A description of the group.
OwnerLogin# String False The user name of the owner of the group. This value should be in the format DOMAIN\username.
OwnerType# String False The type of owner. User or group.
DefaultLogin# String False The user name of the default user for the group. This value should be in the format DOMAIN\username.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
UserName String The logical name of the user to return groups for. Specify this value on the SELECT statement to return only groups the specified User is in.

Roles

Create, update, delete, and query Roles from SharePoint.

Table Specific Information
Select

To use the UserName pseudo column, you must set the value to the LoginName of the user. You can obtain the LoginName by querying the Users table.

Insert

To insert a Role, at least the Name is required:

INSERT INTO Roles (Name) VALUES ('My Role')
Columns
Name Type ReadOnly Description
Name# [KEY] String False The name of the role.
Description# String False A description of the role.
Permissions# Long False A long representing the permissions for the role.
RoleType String True The type of role.
IsHidden Boolean True A boolean indicating if the role is hidden.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
UserName String The login name of the user to return roles for. Specify this value on a SELECT statement to return only roles assigned to the specified user.
GroupName String The name of the group to return roles for. Specify this value on a SELECT statement to return only roles assigned to the specified group.

Users

Update, delete, and query Users from SharePoint.

Table Specific Information
Select

Retrieve all users created for the SharePoint Account:

SELECT * FROM Users

You can retrieve Users that belong to a specific Group. In this case specify the Group Name

SELECT * FROM Users WHERE [Group] = "GroupName"

Or you can retrieve Users that have a specific Role. In this case specify the Role Name

SELECT * FROM Users WHERE [Role] = "RoleName"
Columns
Name Type ReadOnly Description
ID String True The ID of the user.
LoginName# [KEY] String False The login name of the user.
Name# String False The name of the user.
Email# String False The email address of the user.
IsInDomainGroup Boolean True A boolean indicating if the user is in the domain group.
IsSiteAdmin Boolean True A boolean indicating if the user is a site admin.
Notes# String False Optional notes concerning the user.
SecurityId String True The security ID (SID) for the user.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Group String The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.
Role String The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.

Views

Create, update, delete, and query the available lists in SharePoint.

Table Specific Information

Views is a special table. It may be used to get, update, insert, and delete views from a specified List.

Select

In order to return results from Views, either the ID or List must be specified in the SELECT statement. For example:

SELECT * FROM Views WHERE List = 'MyListName'
Insert

The List, Name, Type, and Fields columns are required to insert to this table.

Columns
Name Type ReadOnly Description
ID [KEY] String True The ID of the view.
List String True The list the view is associated with. A list must be specified when performing SELECT statements if the ID is not specified.
ViewID String True The ID of the view. May only be unique for the specific list.
Name String False The name of the view.
Type String False The type of view. This must have a value on inserts and updates. The allowed values are CALENDAR, GRID, HTML. The default value is HTML.
Fields String False A comma separated list of the fields associated with the view. This is space-sensitive.
IsDefault Boolean False A boolean indicating if the view is the default view for the list.
Query String False A query for the view.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Jitterbit Connector for Microsoft SharePoint Views

Name Description
FileVersions Lists the versions of files available on SharePoint.
GetValidTerms Gets a list of valid terms for the specified column on the specified table.
Lists Lists the available lists in SharePoint.
Permissions The permissions for a site or list. Note: If ItemID is empty, the ObjectType should be set to List or web (an ObjectName must be specified when the ObjectType is list). If not, the ObjectName must be specified along with the ItemID.
Subsites This lists the available subsites.

FileVersions

Lists the versions of files available on SharePoint.

View-Specific Information

Library and File must be specified to return results from this view.

Columns
Name Type Description
ID [KEY] String The ID of the version.
Comments String Comments about the particular version.
CreateBy String The username of the SharePoint user who modified this version of the file.
Date Datetime When the file was modified.
Size String The size of this version of the file.
Url String The URL to this version of the file.
Library String The library name on SharePoint you are listing versions from. A library must be specified to retrieve the versions for a file. The default value is Shared Documents.
File String The name of the file on SharePoint to list versions for. A file must be specified to retrieve the versions for a file.

GetValidTerms

Gets a list of valid terms for the specified column on the specified table.

Table Specific Information

GetValidTerms is a special view. It may be used to get valid terms for a Taxonomy or Managed Metadata column of a given list. To use the view, supply both the name of the table and the column for which you are looking to get valid terms. For example:

SELECT * FROM GetValidTerms WHERE List = 'MyListName' AND ColumnName = 'MyManagedMetadataColumn'
Columns
Name Type Description
ID [KEY] String The identifier of the term.
TermLabelValue String The label of the term.
Description String Description of the term set.
NameInRequestedLang String The name of the term set in the language requested by the client.
IsOpen Boolean Boolean indicating if the term set is open.
Deprecated Boolean Boolean indicating if the term is deprecated.
InternalId String Internal identifier for the term.
TermSetContact String Term set contact.
ContainerDesc String Container node for the description.
SingleTermLabelDesc String This fully describes a single term label.
IsDefaultLabel Boolean True if the term label is the default term label.
BelongsTo String This item describes a term set to which a term belongs.
IsTaggingAvailable Boolean If the term set is available for tagging, this value is true.
TermPath String Term path of the term with term labels.
TermpathoftermwithIds String Term path of term with identifiers.
ChildTerms String A string value that indicates a custom sort order for the child terms of the term identified by PertainingToTerm.
HasChildTerms Boolean True if the term has child terms.
PertainingToTerm String Identifier of the term that this term set information is pertaining to.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
List String The name of the list to get valid terms for.
ColumnName String The name of the column to get valid terms for.
LocaleId String The locale ID for the term. Defaults to 1033.

Lists

Lists the available lists in SharePoint.

Table Specific Information

Lists can be used to list the tables in SharePoint. This will only return actual lists in SharePoint and not any special tables associated with the connector.

The following columns can be used in the WHERE clause: Title and BaseTemplate.

Columns
Name Type Description
ID [KEY] String The ID of the list.
Title String The title of the list. This column may be used in the WHERE clause and may be used with a wild card (*) character.
Description String A description for the list.
BaseTemplate String Indicates the type of template used to create the list. This column may be used in the WHERE clause.
Version Double The version of the list.
Url String The default URL of the list.
EmailAlias String The email alias of the list.
ImageUrl String The image URL of the list.
ItemCount Integer The number of items in the list.
Item_Deleted Datetime The last time an item was deleted from this list.
Item_Modified Datetime The last time an item was modified from this list.
SendToUrl String The send-to URL of the list.
Created Datetime The time when the list was created.
AllowDeletion String Whether items can be deleted.
AllowMultiResponses Boolean Boolean indicating if multiple responses are enabled for the survey.
Direction String A string that contains LTR if the reading order is left-to-right, RTL if it is right-to-left, or None.
EnableAssignedToEmail Boolean Boolean indicating if assigned-to emails are enabled. Only applies to issues lists.
EnableAttachments Boolean Boolean indicating if attachments may be added to items in the list. Does not apply to document libraries.
EnableModeration Boolean Boolean indicating if content approval is enabled for the list.
EnableVersioning Boolean Boolean indicating if versioning is enabled for the list.
Hidden Boolean Boolean indicating if the list is hidden so that it does not appear on the Documents and Lists page, Quick Launch bar, Modify Site Content page, or Add Column page as an option for lookup fields.
MultipleDataList Boolean Boolean indicating if a meeting-workspace site contains data for multiple meeting instances within the site.
Ordered Boolean Boolean indicating if items in the list can be sorted on the Edit View page.
Showuser Boolean Boolean indicating if the names of users are shown in the results of the survey.

Permissions

The permissions for a site or list. Note: If ItemID is empty, the ObjectType should be set to List or web (an ObjectName must be specified when the ObjectType is list). If not, the ObjectName must be specified along with the ItemID.

Columns
Name Type Description
MemberID [KEY] String The ID of the permission.
Mask Long A 32-bit integer in 0x00000000 format that represents a Microsoft.SharePoint.SPRights value and defines the permission. Use the pipe symbol ('|') in C# or Or in Microsoft Visual Basic to delimit values when creating a custom permission mask that combines permissions.
MemberIsUser Bool Indicate whether it is the permission for user.
MemberGlobal Bool Indicate whether it is the permission for group.
RoleName String A string that contains the name of the site group, the name of the cross-site group, or the user name (DOMAIN\User_Alias) of the user to whom the permission applies.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
ObjectName String A string that contains the name of the list or site.
ObjectType String A string that specifies either List or Web.
ItemID String ID of the item.

Subsites

This lists the available subsites.

Columns
Name Type Description
Title String The name of the subsite.
Url String The URL of the subsite.

Stored Procedures

Stored procedures* are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft SharePoint.

Stored procedures* accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft SharePoint, along with an indication of whether the procedure succeeded or failed.

Jitterbit Connector for Microsoft SharePoint Stored Procedures

Name Description
AddAttachment Add an Attachment to a SharePoint List item.
AddList Creates a list on a SharePoint site.
AddListColumn Adds a new column to the specified list.
AddUserToGroup Add the user to specified group.
AddUserToRole Add the user to specified role.
CheckInDocument Checks in a document to SharePoint and releases the lock on the document.
CheckOutDocument Checks out a document from SharePoint.
CopyDocument Copies a document from the SharePoint library.
CreateFolder Adds a folder to a document library on a SharePoint site.
CreateSchema Creates the schema file for the specified SharePoint list. The schema file may be customized manually to exclude unwanted columns or include additional information about columns.
DeleteAttachment Delete an attachment from a SharePoint list item.
DeleteDocument Delete a document on the SharePoint library.
DeleteList Permanently deletes a list from a SharePoint site.
DeleteListColumn Deletes a column from the specified list.
DeleteUserFromGroup Delete the user from specified group.
DeleteUserFromRole Delete the user from specified role.
DiscardCheckOutDocument Discards a check out on a document in SharePoint. This does not check a new file into SharePoint. It only releases the lock on the document.
DownloadAttachment Download a document from the SharePoint list.
DownloadDocument Download a document from the SharePoint library.
MoveAttachmentOrDocument Moves a document or attachment from a source folder to a destination folder.
RenameAttachmentOrDocument Renames a document or attachment.
UpdateList Updates a list on a SharePoint site.
UpdateListColumn Updates a column to the specified list.
UploadDocument Upload a document to the SharePoint library.

AddAttachment

Add an Attachment to a SharePoint List item.

Input
Name Type Accepts Input Streams Description
File String False The path of the local file to be added.
List String False The name of the List on the SharePoint server.
ItemID String False The ID of the item on the List to add attachments for.
Content String True The content as InputStream to be uploaded when File is not specified.
FileName String False Name of the file to be uploaded. This will be used if content is not null. For example: test.csv
Result Set Columns
Name Type Description
URL String The URL of the newly created item.
Result String Boolean value indicating whether the stored procedure* was successful.

AddList

Creates a list on a SharePoint site.

Input
Name Type Description
Name String The name of the list on the SharePoint server.
Template String The name of the template to use for the list creation. The allowed values are GenericList, DocumentLibrary, Survey, Links, Announcements, Contacts, Events, Tasks, DiscussionBoard, PictureLibrary, DataSources, WebTemplateCatalog, UserInformation, WebPartCatalog, ListTemplateCatalog, XMLForm, MasterPageCatalog, NoCodeWorkflows, WorkflowProcess, WebPageLibrary, CustomGrid, DataConnectionLibrary, WorkflowHistory, GanttTasks, Meetings, Agenda, MeetingUser, Decision, MeetingObjective, TextBox, ThingsToBring, HomePageLibrary, Posts, Comments, Categories, IssueTracking, AdminTasks. The default value is GenericList.
Description String The description of the list to add.
Columns String The defination of the columns to add, support json/xml/tempTable
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

AddListColumn

Adds a new column to the specified list.

Input
Name Type Description
List String The name of the list on the SharePoint server.
ColumnName String The name of the column to add.
DisplayName String The display name of the column to add.
DefaultValue String The default value of the column to add.
ColumnType String The data type of the column to add. The valid options are defined by the FieldTypes available in the SharePoint API: https://docs.microsoft.com/en-us/previous-versions/office/sharepoint-csom/ee540543(v=office.15).
MaxLength String The values' maximum length of the column to add.
PrimaryKey String Boolean value indicating whether or not the column should be primary key.
ReadOnly String Boolean value indicating whether or not the column is read only.
Required String Boolean value indicating whether or not the column is required.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

AddUserToGroup

Add the user to specified group.

Input
Name Type Description
LoginName String The login name of the user.
Group String The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.
Result Set Columns
Name Type Description
Result Boolean Boolean value indicating whether the stored procedure* was successful.

AddUserToRole

Add the user to specified role.

Input
Name Type Description
LoginName String The login name of the user.
Role String The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.
Result Set Columns
Name Type Description
Result Boolean Boolean value indicating whether the stored procedure* was successful.

CheckInDocument

Checks in a document to SharePoint and releases the lock on the document.

Input
Name Type Description
File String The path of the file you are using to overwrite the document on SharePoint with. For example: C:\myfolder\myfile.txt.
Library String The name of the library on the SharePoint server. For example: Shared Documents.
Comment String A comment to leave when checking the file in.
RemoteFile String The path of the file on the server. This can be the full URL or simply the file name.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

CheckOutDocument

Checks out a document from SharePoint.

Input
Name Type Description
Library String The name of the library on the SharePoint server.
RemoteFile String The path of the file on the server. This can be the full URL or simply the file name.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

CopyDocument

Copies a document from the SharePoint library.

Note

This procedure makes use of indexed parameters. These input parameters are denoted with a # character at the end of their names.

Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

Suppose there is an input parameter named Param#. Input multiple instances of an indexed parameter like this:

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"
Input
Name Type Description
DocumentName String The name of the document in the document library to be copied.
DocumentLibrary String The name of the document library the document is currently stored on.
NewDocumentLibrary String The name of the document library the document is being copied to.
NewDocumentName String The new name of the document once it has been copied. If left blank, this will be the same as the DocumentName.
MetadataName# String The name of a metadata field to be set for the document.
ReturnID String The return ID of the document.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.
\_dlc_DocId String The document ID.
\_dlc_DocIdUrl String The URL of the document ID.
Vti_author String The creator of the document.
Vti_etag String The e-tag of the document.
ID String The ID of the document.
FileRef String The file reference of the document.

CreateFolder

Adds a folder to a document library on a SharePoint site.

Input
Name Type Description
Library String The name of the library on the SharePoint server.
Name String Name of the folder to which the document is to be added.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the stored procedure* was successful.

CreateSchema

Creates the schema file for the specified SharePoint list. The schema file may be customized manually to exclude unwanted columns or include additional information about columns.

Input
Name Type Accepts Output Streams Description
Table String False The name of the table for which to create a schema.
TableDescription String False An optional description of the table.
FileStream String True OutputStream to write the created schema
WriteToFile String False Whether to write to an output file or not. Defaults to true, must be set to false to write to FileStream or FileData.
Result Set Columns
Name Type Description
Result String Returns Success or Failure.
SchemaFile String The generated schema file.
Columns String The number of columns found.
FileData String The generated schema encoded in base64. Only returned if WriteToFile set to false and FileStream is not set.

DeleteAttachment

Delete an attachment from a SharePoint list item.

Input
Name Type Description
URL String Full URL to attachment to be deleted.
List String The name of the List on the SharePoint server.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the stored procedure* was successful.

DeleteDocument

Delete a document on the SharePoint library.

Input
Name Type Description
Library String The name of the library on the SharePoint server.
Path String The path of the file (or folder) to remove from the document library.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the stored procedure* was successful.

DeleteList

Permanently deletes a list from a SharePoint site.

Input
Name Type Description
List String The name of the list on the SharePoint server.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

DeleteListColumn

Deletes a column from the specified list.

Input
Name Type Description
List String The name of the list on the SharePoint server.
ColumnName String The name of the column to delete.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

DeleteUserFromGroup

Delete the user from specified group.

Input
Name Type Description
LoginName String The login name of the user.
Group String The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.
Result Set Columns
Name Type Description
Result Boolean Boolean value indicating whether the stored procedure* was successful.

DeleteUserFromRole

Delete the user from specified role.

Input
Name Type Description
LoginName String The login name of the user.
Role String The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions.
Result Set Columns
Name Type Description
Result Boolean Boolean value indicating whether the stored procedure* was successful.

DiscardCheckOutDocument

Discards a check out on a document in SharePoint. This does not check a new file into SharePoint. It only releases the lock on the document.

Input
Name Type Description
Library String The name of the library on the SharePoint server.
RemoteFile String The name of the file being checked out.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

DownloadAttachment

Download a document from the SharePoint list.

Input
Name Type Accepts Output Streams Description
File String False The path of the file to be saved.
RemoteFile String False The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded.
FileStream String True OutputStream to write the downloaded attachment. Only returned if File is not set.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.
FileData String The downloaded BASE64 encoded file content. Only returned if File and FileStream is not set.

DownloadDocument

Download a document from the SharePoint library.

Input
Name Type Accepts Output Streams Description
File String False The path of the file to be saved.
Library String False The name of the library on the SharePoint server.
RemoteFile String False The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded.
FileStream String True OutputStream to write the downloaded document. Only returned if File is not set.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.
FileData String The downloaded file content. Only returned if File and FileStream is not set.

MoveAttachmentOrDocument

Moves a document or attachment from a source folder to a destination folder.

The MoveAttachmentOrDocument stored procedure* requires the List, SourceFileURL and DestinationFolderURL parameters to move an attachment or document.

For Example:

EXEC MoveAttachmentOrDocument List = 'Test134', SourceFileURL = '/Shared Documents/Dummy.txt', DestinationFolderURL = '/Shared Documents/MySite'
Input
Name Type Description
List String Name of the list from which you want to move the document or attachment.
SourceFileURL String URL of the source file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
DestinationFolderURL String URL of the destination folder where you want to move the file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory: /Shared Documents/ Sub-directory:/Shared Documents/MyFolder/ If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
Result Set Columns
Name Type Description
Result String Value indicating whether the operation was successful.

RenameAttachmentOrDocument

Renames a document or attachment.

The RenameAttachmentOrDocument stored procedure* requires the List, SourceFileURL and NewFileName parameters to rename an attachment or document.

For Example:

EXEC RenameAttachmentOrDocument List = 'Test134', SourceFileURL = '/Shared Documents/Dummy.txt', NewFileName = 'Dummy1.txt'
Input
Name Type Description
List String Name of the list containing the document or attachment you would like to rename.
SourceFileURL String URL of the file you want to rename, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
NewFileName String New name of the file, with extension.
Result Set Columns
Name Type Description
Result String Value indicating whether the operation was successful.

UpdateList

Updates a list on a SharePoint site.

Input
Name Type Description
List String The name of the list on the SharePoint server.
AllowMultiResponses String Set to True to allow multiple responses to the survey.
Description String A string that contains the description for the list.
Direction String A string that contains LTR if the reading order is left-to-right, RTL if it is right-to-left, or None.
EnableAssignedToEmail String Set to True to enable assigned-to e-mail for the issues list.
EnableAttachments String Set to True to enable attachments to items in the list. Does not apply to document libraries.
EnableModeration String Set to True to enable Content Approval for the list.
EnableVersioning String Set to True to enable versioning for the list.
Hidden String Set to True to hide the list so that it does not appear on the Documents and Lists page, Quick Launch bar, Modify Site Content page, or Add Column page as an option for lookup fields.
MultipleDataList String Set to True to specify that the list in a Meeting Workspace site contains data for multiple meeting instances within the site.
Ordered String Set to True to specify that the option to allow users to reorder items in the list is available on the Edit View page for the list.
ShowUser String Set to True to specify that names of users are shown in the results of the survey.
Title String A string that contains the title of the list.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

UpdateListColumn

Updates a column to the specified list.

Input
Name Type Description
List String The name of the list on the SharePoint server.
ColumnName String The name of the column to update.
DisplayName String The updated value of the display name.
DefaultValue String The updated default value of the specified column.
ColumnType String The updated data type of the specified column.
MaxLength String The updated maximum length of the specified column.
PrimaryKey String Use this to make or not the exisitng column, primary key.
ReadOnly String Use this to make or not the exisitng column, readonly.
Required String Use this to make or not the exisitng column, required.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.

UploadDocument

Upload a document to the SharePoint library.

Note

This procedure makes use of indexed parameters. These input parameters are denoted with a # character at the end of their names.

Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

Suppose there is an input parameter named Param#. Input multiple instances of an indexed parameter like this:

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"
Input
Name Type Accepts Input Streams Description
File String False The path of the file to be added.
FileContent String False Base64 encoded content of the file to be added. If specified, the value of 'File' input will be ignored.
Library String False The URL of the directory you would like to upload file(s) to, relative to the base URL supplied in the connector's connection properties. For example: Root directory: Shared Documents If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
Name String False The name assigned to the new file. If uploading to the root directory of the site, supply only the filename. If uploading to a folder nested at some level inside the root directory, prepend the full parent subdirectory. For example: Root Directory: filename.txt Sub-directory:MyFolder/filename.txt
Content String True The content as InputStream to be uploaded when File is not specified.
ReturnID String False Boolean value indicating whether to return the ID and other metadata fields of the newly created record. An extra API request is needed to get this value. The default value is false.
MetadataName# String False The name of a metadata field to be set for the document. It must be the column name present in the table of library.
MetadataValue# String False The value of a metadata field to be set for the document.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the stored procedure* was successful.
ID String The internal ID of the document.
\_dlc_DocId String The document ID.
\_dlc_DocIdUrl String The URL of the document ID.
Vti_author String The creator of the document.
Vti_etag String The e-tag of the document.
FileRef String The file reference of the document.

REST Data Model

The Jitterbit Connector for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.

Tables

Tables describes the available tables.

Lists in your Microsoft SharePoint site are exposed as relational tables dynamically. Which means any change you make in your lists, i.e adding new list or adding new fields, will be reflected on the driver.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They surface additional capabilities of the Microsoft SharePoint API such as searching, updating, and modifying information.

Use OData standard

Since the REST API is OData based, server side filters, are done using OData standard. So the driver takes the most of the server filtering, by reading the metadata file and determing which filters can be done on the server.

Note

When executing "SELECT *" queries, the Microsoft SharePoint REST API response, does not return all the available fields. So to avoid too many null values, the provider will select all the columns explicitly using the $select filter. However, the provider will do this only if the $select filter's length is not bigger than 1500, to avoid an error from Microsoft SharePoint REST API regarding the URL length. This is a limitation of the Microsoft SharePoint REST API, so in these cases, the only way to see the actual value of some columns, is to explicitly select them in your query.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Jitterbit Connector for Microsoft SharePoint Views

Name Description
Attachments Read Attachments for the specified item on the specified list.
Files Query the available files on your sharepoint site.
Groups Query the available groups on your sharepoint site.
Lists Query the available lists on your sharepoint site.
RoleAssignmentMember Get Web RoleAssignments member.
RoleAssignments Get Web RoleAssignments.
RoleDefinitionBindings Get Web Role definition binding.
Roles Query the roles your users can have.
Subsites This lists the available subsites.
Users Query the available users on your sharepoint site.

Attachments

Read Attachments for the specified item on the specified list.

Table Specific Information
Select

Note

List and ItemId are required to return Attachments.

List can be fetched from the Lists view(Title column).

SELECT * FROM Attachments WHERE List = 'TestApp' AND ItemID = 1
Columns
Name Type References Description
Id [KEY] String The ID of the attachment on the item.
Updated Datetime The updated date of the attachment on the item.
FileName String The FileName of the attachment on the item.
ServerRelativePath_DecodedUrl String The ServerRelativePath DecodedUrl of the attachment on the item.
FileNameAsPath_DecodedUrl String The FileNameAsPath DecodedUrl of the attachment on the item.
ServerRelativeUrl String The ServerRelativeUrl of the attachment on the item.
List String The internal name of the list to retrieve attachments from.
ItemID String The ID of the item on the list to retrieve attachments from.

Files

Query the available files on your sharepoint site.

Columns
Name Type References Description
Id [KEY] String
CreatedBy_Id String
CreatedBy_Name String
CreatedBy_Puid String
ETag String
LastModifiedBy_Id String
LastModifiedBy_Name String
LastModifiedBy_Puid String
Name String
Size Int
TimeCreated Datetime
TimeLastModified Datetime
Url String

Groups

Query the available groups on your sharepoint site.

Table Specific Information
Select
SELECT * FROM Groups
Columns
Name Type References Description
Id [KEY] Int The Group Id.
AllowMembersEditMembership Bool
AllowRequestToJoinLeave Bool
AutoAcceptRequestToJoinLeave Bool
CanCurrentUserEditMembership Bool
CanCurrentUserManageGroup Bool
CanCurrentUserViewMembership Bool
Description String
OnlyAllowMembersViewMembership Bool
OwnerTitle String
RequestToJoinLeaveEmailSetting String
LinkedOwner String
LinkedUsers String

Lists

Query the available lists on your sharepoint site.

Columns
Name Type References Description
HasUniqueRoleAssignments [KEY] Bool
LinkedFirstUniqueAncestorSecurableObject String
LinkedRoleAssignments String
ServerRelativeUrl String The relative URL of the file based on the URL for the server. This column is available when the URL is a subsite.
Id [KEY] String
AllowContentTypes Bool
AllowDeletion Bool
BaseTemplate Int
BaseType Int
BrowserFileHandling Int
ContentTypesEnabled Bool
CrawlNonDefaultViews Bool
Created Datetime
CurrentChangeToken_StringValue String
CustomActionElements_Items String
DataSource_Properties String
DefaultContentApprovalWorkflowId String
DefaultDisplayFormUrl String
DefaultEditFormUrl String
DefaultItemOpenUseListSetting Bool
DefaultNewFormUrl String
DefaultViewPath_DecodedUrl String
DefaultViewUrl String
Description String
Direction String
DisableGridEditing Bool
DocumentTemplateUrl String
DraftVersionVisibility Int
EffectiveBasePermissions_High Long
EffectiveBasePermissions_Low Long
EffectiveBasePermissionsForUI_High Long
EffectiveBasePermissionsForUI_Low Long
EnableAssignToEmail Bool
EnableAttachments Bool
EnableFolderCreation Bool
EnableMinorVersions Bool
EnableModeration Bool
EnableRequestSignOff Bool
EnableVersioning Bool
EntityTypeName String
ExcludeFromOfflineClient Bool
ExemptFromBlockDownloadOfNonViewableFiles Bool
FileSavePostProcessingEnabled Bool
ForceCheckout Bool
HasExternalDataSource Bool
Hidden Bool
ImagePath_DecodedUrl String
ImageUrl String
IrmEnabled Bool
IrmExpire Bool
IrmReject Bool
IsApplicationList Bool
IsCatalog Bool
IsEnterpriseGalleryLibrary Bool
IsPrivate Bool
IsSiteAssetsLibrary Bool
IsSystemList Bool
ItemCount Int
LastItemDeletedDate Datetime
LastItemModifiedDate Datetime
LastItemUserModifiedDate Datetime
ListExperienceOptions Int
ListItemEntityTypeFullName String
MajorVersionLimit Int
MajorWithMinorVersionsLimit Int
MultipleDataList Bool
NoCrawl Bool
OnQuickLaunch Bool
PageRenderType Int
ParentWebPath_DecodedUrl String
ParentWebUrl String
ParserDisabled Bool
ReadSecurity Int
SchemaXml String
ServerTemplateCanCreateFolders Bool
TemplateFeatureId String
Title String
ValidationFormula String
ValidationMessage String
WriteSecurity Int
LinkedContentTypes String
LinkedCreatablesInfo String
LinkedDefaultView String
LinkedDescriptionResource String
LinkedEventReceivers String
LinkedFields String
LinkedForms String
LinkedInformationRightsManagementSettings String
LinkedItems String
LinkedParentWeb String
LinkedRootFolder String
LinkedSubscriptions String
LinkedTitleResource String
LinkedUserCustomActions String
LinkedViews String
LinkedWorkflowAssociations String

RoleAssignmentMember

Get Web RoleAssignments member.

Table Specific Information
Select

Note

PrincipalId is required to return RoleAssignmentMember.

SELECT * FROM RoleAssignmentMember WHERE PrincipalId = 3
SELECT * FROM RoleAssignmentMember WHERE List = 'TestApp' AND PrincipalId = 3

SELECT * FROM RoleAssignmentMember WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns
Name Type References Description
ID [KEY] String The ID of the role assigned member.
Updated Datetime The updated date for role assigned member.
IsHiddenInUI Boolean A boolean indicating if the assigned role member is hidden in UI.
LoginName String The login name of the role assigned member.
Title String The title of the role assigned member.
AllowMembersEditMembership Boolean A boolean indicating whether to allow members edit membership.
AllowRequestToJoinLeave Boolean A boolean indicating whether to allow request to join leave.
AutoAcceptRequestToJoinLeave Boolean A boolean indicating whether to auto accept request to join leave.
Description String The description of the role assigned member.
OnlyAllowMembersViewMembership Boolean A boolean indicating whether to only allow members view membership.
OwnerTitle String The owner title of the role assigned member.
RequestToJoinLeaveEmailSetting String The request to join leave email setting of the role assigned member.
PrincipalId String RoleAssignments.PrincipalId The Principal Id.
List String Lists.Title The internal name of the list to retrieve role assigned member from.
ItemId String List item ID for role assignment.

RoleAssignments

Get Web RoleAssignments.

Table Specific Information
Select
SELECT * FROM RoleAssignments WHERE List = 'TestApp'

SELECT * FROM RoleAssignments WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns
Name Type References Description
ID [KEY] String The ID of the role assigned.
PrincipalId Long The Principal ID for role assigned.
Updated Datetime The updated date for role assigned.
List String Lists.Title The internal name of the list to retrieve role assigned from.
ItemId String List item ID for role assignment.

RoleDefinitionBindings

Get Web Role definition binding.

Table Specific Information
Select

Note

PrincipalId is required to return RoleDefinitionBindings.

SELECT * FROM RoleDefinitionBindings WHERE PrincipalId = 3
SELECT * FROM RoleDefinitionBindings WHERE List = 'TestApp' AND PrincipalId = 3

SELECT * FROM RoleDefinitionBindings WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns
Name Type References Description
ID [KEY] String The ID of the role assigned member.
BasePermissions_High Int64 The base permissions high.
BasePermissions_Low Int64 The base permissions low.
Description String The description.
Hidden Boolean A boolean indicating if it is hidden.
Name String The name.
Order Int The order.
RoleTypeKind Int The role type kind.
PrincipalId String RoleAssignments.PrincipalId The Principal Id.
List String Lists.Title The internal name of the list to retrieve role assigned member from.
ItemId String List item ID for role defintion.

Roles

Query the roles your users can have.

Columns
Name Type References Description
Id [KEY] Int
BasePermissions_High Long
BasePermissions_Low Long
Description String
Hidden Bool
Name String
Order Int
RoleTypeKind Int

Subsites

This lists the available subsites.

Table Specific Information
Select
SELECT * FROM Subsites
Columns
Name Type References Description
Id [KEY] String The ID of the subsite.
AllowRssFeeds Boolean The AllowRssFeeds of the subsite.
AlternateCssUrl String The AlternateCssUrl of the subsite.
AppInstanceId String The AppInstanceId of the subsite.
ClassicWelcomePage String The ClassicWelcomePage of the subsite.
Configuration Int The Configuration of the subsite.
Created Datetime The Created of the subsite.
CurrentChangeToken_StringValue String The CurrentChangeToken_StringValue of the subsite.
CustomMasterUrl String The CustomMasterUrl of the subsite.
Description String The Description of the subsite.
DesignPackageId String The DesignPackageId of the subsite.
DocumentLibraryCalloutOfficeWebAppPreviewersDisabled Boolean The DocumentLibraryCalloutOfficeWebAppPreviewersDisabled of the subsite.
EnableMinimalDownload Boolean The EnableMinimalDownload of the subsite.
FooterEmphasis Int The FooterEmphasis of the subsite.
FooterEnabled Boolean The FooterEnabled of the subsite.
FooterLayout Int The FooterLayout of the subsite.
HeaderEmphasis Int The HeaderEmphasis of the subsite.
HeaderLayout Int The HeaderLayout of the subsite.
HideTitleInHeader Boolean The HideTitleInHeader of the subsite.
HorizontalQuickLaunch Boolean The HorizontalQuickLaunch of the subsite.
IsHomepageModernized Boolean The IsHomepageModernized of the subsite.
IsMultilingual Boolean The IsMultilingual of the subsite.
IsRevertHomepageLinkHidden Boolean The IsRevertHomepageLinkHidden of the subsite.
KeepFieldUserResources Boolean The KeepFieldUserResources of the subsite.
Language Int The Language of the subsite.
LastItemModifiedDate Datetime The LastItemModifiedDate of the subsite.
LastItemUserModifiedDate Datetime The LastItemUserModifiedDate of the subsite.
LogoAlignment Int The LogoAlignment of the subsite.
MasterUrl String The MasterUrl of the subsite.
MegaMenuEnabled Boolean The MegaMenuEnabled of the subsite.
NavAudienceTargetingEnabled Boolean The NavAudienceTargetingEnabled of the subsite.
NoCrawl Boolean The NoCrawl of the subsite.
ObjectCacheEnabled Boolean The ObjectCacheEnabled of the subsite.
OverwriteTranslationsOnChange Boolean The OverwriteTranslationsOnChange of the subsite.
ResourcePath_DecodedUrl String The ResourcePath_DecodedUrl of the subsite.
QuickLaunchEnabled Boolean The QuickLaunchEnabled of the subsite.
RecycleBinEnabled Boolean The RecycleBinEnabled of the subsite.
SearchScope Int The SearchScope of the subsite.
ServerRelativeUrl String The ServerRelativeUrl of the subsite.
SiteLogoUrl String The SiteLogoUrl of the subsite.
SyndicationEnabled Boolean The SyndicationEnabled of the subsite.
TenantAdminMembersCanShare Int The TenantAdminMembersCanShare of the subsite.
Title String The Title of the subsite.
TreeViewEnabled Boolean The TreeViewEnabled of the subsite.
UIVersion Int The UIVersion of the subsite.
UIVersionConfigurationEnabled Boolean The UIVersionConfigurationEnabled of the subsite.
Url String The Url of the subsite.
WebTemplate String The WebTemplate of the subsite.
WelcomePage String The WelcomePage of the subsite.

Users

Query the available users on your sharepoint site.

Table Specific Information
Select
SELECT * FROM Users // Fetch all the Users

SELECT * FROM Users WHERE GroupId = 5 // Fetch a user for a particular Group
Columns
Name Type References Description
AadObjectId_NameId String
AadObjectId_NameIdIssuer String
Email String
Expiration String
IsEmailAuthenticationGuestUser Bool
IsShareByEmailGuestUser Bool
IsSiteAdmin Bool
UserId_NameId String
UserId_NameIdIssuer String
UserPrincipalName String
LinkedAlerts String
LinkedGroups String
LoginName String The login name of the user.
Title String The Title of the user.
IsHiddenInUI Boolean A boolean indicating if the user is hidden in UI.
GroupId Int The group ID the user is added to.

Stored Procedures

Stored procedures* are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft SharePoint.

Stored procedures* accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft SharePoint, along with an indication of whether the procedure succeeded or failed.

Jitterbit Connector for Microsoft SharePoint Stored Procedures

Name Description
AddAttachment Add an attachment into a sharepoint list.
CheckInDocument Check in specific document inside document library.
CheckOutDocument Check out specific document inside document library.
CopyDocument Copies the file to the destination URL..
CreateFolder Adds a folder to a document library on a SharePoint site.
DeleteAttachment Deletes an attachment from Sharepoint list.
DeleteDocument Delete a Document from the SharePoint document library.
DiscardCheckOutDocument Reverts an existing checkout for the file.
DownloadAttachment Download a document from the SharePoint list.
DownloadDocument Download a document from the SharePoint library.
GetAdminConsentURL Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.
GetCurrentUser Retrieves information about the current logged in user.
GetOAuthAccessToken Gets the OAuth access token from SharePoint.
GetOAuthAuthorizationURL Gets the SharePoint authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to SharePoint.
MoveAttachmentOrDocument Moves a document or attachment from a source folder to a destination folder.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with SharePoint.
RenameAttachmentOrDocument Renames a document or attachment.
UploadDocument Uploads a document into the SharePoint library.

AddAttachment

Add an attachment into a sharepoint list.

Input
Name Type Accepts Input Streams Description
ListTitle String False Title of the list item.
ItemId String False ID of the list item.
FileName String False Name of the File to be added in document library
InputFilePath String False Location of the file to be attached.
Content String True The content as InputStream to be uploaded when InputFilePath is not specified.
Result Set Columns
Name Type Description
RelativeUrl String Relative URL of the attachment that has been added.
Status String Message indicating whether the operation was successful or not.

CheckInDocument

Check in specific document inside document library.

Input
Name Type Description
RelativeURL String Relative URL of the folder.
DocumentName String Name of the File to be Checked in.
Comment String Optional message while Checking in Document.
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

CheckOutDocument

Check out specific document inside document library.

Input
Name Type Description
RelativeURL String Relative URL of the folder.
DocumentName String Name of the File to be Checked in.
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

CopyDocument

Copies the file to the destination URL..

Input
Name Type Description
SourceFileRelativeUrl String Relative URL of the source file.
DestFileRelativeUrl String Relative URL of the destination file.
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

CreateFolder

Adds a folder to a document library on a SharePoint site.

Input
Name Type Description
RelativeURL String Relative URL of the folder.
FolderName String Name of the folder to be created.
Result Set Columns
Name Type Description
Id String Unique identifier Value Returned after completing the operation.
Status String Message indicating whether the operation was successful or not.

DeleteAttachment

Deletes an attachment from Sharepoint list.

Input
Name Type Description
ListTitle String Title of the list item.
ItemId String ID of the list item.
FileName String Name of the File to be added in document library
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

DeleteDocument

Delete a Document from the SharePoint document library.

Input
Name Type Description
RelativeURL String Relative URL of the Folder.
DocumentName String Name of the File to be Deleted.
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

DiscardCheckOutDocument

Reverts an existing checkout for the file.

Input
Name Type Description
RelativeURL String Relative URL of the folder.
DocumentName String Name of the File to be Checked in.
Result Set Columns
Name Type Description
Status String Message indicating whether the operation was successful or not.

DownloadAttachment

Download a document from the SharePoint list.

Stored Procedure* Specific Information
Insert

RemoteFile can be both relative to the server, or it can be the full URL of the file. Below are some examples:

EXECUTE DownloadAttachment File = 'C:/Users/User/Desktop/DownloadedFile.txt', RemoteFile = 'https://mysite.sharepoint.com/Lists/MyCustomList/Attachments/1/FileToDownload.txt';

EXECUTE DownloadAttachment File = 'C:/Users/User/Desktop/DownloadedFile.txt', RemoteFile = '/Lists/MyCustomList/Attachments/1/FileToDownload.txt';
Input
Name Type Accepts Output Streams Description
File String False The path of the file to be saved.
RemoteFile String False The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded.
Encoding String False The data will be output to FileData in the specified encoding. The allowed values are NONE, BASE64. The default value is BASE64.
FileStream String True OutputStream to write the downloaded attachment. Only used if File is not provided.
ReadTimeout String False How many seconds the download may run. If given the download always fails if it runs longer than this, unlike Timeout which only triggers if the download stalls.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.
FileData String The downloaded file content. Only returned if File and FileStream is not set.

DownloadDocument

Download a document from the SharePoint library.

Stored Procedure* Specific Information
Insert

RemoteFile can be both relative to the library, or it can be the full URL of the file. Below are some examples:

EXECUTE DownloadDocument File = 'C:/Users/User/Desktop/DownloadedFile.txt', Library = 'Shared Documents', RemoteFile = 'https://mysite.sharepoint.com/Shared Documents/newFolder/FileToDownload.txt';

EXECUTE DownloadDocument File = 'C:/Users/User/Desktop/DownloadedFile.txt', Library = 'Shared Documents', RemoteFile = '/newFolder/FileToDownload.txt';
Input
Name Type Accepts Output Streams Description
File String False The path of the file to be saved. You should include the new filename. For example, 'C:/Users/User/Desktop/DownloadedFile.txt'.
Library String False The name of the library on the SharePoint server. For example, 'Shared Documents'.
RemoteFile String False This can be either the relative path to the library or the full URL of the file.
Encoding String False The data will be output to FileData in the specified encoding. The allowed values are NONE, BASE64. The default value is BASE64.
FileStream String True OutputStream to write the downloaded document. Only returned if File is not set.
ReadTimeout String False How many seconds the download may run. If given the download always fails if it runs longer than this, unlike Timeout which only triggers if the download stalls.
Result Set Columns
Name Type Description
Result String Boolean value indicating whether the operation was successful.
FileData String The downloaded file content. Only returned if File and FileStream is not set.

GetAdminConsentURL

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input
Name Type Description
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Reply URL in the Azure AD app settings.
State String The same value for state that you sent when you requested the authorization code.
Scope String The scope or permissions you are requesting from the Admin The default value is AllSites.Manage.
Result Set Columns
Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

GetCurrentUser

Retrieves information about the current logged in user.

Result Set Columns
Name Type Description
Id Int The ID of the user.
Title String The title of the user.
Email String The email of the user.
IsSiteAdmin Boolean Whether the user is a site admin or not.

GetOAuthAccessToken

Gets the OAuth access token from SharePoint.

Input
Name Type Description
AuthMode String The type of authentication mode to use. The allowed values are APP, WEB.
Verifier String The verifier token returned by SharePoint after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode.
CallbackUrl String The URL the user will be redirected to after authorizing your application.
Scope String The scope or permissions you are requesting from the user.
State String Any value that you wish to be sent with the callback.
Prompt String Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from SharePoint.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime for the access token in seconds.

GetOAuthAuthorizationURL

Gets the SharePoint authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to SharePoint.

Input
Name Type Description
CallbackUrl String The URL that Sharepoint will return to after the user has authorized your app.
Scope String The scope or permissions you are requesting from the user. The default value is AllSites.Manage.
State String Any value that you wish to be sent with the callback.
Prompt String Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
URL String The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with.

MoveAttachmentOrDocument

Moves a document or attachment from a source folder to a destination folder.

The MoveAttachmentOrDocument stored procedure* requires the SourceFileURL and DestinationFolderURL parameters to move an attachment or document.

For Example:

EXEC MoveAttachmentOrDocument SourceFileURL = '/Shared Documents/Dummy.txt', DestinationFolderURL = '/Shared Documents/MySite'
Input
Name Type Description
SourceFileURL String URL of the source file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
DestinationFolderURL String URL of the destination folder where you want to move the file , relative to the base URL supplied in the connector's connection properties. For example: Root Directory: /Shared Documents/ Sub-directory:/Shared Documents/MyFolder/ If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
Result Set Columns
Name Type Description
Result String Value indicating whether the operation was successful.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with SharePoint.

Input
Name Type Description
OAuthRefreshToken String The old token to be refreshed.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from SharePoint.
ExpiresIn String The remaining lifetime on the access token.

RenameAttachmentOrDocument

Renames a document or attachment.

The RenameAttachmentOrDocument stored procedure* requires the SourceFileURL and NewFileName parameters to rename an attachment or document.

For Example:

EXEC RenameAttachmentOrDocument  SourceFileURL = '/Shared Documents/Dummy.txt', NewFileName = 'Dummy1.txt'
Input
Name Type Description
SourceFileURL String URL of the file you want to rename, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
NewFileName String New name of the file, with extension.
Result Set Columns
Name Type Description
Result String Value indicating whether the operation was successful.

UploadDocument

Uploads a document into the SharePoint library.

Input
Name Type Accepts Input Streams Description
RelativeUrl String False The URL of the folder you would like to upload file(s) to, relative to the base URL supplied in the connector's connection properties. For example: Root folder: Shared Documents Sub-folder: Shared Documents/MyFolder If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL.
InputFilePath String False The location of the file to be uploaded.
FileName String False Name of the File to be created.
Overwrite String False Boolean value to overwrite the existing file data.
Content String True The content as InputStream to be uploaded when InputFilePath is not specified.
Result Set Columns
Name Type Description
Id String Unique identifier Value Returned after completing the operation.
Status String Message indicating whether the operation was successful or not.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft SharePoint:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Calendar table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Calendar' 
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure* parameters.

The following query returns information about all of the input parameters for the ListItems stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='ListItems' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure* containing the parameter.
ColumnName String The name of the stored procedure* parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Calendar table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Calendar'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:sharepoint:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discover the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures* that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the SOAP Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Data Type Mapping

Data Type Mappings

The connector maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Microsoft SharePoint Schema
Choice (menu) string
Currency float
Date and Time datetime
Hyperlink or Picture string
Lookup string
Multiple lines of text string
Number float
Person or Group string
Single line of text string
Task Outcome string
Yes/No bool

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
AuthScheme The scheme used for authenticating to SharePoint.
URL The base URL for the site.
SharePointEdition The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.
User The SharePoint user account used to authenticate.
Password The password used to authenticate the user.

Azure Authentication

Property Description
AzureTenant The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment The Azure Environment to use when establishing a connection.

SSO

Property Description
SSOLoginURL The identity provider's login URL.
SSODomain The domain of the user when using single sign-on (SSO).
SSOProperties Additional properties required to connect to the identity provider in a semicolon-separated list.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
Scope The scope used for the OAuth flow to access data from the Application.
State Optional value for representing extra OAuth state information.
OAuthGrantType The grant type for the OAuth flow.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

JWT OAuth

Property Description
OAuthJWTCert The JWT Certificate store.
OAuthJWTCertType The type of key store containing the JWT Certificate.
OAuthJWTIssuer The issuer of the Java Web Token.

Kerberos

Property Description
KerberosKDC The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm The Kerberos Realm used to authenticate the user.
KerberosSPN The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm The Kerberos realm of the service.
KerberosServiceKDC The Kerberos KDC of the service.
KerberosTicketCache The full file path to an MIT Kerberos credential cache file.

SSL

Property Description
SSLClientCert The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
SSLClientCertType The type of key store containing the TLS/SSL client certificate.
SSLClientCertPassword The password for the TLS/SSL client certificate.
SSLClientCertSubject The subject of the TLS/SSL client certificate.
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.
Schema The type of schema to use.

Miscellaneous

Property Description
CalculatedDataType The data type to be used for calculated fields.
ContinueOnError Indicates whether or not to continue updating items in a batch after an error.
CreateIDColumns Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.
DisableFilterLimit Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead.
FolderOption An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
IncludeLookupColumns This option controls whether the driver returns the lookup columns defined on a table.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Microsoft SharePoint.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
ShowHiddenColumns Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.
ShowPredefinedColumns Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing.
ShowVersionViews Indicate whether to display the view of list versions. Such as ListA_Versions.
STSURL The URL of the security token service (STS) when using single sign-on (SSO).
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseDisplayNames Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas.
UseEntityTypeName Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.
UseNTLMV1 Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.
UseSimpleNames Boolean determining if simple names should be used for tables and columns.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
AuthScheme The scheme used for authenticating to SharePoint.
URL The base URL for the site.
SharePointEdition The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.
User The SharePoint user account used to authenticate.
Password The password used to authenticate the user.

AuthScheme

The scheme used for authenticating to SharePoint.

Data Type

string

Default Value

NTLM

Remarks

If authenticating to Sharepoint On-Premise instance, together with Password and User, this field is used to authenticate against the server. NTLM is the default option. Use the following options to select your authentication scheme:

  • NTLM: Set this to use your Windows credentials for authentication.
  • Negotiate: If AuthScheme is set to Negotiate, the connector will Negotiate an authentication mechanism with the server. Set AuthScheme to Negotiate if you want to use Kerberos authentication.
  • None: Set this to use anonymous authentication; for example, to access a public site.
  • Basic: Set this to use HTTP Basic authentication.
  • ADFS: Set this to use Single Sign-On authentication with ADFS.

If authenticating to Sharepoint Online, OAuth is the default option. Use the following options to select your authentication scheme:

  • AzureAD: Set this to perform Azure Active Directory OAuth Authentication.
  • AzurePassword: Set this to authenticate using OAuth with Password GrantType.
  • OAuthJWT: If AuthScheme is set to OAuthJWT, the connector will try to connect with a self signed certificate with Azure AD.
  • PingFederate: Set this to use Single Sign-On authentication with PingFederate.
  • ADFS: Set this to use Single Sign-On authentication with ADFS.
  • OneLogin: Set this to use Single Sign-On authentication with OneLogin.
  • OKTA: Set this to use Single Sign-On authentication with OKTA.
  • OAuth: Set this to use OAuth 2 authentication with GrantType CODE or PASSWORD.
  • AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
  • SharePointOAuth: Set this to use OAuth authentication with Sharepoint APP for Client Credentials.

URL

The base URL for the site.

Data Type

string

Default Value

""

Remarks

The following are examples of valid URLs:

  • http://server/SharePoint/
  • http://server/Sites/mysite/
  • http://server:90/

The provider will use URL to derive URLs for other calls to the server.

SharePointEdition

The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.

Data Type

string

Default Value

SharePoint OnPremise

Remarks

The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.

User

The SharePoint user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the SharePoint server.

For SharePoint On-Premise, User should include the domain and will look similar to the following: DOMAIN\Username.

For SharePoint Online, User will look similar to the following: username@domain.onmicrosoft.com.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

Azure Authentication

This section provides a complete list of Azure authentication properties you can configure.

Property Description
AzureTenant The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment The Azure Environment to use when establishing a connection.

AzureTenant

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Data Type

string

Default Value

""

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory ID in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

AzureEnvironment

The Azure Environment to use when establishing a connection.

Possible Values

GLOBAL, CHINA, USGOVT, USGOVTDOD

Data Type

string

Default Value

GLOBAL

Remarks

In most cases, leaving the environment set to global works. However, if you have added your Azure Account to a different environment, you can use AzureEnvironment to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.

Use this property to resolve issues with the URL suffix (e.g., set it to USGOVT if your URL ends in .us), based on which cloud the you are using. More information about this setting can be found in Microsoft documentation about National clouds

SSO

This section provides a complete list of SSO properties you can configure.

Property Description
SSOLoginURL The identity provider's login URL.
SSODomain The domain of the user when using single sign-on (SSO).
SSOProperties Additional properties required to connect to the identity provider in a semicolon-separated list.

SSOLoginURL

The identity provider's login URL.

Data Type

string

Default Value

""

Remarks

The identity provider's login URL.

SSODomain

The domain of the user when using single sign-on (SSO).

Data Type

string

Default Value

""

Remarks

This property is only applicable when using single sign-on (AuthScheme is configured to use an SSO authentication scheme) and if the domain of the User (e.g. user@mydomain.com) is different than the domain configured within the SSO service (e.g. user@myssodomain.com).

This property may be required when using the ADFS, OneLogin, or OKTA SSO services.

SSOProperties

Additional properties required to connect to the identity provider in a semicolon-separated list.

Data Type

string

Default Value

""

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in .

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
Scope The scope used for the OAuth flow to access data from the Application.
State Optional value for representing extra OAuth state information.
OAuthGrantType The grant type for the OAuth flow.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your Add-In settings.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\SharePoint Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\SharePoint Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."
  • Mac: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

Scope

The scope used for the OAuth flow to access data from the Application.

Possible Values

NONE, AllSites.Manage, AllSites.Read, AllSites.Write, .default

Data Type

string

Default Value

NONE

Remarks

The scope used for the OAuth flow to access data from the Application.

Note

If not specified or left to 'NONE', the connector sets the default scope dependant upon OAuthGrantType. Defaults to 'AllSites.Manage' unless using JWT OAuth, Azure Service Principal, or CLIENT grant type. The available values are:

  • AllSites.Read: Enables reading custom lists
  • AllSites.Write: Enables reading & writing custom lists
  • AllSites.Manage: Enables reading, writing and creating custom lists
  • .default: Requests application permissions without a user context. All the application permissions that have been granted for that web API are included in the retrieved OAuthAccessToken.

State

Optional value for representing extra OAuth state information.

Data Type

string

Default Value

""

Remarks

Optional value for representing extra OAuth state information.

OAuthGrantType

The grant type for the OAuth flow.

Possible Values

CODE, PASSWORD, SAML_1, CLIENT

Data Type

string

Default Value

CLIENT

Remarks

The following options are available: CODE,PASSWORD,SAML_1,CLIENT

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

JWT OAuth

This section provides a complete list of JWT OAuth properties you can configure.

Property Description
OAuthJWTCert The JWT Certificate store.
OAuthJWTCertType The type of key store containing the JWT Certificate.
OAuthJWTIssuer The issuer of the Java Web Token.

OAuthJWTCert

The JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The name of the certificate store for the client certificate.

The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject

field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

Property Description
MY A certificate store holding personal certificates with their associated private keys.
CA Certifying authority certificates.
ROOT Root certificates.
SPC Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

OAuthJWTCertType

The type of key store containing the JWT Certificate.

Data Type

string

Default Value

USER

Remarks

This property can take one of the following values:

Property Description
USER For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
MACHINE For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFILE The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBLOB The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKEY_FILE The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFILE The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB The certificate store is a string that contains a certificate in XML format.

OAuthJWTIssuer

The issuer of the Java Web Token.

Data Type

string

Default Value

""

Remarks

The issuer of the Java Web Token. In most cases, this takes the value of the OAuth App ID (Client Id) connection property and does not need to be individually set.

Kerberos

This section provides a complete list of Kerberos properties you can configure.

Property Description
KerberosKDC The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm The Kerberos Realm used to authenticate the user.
KerberosSPN The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm The Kerberos realm of the service.
KerberosServiceKDC The Kerberos KDC of the service.
KerberosTicketCache The full file path to an MIT Kerberos credential cache file.

KerberosKDC

The Kerberos Key Distribution Center (KDC) service used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:

  • KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
  • Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
  • Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the configured domain name and host.

Note

Windows authentication is supported in JRE 1.6 and above only.

The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:

  • KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
  • Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
  • Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the configured domain name and host.

Note

Windows authentication is supported in JRE 1.6 and above only.

KerberosRealm

The Kerberos Realm used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.

If Kerberos Realm is not specified, the connector will attempt to detect these properties automatically from the following locations:

  • KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
  • Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
  • Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the user-configured domain name and host. This might work in some Windows environments.

Note

Kerberos-based authentication is supported in JRE 1.6 and above only.

KerberosSPN

The service principal name (SPN) for the Kerberos Domain Controller.

Data Type

string

Default Value

""

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

KerberosKeytabFile

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Data Type

string

Default Value

""

Remarks

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

KerberosServiceRealm

The Kerberos realm of the service.

Data Type

string

Default Value

""

Remarks

The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosServiceKDC

The Kerberos KDC of the service.

Data Type

string

Default Value

""

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosTicketCache

The full file path to an MIT Kerberos credential cache file.

Data Type

string

Default Value

""

Remarks

This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLClientCert The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
SSLClientCertType The type of key store containing the TLS/SSL client certificate.
SSLClientCertPassword The password for the TLS/SSL client certificate.
SSLClientCertSubject The subject of the TLS/SSL client certificate.
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLClientCert

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).

Data Type

string

Default Value

""

Remarks

The name of the certificate store for the client certificate.

The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.

SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

Property Description
MY A certificate store holding personal certificates with their associated private keys.
CA Certifying authority certificates.
ROOT Root certificates.
SPC Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).

SSLClientCertType

The type of key store containing the TLS/SSL client certificate.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_FILE, SSHPUBLIC_KEY_BLOB, P7BFILE, PPKFILE, XMLFILE, XMLBLOB

Data Type

string

Default Value

USER

Remarks

This property can take one of the following values:

Property Description
USER - default For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java.
MACHINE For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java.
PFXFILE The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java.
JKSBLOB The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java.
PEMKEY_FILE The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE The certificate store is the name of a file that contains a PuTTY Private Key (PPK).
XMLFILE The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB The certificate store is a string that contains a certificate in XML format.

SSLClientCertPassword

The password for the TLS/SSL client certificate.

Data Type

string

Default Value

""

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

SSLClientCertSubject

The subject of the TLS/SSL client certificate.

Data Type

string

Default Value

*

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.

Field Meaning
CN Common Name. This is commonly a host name like www.server.com.
O Organization
OU Organizational Unit
L Locality
S State
C Country
E Email Address

If a field value contains a comma, it must be quoted.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.
Schema The type of schema to use.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\SharePoint Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note

Given that this connector supports multiple schemas, the structure for Microsoft SharePoint custom schema files is as follows:

  • Each schema is given a folder corresponding to that schema name.
  • These schema folders are contained in a parent folder.
  • The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\SharePoint Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Schema

The type of schema to use.

Possible Values

SOAP, REST

Data Type

string

Default Value

SOAP

Remarks

The schemas available are REST (to use SharePoint REST API) and SOAP (to use Sharepoint SOAP API).

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
CalculatedDataType The data type to be used for calculated fields.
ContinueOnError Indicates whether or not to continue updating items in a batch after an error.
CreateIDColumns Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.
DisableFilterLimit Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead.
FolderOption An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
IncludeLookupColumns This option controls whether the driver returns the lookup columns defined on a table.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Microsoft SharePoint.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
ShowHiddenColumns Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.
ShowPredefinedColumns Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing.
ShowVersionViews Indicate whether to display the view of list versions. Such as ListA_Versions.
STSURL The URL of the security token service (STS) when using single sign-on (SSO).
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseDisplayNames Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas.
UseEntityTypeName Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.
UseNTLMV1 Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.
UseSimpleNames Boolean determining if simple names should be used for tables and columns.

CalculatedDataType

The data type to be used for calculated fields.

Possible Values

Calculated, Boolean, Date, DateTime, Double, Integer, String

Data Type

string

Default Value

Calculated

Remarks

The data type to be used for calculated fields. By default (Calculated), the data type is determined by the type of calculated field in SharePoint. However, in some cases these calculated fields may return values that are not appropriate for the specified type. In these instances, you may wish to set the Calculated Data Type to the appropriate type.

ContinueOnError

Indicates whether or not to continue updating items in a batch after an error.

Data Type

bool

Default Value

true

Remarks

If this property is set to True (default), the connector will continue adding, updating, or deleting items when an error is encountered on one of the items. When set to False, the connector will stop adding, updating, or deleting items after an error is encountered (entries preceeding the problematic entry will still be added, updated, or deleted).

CreateIDColumns

Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.

Data Type

bool

Default Value

true

Remarks

Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists (like "Lookup" or "Person or Group" columns). The ID column that is created will contain the related entry's ID (in the context of its original List). If set to false, the ID columns will not be created, the ID will be ignored, and only the value of the referenced column will be returned.

DisableFilterLimit

Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead.

Data Type

bool

Default Value

false

Remarks

Regardless of whether this property is set to true or false, for list filters with 5000 or fewer records, server-side processing of list filter queries is supported by Microsoft SharePoint, leading to the fastest possible performance.

When this property is set to true and the list filter exceeds 5000 entries, the connector will process the query filters client-side, which has an extra cost to performance.

However, if this property is set to false and the list filter exceeds 5000 entries, the connector will query from the list itself (as opposed to the list filter). This causes the set of columns in the query result to reflect the columns in the list instead of the list filter.

FolderOption

An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.

Possible Values

FilesOnly, FilesAndFolders, RecursiveAll

Data Type

string

Default Value

RecursiveAll

Remarks

An option to determine how to display folders in results. FilesOnly will display only files in specified lists or libraries. FilesAndFolders will display files and folders in the specified list. RecursiveAll will display files in the specified list and all subfolders.

IncludeLookupColumns

This option controls whether the driver returns the lookup columns defined on a table.

Data Type

bool

Default Value

true

Remarks

This option controls whether the driver returns the lookup columns defined on a table. The SharePoint server may reject the request if too many lookup columns are included in a single query.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Microsoft SharePoint.

Data Type

int

Default Value

1000

Remarks

The Pagesize property affects the maximum number of results to return per page from Microsoft SharePoint. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

ShowHiddenColumns

Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.

Data Type

bool

Default Value

false

Remarks

Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.

ShowPredefinedColumns

Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing.

Data Type

bool

Default Value

true

Remarks

Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing. These columns are normally system columns such as CreatedBy and Author. But, predefined columns may also include common columns such as Title.

ShowVersionViews

Indicate whether to display the view of list versions. Such as ListA_Versions.

Data Type

bool

Default Value

false

Remarks

Indicate whether to display the view of list versions. Such as ListA_Versions.

STSURL

The URL of the security token service (STS) when using single sign-on (SSO).

Data Type

string

Default Value

""

Remarks

The URL of the security token service (STS) when using single sign-on (SSO). This rarely needs to be set explicitly.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UseDisplayNames

Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas.

Data Type

bool

Default Value

true

Remarks

Boolean determining if the display names for the columns should be used instead of the API names. In the REST schema, this property works with all Lists. Note that enabling this might impact performance and change the column datatype.

UseEntityTypeName

Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.

Data Type

bool

Default Value

false

Remarks

Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.

UseNTLMV1

Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

Data Type

bool

Default Value

false

Remarks

Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Calendar WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

UseSimpleNames

Boolean determining if simple names should be used for tables and columns.

Data Type

bool

Default Value

false

Remarks

Microsoft SharePoint tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the connector easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.