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 Kerberos 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 TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

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 authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

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 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 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#	String	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, 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.

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.

View 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 in the specified SharePoint List.

Add an Attachment to a SharePoint List item.

Input

Name	Type	Required	Description
File	String	False	The path of the local file to be added.
List	String	True	The name of the List on the SharePoint server.
ItemID	String	True	The Id of the item on the List to add attachments for.
FileName	String	False	Name of the file to be uploaded. This will be used if content is not null. For example: test.csv

Result Set Columns

Name	Type	Description
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	Required	Description
Name	String	True	The name of the list on the SharePoint server.
Template	String	True	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	False	The description of the list to add.
Columns	String	False	The definition 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	Required	Description
List	String	True	The name of the list on the SharePoint server.
ColumnName	String	True	The name of the column to add.
DisplayName	String	False	The display name of the column to add.
DefaultValue	String	False	The default value of the column to add.
ColumnType	String	True	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). The allowed values are Integer, Text, Note, DateTime, Counter, Choice, Lookup, Boolean, Number, Currency, URL, Computed, Threading, Guid, MultiChoice, GridChoice, Calculated, File, Attachments, User, Recurrence, CrossProjectLink, ModStat, Error, ContentTypeId, PageSeparator, ThreadIndex, WorkflowStatus, AllDayEvent, WorkflowEventType, Geolocation, OutcomeChoice. The default value is Text.
MaxLength	String	False	The column's maximum length.
PrimaryKey	Boolean	False	A Boolean value indicating whether the column should be the primary key.
ReadOnly	Boolean	False	A Boolean value indicating whether the column is read only.
Required	Boolean	False	A Boolean value indicating whether the column is required.

Result Set Columns

Name	Type	Description
Success	Boolean	A Boolean value indicating whether the operation was successful.

Add the user to specified group.

Input

Name	Type	Required	Description
LoginName	String	True	The login name of the user.
Group	String	True	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	Required	Description
LoginName	String	True	A comma-seperated value containing the login names of the users you want to assign the role to. For example: 'Domain\user1,Domain\user2'.
Role	String	True	The role you want to assign to the users.

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	Required	Description
File	String	False	The path of the file you are using to overwrite the document on SharePoint with. For example: C:/myfolder/myfile.txt.
Library	String	True	The name of the library on the SharePoint server. For example: Shared Documents.
Comment	String	False	A comment to leave when checking the file in.
RemoteFile	String	True	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	Required	Description
Library	String	True	The name of the library on the SharePoint server.
RemoteFile	String	True	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.

Execute

If the NewDocumentName parameter is not specified, the value specified in DocumentName will be used as the destination path. If the NewDocumentLibrary parameter is not specified, the value specified in DocumentLibrary will be used as the new library. In any case, at least one of these two parameters must be specified (they can't both be unspecified at the same time). The DocumentLibrary and DocumentName parameters on the other hand are always required. Refer to the query examples below:

EXEC CopyDocument DocumentLibrary = 'Documents', DocumentName = 'Source Folder/Subfolder/Source Document.txt', NewDocumentName = 'Destination Document.txt';
EXEC CopyDocument DocumentLibrary = 'Documents', DocumentName = 'Source Document.txt', NewDocumentLibrary = 'Destination Library';
EXEC CopyDocument DocumentLibrary = 'Documents', DocumentName = 'Source Folder/Subfolder/Source Document.txt', NewDocumentLibrary = 'Destination Library', NewDocumentName = 'Destination Document.txt';

If the NewDocumentName parameter is a folder (ends with a '/'), the document name from DocumentName will be used as the new file name instead. Refer to the query example below:

EXEC CopyDocument DocumentLibrary = 'Documents', DocumentName = 'Source Folder/Subfolder/Source Document.txt', NewDocumentLibrary = 'Destination Library', NewDocumentName = 'Destination Folder/Subfolder/';

Additionally, you can copy the document to a different site in your SharePoint instance by specifying its full URL. In this scenario, NewDocumentLibrary is required. Refer to the query example below:

EXEC CopyDocument DocumentLibrary = 'Documents', DocumentName = '/Source Folder/Subfolder/Source Document.txt', NewDocumentLibrary = 'Destination Library', NewDocumentName = 'https://mysite.sharepoint.com/sites/Destination%20Site/Destination%20Library/Destination%20Folder/Subfolder/Destination%20Document.txt';

Input

Name	Type	Required	Description
DocumentName	String	True	The original document location relative to its library.
DocumentLibrary	String	True	The display name of the library in which the original document is stored.
NewDocumentLibrary	String	False	The display name of the library in which the copy document will be stored. If left blank, this will be the same as DocumentLibrary.
NewDocumentName	String	False	The copy document location relative to its new library. If left blank, this will be the same as 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	Required	Description
Library	String	True	The name of the library on the SharePoint server.
Name	String	True	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	Required	Description
URL	String	True	Full URL to the attachment to be deleted.
List	String	False	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	Required	Description
Library	String	True	The name of the library on the SharePoint server.
Path	String	True	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	Required	Description
List	String	True	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	Required	Description
List	String	True	The name of the list on the SharePoint server.
ColumnName	String	True	The name of the column to delete.

Result Set Columns

Name	Type	Description
Success	Boolean	A Boolean value indicating whether the operation was successful.

Deletes the user from the specified group.

Input

Name	Type	Required	Description
LoginName	String	True	The login name of the user.
Group	String	True	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	Required	Description
LoginName	String	True	A comma-seperated value containing the login names of the users you want to unassign the role from. For example: 'Domain\user1,Domain\user2'.
Role	String	True	The role you want to unassign from the users.

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	Required	Description
Library	String	True	The name of the library on the SharePoint server.
RemoteFile	String	True	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.

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

Input

Name	Type	Required	Description
List	String	True	The name of the list from which you want to move the document or attachment.
SourceFileURL	String	True	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	True	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.

Input

Name	Type	Required	Description
List	String	True	Name of the list containing the document or attachment you would like to rename.
SourceFileURL	String	True	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	True	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	Required	Description
List	String	True	The list's name or GUID.
AllowMultiResponses	String	False	Set to True to allow multiple responses to the survey.
Description	String	False	A string that contains the description for the list.
Direction	String	False	A string that contains LTR if the reading order is left-to-right, RTL if it is right-to-left, or None. The allowed values are LTR, RTL, None.
EnableAssignedToEmail	String	False	Set to True to enable assigned-to e-mail for the issues list.
EnableAttachments	String	False	Set to True to enable attachments to items in the list. Does not apply to document libraries.
EnableModeration	String	False	Set to True to enable Content Approval for the list.
EnableVersioning	String	False	Set to True to enable versioning for the list.
Hidden	String	False	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	False	Set to True to specify that the list in a Meeting Workspace site contains data for multiple meeting instances within the site.
Ordered	String	False	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	False	Set to True to specify that names of users are shown in the results of the survey.
Title	String	False	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 in the specified SharePoint List.

Input

Name	Type	Required	Description
List	String	True	The GUID or name of the list in which the column belongs.
ColumnName	String	True	The name of the column to update.
DisplayName	String	False	The new value for the column's display name.
DefaultValue	String	False	The new value for the column's default value.
ColumnType	String	False	The new value for the column's type. The valid options are defined by the FieldTypes available in the SharePoint API: https://learn.microsoft.com/en-us/previous-versions/office/sharepoint-csom/ee540543(v=office.15). The allowed values are Integer, Text, Note, DateTime, Counter, Choice, Lookup, Boolean, Number, Currency, URL, Computed, Threading, Guid, MultiChoice, GridChoice, Calculated, File, Attachments, User, Recurrence, CrossProjectLink, ModStat, Error, ContentTypeId, PageSeparator, ThreadIndex, WorkflowStatus, AllDayEvent, WorkflowEventType, Geolocation, OutcomeChoice.
MaxLength	String	False	The new value for the column's maximum length.
PrimaryKey	Boolean	False	A Boolean value indicating whether the column should be the primary key.
ReadOnly	Boolean	False	A Boolean value indicating whether the column is read only. This will hide the column in New or Edit forms.
Required	Boolean	False	A Boolean value indicating whether the column is required in New or Edit forms.

Result Set Columns

Name	Type	Description
Success	Boolean	A Boolean value indicating whether the operation was successful.

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.

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

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 to the specified item to the specified list.
Comments	This view represents comments within SharePoint, detailing their authors, content, replies, and associated metadata.
Files	Reads attachments to the specified item to the specified list.
Groups	Retrieve group information from the specified SharePoint Site, including membership settings, permissions, and ownership details.
Lists	Query the available lists on your sharepoint site.
ListItems	This view represents general SharePoint items containing standard columns across various SharePoint lists.
RoleAssignmentMember	Get Web RoleAssignments member.
RoleAssignments	Acquires Web RoleAssignments.
RoleDefinitionBindings	Get Web Role definition binding.
Roles	Contains details about role definitions available in the SharePoint site collection.
Subsites	This lists the available subsites.
Users	Represents users and their roles in the SharePoint site or group.

Reads attachments to the specified item to 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 to 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	Lists.Title	The internal name of the list to retrieve attachments from.
ItemID	Int		The Id of the item in the list to retrieve attachments from.

This view represents comments within SharePoint, detailing their authors, content, replies, and associated metadata.

Columns

Name	Type	References	Description
Id [KEY]	Int		A unique identifier for each comment.
ItemId [KEY]	Int	ListItems.Id	The ID of the item associated with the comment.
List	String	Lists.Title	The name of the SharePoint list containing the item and its comments.
ListId	String		The unique ID of the list the comment belongs to.
AuthorId	Int		The unique identifier of the comment's author.
AuthorEmail	String		The email address of the comment's author.
IsReply	Bool		Indicates whether the comment is a reply to another comment.
ParentId	Int	Comments.Id	The unique ID of the parent comment if this is a reply.
ReplyCount	Int		The total number of replies to the comment.
LikeCount	Int		The total number of likes on the comment.
IsLikedByUser	Bool		Indicates whether the comment is liked by the current user.
Text	String		The text content of the comment.
Mentions	String		A list of users mentioned in the comment, if any.
CreatedDate	Datetime		The date and time when the comment was originally created.
ModifiedDate	Datetime		The date and time when the comment was last modified.

Reads attachments to the specified item to the specified list.

Columns

Name	Type	References	Description
Id [KEY]	String		Unique identifier
CreatedBy_Id	String		The ID of the user that added the file
CreatedBy_Name	String		The name of the user that added the file
CreatedBy_Puid	String		The personal ID of the user that added the file
ETag	String		ETag value
LastModifiedBy_Id	String		The ID of the user that last modified the file
LastModifiedBy_Name	String		The name of the user that last modified the file
LastModifiedBy_Puid	String		The personal ID of the user that last modified the file
Name	String		Name of the file including the extension
Size	Long		The size of the file in bytes, excluding the size of any Web Parts that are used in the file.
TimeCreated	Datetime		Specifies when the file was created.
TimeLastModified	Datetime		Specifies when the file was last modified.
Url	String		URL of the file
List	String		The display name of the list to retrieve files from.

Retrieve group information from the specified SharePoint Site, including membership settings, permissions, and ownership details.

Columns

Name	Type	References	Description
Id [KEY]	Int		The unique identifier for the group.
LoginName	String		The login name or alias associated with the group.
Title	String		The display name of the group or principal.
AllowMembersEditMembership	Bool		Indicates whether group members are allowed to modify the membership of the group.
AllowRequestToJoinLeave	Bool		Indicates whether users can request to join or leave the group.
AutoAcceptRequestToJoinLeave	Bool		Indicates whether requests to join or leave the group are automatically accepted.
CanCurrentUserEditMembership	Bool		Indicates whether the current user has permissions to edit the group's membership.
CanCurrentUserManageGroup	Bool		Indicates whether the current user has permissions to manage the group.
CanCurrentUserViewMembership	Bool		Indicates whether the current user can view the group's membership details.
Description	String		A brief description or summary of the group's purpose or function.
IsHiddenInUI	Bool		Indicates whether the group is hidden from the user interface.
OnlyAllowMembersViewMembership	Bool		Indicates whether only members of the group are allowed to view its membership details.
OwnerTitle	String		The display name of the group's owner.
RequestToJoinLeaveEmailSetting	String		The email address where membership requests (to join or leave) are sent.
PrincipalType	Int		Specifies the type of principal. This is a bitwise value where possible. Types include: None = 0; User = 1; DistributionList = 2; SecurityGroup = 4; SharePointGroup = 8; All = 15.

Query the available lists on your sharepoint site.

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

This view represents general SharePoint items containing standard columns across various SharePoint lists.

Columns

Name	Type	References	Description
ID [KEY]	Int		Unique identifier for the list item.
Title	String		Title of the list item.
Attachments	Bool		Indicates if the item has attachments.
Description	String		Detailed description of the item.
List [KEY]	String	Lists.Title	The display name of the list to retrieve items from.
ContentTypeID	String		Identifier for the content type of the item.
FileSystemObjectType	Int		Indicates the type of object in the file system, such as: '-1': Invalid, '0': File, '1': Folder, '2': Web.
GUID	String		Globally unique identifier for the item.
Version	String		The version string of the item indicates its version history.
CreatedBy	Int	Users.Id	Identifier for the author of the item.
ModifiedBy	Int	Users.Id	Identifier for the last editor of the item.
Created	Datetime		Date and time when the item was created.
Modified	Datetime		Date and time when the item was last modified.

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 = 'MyTestList' AND ItemId = '3'

Columns

Name	Type	References	Description
ID [KEY]	Int		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.
PrincipalType	Int		The principal type of the role assigned member. None = 0; User = 1; DistributionList = 2; SecurityGroup = 4; SharePointGroup = 8; All principal types = 15.
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.
List	String	Lists.Title	The display name of the list to retrieve role assigned member from.
ItemId	Int		List item id for role assignment.
PrincipalId	Int	RoleAssignments.PrincipalId	The Principal Id.

Acquires Web RoleAssignments.

Table Specific Information

Select

SELECT * FROM RoleAssignments WHERE List = 'TestApp'
SELECT * FROM RoleAssignments WHERE PrincipalId = 5 AND list = 'MyListName' AND ItemId = '3'

Columns

Name	Type	References	Description
ID [KEY]	String		The Id of the role assigned.
PrincipalId	Int		The Principal ID for role assigned.
Updated	Datetime		The updated date for role assigned.
List	String	Lists.Title	The display name of the list to retrieve role assigned from.
ItemId	Int		List item id for role assignments.

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
PrincipalId [KEY]	Int	RoleAssignments.PrincipalId	The Principal Id.
ID [KEY]	Int		The ID of the role assigned member.
BasePermissions_High	Long		The base permissions high.
BasePermissions_Low	Long		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.
List	String	Lists.Title	The display name of the list to retrieve role assigned member from.
ItemId	Int		List item id for role defintion.

Contains details about role definitions available in the SharePoint site collection.

Columns

Name	Type	References	Description
Id [KEY]	Int		Specifies the unique identifier of the role definition.
BasePermissions_High	Long		Specifies the high-level base permissions set for the role definition.
BasePermissions_Low	Long		Specifies the low-level base permissions set for the role definition.
Description	String		Provides a description of the role definition's purpose and permissions.
Hidden	Bool		Indicates whether the role definition is hidden from the Permission Levels page.
Name	String		Defines the name assigned to the role definition.
Order	Int		Specifies the display order of the role definition on the Permission Levels page in the site collection.
RoleTypeKind	Int		Represents the type of role definition, mapping to an SP.RoleType enumeration.

This lists the available 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.
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.
AccessRequestListUrl	String		The AccessRequestListUrl of the subsite.
AccessRequestSiteDescription	String		The AccessRequestSiteDescription of the subsite.
Acronym	String		The Acronym of the subsite.
AllowAutomaticASPXPageIndexing	Boolean		The AllowAutomaticASPXPageIndexing of the subsite.
AllowCreateDeclarativeWorkflowForCurrentUser	Boolean		The AllowCreateDeclarativeWorkflowForCurrentUser of the subsite.
AllowDesignerForCurrentUser	Boolean		The AllowDesignerForCurrentUser of the subsite.
AllowMasterPageEditingForCurrentUser	Boolean		The AllowMasterPageEditingForCurrentUser of the subsite.
AllowRevertFromTemplateForCurrentUser	Boolean		The AllowRevertFromTemplateForCurrentUser of the subsite.
AllowSaveDeclarativeWorkflowAsTemplateForCurrentUser	Boolean		The AllowSaveDeclarativeWorkflowAsTemplateForCurrentUser of the subsite.
AllowSavePublishDeclarativeWorkflowForCurrentUser	Boolean		The AllowSavePublishDeclarativeWorkflowForCurrentUser of the subsite.
CommentsOnSitePagesDisabled	Boolean		The CommentsOnSitePagesDisabled of the subsite.
ContainsConfidentialInfo	Boolean		The ContainsConfidentialInfo of the subsite.
CustomSiteActionsDisabled	Boolean		The CustomSiteActionsDisabled of the subsite.
DefaultNewPageTemplateId	String		The DefaultNewPageTemplateId of the subsite.
DescriptionForExistingLanguage	String		The DescriptionForExistingLanguage of the subsite.
DescriptionTranslations	String		The DescriptionTranslations of the subsite.
DesignerDownloadUrlForCurrentUser	String		The DesignerDownloadUrlForCurrentUser of the subsite.
DisableAppViews	Boolean		The DisableAppViews of the subsite.
DisableFlows	Boolean		The DisableFlows of the subsite.
DisableRecommendedItems	Boolean		The DisableRecommendedItems of the subsite.
EffectiveBasePermissions_High	Long		The EffectiveBasePermissions_High of the subsite.
EffectiveBasePermissions_Low	Long		The EffectiveBasePermissions_Low of the subsite.
ExcludeFromOfflineClient	Boolean		The ExcludeFromOfflineClient of the subsite.
HasWebTemplateExtension	Boolean		The HasWebTemplateExtension of the subsite.
IsEduClass	Boolean		The IsEduClass of the subsite.
IsEduClassProvisionChecked	Boolean		The IsEduClassProvisionChecked of the subsite.
IsEduClassProvisionPending	Boolean		The IsEduClassProvisionPending of the subsite.
IsProvisioningComplete	Boolean		The IsProvisioningComplete of the subsite.
LastItemModifiedDate	Datetime		The LastItemModifiedDate of the subsite.
LastItemUserModifiedDate	Datetime		The LastItemUserModifiedDate of the subsite.
LogoAlignment	Int		The LogoAlignment of the subsite.
NotificationsInOneDriveForBusinessEnabled	Boolean		The NotificationsInOneDriveForBusinessEnabled of the subsite.
NotificationsInSharePointEnabled	Boolean		The NotificationsInSharePointEnabled of the subsite.
PrimaryColor	String		The PrimaryColor of the subsite.
RelatedHubSiteIds	String		The RelatedHubSiteIds of the subsite.
RequestAccessEmail	String		The RequestAccessEmail of the subsite.
SaveSiteAsTemplateEnabled	Boolean		The SaveSiteAsTemplateEnabled of the subsite.
SearchBoxInNavBar	Int		The SearchBoxInNavBar of the subsite.
SearchBoxPlaceholderText	String		The SearchBoxPlaceholderText of the subsite.
ServerRelativePath_DecodedUrl	String		The ServerRelativePath_DecodedUrl of the subsite.
ShowUrlStructureForCurrentUser	Boolean		The ShowUrlStructureForCurrentUser of the subsite.
SiteLogoDescription	String		The SiteLogoDescription of the subsite.
SupportedUILanguageIds	String		The SupportedUILanguageIds of the subsite.
TenantTagPolicyEnabled	Bit		The TenantTagPolicyEnabled of the subsite.
ThemeData	String		The ThemeData of the subsite.
ThemedCssFolderUrl	String		The ThemedCssFolderUrl of the subsite.
ThirdPartyMdmEnabled	Boolean		The ThirdPartyMdmEnabled of the subsite.
TitleForExistingLanguage	String		The TitleForExistingLanguage of the subsite.
TitleTranslations	String		The TitleTranslations of the subsite.
WebTemplateConfiguration	String		The WebTemplateConfiguration of the subsite.
WebTemplatesGalleryFirstRunEnabled	Boolean		The WebTemplatesGalleryFirstRunEnabled of the subsite.
WebTemplatesGalleryFirstRunComplete	Boolean		The WebTemplatesGalleryFirstRunComplete of the subsite.

Represents users and their roles in the SharePoint site or group.

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
Id [KEY]	Int		Unique identifier for each user in the SharePoint environment.
LoginName	String		The unique login name used by the user to access the SharePoint environment.
Title	String		The display name or title associated with the user.
IsHiddenInUI	Bool		Indicates whether the user is hidden from the SharePoint user interface.
GroupId	Int		The identifier of the group to which the user belongs.
AadObjectId_NameId	String		Azure Active Directory (AAD) object ID representing the user's unique identifier.
AadObjectId_NameIdIssuer	String		The issuer of the Azure Active Directory (AAD) NameId for the user.
Email	String		Primary email address associated with the user.
EmailWithFallback	String		An alternate or fallback email address for the user if the primary is unavailable.
Expiration	String		Specifies the expiration date of the user's access, if applicable.
HexCid	String		A hexadecimal representation of the user's client ID.
IsEmailAuthenticationGuestUser	Bool		Indicates if the user is a guest authenticated via email.
IsShareByEmailGuestUser	Bool		Indicates if the user is a guest invited via email for sharing purposes.
IsSiteAdmin	Bool		Specifies whether the user has administrative rights for the site collection.
UserId_NameId	String		The unique identifier for the user in SharePoint's user ID system.
UserId_NameIdIssuer	String		The issuer of the user's ID in SharePoint's user ID system.
UserPrincipalName	String		User Principal Name (UPN) for the user, usually an email-style identifier.
PrincipalType	Int		Defines the type of principal, using bitwise values: None=0, User=1, DistributionList=2, SecurityGroup=4, SharePointGroup=8, All=15.

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.
AddImage	Adds an Image into a sharepoint list item.
AddList	Creates a list on a SharePoint site.
AddListColumn	Adds a new column to the specified list.
AddRoleAssignment	Adds a new role assignment with the specified principal and role definitions to the list, or list item.
BreakRoleInheritance	Breaks the security inheritance of a list, or list item so that role assignments on the parent object no longer apply to the child object.
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.
DeleteList	Permanently deletes a list from a SharePoint site.
DeleteListColumn	Deletes a column from the specified list.
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.
RemoveRoleAssignment	Removes the role assignment with the specified principal and role definition from the list, or list item.
RenameAttachmentOrDocument	Renames a document or attachment.

Adds an attachment into a sharepoint list.

Input

Name	Type	Required	Description
ListTitle	String	True	Title of the list item.
ItemId	String	True	Id of the list item.
FileName	String	True	The name of the file to be added to the document library
InputFilePath	String	False	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.

Adds an Image into a sharepoint list item.

Input

Name	Type	Required	Description
ListName	String	True	The display name of the list on SharePoint.
ItemId	String	True	Id of the list item.
ColumnInternalName	String	True	The name of the column to add.
FileName	String	True	The name of the file to be added as image
InputFilePath	String	False	The location of the file to be attached.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful.
ErrorCode	Integer	When this procedure is not executed successfully, this output displays the error code associated with the failed execution.
ErrorMessage	String	When this procedure is not executed successfully, this output displays the error message associated with the failed execution.

Creates a list on a SharePoint site.

Input

Name	Type	Required	Description
Name	String	True	The name of the list on the SharePoint server.
Template	String	False	The name or the id of the template to use for the list creation.
Description	String	False	The description of the list to add.

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.

Adds a new column to the specified list.

Input

Name	Type	Required	Description
ListName	String	True	The display name of the list on SharePoint.
ColumnDisplayName	String	True	The display name of the column to add.
ColumnType	String	True	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).
ColumnInternalName	String	False	The name of the column to add.
IsRequired	Boolean	False	A Boolean value indicating whether the column is required.
EnforceUniqueValues	Boolean	False	A Boolean value indicating whether to enforce unique values for the column.
DisplayAfterCreation	Boolean	False	A Boolean value indicating whether to display the added field on the SharePoint UI. The default value is true.
LookupListId	String	False	The list Id of the location of the field that you want to look up.
LookupFieldName	String	False	The display name of the field that you want to look up.

Result Set Columns

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

Adds a new role assignment with the specified principal and role definitions to the list, or list item.

Input

Name	Type	Required	Description
RoleId	Int	True	The ID of the role definition that defines the permissions to assign.
PrincipalId	Int	True	The ID of the user or group to assign permissions to.
List	String	True	The internal name of the list to add the role assignment.
ItemId	Int	False	The list item id to add the role assignment.

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.

Breaks the security inheritance of a list, or list item so that role assignments on the parent object no longer apply to the child object.

Input

Name	Type	Required	Description
List	String	True	The internal name of the list to break the security inheritance.
ItemId	Int	False	The list item id to break the security inheritance.
CopyRoleAssignments	Boolean	False	Specifies whether to copy the role assignments from the parent object. The default value is true.

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.

Checks in a specific document to the document library.

Input

Name	Type	Required	Description
RelativeURL	String	True	The relative URL of the folder.
DocumentName	String	True	The name of the file to be checked in.
Comment	String	False	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	Required	Description
RelativeURL	String	True	The relative URL of the folder.
DocumentName	String	True	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	Required	Description
SourceFileRelativeUrl	String	True	The relative URL of the source file.
DestFileRelativeUrl	String	True	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	Required	Description
RelativeURL	String	True	The relative URL of the folder.
FolderName	String	True	The name of the folder to be created.
SiteURL	String	False	The base URL of the site (in the same SharePoint instance/same domain as the one in 'URL' connection property) in which the folder should be created. If this parameter is used, the value here takes precedence over the value in the 'URL' connection property.

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	Required	Description
ListTitle	String	True	The Title of the list item.
ItemId	String	True	The Id of the list item.
FileName	String	True	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	Required	Description
RelativePath	String	True	The path of the document you want to delete, relative to the value specified in the URL connection property. For example: '/Shared Documents/My Folder/My Document.txt'.
Permanently	String	False	If True, deletes the document permanently. Otherwise, the Cloud moves the document to the recycle bin. The default value is false.

Result Set Columns

Name	Type	Description
Success	Boolean	Indicates whether the operation was successful.
ErrorCode	Integer	When this procedure is not executed successfully, this output displays the error code associated with the failed execution.
ErrorMessage	String	When this procedure is not executed successfully, this output displays the error message associated with the failed execution.

Permanently deletes a list from a SharePoint site.

Input

Name	Type	Required	Description
Name	String	True	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.
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.

Deletes a column from the specified list.

Input

Name	Type	Required	Description
ListName	String	True	The display of the list on SharePoint.
ColumnName	String	True	The display name of the column to delete.

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	Required	Description
RelativeURL	String	True	The relative URL of the folder.
DocumentName	String	True	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	Required	Description
CallbackUrl	String	False	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	False	The same value for state that you sent when you requested the authorization code.
Scope	String	False	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.

Execute

The paths specified in SourceFileURL and DestinationFolderURL must be relative to what you have used in Url. Refer to the query example below:

/* URL = https://mysite.sharepoint.com/sites/Subsite */
EXEC MoveAttachmentOrDocument SourceFileURL = '/Shared Documents/Source Folder/Subfolder/Original Document.txt', DestinationFolderURL = '/Destination Library/Destination Folder/';

Input

Name	Type	Required	Description
SourceFileURL	String	True	The path of the source file, relative to the base Url supplied in the Cloud's connection properties.
DestinationFolderURL	String	True	The path of the destination folder where you want to move the file, relative to the base Url supplied in the Cloud's connection properties.

Result Set Columns

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

Removes the role assignment with the specified principal and role definition from the list, or list item.

Input

Name	Type	Required	Description
RoleId	Int	True	The ID of the role definition in the role assignment.
PrincipalId	Int	True	The ID of the user or group in the role assignment
List	String	True	The internal name of the list to remove the role assignment.
ItemId	Int	False	The list item id to remove the role assignment.

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.

Renames a document or attachment.

Execute

The path specified in SourceFileURL must be relative to what you have used in Url. Refer to the query example below:

/* URL = https://mysite.sharepoint.com/sites/Subsite */
EXEC RenameAttachmentOrDocument SourceFileURL = '/Shared Documents/Source Folder/Subfolder/Original Document.txt', NewFileName = 'Renamed Document.txt';

Input

Name	Type	Required	Description
SourceFileURL	String	True	The path of the file you want to rename, relative to the base Url supplied in the Cloud's connection properties.
NewFileName	String	True	The new name of the file, with a file extension.

Result Set Columns

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

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	Identifies the Microsoft SharePoint tenant being used to access data, either by name (for example, contoso.omnicrosoft.com) or ID. (Conditional).
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, formatted as a semicolon-separated list.

OAuth

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the 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	Specifies the grant type for the chosen OAuth flow. This value should be the same as the grant_type that was set during OAuth custom application creation.

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 used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
OAuthJWTIssuer	The issuer of the Java Web Token.

SSL

---

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

Logging

---

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Schema

---

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all 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. This property applies only to the SOAP schema.
ContinueOnError	Indicates whether to continue updating items in a batch after an error.
CreateIDColumns	Create supplemental ID columns for SharePoint columns that use values from information stored in other Lists. This property applies only to the SOAP schema.
DisableFilterLimit	Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Use this property to pull more than 5000 records from the list filter at the cost of additional performance overhead. This property applies only to the REST schema.
FolderOption	An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll. This property applies only to the SOAP schema.
GetColumnsMetadata	Specifies when the provider should retrieve column metadata. This property applies only to the REST schema.
IncludeLookupColumns	This option controls whether the driver returns the lookup columns defined on a table. This property applies only to the SOAP schema.
IncludeLookupDisplayValueColumns	Determines whether the display values for lookup columns should be included. This property applies only to the REST schema.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Pagesize	Specifies the maximum number of results to return from Microsoft SharePoint, per page. This setting overrides the default page size set by the datasource, which is optimized for most use cases.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
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	Indicates whether to include the list version views during metadata discovery. This property applies only to the SOAP schema.
STSURL	The URL of the security token service (STS) when using single sign-on (SSO).
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UseDisplayNames	Boolean determining if the display names for the columns should be used instead of the API names.
UseEntityTypeName	Boolean determining if the list's EntityTypeName field should be used as the table name during metadata discovery instead of the Title field. This property applies only to 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.

Possible Values

AzureAD, AzureServicePrincipalCert, AzurePassword, PingFederate, ADFS, OneLogin, Okta, OAuthJWT, Negotiate, None

Data Type

string

Default Value

"AzureAD"

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, AzureAD is the default option. Use the following options to select your authentication scheme:

• AzureAD: Set this to perform Azure Active Directory OAuth Authentication.
• AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a certificate.
• AzurePassword: Set this to authenticate using OAuth with Password GrantType.
• 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.
• 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.