skip to main content
Parameters for all supported Data Store types : Microsoft SQL Server connection parameters : Microsoft SQL Server connection parameters (on-premise)
 

Try DataDirect Cloud Now

Microsoft SQL Server connection parameters (on-premise)

Watch a video to see how to create and test a connection to an on-premise Microsoft SQL Server database, using the On-Premises Connector. The video also describes how to connect to a cloud instance of Microsoft SQL Server.
The connection parameters also apply to Microsoft Azure SQL Database, unless specifically noted.
The following tables describe parameters available on the tabs of a Microsoft SQL Server® On-Premise Data Source dialog:
*General Tab
*Security Tab
*OData Tab
*DataTypes Tab
*Advanced Tab

General Tab

Click the thumbnail to view the screen. Required fields are marked with an asterisk.
General tab of the SQL Server Data Source Dialog (On-Premise)General tab of the SQL Server Data Source Dialog (On-Premise)
Table 59. General tab Connection Parameters for Microsoft SQL Server
Field
Description
Connector ID
The unique identifier of the DataDirect Cloud On-Premise Connector that is to be used to access the on-premise data source. Click the arrow Down arrow for a field) and select the Connector that you want to use. The identifier can be a descriptive name, the name of the machine where the Connector is installed, or the Connector ID for the Connector.
If you have not installed an On-Premises Connector, and no Connectors have been shared with you, this field and drop-down list are empty.
If you own multiple Connectors that have the same name, for example, Production, an identifier is appended to each Connector, for example, Production_dup0 and Production_dup1. If the Connectors in the drop-down list were shared with you, the owner's name is appended, for example, Production(owner1) and Production(owner2).
Data Source Name
A unique name for this Data Source definition.
Note: Names can contain only alphanumeric characters and underscore.
Database
The name of the database that is running on the database server. If not specified, the default database for your login is used.
Description
A description of this set of connection parameters.
Password
A case-sensitive password that is used to connect to your Microsoft SQL Server database. A password is required if user ID/password authentication is enabled on your database. Contact your system administrator to obtain your password.
By default, the characters in the Password field you type are not shown. If you want the password to be displayed in clear text, click the eye Watchful eye password button button. Click the button again to conceal the password.
Note: By default, the password is encrypted.
Port Number
The TCP port of the primary database server that is listening for connections to the database.
Server Name
Specifies either the IP address in IPv4 or IPv6 format, or the server name (if your network supports named servers) of the primary database server, for example, 122.23.15.12 or mysqlserver.
User Id
The user name that is used to connect to the database. A user name is required only if SQL Server authentication is enabled on your database. Contact your system administrator to obtain your user name

Security Tab

Click the thumbnail to view the screen.
Security tab of the SQL Server Data Source Dialog (On-Premise)Security tab of the SQL Server Data Source Dialog (On-Premise)
Table 60. Security tab Connection Parameters for Microsoft SQL Server On-Premise
Field
Description
Authentication Method
Determines which authentication method the connectivity service uses when establishing a connection. If the specified authentication method is not supported by the database server, the connection fails and the connectivity service returns an error.
Valid Values:
ntlmjava | ntlm2java | userIdPassword
If set to ntlmjava, the connectivity service uses NTLM authentication, but requires a user ID and password to be specified. You must specify the name of the domain server that administers the database. You can specify the domain server using the Domain property. If the Domain property is not specified, the connectivity service tries to determine the domain server from the User property. If the connectivity service cannot determine the domain server name, it returns an error.
If set to ntlm2java, the connectivity service uses NTLMv2 authentication, but requires a user ID and password to be specified. You must specify the name of the domain server that administers the database. You can specify the domain server using the Domain property. If the Domain property is not specified, the connectivity service tries to determine the domain server from the User property. If the connectivity service cannot determine the domain server name, it returns an error. This value is supported for Windows and UNIX/Linux clients.
If set to userIdPassword, the connectivity service uses SQL Server authentication when establishing a connection. If a user ID is not specified, the connectivity service returns an error.
The default value is userIdPassword.
Crypto Protocol Version
Specifies a comma-separated list of the protocol versions that can be used in creating an SSL connection to the Data Source. If the specified protocol is not supported by the database server, the connection fails and the connectivity service returns an error.
Valid Values:
cryptographic_protocol [[, cryptographic_protocol ]...]
where:
cryptographic_protocol
is one of the following cryptographic protocols:
TLSv1 | TLSv1.1 | TLSv1.2
The client must send the highest version that it supports in the client hello.
Note: Good security practices recommend using TLSv1.2 if your data source supports that protocol version, due to known vulnerabilities in the earlier protocols.
Example
Your security environment specifies that you can use TLSv1.1 and TLSv1.2. When you enter the following values, the connectivity service sends TLSv1.2 to the server first:
TLSv1.1,TLSv1.2
Default: TLSv1, TLSv1.1, TLSv1.2
Domain
Specifies the name of the domain server that administers the database. Set this parameter only if you are using NTLM authentication (Authentication Method=ntlmjava). If the Domain property is unspecified, the connectivity service tries to determine the domain server name from the User property.
Valid Values:
string
where string is the name of the domain server.
Default: empty string
Encryption Method
Determines whether data is encrypted and decrypted when transmitted over the network between the DataDirect Cloud connectivity service and the on-premise database server.
Valid Values:
noEncryption | SSL
If set to noEncryption, data is not encrypted or decrypted.
If set to SSL, data is encrypted using SSL. If the database server does not support SSL, the connection fails and the DataDirect Cloud connectivity service throws an exception.
*Connection hangs can occur when the DataDirect Cloud connectivity service is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the Login Timeout parameter to avoid problems when connecting to a server that does not support SSL.
*When SSL is enabled, the following properties also apply:
Host Name In Certificate
ValidateServerCertificate
Crypto Protocol Version
The default value is noEncryption.
HostNameInCertificate
Specifies a host name for certificate validation when SSL encryption is enabled (Encryption Method=SSL) and validation is enabled (Validate Server Certificate=true). This optional parameter provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server that the DataDirect Cloud connectivity service is connecting to is the server that was requested.
Valid Values:
host_name | #SERVERNAME#
where host_name is a valid host name.
If host_name is specified, the DataDirect Cloud connectivity service compares the specified host name to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the DataDirect Cloud connectivity service compares the host name with the Common Name (CN) part of the certificate’s Subject name. If the values do not match, the connection fails and the DataDirect Cloud connectivity service throws an exception.
If #SERVERNAME# is specified, the DataDirect Cloud connectivity service compares the server name that is specified in the connection URL or data source of the connection to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the DataDirect Cloud connectivity service compares the host name to the CN part of the certificate’s Subject name. If the values do not match, the connection fails and the DataDirect Cloud connectivity service throws an exception. If multiple CN parts are present, the DataDirect Cloud connectivity service validates the host name against each CN part. If any one validation succeeds, a connection is established.
The default is an empty string.
ValidateServer Certificate
Determines whether the DataDirect Cloud connectivity service validates the certificate that is sent by the database server when SSL encryption is enabled (Encryption Method=SSL). When using SSL server authentication, any certificate that is sent by the server must be issued by a trusted Certificate Authority (CA). Allowing the DataDirect Cloud connectivity service to trust any certificate that is returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.
Valid Values:
true | false
If the check box is selected (true), the DataDirect Cloud connectivity service validates the certificate that is sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. If the Host Name In Certificate parameter is specified, the DataDirect Cloud connectivity service also validates the certificate using a host name. The Host Name In Certificate parameter is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the DataDirect Cloud connectivity service is connecting to is the server that was requested.
If the check box is not selected (false), the DataDirect Cloud connectivity service does not validate the certificate that is sent by the database server. The DataDirect Cloud connectivity service ignores any Java system properties.
Default: true

OData Tab

The following table describes the controls on the OData tab. For information on using the Configure Schema editor, see Enabling OData and working with Data Source groups. For information on formulating OData requests, see "Formulating queries" under Querying with OData.
Click the thumbnail to view the screen. Required fields are marked with an asterisk.
OData tabOData tab
Table 61. OData tab connection parameters for Microsoft SQL Server
Field
Description
Access URI
Specifies the base URI for the OData feed to access your DataDirect Cloud data source, for example, https://service.datadirectcloud.com/api/odata. You can copy the URI and paste it into your application's OData configuration.
The URI contains the case-insensitive name of the data source to connect to, and the query that you want to execute. This URI is the OData Service Root URI for the OData feed. The Service Document for the data source is returned by issuing a GET request to the data source's service root.
The OData Service Document returns the names of the entities exposed by the Data Source OData service. To get details such as the properties of the entities exposed, the data types for those properties and the relationships between entities, the Service Metadata Document can be fetched by adding /$metadata to the service root URI.
Schema Map
Enables OData support. If a schema map is not defined, the OData API cannot be used to access the data store using this Data Source definition. Use the Configure Schema editor to select the tables to expose through OData.
See Using the Configure Schema editor for more information.
Data Source Caching
Specifies whether the connection to the backend data source is cached in a session associated with the data source. Caching the back end connection improves performance when multiple OData queries are submitted to the same data source because the connection does not need to be created on every query.
Caching of the back end connection can get in the way when trying to configure a data source for OData. If a change is made to any of the DataDirect Cloud data source connection parameters, those changes will not be seen because the connection was established using the old data source definition, and was cached. The session that caches the backend connection is discarded if there is no activity to the data source for approximately 5 minutes.
When you configure a data source for OData, it is recommended that the OData session caching be disabled. Once you are satisfied with the OData configuration for the data source, enable the parameter to get the performance improvement provided by caching the connection to the backend data source.
Valid Values:
When set to 1, session caching is enabled. This provides better performance for production.
When set to 0, session caching is disabled. Use this value when you are configuring the data source.
Default: 1
Page Size
Determines the number of entities returned on each page for paging controlled on the server side. On the client side, requests can use the $top and $skip parameters to control paging. In most cases, server side paging works well for large data sets. Client side pagination works best with a smaller data sets where it is not as expensive to fetch subsequent pages.
Valid Values: 0 | n
where n is an integer from 1 to 10000.
When set to 0, the server default of 2000 is used.
Default: 0
Refresh Result
Controls what happens when you fetch the first page of a cached result when using Client Side Paging. Skip must be omitted or set to 0. You can use the cached copy of that first page, or you can re-execute the query to get a new result, discarding the previously cached result. Re-executing the query is useful when the data being fetched may change between two requests for the first page. Using the cached result is useful if you are paging back and forth through results that are not expected to change.
Valid Values:
When set to 0, the OData service caches the first page of results.
When set to 1, the OData service re-executes the query.
Default: 1
Inline Count Mode
Specifies how the connectivity service satisfies requests that include the $inlinecount parameter when it is set to allpages. These requests require the connectivity service to include the total number of entities that are defined by the OData query request. The count must be included in the first page in server-driven paging and must be included in every page when using client-driven paging.
The optimal setting depends on the data store and the size of results. The OData service can run a separate query using the count(*) aggregate to get the count, before running the query used to generate the entities. In very large results, this approach can often lead to the first page being returned faster. Alternatively, the OData service can fetch the entire result before returning the first page. This approach works well for small results and for data stores that cannot optimize the count(*) aggregate; however, it may have a longer initial response time for the first page if the result is large.
Valid Values:
When set to 1, the connectivity service runs a separate count(*) aggregate query to get the count of entities before executing the query to return results. In very large results, this approach can often lead to the first page being returned faster.
When set to 2, the connectivity service fetches all entities before returning the first page. For small results, this approach is always faster. However, the initial response time for the first page may be longer if the result is large.
Default: 1
Top Mode
Indicates how requests typically use $top and $skip for client side pagination, allowing the service to better anticipate how to process queries.
Valid Values:
Set to 0 when the application generally uses $top to limit the size of the result and rarely attempts to get additional entities by combining $top and $skip.
Set to 1 when the application uses $top as part of client-driven paging and generally combines $top and $skip to page through the result.
Default: 0
OData Read Only
Controls whether write operations can be performed on the OData service. Write operations generate a 405 Method Not Allowed response if this option is enabled.
Existing OData-enabled data sources are read only (write operations are disabled). To enable write operations for an existing OData enabled data source, clear the OData Read Only option on the OData tab. Then, on the Data Sources tab, regenerate the OData model for the data source by clicking on the OData model icon Synch completed successfully.
Valid Values:
true | false
When the check box is selected (set to true), OData access is restricted to read-only mode.
When the check box is not selected (set to false), write operations can be performed on the OData service.
Default: false

DataTypes Tab

Click the thumbnail to view the screen.
Data Types tab of the SQL Server Data Source Dialog (On-Premise)Data Types tab of the SQL Server Data Source Dialog (On-Premise)
Table 62. DataTypes tab Connection Parameters for Microsoft SQL Server
Field
Description
Date Time Input Parameters
Specifies how the DataDirect Cloud connectivity service describes the data type for Date/Time/Timestamp input parameters.
This parameter only applies to connections to Microsoft SQL Server 2008 and higher and Microsoft Azure SQL Database. For connections to prior versions of Microsoft SQL Server, the DataDirect Cloud connectivity service always describes Date/Time/Timestamp input parameters as datetime.
Valid Values:
auto | dateTime | dateTimeOffset
If set to auto, the DataDirect Cloud connectivity service uses the following rules to describe the data type of Date/Time/Timestamp input parameters:
*If an input parameter is set using setDate(), the DataDirect Cloud connectivity service describes it as date.
*If an input parameter is set using setTime(), the DataDirect Cloud connectivity service describes it as time.
*If an input parameter is set using setTimestamp(), the DataDirect Cloud connectivity service describes it as datetimeoffset.
If set to dateTime, the DataDirect Cloud connectivity service describes Date/Time/Timestamp input parameters as datetime.
If set to dateTimeOffset, the DataDirect Cloud connectivity service describes Date/Time/Timestamp input parameters as datetimeoffset.
Default: auto
Describe Input Parameters
Determines whether the DataDirect Cloud connectivity service attempts to determine, at execute time, which data type to use to send input parameters to the database server. Sending parameters as the data type the database expects improves performance and prevents locking issues caused by data type mismatches.
Valid Values:
noDescribe | describeIfString | describeIfDateTime | describeAll
If set to noDescribe, the DataDirect Cloud connectivity service sends String and Date/Time/Timestamp input parameters to the server as specified by the StringInputParameterType and DateTime Input Parameter Type parameters.
If set to describeIfString, the DataDirect Cloud connectivity service submits a request to the database to describe String input parameters. The DataDirect Cloud connectivity service uses the data types that it returns to determine whether to describe the String input parameters as nvarchar or varchar. If this operation fails, the DataDirect Cloud connectivity service sends String input parameters to the server as specified by the String Input Parameter Type parameter.
If set to describeIfDateTime, the DataDirect Cloud connectivity service submits a request to the database to describe Date/Time/Timestamp input parameters. The DataDirect Cloud connectivity service uses the data types that it returns to determine how to describe the Date/Time/Timestamp input parameters. If this operation fails, the DataDirect Cloud connectivity service sends Date/Time/Timestamp input parameters to the server as specified by the DateTime Input Parameter Type connection parameter.
If set to describeAll, the DataDirect Cloud connectivity service submits a request to the database to describe both String and Date/Time/Timestamp input parameters and uses the data types that it returns to determine which data type to use to describe the input parameters. If this operation fails, the DataDirect Cloud connectivity service sends String input parameters to the server as specified by the String Input Parameter Type parameter and sends Date/Time/Timestamp input parameters to the server as specified by the Date Time Input Parameter connection parameter.
Default: noDescribe
Fetch TWFS AsTime
Determines whether the DataDirect Cloud connectivity service returns column values with the time data type as the JDBC data type TIME or TIMESTAMP.
Supported only for Microsoft SQL Server 2008 and higher.
Valid Values:
true | false
If the check box is selected (true), the DataDirect Cloud connectivity service returns column values with the time data type as the JDBC data type TIME. The fractional seconds portion of the value is truncated.
If the check box is not selected (false), the DataDirect Cloud connectivity service returns column values with the time data type as the JDBC data type TIMESTAMP. The fractional seconds portion of the value is preserved. Time columns are not searchable when they are described and fetched as timestamp.
Default: false
FetchTSWTZAs Timestamp
Determines whether column values with the datetimeoffset data type are returned as a JDBC VARCHAR or TIMESTAMP data type.
This parameter only applies to connections to Microsoft SQL Server 2008 and higher and Microsoft Azure SQL Database.
Valid Values: true | false
If the check box is selected (true), column values with the datetimeoffset data type are returned as a JDBC TIMESTAMP data type.
If the check box is not selected (false), column values with the datetimeoffset data type are returned as a JDBC VARCHAR data type.
Default: false
String Input Parameter Type
Determines whether the DataDirect Cloud connectivity service sends String input parameters to the database in Unicode or in the default character encoding of the database.
Valid Values: nvarchar | varchar
If set to nvarchar, the DataDirect Cloud connectivity service sends String input parameters to the database in Unicode.
If set to varchar, the DataDirect Cloud connectivity service sends String input parameters to the database in the default character encoding of the database. This value can improve performance because the server does not need to convert Unicode characters to the default encoding.
Notes
*When set to nvarchar and a value is specified for the CodePageOverride parameter, this parameter is ignored and a warning is generated.
Default: nvarchar
Truncate Fractional Seconds
Determines whether the DataDirect Cloud connectivity service truncates timestamp values to three fractional seconds. For example, a value of the datetime2 data type can have a maximum of seven fractional seconds.
Valid Values:
true | false
If the check box is selected (true), the DataDirect Cloud connectivity service truncates all timestamp values to three fractional seconds.
If the check box is not selected (false), the DataDirect Cloud connectivity service does not truncate fractional seconds.
Default: (true)
XML Describe Type
Determines whether the DataDirect Cloud connectivity service maps XML data to the LONGVARCHAR or LONGVARBINARY data type.
Valid Values:
longvarchar | longvarbinary
If set to longvarchar, the DataDirect Cloud connectivity service maps XML data to the LONGVARCHAR data type.
If set to longvarbinary, the DataDirect Cloud connectivity service maps XML data to the LONGVARBINARY data type.
Default: empty string

Advanced Tab

To see a larger view of the screenshot of the Advanced tab, click the thumbnail; or, right-click the thumbnail and select an option to open the thumbnail in a different window or tab.
Advanced tab of the SQL Server Data Source DialogAdvanced tab of the SQL Server Data Source Dialog
Table 63. Advanced tab Connection Parameters for Microsoft SQL Server
Field
Description
Alternate Servers
Specifies one or more alternate servers for failover and is required for all failover methods. To turn off failover, do not specify a value for the Alternate Servers connection property.
Valid Values:
(servername1[:port1][,servername2[:port2]]...)
The server name (servername1, servername2, and so on) is required for each alternate server entry. Port number (port1, port2, and so on) is optional for each alternate server entry. If the port is unspecified, the port number of the primary server is used. If the port number of the primary server is unspecified, the default port number is used.
Default: None
Always Report Trigger Results
Determines how the DataDirect Cloud connectivity service reports results that are generated by database triggers (procedures that are stored in the database and executed, or fired, when a table is modified). For Microsoft SQL Server 2005 and higher and Azure, this includes triggers that are fired by Data Definition Language (DDL) events.
Valid Values:
true | false
If the check box is selected (true), the DataDirect Cloud connectivity service returns all results, including results that are generated by triggers. Multiple trigger results are returned one at a time. You can use the SQLMoreResults function to return individual trigger results. Warnings and errors are reported in the results as they are encountered.
If the check box is not selected (false):
*For Microsoft SQL Server 2005 and higher and Microsoft Azure SQL Database, the DataDirect Cloud connectivity service does not report trigger results if the statement is a single INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, or DENY statement.
*For other Microsoft SQL Server databases, the DataDirect Cloud connectivity service does not report trigger results if the statement is a single INSERT, UPDATE, or DELETE statement.
If the check box is not selected (false), the only result that is returned is the update count that is generated by the statement that was executed (if no errors occurred). Although trigger results are ignored, any errors and warnings that are generated by the trigger are reported. If errors are reported, the update count is not reported.
Default: false
Application Intent
Specifies whether the DataDirect Cloud connectivity service connects to read-write databases or requests read-only routing to connecto to read-only database replicas. Read-only routing applies to connections to Microsoft SQL Server 2012 where AlwaysOn Availability Groups have been deployed.
Valid Values:
ReadOnly | ReadWrite
If set to ReadOnly, the DataDirect Cloud connectivity service requests read-only routing and connects to the read-only database replicas as specified by the server.
If set to ReadWrite, the DataDirect Cloud connectivity service connects to a read-write node in the AlwaysOn environment.
Default: ReadWrite
Note: By setting Application Intent to ReadOnly and querying read-only database replicas when possible, you shift load away from the read-write nodes of your database cluster to read-only nodes.
Bulk Load Options
Enables bulk load protocol options for batch inserts that the DataDirect Cloud connectivity service can take advantage of when Enable Bulk Load is set to a value of true.
Valid Values:
0 | 1 | 2 | 16 | 32 | 64
Value
Option Enabled
0
All of the options are disabled.
1
The KeepIdentity option preserves identity values. If unspecified, identity values are ignored in the source and are assigned by the destination.
Note: If using the bulk load feature with batch inserts, this option has no effect if enabled.
2
The TableLock option assigns a table lock for the duration of the bulk copy operation. Other applications cannot update the table until the operation completes. If unspecified, the default bulk locking mechanism specified by the database server is used.
16
The CheckConstraints option checks integrity constraints while data is being copied. If unspecified, constraints are not checked.
32
The FireTriggers option causes the database server to fire insert triggers for the rows being inserted into the database. If unspecified, triggers are not fired.
64
The KeepNulls option preserves null values in the destination table regardless of the settings for default values. If unspecified, null values are replaced by column default values where applicable.
Example
A value of 67 means the KeepIdentity, TableLock, and KeepNulls options are enabled (1 + 2 + 64).
Default: 2
Catalog Options
Determines which type of metadata information is included in result sets when a JDBC application calls DatabaseMetaData methods. To include multiple types of metatdata information, add the sum of the values that you want to include. In this case, specify 6 to include synonyms and to emulate getColumns() calls.
Valid Values:
2 | 4
If set to 2, result sets contain synonyms that are returned from the following DatabaseMetaData methods: getColumns(), getExportedKeys(), getFunctionColumns(), getFunctions(), getImportedKeys(), getIndexInfo(), getPrimaryKeys(), getProcedureColumns(), and getProcedures().
If set to 4, a hint is provided to the DataDirect Cloud connectivity service to emulate getColumns() calls using the ResultSetMetaData object instead of querying database catalogs for column information. Result sets contain synonyms. Using emulation can improve performance because the SQL statement that is formulated by the emulation is less complex than the SQL statement that is formulated using getColumns(). The argument to getColumns() must evaluate to a single table. If it does not, because of a wildcard or null value, for example, the DataDirect Cloud connectivity service reverts to the default behavior for getColumns() calls.
Default:2
Code Page Override
The code page the DataDirect Cloud connectivity service uses to convert Character and Clob data. The specified code page overrides the default database code page or column collation. All Character and Clob data that is returned from or written to the database is converted using the specified code page.
By default, the DataDirect Cloud connectivity service automatically determines which code page to use to convert Character data. Use this parameter only if you need to change the DataDirect Cloud connectivity service’s default behavior.
Valid Values:
string
where string is the name of a valid code page that is supported by your JVM. For example, CP950.
Default: empty string
Enable Bulk Load
Specifies whether to use the bulk load protocol for insert, update, delete, and batch operations. This increases the number of rows that the DataDirect Cloud connectivity service loads to send to the data store. Bulk load reduces the number of network trips.
Valid Values:
true | false
If the check box is selected (true), the DataDirect Cloud connectivity service uses the native bulk load protocols for batch inserts. If the check box is not selected (false), the DataDirect Cloud connectivity service uses the batch mechanism for batch inserts.
Default: false
Extended Options
Specifies a semi-colon separated list of connection options and their values. Use this configuration option to set the value of undocumented connection options that are provided by Progress DataDirect technical support. You can include any valid connection option in the Extended Options string, for example:
Database=Server1;UndocumentedOption1=value[;UndocumentedOption2=value;]
If the Extended Options string contains option values that are also set in the setup dialog, the values of the options specified in the Extended Options string take precedence.
Valid Values: string
Default: empty string
Initialization String
A semicolon delimited set of commands to be executed on the cloud data store after DataDirect Cloud has established and performed all initialization for the connection. If the execution of a SQL command fails, the connection attempt also fails and DataDirect Cloud returns an error indicating which SQL commands failed.
Syntax:
SQLcommand[[; SQLcommand]...]
where:
SQLcommand is a SQL command. Multiple commands must be separated by semicolons.
The default is an empty string.
Load Balancing
Determines whether the connectivity service uses client load balancing in its attempts to connect to the servers (primary and alternate) defined in a Connector group. You can specify one or multiple alternate servers by setting the AlternateServers property.
Valid Values: true | false
If set to true, the connectivity service uses client load balancing and attempts to connect to the servers (primary and alternate) in random order. The connectivity service randomly selects from the list of primary and alternate On Premise Connectors which server to connect to first. If that connection fails, the connectivity service again randomly selects from this list of servers until all servers in the list have been tried or a connection is successfully established.
If set to false, the connectivity service does not use client load balancing and connects to each servers based on their sequential order (primary server first, then, alternate servers in the order they are specified).
Default: false
Notes
*The Alternate Servers connection parameter specifies one or multiple alternate servers for failover and is required for all failover methods. To turn off failover, do not specify a value for the Alternate Servers property.
Login Timeout
The amount of time, in seconds, to wait for a connection to be established  before timing out the connection request.
Valid Values:
0 | x
where x is a positive integer that represents a number of seconds.
If set to 0, the connectivity service does not time out a connection request.
If set to x, the connectivity service waits for the specified number of seconds before returning control to the application and throwing a timeout exception.
Default: 30
Max Pooled Statements
The maximum number of prepared statements to cache for this connection. If the value of this property is set to 20, the connectivity service caches the last 20 prepared statements that are created by the application.
The default value is 0.
Query Timeout
Sets the default query timeout (in seconds) for all statements that are created by a connection.
Valid Values:
-1 | 0 | x
If set to -1, the query timeout functionality is disabled. The DataDirect Cloud connectivity service silently ignores calls to the Statement.setQueryTimeout() method.
If set to 0, the default query timeout is infinite (the query does not time out).
If set to x, the DataDirect Cloud connectivity service uses the value as the default timeout for any statement that is created by the connection. To override the default timeout value set by this connection option, call the Statement.setQueryTimeout() method to set a timeout value for a particular statement.
The default value is 0.
Result Set Meta Data Options
Determines whether the DataDirect Cloud connectivity service returns table name information in the ResultSet metadata for Select statements.
Valid Values:
0 | 1
If set to 0 and the ResultSetMetaData.getTableName() method is called, the DataDirect Cloud connectivity service does not perform additional processing to determine the correct table name for each column in the result set. The getTableName() method may return an empty string for each column in the result set.
If set to 1 and the ResultSetMetaData.getTableName() method is called, the DataDirect Cloud connectivity service performs additional processing to determine the correct table name for each column in the result set. The DataDirect Cloud connectivity service returns schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the DataDirect Cloud connectivity service can determine that information.
Default: 0
Select Method
A hint to the DataDirect Cloud connectivity service that determines whether the DataDirect Cloud connectivity service requests a database cursor for Select statements. Performance and behavior of the DataDirect Cloud connectivity service are affected by this property, which is defined as a hint because the DataDirect Cloud connectivity service may not always be able to satisfy the requested method.
Valid Values:
direct | cursor
If set to direct, the database server sends the complete result set in a single response to the DataDirect Cloud connectivity service when responding to a query. A server-side database cursor is not created if the requested result set type is a forward-only result set. Typically, responses are not cached by the DataDirect Cloud connectivity service. Using this method, the DataDirect Cloud connectivity service must process the entire response to a query before another query is submitted. If another query is submitted (using a different statement on the same connection, for example), the DataDirect Cloud connectivity service caches the response to the first query before submitting the second query. Typically, the direct method performs better than the cursor method.
If set to cursor, a server-side cursor is requested. When returning forward-only result sets, the rows are returned from the server in blocks. The setFetchSize() method can be used to control the number of rows that are returned for each request when forward-only result sets are returned. Performance tests show that, when returning forward-only result sets, the value of Statement.setFetchSize() significantly impacts performance. There is no simple rule for determining the setFetchSize() value that you should use. We recommend that you experiment with different setFetchSize() values to determine which value gives the best performance for your application. The cursor method is useful for queries that produce a large amount of data, particularly if multiple open result sets are used.
Default: direct
Snapshot Serializable
For Microsoft SQL Server 2005 and higher and Microsoft Azure SQL Database only. Allows your application to use Snapshot Isolation for connections.
This parameter is useful for applications that have the Serializable isolation level set. Using the Snapshot Serializable parameter allows you to use Snapshot Isolation with no or minimum code changes. If you are developing a new application, you may find that using the constant TRANSACTION_SNAPSHOT is a better choice.
Valid Values:
true | false
If the check box is selected (true) and your application has the transaction isolation level set to Serializable, the application uses Snapshot Isolation for connections.
If the check box is not selected and your application has the transaction isolation level set to Serializable, the application uses the Serializable isolation level.
Note: To use Snapshot Isolation, your database also must be configured for Snapshot Isolation.
Default: false
Suppress Connection Warnings
Determines whether the DataDirect Cloud connectivity service suppresses "changed database" and "changed language" warnings when connecting to the database server.
Valid Values:
true | false
If the check box is selected, warnings are suppressed.
If the check box is not selected, warnings are not suppressed.
Default: false
Transaction Mode
Specifies how the DataDirect Cloud connectivity service delimits the start of a local transaction.
Valid Values: implicit | explicit
If set to implicit, the DataDirect Cloud connectivity service uses implicit transaction mode. This means that the database, not the DataDirect Cloud connectivity service, automatically starts a transaction when a transactionable statement is executed. Typically, implicit transaction mode is more efficient than explicit transaction mode because the DataDirect Cloud connectivity service does not have to send commands to start a transaction and a transaction is not started until it is needed. When TRUNCATE TABLE statements are used with implicit transaction mode, the database may roll back the transaction if an error occurs. If this occurs, use the explicit value for this parameter.
If set to explicit, the DataDirect Cloud connectivity service uses explicit transaction mode. This means that the DataDirect Cloud connectivity service, not the database starts a new transaction if the previous transaction was committed or rolled back.
Default: implicit
See the steps for:
Creating a Data Source definition