This page provides a guide to Establishing a Connection to Microsoft SharePoint in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Microsoft SharePoint

Establishing a Connection shows how to authenticate to Microsoft SharePoint and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Microsoft SharePoint through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Microsoft SharePoint by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting 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.

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:

• AzureTenant: The tenant you wish to connect to.
• OAuthJWTCert: The JWT certificate store.
• OAuthJWTCertType: The type of key store containing the JWT certificate.
• OAuthJWTIssuer: The OAuth client Id.
• OAuthJWTCertPassword: The password associated with the JWT certificate. Set this is your certificate type requires a password.

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.

Kerberos

To authenticate to Microsoft SharePoint with Kerberos, set AuthScheme to NEGOTIATE.

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

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 Cloud 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 Cloud 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 Cloud 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.

Enabling 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 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 Cloud 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 Cloud.

Customizing the SSL Configuration

By default, the Cloud 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 Cloud 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.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud 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 Cloud sets defaults to facilitate the maximum number of integrations; however, the following connection properties allow a greater granularity of customization, which is 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 to report pseudo columns as columns in the table metadata.

Tables

Tables describes the available tables.

The Cloud 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.

The Cloud models the data in Microsoft SharePoint as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Microsoft SharePoint Tables

Name	Description
Attachments	Reads or deletes attachments to the specified item on the specified list.
Groups	Creates, updates, deletes, and queries Groups from SharePoint.
Roles	Creates, updates, deletes, and queries Roles from SharePoint.
Users	Updates, deletes, and queries Users from SharePoint.
Views	Creates, updates, deletes, and queries the available lists in SharePoint.

Reads or deletes attachments to 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.

Creates, updates, deletes, and queries 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.

Creates, updates, deletes, and queries 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 a minimum 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.

Updates, deletes, and queries Users from SharePoint.

Table Specific Information

Select

Retrieves 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 you must specify either Group or Role for inserts and selects, but you can optionally specify 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 you must specify either Group or Role for inserts and selects, but but you can optionally specify deletions.

Creates, updates, deletes, and queries 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

To return results from Views, yout must specify either the ID or List 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 are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - 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, set the ObjectType to List or web (an ObjectName must be specified when the ObjectType is list). If not, you must specify the ObjectName along with the ItemID.
Subsites	Lists the available subsites.

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. You must specify a file to retrieve the versions for a file.

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	A Boolean indicating if the term set is open.
Deprecated	Boolean	A Boolean indicating if the term is deprecated.
InternalId	String	An internal identifier for the term.
TermSetContact	String	The term set contact.
ContainerDesc	String	The 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	An 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 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 Cloud.

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	A 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	A Boolean indicating if assigned-to emails are enabled. Only applies to issues lists.
EnableAttachments	Boolean	A Boolean indicating if attachments may be added to items in the list. Does not apply to document libraries.
EnableModeration	Boolean	A Boolean indicating if content approval is enabled for the list.
EnableVersioning	Boolean	A Boolean indicating if versioning is enabled for the list.
Hidden	Boolean	A 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	A Boolean indicating if a meeting-workspace site contains data for multiple meeting instances within the site.
Ordered	Boolean	A Boolean indicating if items in the list can be sorted on the Edit View page.
Showuser	Boolean	A Boolean indicating if the names of users are shown in the results of the survey.

The permissions for a site or list. Note: If ItemId is empty, set the ObjectType to List or web (an ObjectName must be specified when the ObjectType is list). If not, you must specify the ObjectName 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.

Lists the available subsites.

Columns

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

Stored procedures are function-like interfaces that extend the functionality of the Cloud 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.

CData Cloud - 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.
DeleteAttachment	Deletes an attachment from a SharePoint list item.
DeleteDocument	Deletes a document on the SharePoint library.
DeleteList	Permanently deletes a list from a SharePoint site.
DeleteListColumn	Deletes a column from the specified list.
DeleteUserFromGroup	Deletes the user from the specified group.
DeleteUserFromRole	Deletes 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.
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.

Add an Attachment to a SharePoint List item.

Input

Name	Type	Description
File	String	The path of the local file to be added.
List	String	The name of the List on the SharePoint server.
ItemID	String	The Id of the item on the List to add attachments for.
FileName	String	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
Success	Boolean	Indicates whether the operation was successful or not.
URL	String	The URL of the newly created item.

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
Success	Boolean	Indicates whether the operation was successful or not.

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	A Boolean value indicating whether the column should be primary key.
ReadOnly	String	A Boolean value indicating whether the column is read only.
Required	String	A Boolean value indicating whether the column is required.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

Copies a document from the SharePoint library.

Note: This procedure makes use of indexed parameters. Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

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

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"

In the table below, indexed parameters are denoted with a '#' character at the end of their names.

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.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.
Id	String	The Id of the document.
DocumentId	String	The document Id.
DocumentIdUrl	String	The URL of the document Id.
FileRef	String	The file reference of the document.

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
Success	Boolean	Indicates whether the operation was successful or not.

Deletes 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
Success	Boolean	Indicates whether the operation was successful or not.

Deletes 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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

Deletes the user from the 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
Success	Boolean	Indicates whether the operation was successful or not.

Deletes 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
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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	The name of the list from which you want to move the document or attachment.
SourceFileURL	String	The URL of the source file, relative to the base Url supplied in the Cloud'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 corresponds 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	The URL of the destination folder where you want to move the file, relative to the base Url supplied in the Cloud'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 corresponds to a path on the base site. If the Url connection property points to a specific site, the relative URL is relative to the site supplied in the Url.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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 Cloud'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 corresponds with a path to the base site. If the Url connection property points to a specific site, the relative URL is relative to the site supplied in the Url.
NewFileName	String	New name of the file, with extension.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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
Success	Boolean	Indicates whether the operation was successful or not.

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 set the existing column as primary key.
ReadOnly	String	Use this to set the existing column as readonly.
Required	String	Use this to set the exisitng column as required.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

The CData Cloud 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

Lists in your Microsoft SharePoint site are generated as relational tables dynamically. This means any change you make in your lists, i.e adding a new list or new fields, is reflected in the driver.

Since tables are generated dynamically, documentation on specific tables is not available.

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.

Using 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 are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Microsoft SharePoint Views

Name	Description
Attachments	Reads 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.

Reads 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 to the item.
FileName	String		The FileName of the attachment to the item.
ServerRelativePath_DecodedUrl	String		The ServerRelativePath DecodedUrl of the attachment to the item.
FileNameAsPath_DecodedUrl	String		The FileNameAsPath DecodedUrl of the attachment to the item.
ServerRelativeUrl	String		The ServerRelativeUrl of the attachment to 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.

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

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

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

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.

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.

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.

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

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.

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 are function-like interfaces that extend the functionality of the Cloud 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.

CData Cloud - Microsoft SharePoint Stored Procedures

Name	Description
AddAttachment	Adds an attachment into a sharepoint list.
CheckInDocument	Checks in a specific document to the document library.
CheckOutDocument	Checks out a specific document inside the 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	Deletes a Document from the SharePoint document library.
DiscardCheckOutDocument	Reverts an existing checkout for the file.
GetAdminConsentURL	Acquires 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.
MoveAttachmentOrDocument	Moves a document or attachment from a source folder to a destination folder.
RenameAttachmentOrDocument	Renames a document or attachment.

Adds an attachment into a sharepoint list.

Input

Name	Type	Description
ListTitle	String	Title of the list item.
ItemId	String	Id of the list item.
FileName	String	The name of the file to be added to the document library
InputFilePath	String	The location of the file to be attached.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.
RelativeUrl	String	The relative URL of the attachment that has been added.

Checks in a specific document to the document library.

Input

Name	Type	Description
RelativeURL	String	The relative URL of the folder.
DocumentName	String	The name of the file to be checked in.
Comment	String	An optional message while checking in a document.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

Checks out a specific document inside the document library.

Input

Name	Type	Description
RelativeURL	String	The relative URL of the folder.
DocumentName	String	The name of the file to be checked in.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

Copies the file to the destination URL..

Input

Name	Type	Description
SourceFileRelativeUrl	String	The relative URL of the source file.
DestFileRelativeUrl	String	The relative URL of the destination file.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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

Input

Name	Type	Description
RelativeURL	String	The relative URL of the folder.
FolderName	String	The name of the folder to be created.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.
Id	String	A unique identifier value returned after completing the operation.

Deletes an attachment from Sharepoint list.

Input

Name	Type	Description
ListTitle	String	The Title of the list item.
ItemId	String	The Id of the list item.
FileName	String	The name of the file to be added to the document library

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

Deletes a Document from the SharePoint document library.

Input

Name	Type	Description
RelativePath	String	The path of the document you want to delete relative to the URL specified in the connection property. For example: '/Shared Documents/My Folder/My Document.txt'.
Permanently	Boolean	Whether to delete the document permanently or just move it to the recycle bin. Default behavior is to move it to the recycle bin. The default value is false.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.
ErrorCode	Integer	The error code in case the procedure is not executed successfully.
ErrorMessage	String	The error message in case the procedure is not executed successfully.

Reverts an existing checkout for the file.

Input

Name	Type	Description
RelativeURL	String	The relative URL of the folder.
DocumentName	String	The name of the file to be checked in.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

Acquires 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.

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.

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	The URL of the source file, relative to the base Url supplied in the Cloud'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 corresponds with a path on the base site. If the Url connection property points to a specific site, the relative URL is 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 Cloud'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 corresponds to a path on the base site. If the Url connection property points to a specific site, the relative URL is relative to the site supplied in the Url.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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	The URL of the file you want to rename, relative to the base Url supplied in the Cloud'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 corresponds to a path on the base site. If the Url connection property points to a specific site, the relative URL is relative to the site supplied in the Url.
NewFileName	String	The new name of the file, with extension.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful or not.

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 Cloud 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.

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.

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.

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.

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.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

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.

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.

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.

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.

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.

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.

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.

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

See SQL Compliance for SQL syntax details.

Discovering 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
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
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 CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData 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.

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 Mappings

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

Microsoft SharePoint	CData 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

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

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
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.
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.

JWT OAuth

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.

SSL

---

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

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
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.
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.
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).
UseSimpleNames	Boolean determining if simple names should be used for tables and columns.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

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.

The scheme used for authenticating to SharePoint.

Data Type

string

Default Value

"OAuth"

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 Cloud 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 Cloud 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.

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.

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.

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: [email protected].

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.

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

---

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.

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.

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

This section provides a complete list of the SSO properties you can configure in the connection string for this provider.

---

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.

The identity provider's login URL.

Data Type

string

Default Value

""

Remarks

The identity provider's login URL.

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. [email protected]) is different than the domain configured within the SSO service (e.g. [email protected]).

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

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 .

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
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.
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.

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.

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.

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 Cloud 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.

Optional value for representing extra OAuth state information.

Data Type

string

Default Value

""

Remarks

Optional value for representing extra OAuth state information.

The grant type for the OAuth flow.

Possible Values

CODE, PASSWORD, SAML_1, CLIENT

Data Type

string

Default Value

"CODE"

Remarks

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

This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.

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:

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).

The type of key store containing the JWT Certificate.

Data Type

string

Default Value

"PEMKEY_BLOB"

Remarks

This property can take one of the following values:

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.