Overview
SharePoint Connection Manager is SSIS Connection Manager for establishing SharePoint connections. Use the API to build components based on SharePoint Connection Manager.
Demonstration
Quick Start
In this section we will show you how to set up a SharePoint Connection Manager to access your SharePoint service.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down the list to find the SHAREPOINT connection manager and select it. Click the Add button.
- Once the connection manager dialog opens, select the appropriate Deployment option (Premise or Online). In this example, we selected Online.
- Enter the URL for your SharePoint Server.
- Enter your User name, which is the email address associated with your SharePoint account.
- Enter your Password.
- Press Test Connection.
- When you see Test Connection succeeded, click OK.
- Click OK to close the connection manager dialog.
Congratulations! You have successfully configured a SharePoint connection manager.
Multi-Factor Authentication (MFA) enabled systems do require the input of random value that only a human can provide for a successful connection. To make it possible to connect to MFA-enabled systems, you can manually enter a long-lived token/cookie to connect to the system. Check the steps below for instructions on what is required to make it work.
- Open Chrome to the SharePoint authentication page and enter your credentials. Enter the required MFA code when asked. After successful authentication, the main SharePoint page will open.
To get the required token/cookie, you have to open a connection to SharePoint Online using a web browser. For this tutorial, we will use the Google Chrome browser and specific functionality found in Chrome to get the cookie. You can extract the required cookie with other tools like Fiddler.
- Open Chrome DevTools by right-click and select Inspect (Ctrl-Shift-I).
- Inspect the "Application" tab and under "Storage" > "Cookies" sections find your Sharepoint application.
- From the list of cookies, you need to extract two cookies -
FedAuthandrtFa. Copy and paste their values into Notepad.
- The two cookies have to be combined into one element, separated with a newline character. This is the needed authentication token. The result value should look like:
FedAuth=77u/PD94bWw...\nrtFa=+BNjXfKOuFc2az99...
Notice above the special combination above used as a newline separator.
- Open an SSIS package with a COZYROC SharePoint connection manager in it. Right-click on it and select Properties. Find a property named Token and press the '...' to open the entry dialog.
- Enter the token you have assembled in the prior step and press the OK button.
Done. Your connection will now use the specified token to authenticate to the SharePoint service. You can also configure an expression to modify this property with an external configuration file or a table.
Parameters
Server
Use the Server page of the SharePoint Connection Manager dialog to specify parameters for connecting to SharePoint service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify deployment type. This parameter has the options listed in the following table.
Value Description Premise Connect to on-premise SharePoint server. Online Connect to SharePoint Online (Office 365) service. Specify the name or IP address of the SharePoint server.
Specify user name to access SharePoint. If not specified, the connection will be established using Windows authentication.
Note: Windows authentication cannot be used with SharePoint Online.Specify password to access SharePoint.
Specify the number of seconds before timing out session connect. The default value of this parameter is 100 seconds.
Specify if all clients should use the same connection. This parameter has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Proxy
Use the Proxy page of the SharePoint Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
- Where can I find the documentation for the SharePoint Connection?
- When I try to use SharePoint 2010 server with mixed-mode authentication I get error: "403 forbidden"
- After I upgraded, why are FedAuth and rtFa options missing from the Sharepoint Connection?
- Error Message: 'The remote server returned an error (429)' when using Sharepoint Online.
- Error Message: The operation has timed out.
What's New
- New: Experimental feature to explicitly specify authentication token when using MFA-enabled service. For further information please review 'Establish connection to MFA-enabled service' Quick Start.
- New: Support for on-premises servers that use ADFS authentication.
- Fixed: Failed to delete file with ampersand (&) special symbol in the name when using File Transfer Task (Thank you, Nandu).
- Fixed: File Transfer Task was not working properly because of different file name case.
- Fixed: File Transfer Task 'Create Remote Directory', 'Remove Remote Files', 'Rename Remote File' actions did not work properly when used with SharePoint sub-site.
- Fixed: File Transfer Task did not report failure errors.
- Fixed: Failed to connect to SharePoint Online when third-party Ping token service was used (Thank you, Pradeep).
- Fixed: File Transfer Task was not working properly when used with SharePoint sub-site (Thank you, Michael).
- New: Support for sites migrated from BPOS (Thank you, Angel).
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Support for Office 365 deployed systems with Federated (delegated) security (Thank you, Phil).
- New: Introduced connection.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.