Adding a Connection to Excel Services

To add a connection to Excel Services:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Excel Services icon is not available, click the Add More icon to download and install the Excel Services connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

This section provides the details for connecting SharePoint On Premises.

Connecting to a Workbook

Regardless of which edition of SharePoint you are using, set File to the Excel workbook. This path is relative to the following properties:

• Library: The Shared Documents library is used by default. You can use this property to specify another document library in your organization; for example, if you want to connect to OneDrive for Business, set this property to "Documents".
• Folder: You can use this property to specify a path to a subfolder in a library. The path is relative to the library name specified in Library.

Connecting to Spreadsheet Data as Tables

The Sync App detects the available tables based on the available objects in the underlying API.

The APIs surface different API objects; select the API based on the organization of your spreadsheets and your SharePoint version:

• OData: The OData API allows access to tables defined from Excel table objects (not ranges or spreadsheets), which you create by clicking Insert > Table in Excel. When connecting to the OData API, the Sync App may not return any tables if you do not have table objects defined in your workbook. Set UseRESTAPI to true to connect to spreadsheets or ranges as tables.
• REST: The REST API enables access to tables defined from Excel table objects, ranges, and spreadsheets. This is the default API. Requesting a large number of rows from ranges and spreadsheets is restricted by the REST API, and the Sync App limits the number of rows returned to 100 by default. The Sync App also defaults to detecting column names from the first row; set Header to disable this.

With DefineTables additionally set, you can define tables based on ranges, using the Excel range syntax. A range that is too large will be restricted by the API.

See Data Model for more information on how the Sync App detects tables and how to query them.

Connecting to SharePoint On Premises

Set the Url to your server's name or IP address. Additionally, set SharePointVersion and the authentication values.

To authenticate to SharePoint OnPremise, set AuthScheme to the authentication type and set User and Password, if necessary.

Note: When connecting to SharePoint On-Premises 2010, you must set UseRESTAPI to true.

• Windows (NTLM)

  This is the most common authentication type. As such, the Sync App is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.

• Kerberos

  Please see Using Kerberos for details on how to authenticate with Kerberos.

• Forms

  This allows authentication through a custom authentication method, instead of Active Directory. To use this authentication type, set AuthScheme to FORMS and set the User and Password.

• ADFS

  Set the AuthScheme to ADFS. The following connection properties need to be set:

  • User: Set this to the ADFS user.
  • Password: Set this to ADFS password for the user.
  • SSOLoginURL: Set this to the WS-trust endpoint of the ADFS server.
  The following SSOProperties are needed to authenticate to ADFS:
  • RelyingParty: The value of the relying party identifier on the ADFS server for Sharepoint.
  Below is an example connection string:
  

  AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SSOLoginURL=https://<authority>/adfs/services/trust/2005/usernamemixed;SSO Properties ='RelyingParty=urn:sharepoint:sp2016;';

• Anonymous Access

  Set the AuthScheme to NONE along with the URL.

Fine Tuning Data Access

To make it easier to access data in advanced integrations, you can use the following:

• TypeDetectionScheme: You can use this property to enable or disable automatic type detection based on the value specified in RowScanDepth.
• RowScanDepth: This property determines the number of rows that will be scanned to determine column data types.

Troubleshooting

413 Request Entity Too Large

This error occurs when a workbook has too much data per row. Generally, solution to this error will be to make the Excel table smaller or to make the rows less dense.

However, setting the following connection properties will enable you to work around this limitation. Note: This workaround is only supported when using the REST API.

• DefineTables: Set this to the desired cell range in your sheet.
• Other: Set this to PageSize=X to determine the number of records that will be paged through at once. Each page should contain less than 100kb. Note that smaller page sizes also entail slower perfomance.

Kerberos

To authenticate to Excel Services with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to Excel Services 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 Sync App provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
3. To obtain a ticket:
   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The Sync App uses the cache file to obtain the Kerberos ticket to connect to Excel Services.

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 Sync App uses the specified cache file to obtain the Kerberos ticket to connect to Excel Services.

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

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.

This section details a selection of advanced features of the Excel Services Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Excel Services and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

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

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

Other Proxies

Set the following properties:

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

The Sync App supports retrieving Excel data from SharePoint On-Premises through the REST API and through the OData API introduced in SharePoint 2013. Excel files must have the .xlsx format used in Excel 2007 and later.

Note: Microsoft has stated that they plan to sunset the Excel Services API for SharePoint Online in February 2022. Please consider migrating to the CData Sync App for Excel Online for future access to your SharePoint data.

The Sync App can detect the tables in your workbook based on the following:

• Spreadsheets
• Excel tables
• Ranges

The following sections provide more details on the available API objects.

Dynamic Metadata Querying

Through dynamic metadata querying, any changes you make in Excel, such as adding an Excel table or spreadsheet or modifying columns, are reflected when you reconnect.

Configuring Table Detection

The OData API and REST API (the default) each surface different API objects. Select which API to use based on how your workbook is organized and your Excel Services version.

Connecting to Excel Tables

The OData API surfaces only the Excel tables you defined in your spreadsheets with Insert -> Table.

Connecting to Spreadsheets, Ranges, and Excel Tables

If you need to access data outside of Excel tables, set UseRESTAPI to true; the REST API enables access to spreadsheets, ranges, and Excel tables as database tables.

See REST API for more information on executing SQL to spreadsheets and ranges.

Supported APIs by SharePoint Version

The following table shows which APIs are available for each SharePoint version:

SharePoint Version	Available APIs
SharePoint Online	REST or OData
SharePoint On-Premises 2016	REST or OData
SharePoint On-Premises 2013	REST or OData
SharePoint On-Premises 2010	REST

When using the OData protocol, you can access only data defined within Excel tables in the spreadsheet, which you create by clicking Insert -> Table in Excel.

Therefore if you have data outside a table object, this data cannot be retrieved using the OData API. Thus you may see a case where no tables are returned. This is a function of the OData API and you may want to look at using the REST API in such a case.

When you are using the REST API, you can select data from the following tables:

• Spreadsheets in the workbook
• Excel table objects
• Ranges you specify in DefineTables

Resolving Name Collisions

When one or more tables have the same name, the Sync App uses the following precedence in decreasing order to resolve the collision: ranges -> Excel tables -> sheets.

For example: MyTable is in DefineTables and there is also an Excel table with the same name in the workbook. The definition specified within DefineTables overrides the Excel table.

Selecting from Spreadsheets

To select data from a spreadsheet, specify the spreadsheet name in the FROM clause. By default, the first row is used to determine column names. You can set Header to false to disable automatic column name detection.

The data range is A1:Z100. To specify a different range, use DefineTables.

Selecting from Excel Tables

To select data from an Excel table object, specify the table name in the FROM clause. These are the tables you create by clicking Insert -> Table in Excel.

Selecting from Ranges

You can use the standard Excel format to define ranges; map the range to a table name in DefineTables.

Data Type Mappings

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

Excel Services (OData V3)	Excel Services (OData V4)	CData Schema
Edm.Binary	Edm.Binary	binary
Edm.Boolean	Edm.Boolean	bool
Edm.DateTime	Edm.DateTimeOffset	datetime
Edm.Double	Edm.Double	double
Edm.Guid	Edm.Guid	guid
Edm.Int32	Edm.Int32	int
Edm.String	Edm.String	string
Edm.TimeOfDay	Edm.TimeOfDay	time

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 authentication. Accepted entries are NTLM,Basic,Digest,Forms,None,Negotiate,ADFS.
URL	The base URL for a site or site collection.
User	The username of the account used to authenticate to the server.
Password	The password of the account used to authenticate to the server.
SharePointVersion	The version of the SharePoint server to which you are connecting.
STSURL	The URL of the security token service (STS) when using Single Sign On (SSO).
UseNTLMV1	Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

Connection

---

Property	Description
DefineTables	Define the tables within a spreadsheet.
File	The name of the Excel file to which to connect.
Folder	The folder containing the workbook specified by the File property.
Header	Indicates whether the first row should be used as a column header when using the REST API.
Library	The Document Library to which to connect.
IgnoreCalcError	Indicates whether to ignore errors that occurred during the calculation.
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
TypeDetectionScheme	Determines how to determine the data type of columns.
UseRESTAPI	Whether or not the REST API is used for retrieving data.

SSO

---

Property	Description
SSOLoginURL	The identity provider's login URL.
SSODomain	The domain of the user when using single sign-on (SSO).
SSOProperties	Additional properties required to connect to the identity provider in a semicolon-separated list.

Kerberos

---

Property	Description
KerberosKDC	The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm	The Kerberos Realm used to authenticate the user.
KerberosSPN	The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile	The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm	The Kerberos realm of the service.
KerberosServiceKDC	The Kerberos KDC of the service.
KerberosTicketCache	The full file path to an MIT Kerberos credential cache file.

SSL

---

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

Firewall

---

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

---

Property	Description
LogModules	Core modules to be included in the log file.

Schema

---

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Miscellaneous

---

Property	Description
IncludeNavigationParentColumns	Indicates if navigation parent columns should be included on navigation views.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
ShowEmptyRows	Indicates whether or not the empty rows should be pushed when UseRESTAPI = true.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

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 authentication. Accepted entries are NTLM,Basic,Digest,Forms,None,Negotiate,ADFS.
URL	The base URL for a site or site collection.
User	The username of the account used to authenticate to the server.
Password	The password of the account used to authenticate to the server.
SharePointVersion	The version of the SharePoint server to which you are connecting.
STSURL	The URL of the security token service (STS) when using Single Sign On (SSO).
UseNTLMV1	Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

The scheme used for authentication. Accepted entries are NTLM,Basic,Digest,Forms,None,Negotiate,ADFS.

Remarks

Together with Password and User, this field is used to authenticate against the server. NTLM is the default option. Use the following options to vccselect your authentication scheme:

• NTLM: Set this to use your Windows credentials for authentication.
• Basic: Set this to use HTTP Basic authentication.
• Digest: Set this to HTTP Digest authentication.
• Forms: Set this to use Forms authentication.
• Negotiate: If AuthScheme is set to Negotiate, the Sync App will Negotiate an authentication mechanism with the server. Set AuthScheme to Negotiate if you want to use Kerberos authentication.
• ADFS: Set this to use Single Sign-On authentication with ADFS.
• None: Set this to use anonymous authentication.

The base URL for a site or site collection.

Remarks

The following are examples of valid URLs:

• http://server/SharePoint/
• http://server/Sites/mysite/
• http://server:90/

The provider will use URL to derive URLs for other calls to the server.

The username of the account used to authenticate to the server.

Remarks

Together with Password, this field is used to authenticate to the SharePoint OnPremise specified in Url.

The password of the account used to authenticate to the server.

Remarks

Together with User, this field is used to authenticate against the server;.

The version of the SharePoint server to which you are connecting.

Remarks

Accepted entries are SharePoint 2013 and SharePoint 2010.

The URL of the security token service (STS) when using Single Sign On (SSO).

Remarks

This property only needs to be set when using Single Sign On with a local Active Directory Federation Services (ADFS).

Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

Remarks

Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

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

---

Property	Description
DefineTables	Define the tables within a spreadsheet.
File	The name of the Excel file to which to connect.
Folder	The folder containing the workbook specified by the File property.
Header	Indicates whether the first row should be used as a column header when using the REST API.
Library	The Document Library to which to connect.
IgnoreCalcError	Indicates whether to ignore errors that occurred during the calculation.
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
TypeDetectionScheme	Determines how to determine the data type of columns.
UseRESTAPI	Whether or not the REST API is used for retrieving data.

Define the tables within a spreadsheet.

Remarks

This property is used to define the ranges within a spreadsheet that will appear as tables, when using the REST API. The value is a comma-separated list of name-value pairs in the form [Table Name]=[Sheet Name]![Range]. Table Name is the name of the table you want to use for the data and will be used when issuing queries. Sheet Name is the name of the sheet within the spreadsheet and Range is the range of cells that contain the data for the table.

Here is an example DefineTables value: DefineTables="DefinedTable1=Sheet1!A1:N25,DefinedTable2=Sheet2!C3:M53"

Note: If the name of a defined table is the same as one returned by default (e.g. same name as a worksheet), the defined table will replace the default table.

The name of the Excel file to which to connect.

Remarks

The name of Excel file to which to connect (including the extension ".xlsx"). The file must exist.

The folder containing the workbook specified by the File property.

Remarks

The full, hierarchical path of the subfolder in a Library where the File can be found. For example if the File is located in a folder called "SubFolder" within the folder called "BaseFolder", the property will be set to "/BaseFolder/SubFolder/".

Indicates whether the first row should be used as a column header when using the REST API.

Remarks

If true, the first row will be used as a column header. Otherwise, the pseudo column names A, B, C, etc. will be used.

Note: This property is only used when UseRESTAPI is 'True'.

The Document Library to which to connect.

Remarks

This property indicates the name of the Document Library containing the Excel file. If no library is specified, the "Shared Documents" library will be used.

If you wish to connect to OneDrive for Business, set this property to "Documents".

Note:

If the language of your SharePoint site is not English, set this property to the name of the library for the corresponding language.

Here are some steps to get the value:

1.Go to your SharePoint site collection.

2.Go to the document library page.

3.Click on Settings menu.

4.lick on the Library settings on the settings menu.

5.Under the List Information section, there is a flag "Web Address".

For example, the address is "https://Your SharePoint Site/subSites/.../subSites/Documents partages/Forms/AllItems.aspx" if the language of SharePoint site belongs to the French. And the value "Documents partages" is the name of the library for the corresponding language.

Indicates whether to ignore errors that occurred during the calculation.

Remarks

IgnoreCalcError defaults to false. If IgnoreCalcError is set to true, the provider will ignore any errors that occur due to formula calculation. If this happens, the formula result may be unreliable, but other data will be accurate.

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Determines how to determine the data type of columns.

Remarks

None	Setting TypeDetectionScheme to None will return all columns as the string type.
RowScan	Setting TypeDetectionScheme to RowScan will scan rows to heuristically determine the data type. The RowScanDepth determines the number of rows to be scanned.

Whether or not the REST API is used for retrieving data.

Remarks

Whether or not the REST API is used for retrieving data. SharePoint 2010 must use the REST API. In SharePoint versions after 2010, both the REST API and OData API are available.

Use the REST API to access spreadsheets and ranges as tables as well as table objects. The OData API enables access to only table objects.

See Data Model for more information on querying spreadsheets organized in different ways.

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

---

Property	Description
SSOLoginURL	The identity provider's login URL.
SSODomain	The domain of the user when using single sign-on (SSO).
SSOProperties	Additional properties required to connect to the identity provider in a semicolon-separated list.

The identity provider's login URL.

Remarks

The identity provider's login URL.

The domain of the user when using single sign-on (SSO).

Remarks

This property is only applicable when using single sign-on (UseSSO is set to true) and if the domain of the User (e.g. [email protected]) is different than the domain configured within the SSO service (e.g. [email protected]).

This property may be required when using the ADFS services.

Additional properties required to connect to the identity provider in a semicolon-separated list.

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in .

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

---

Property	Description
KerberosKDC	The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm	The Kerberos Realm used to authenticate the user.
KerberosSPN	The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile	The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm	The Kerberos realm of the service.
KerberosServiceKDC	The Kerberos KDC of the service.
KerberosTicketCache	The full file path to an MIT Kerberos credential cache file.

The Kerberos Key Distribution Center (KDC) service used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Sync App will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the configured domain name and host.

The Kerberos Realm used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.

If Kerberos Realm is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the user-configured domain name and host. This might work in some Windows environments.

The service principal name (SPN) for the Kerberos Domain Controller.

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Remarks

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

The Kerberos realm of the service.

Remarks

The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The Kerberos KDC of the service.

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The full file path to an MIT Kerberos credential cache file.

Remarks

This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

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

---

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

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

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

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

---

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to Excel Services and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

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

---

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

This indicates whether to use the system proxy settings or not.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

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

---

Property	Description
LogModules	Core modules to be included in the log file.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

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

---

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\Microsoft SharePoint Excel Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

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

---

Property	Description
IncludeNavigationParentColumns	Indicates if navigation parent columns should be included on navigation views.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
ShowEmptyRows	Indicates whether or not the empty rows should be pushed when UseRESTAPI = true.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

Indicates if navigation parent columns should be included on navigation views.

Remarks

When NavigationPropertiesAsViews is set to true, this property controls if parent columns from the navigation property will be displayed or not on the view. It may be worth displaying them in order to take advantage of being able to filter based on information about the parent.

When set to false, the primary keys of the parent will still be displayed to allow for joining back to the parent, but other other columns will not be.

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

The maximum number of rows to scan to look for the columns available in a table.

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

Indicates whether or not the empty rows should be pushed when UseRESTAPI = true.

Remarks

If true, the empty rows will be pushed at the output when UseRESTAPI = true.

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Account WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json

Note that the specified path is not embedded in quotation marks.