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.

OAuth: Admin Consent

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.

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

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

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

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.

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

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.

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

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.

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

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

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

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

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.

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

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

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.

Subsites

This lists the available subsites.

Columns

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

AddAttachment

Add an Attachment to a SharePoint List item.

Input

Result Set Columns

AddList

Creates a list on a SharePoint site.

Input

Result Set Columns

AddListColumn

Adds a new column to the specified list.

Input

Result Set Columns

AddUserToGroup

Add the user to specified group.

Input

Result Set Columns

AddUserToRole

Add the user to specified role.

Input

Result Set Columns

CheckInDocument

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

Input

Result Set Columns

CheckOutDocument

Checks out a document from SharePoint.

Input

Result Set Columns

CopyDocument

Copies a document from the SharePoint library.

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

Result Set Columns

CreateFolder

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

Input

Result Set Columns

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

Result Set Columns

DeleteAttachment

Delete an attachment from a SharePoint list item.

Input

Result Set Columns

DeleteDocument

Delete a document on the SharePoint library.

Input

Result Set Columns

DeleteList

Permanently deletes a list from a SharePoint site.

Input

Result Set Columns

DeleteListColumn

Deletes a column from the specified list.

Input

Result Set Columns

DeleteUserFromGroup

Delete the user from specified group.

Input

Result Set Columns

DeleteUserFromRole

Delete the user from specified role.

Input

Result Set Columns

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

Result Set Columns

DownloadAttachment

Download a document from the SharePoint list.

Input

Result Set Columns

DownloadDocument

Download a document from the SharePoint library.

Input

Result Set Columns

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

Result Set Columns

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

Result Set Columns

UpdateList

Updates a list on a SharePoint site.

Input

Result Set Columns

UpdateListColumn

Updates a column to the specified list.

Input

Result Set Columns

UploadDocument

Upload a document to the SharePoint library.

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

Result Set Columns

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.

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

Attachments

Read Attachments for the specified item on the specified list.

Table Specific Information

Select

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

SELECT * FROM Attachments WHERE List = 'TestApp' AND ItemID = 1

Columns

Files

Query the available files on your sharepoint site.

Columns

Groups

Query the available groups on your sharepoint site.

Table Specific Information

Select

SELECT * FROM Groups

Columns

Lists

Query the available lists on your sharepoint site.

Columns

RoleAssignmentMember

Get Web RoleAssignments member.

Table Specific Information

Select

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

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

RoleDefinitionBindings

Get Web Role definition binding.

Table Specific Information

Select

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

Roles

Query the roles your users can have.

Columns

Subsites

This lists the available subsites.

Table Specific Information

Select

SELECT * FROM Subsites

Columns

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

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

AddAttachment

Add an attachment into a sharepoint list.

Input

Result Set Columns

CheckInDocument

Check in specific document inside document library.

Input

Result Set Columns

CheckOutDocument

Check out specific document inside document library.

Input

Result Set Columns

CopyDocument

Copies the file to the destination URL..

Input

Result Set Columns

CreateFolder

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

Input

Result Set Columns

DeleteAttachment

Deletes an attachment from Sharepoint list.

Input

Result Set Columns

DeleteDocument

Delete a Document from the SharePoint document library.

Input

Result Set Columns

DiscardCheckOutDocument

Reverts an existing checkout for the file.

Input

Result Set Columns

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

Result Set Columns

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

Result Set Columns

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

Result Set Columns

GetCurrentUser

Retrieves information about the current logged in user.

Result Set Columns

GetOAuthAccessToken

Gets the OAuth access token from SharePoint.

Input

Result Set Columns

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

Result Set Columns

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

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with SharePoint.

Input

Result Set Columns

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

Result Set Columns

UploadDocument

Uploads a document into the SharePoint library.

Input

Result Set Columns

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:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure* parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

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

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

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

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

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

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

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

sys_primarykeys

Describes the primary keys.

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

SELECT * FROM sys_primarykeys

Columns

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

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

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.

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