Create a connection to SQL Server by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the SQL Server icon is not available, click the Add More icon to download and install the SQL Server connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

You can use the SQL Server Connector for CData Sync to connect to any instance of Microsoft SQL Server, Azure SQL Server, or Azure Data Warehouse.

Connecting to to Microsoft SQL Server

Specify the following connection properties to connect to SQL Server:

• Server: The name of the server running SQL Server.
• Database: The name of the SQL Server database.

Authenticating to SQL Server

Authenticate using Standard Authentication

To authenticate to Microsoft SQL Server using standard authentication, set the following:

• AuthScheme: Set AuthScheme to Password (SQL Server credentials) or NTLM (Windows Credentials).
• User: The username provided for authentication with SQL Server.
• Password: The password associated with the authenticating user.

Authenticating with Kerberos

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

Connecting to Azure SQL Server and Azure Data Warehouse

You can connect to Azure SQL Server or Azure Data Warehouse by setting the following connection properties:

• Server: The server running Azure. You can find this by logging into the Azure portal and navigating to SQL databases (or SQL data warehouses) -> Select your database -> Overview -> Server name.
• Database: The name of the database, as seen in the Azure portal on the SQL databases (or SQL warehouses) page.

Authenticating to Azure

Standard credentials may be used to authenticate to Azure hosted SQL Server. To do so, set the following:

• AuthScheme: Set AuthScheme to Password (SQL Server credentials) or NTLM (Windows Credentials).
• User: The name of the user authenticating to Azure.
• Password: The password associated with the authenticating user.

Alternatively, a form of OAuth may be used by setting AuthScheme to one of AzureAd, AzurePassword, or AzureMSI. All OAuth connections require setting Tenant:

• AzureTenant: The id of the tenant the SQL Server database is hosted on.

Authenticating using Azure AD

Azure AD is a connection type that goes through OAuth. Set your AuthScheme to AzureAD and see Using OAuth Authentication for an authentication guide.

Authenticating using Azure Service Principal

Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Azure Service Principal Authentication for an authentication guide.

Authenticating using Azure Password

To connect using your Azure credentials directly, specify the following connection properties:

• AuthScheme: Set this to AzurePassword.
• User: Set this to your user account you use to connect to Azure.
• Password: Set this to the password you use to connect to Azure.

Authenticating using MSI Authentication

If you are running SQL Server on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:

• AuthScheme: Set this to AzureMSI.

The MSI credentials will then be automatically obtained for authentication.

This section shows how to use the Sync App to authenticate to SQL Server using Kerberos.

Authenticating with Kerberos

To authenticate to SQL Server using Kerberos, set the following properties:

• AuthScheme: Set this to KERBEROS.
• KerberosKDC: Set this to the host name or IP Address of your Kerberos KDC machine.
• KerberosSPN: Set this to the service and host of the SQL Server Kerberos Principal. This will be the value prior to the '@' symbol (for instance, ServiceName/MyHost) of the principal value (for instance, ServiceName/MyHost@EXAMPLE.COM).

Retrieve the Kerberos Ticket

You can use one of the following options to retrieve the required Kerberos ticket.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you won't need to set the User or Password connection properties with this option.

1. Ensure that you have an environment variable created called KRB5CCNAME.
2. Set the KRB5CCNAME environment variable to a path pointing to your credential cache file (for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file will be created when generating your ticket with MIT Kerberos Ticket Manager.
3. To obtain a ticket, open the MIT Kerberos Ticket Manager application, click Get Ticket, enter your principal name and password, then click OK. If successful, ticket information will appear in Kerberos Ticket Manager and will now be stored in the credential cache file.
4. Now that the credential cache file has been created, the Sync App will use the cache file to obtain the kerberos ticket to connect to SQL Server.

As an alternative to setting the KRB5CCNAME environment variable, you can directly set the file path using the KerberosTicketCache property. When set, the Sync App will use the specified cache file to obtain the kerberos ticket to connect to SQL Server.

Keytab File

If the KRB5CCNAME environment variable has not been set, you can retrieve a Kerberos ticket using a Keytab File. To do this, 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 both the KRB5CCNAME environment variable and the KerberosKeytabFile property have not been set, you can retrieve a ticket using a User and Password combination. To do this, set the User and Password properties to the user/password combo that you use to authenticate with SQL Server.

Cross-Realm Authentication

More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g. where one realm/KDC is used for user authentication and another realm/KDC used for obtaining the service ticket).

In such an environment, the KerberosRealm and KerberosKDC properties can be set to the values required for user authentication. The KerberosServiceRealm and KerberosServiceKDC properties can be set to the values required to obtain the service ticket.

SQL Server offers 2 different methods for tracking the changes from your source database:

• Change Tracking
• Change Data Capture

Change Tracking vs. Change Data Capture

Change Tracking is a lightweight solution that provides an efficient tracking mechanism for CData Sync. Once configured on your tables, any DML statement that affects rows in the source table will cause change tracking information for each modified row to be recorded to the change tracking table. To identify the rows that have changed, SQL Server tracks the primary key of the table, the operation the caused the change (Insert, Update, Delete) and the current rowversion. This means only tables that have a primary key can use Change Tracking. Sync then joins the change tracking table to the source table to get the changes.

Change Data Capture on the other hand tracks every change that is applied to a table and records those changes in a shadow history table. Instead of only capturing the primary key like Change Tracking, CDC will record the full row data to the history table allowing CDC to work with tables that do not include the primary key. To get the changes, Sync selects from the history view instead of the source table. As a result, CDC has less impact on the performance of source tables since Sync does not interact directly with the source table when doing incremental replication.

Enable Change Tracking

1. Enable Change Tracking on your database:
   

   	ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

   CHANGE_RETENTION specifies the time period for which change tracking information is kept in your database. It is best to set a larger window to give Sync time to resolve conflicts and errors. If the last successful Job run is outside the retention period (i.e. CHANGE_RETENTION is set to 7 Days but the last successful sync was 8 days previous), Sync will automatically replicate the full table to ensure no changes were missed.
2. Enable Change Tracking on each table:
   

   	ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;

   Note: To use Change Tracking, each table must have at least 1 primary key.

In CData Sync, create a Job with you SQL Server source and select the Use SQL Change Tracking option.

Enable Change Data Capture for CData Sync

1. Enable Change Data Capture on your database:
   

   	USE [<database>];
   	EXEC sys.sp_cdc_enable_db;
   	GO			

2. Enable Change Data Capture on each table by creating a CDC instance:
   

   	USE [<database>];
   	EXEC sys.sp_cdc_enable_table  
   	@source_schema = [<schema>],
   	@source_name   = [<table>],
   	@role_name     = NULL
   	GO	

In CData Sync, create a Job with you SQL Server source and select the Use SQL Change Data Capture option.

Alter Schema

When using Change Tracking, Sync will automatically update the destination table when changes are made to the source table structure like adding a column or changing a datatype. When using Change Data Capture, SQL Server will not automatically track new columns so you must create a new CDC instance and drop the old instance. This will trigger a full refresh of the table in Sync.

Deletion Behavior

Both tracking mechanisms allow Sync to capture deleted records. When using Change Data Capture, Sync only supports the SoftDelete option while Change Tracking supports both Soft and Hard Delete. More information can be found here: CData Sync - Capturing Deletes

This section details a selection of advanced features of the SQL Server Sync App.

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.

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.

Client SSL Certificates

The SQL Server Sync App also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

HTTP Proxies

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

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

Other Proxies

Set the following properties:

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

In addition to modeling data directly from SQL Server, the CData Sync App also includes a few built in stored procedures designed for assisting with OAuth connections against Azure hosted SQL Server. The stored procedures are listed here.

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.

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.

Authentication

Azure Authentication

OAuth

Kerberos

SSL

SSH

Firewall

Proxy

Logging

Schema

Miscellaneous

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

The scheme used for authentication. Accepted entries are Password, NTLM, Kerberos, AzurePassword, AzureAD, AzureMSI, AzureServicePrincipal.

Remarks

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

• Password: Set this to use your SQL Server Password.
• NTLM: Set this to use your Windows credentials to authenticate.
• Kerberos: Set this to use Kerberos authentication.
• AzurePassword: Set this to use Azure Active Directory Password authentication.
• AzureAD: Set this to use Azure Active Directory OAuth authentication.
• AzureMSI: Set this to use Azure Active Directory Managed Service Identity authentication.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal.

The name of the server running SQL Server.

Remarks

Set this property to the name or network address of the SQL Server instance.

The port of the MS SQL Server.

Remarks

The port of the Server hosting the MS SQL Server Database.

The name of the SQL Server database.

Remarks

The name of the SQL Server database running on the specified Server.

The SQL Server user account used to authenticate.

Remarks

Together with Password, this field is used to authenticate against the SQL Server server.

The password used to authenticate the user.

Remarks

The User and Password are together used to authenticate with the server.

The name of the domain for a Windows (NTLM) security login.

Remarks

The name of the domain for a Windows (NTLM) security login.

Whether or not to authenticate with Windows Integrated Security.

Remarks

When this is set to true, a Windows identity will be used to perform Windows authentication. If this value is false, SQL Server authentication will be used.

The NTLM version.

Remarks

This property specifies the NTLM version to use.

This field sets whether SSL is enabled.

Remarks

This field sets whether the Sync App will attempt to negotiate TLS/SSL connections to the server. By default, the Sync App checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.

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

The Microsoft Online tenant being used to access data. If not specified, your default tentant will be used.

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

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

The client Id assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

The client secret assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

The grant type for the OAuth flow.

Remarks

The grant type for the OAuth flow. The following options are available: CODE,CLIENT

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

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: The Sync App will infer the Kerberos Realm and Kerberos KDC from the configured domain name and host as a last resort.

The Kerberos Realm used to authenticate the user with.

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: The Sync App will infer the Kerberos Realm and Kerberos KDC from the user-configured domain name and host as a last resort. 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.

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).

Remarks

The name of the certificate store for the client certificate.

The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.

SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).

The type of key store containing the TLS/SSL client certificate.

Remarks

This property can take one of the following values:

The password for the TLS/SSL client certificate.

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

The subject of the TLS/SSL client certificate.

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.

If a field value contains a comma, it must be quoted.

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:

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 SSH properties you can configure in the connection string for this provider.

The authentication method to be used to log on to an SFTP server.

Remarks

• None: No authentication will be performed. The current User value is ignored, and the connection will be logged in as anonymous.
• Password: The Sync App will use the values of User and Password to authenticate the user.
• Public_Key: The Sync App will use the values of User and SSHClientCert to authenticate the user. SSHClientCert must have a private key available for this authentication method to succeed.

A certificate to be used for authenticating the user.

Remarks

In order to use public key authentication, SSHClientCert must contain a certificate with a valid private key. The certificate's public key value is sent to the server along with a signature produced using the private key. The server will first check to see if the public key values match what is known for the user and then will attempt to use those values to verify the signature.

The SSHClientCertType field specifies the type of the certificate store specified by SSHClientCert. If the store is password protected, specify the password in SSHClientCertPassword.

SSHClientCert is used in conjunction with the SSHClientCertSubject field in order to specify client certificates. If SSHClientCert has a value, and SSHClientCertSubject is set, a search for a certificate is initiated. Please refer to the SSHClientCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

The password of the SSHClientCert certificate if it has one.

Remarks

This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.

The subject of the SSH client certificate.

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.

If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=support@cdata.com". Common fields and their meanings are displayed below.

If a field value contains a comma it must be quoted.

The type of SSHClientCert certificate.

Remarks

The type of SSHClientCert certificate. This property can take one of the following values:

The SSH server.

Remarks

The SSH server.

The SSH port.

Remarks

The SSH port.

The SSH user.

Remarks

The SSH user.

The SSH password.

Remarks

The SSH password.

The SSH server fingerprint.

Remarks

The SSH server fingerprint.

Whether to tunnel the SQL Server connection over SSH. Use SSH.

Remarks

By default the Sync App will attempt to connect directly to SQL Server. When this option is enabled, the Sync App will instead establish an SSH connection with the SSHServer and tunnel the connection to SQL Server through it.

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

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.

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.

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

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:

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.

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.

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\\SQL Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

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.

The application intent connection string property expresses the client application's request to be directed either to a read-write or read-only version of an availability group database. To use read-only routing, a client must use an application intent of read-only in the connection string when connecting to the availability group listener. Without the read-only application intent, connections to the availability group listener are directed to the database on the primary replica.

Remarks

ApplicationIntent allows you to connect to a read-only secondary when connecting to an Availability Group Listener.

The application name connection string property expresses the HTTP User-Agent.

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.

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.

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

This option passes the query to the SQL Server server as is.

Remarks

When this is set, queries are passed through directly to SQL Server.

A timeout for the provider.

Remarks

If the Timeout property is set to 0, operations will not time out; instead, they will run until they complete successfully or encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App raises an error condition.