SharePoint connection strings

CData ADO.NET Provider for SharePoint

Standard

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;

Set dataset page size

Default is 1000 results per page. High page size results in fewer requests, but it may time out.

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Page Size=2000;

Accept all SSL certificates (not only certs that the machine trusts)

URL=https://sharepointServerName/;User=myUsername;Password=myPassword;SSL Cert=*;

This property can also be used to specify a single SSL certificate to be accepted. Use the value of a full PEM certificate, the path to a file containing the certificate, the public key, the MD5 thumbprint, or the SHA1 thumbprint.

Use timeout

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Timeout=60;

If an operation is not complete within the timeout value an exception is thrown.

Caching data automatically

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Cache Location=C:\cache.db;Auto Cache=true;Offline=false;

To retrieve data from the cache, add "#Cache" to the table name. For example, to query cached data from the "Calendar " table, execute "SELECT * FROM [Calendar#Cache]".

Caching data and metadata

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Cache Location=C:\cache.db;Auto Cache=true;Offline=false;Cache Metadata=true;

The table metadata will also be cached instead of retrieving it from the data source. This improves connection performance. Read more here →

Cached data only / Offline mode

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Offline=true;QueryPassthrough=True;Cache Location=C:\cache.db;

SELECT statements will always retrieve data from the cache. DELETE/UPDATE/INSERT statements is not allowed and will throw an exception.

Using an External Cache Provider

RSSBus drivers have the ability to cache data in a separate database such as SQL Server or MySQL instead of in a local file using the following syntax:

Cache Provider=Provider.Namespace;Cache Connection='Connection String to Cache Database';

Above is just an example to show how it works. It can be used both with "Auto Cache" and with "Cached Data Only / Offline Mode". Read more about using RSSBus Cache Provider in this article >>>

Connect through a firewall

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Firewall Server=fireWallIPorDNSname;Firewall User=fwUserName;Firewall Password=fwPassword;

Specify firewall port

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Firewall Server=fireWallIPorDNSname;Firewall User=fwUserName;Firewall Password=fwPassword;Firewall Port=1234;

Specify firewall type

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Firewall Server=fireWallIPorDNSname;Firewall User=fwUserName;Firewall Password=fwPassword;Firewall Type=SOCKS5;

Valid values for "Firewall Type" is TUNNEL, SOCKS4 and SOCKS5. TUNNEL uses port 80 as default. SOCKS4/5 uses port 1080 as default.

Setting the Service Principal Name

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;Kerberos SPN=myServicePrincipalName;

Use this when the Service Principal Name on the Kerberos Domain Controller is not the same as the URL that you are authenticating to.

Camelot .NET Connector for Microsoft SharePoint

Standard

Server=myServerAddress;Database=mySite;Domain=myDomain;User=myUsername;Password=myPassword;

NTLM is default authentication method. Domain and Database are optional parameters.

Using SSL encryption

Server=myServerAddress;Database=mySite;Domain=myDomain;User=myUsername;Password=myPassword;SSL=True;

Always use SSL, deny connection if server does not support SSL or certificate is invalid.

Specifying Default authentication

Server=myServerAddress;Database=mySite;Authentication=Default;

Connect to server using default authentication, meaning the authenticated user under which the application is running or is impersonated.

Microsoft ACE OLEDB 12.0

Read, update and delete

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://mysharepointsite.com/documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698};

The keyword "Database" specifies the SharePoint URL

The keyword "List" specifies the GUID value for the desired SharePoint list (list=table)

Query without specifying table names. Ie use "SELECT FROM table" or "SELECT FROM list" (same result from both).

Read more about Finding the Id (Guid) for a SharePoint List.

Read only

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http://mysharepointsite.com/documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698};

Write

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=http://mysharepointsite.com/documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698};

Use different SharePoint list connections for read (IMEX=2) and writes (IMEX=0), do not mix them.