Label*

Default Value: N/A
Example: SQL Server Account

String

Specify a unique label for the account.

Account properties*

Use this fieldset to enter in the information to create a connection to the databse.

Hostname*

Default Value: N/A
Example:

• sqlservertest.abcdefghijkl.us-west-5.rds.amazonaws.com

• 190.159.0.124

String/Expression

Specify the server's address to which you must connect.

Port number*

Default Value: 3306
Example: 1433

Integer/Expression

Specify the port number of the database server to which you must connect.

Database name*

Default Value: N/A
Example: SnapLogicDB1

String/Expression

 Specify the name of the database to which you must connect.

Username

Default Value: N/A
Example: snaplogic

String/Expression

Specify the username to connect to the database.

This username is used as the default username when retrieving connections and must be valid to set up the data source.

Password

Default Value: N/A
Example: P#2,nxu0oiX2&?

String/Expression

Specify the password used to connect to the data source.

This password is used as the default password when retrieving connections and must be valid to set up the data source.

JDBC JARs

Use this fieldset to specify the list of JDBC drivers to connect to the SQL Database.

JDBC driver

Default Value: com.microsoft.sqlserver.jdbc.SQLServerDriver
Example: com.microsoft.sqlserver.jdbc.SQLServerDriver

String

The SQL Server Snap Pack is bundled with com.microsoft.sqlserver.jdbc.SQLServerDriver by default. However, you can specify a custom JBDC driver. If you do not upload any JAR file, the default JAR is used.

Open

JDBC driver class*

Default Value: com.microsoft.sqlserver.jdbc.SQLServerDriver
Example: com.microsoft.sqlserver.jdbc.SQLServerDriver

String

Specify the JDBC driver class name to use.

Advanced properties

Use this fieldset to specify the advanced properties for connecting to the database.

Auto commit

Default value: Selected

Checkbox/Expression

Select this checkbox to commit each of the batches immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back.

Deselect this checkbox to commit the output only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.

Batch size*

Default value: 50
Example: 60

Integer/Expression

Specify the number of statements to execute at a time.

Fetch size*

Default value: 100
Example: 80

Integer/Expression

Specify the number of rows to fetch at a time when executing a query.

Max pool size*

Default value: 50
Example: 60

Integer/Expression

Specify the maximum number of connections a pool will maintain at a time.

Max lifetime (minutes)*

Default value: 30
Example: 50

Integer/Expression

Specify the maximum lifetime of a connection in the pool.

Idle timeout (minutes)*

Default value: 5
Example: 8

Integer/Expression

Specify the maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

Checkout timeout (milliseconds)*

Default value: 10000
Example: 100

Integer/Expression

Specify the number of milliseconds to wait for a connection to be available when the pool is exhausted.

URL properties

Use this field set to define URL properties to use if any.

URL property name

Default Value: selectMethod
Example: selectMethod

String/Expression

Specify a name for the URL property if any.

URL property value

Default Value: cursor
Example: cursor

String/Expression

Specify a value for the URL property name.

domain

Your domain name

integratedSecurity

true

IntegratedSecurity

True

AuthenticationScheme

JavaKerberos

domain

java.lang.UnsatisfiedLinkError: Native Library C:\\opt\\snaplogic\\ldlib\\sqljdbc_auth.dll already loaded in another classloader.

You have uploaded customized JDBC JAR files to the directory.

Remove all JDBC JARs from the SQL Server account and restart the JCC node.

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

The correct sqljdbc_auth.dll file is not available in the ldlib folder.

Download the appropriate sqljdbc_auth.dll file  and place it in $SL_ROOT/ldlib.

No valid credentials provided
OR
Server not found in Kerberos database.

The Service Principal Name (SPN) is not setup properly.

Ensure that you have setup the SPN for MSSQLSvc service properly.

Defective token detected.

Kerberos fails when a mismatch occurs between the SPN for the SQL Server service and the hostName.

For example, if FQDN of the machine is used to setup SPN, the hostname must be an FQDN and not an IP address, while establishing a connection.

Provide a value for hostName that matches with the SPN in context. Also ensure that the properties authenticationScheme, domain and integratedSecurity are defined for the account.