In this article

Overview

You can use this account type to connect SQL Server Snaps with data sources that use SQL Server database accounts. Account details specified in this account (at a Snap level) are called Service Accounts.

Limitations

• Authentication of SQL Server Account using NTLM Protocol is limited to Windows Snaplexes.

Account Settings

...

...

Field Name*

...

Field Type

...

Description

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.

...

In this article

Overview

You can use this account type to connect SQL Server Snaps with data sources that use SQL Server database accounts. Account details specified in this account (at a Snap level) are called Service Accounts.

Limitations

Authentication of SQL Server Account using NTLM Protocol is limited to Windows Snaplexes.

Account Settings

...

Method 2: Using the JTDI file

Steps

Groundplex

Cloudplex

1. Download the distribution ZIP file

jtds-1.3.1-dist.zip

jtds-1.3.1-dist.zip from https://sourceforge.net/projects/jtds/files/jtds/1.3.1/

2. Extract and upload the zip file contents to the desired folder.

1. jtdi-1.3.1.jar driver to the SnapLogic shared/jar_file folder.

2. ntlmauth.dll file to the  $SL_ROOT/ldlib folder.

jtdi-1.3.1.jar driver to the SnapLogic shared/jar_file folder.

3. Configure yourSQL Server account settings

• JDBC Driver Class

net.sourceforge.jtds.jdbc.Driver

net.sourceforge.jtds.jdbc.DriverUsername

Password

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

JDBC JARs*

JDBC Driver

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

JDBC Driver Class*

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

Auto commit

Default value: Selected

Batch size*

Default value: 50
Example: 60

Fetch size*

Default value: 100
Example: 80

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

Large values could cause the server to run out of memory.

Max pool size*

Default value: 50
Example: 60

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

Max life time*

Default value: 30
Example: 50

Idle Timeout*

Default value: 5
Example: 8

Checkout timeout*

Default value: 10000
Example: 100

URL Property Name

Default Value: selectMethod
Example: selectMethod

URL Property Value

Default Value: cursor
Example: cursor

Field Name*		Field Type	Description
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: SnapLogicDB1 Example: sqlservertest.abcdefghijkl.us-west-5.rds.amazonaws.com 190.159.0.124		String/Expression	Specify the name of the database Specify the server's address to which you must connect.	Info If you need to connect to an on-premise server, specify the domain name or the IP address. For example, test.mydbserver.com or 190.159.0.124.
Port number* Default Value: N/A 3306 Example: snaplogic 1433		StringInteger/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.	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.	Use this fieldset to specify the list of JDBC drivers to connect to the SQL Database.			String	Specify the JBDC driver to use. If you do not upload any JAR file, the default JAR is used. Note SQL Server Snap Pack does not support jTDS driver. The SQL Server Snap Pack is upgraded to the Microsoft JDBC Driver 12.2.0.jre11. Existing pipelines that do not use the default driver (bundled with the SQL Server Snap Pack) are not impacted. If you want to use NTLM or other authentications with the latest default driver (where the DLL file is mandatory), then you must copy the DLL file to the Snaplex path. If you create a new SQL Server Account with the default driver, you must add the trustServerCertificate URL connection property set to true in the Url properties field set. Image Removed	String	Specify the JDBC driver class name to use.
Advanced properties		Use this fieldset to specify the advanced properties for connecting to the database.
Checkbox	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.	Integer	Specify the number of statements to execute at a time. Select queries are not batched. Using a large batch size could use up the JDBC placeholder limit of 2100.	Integer	Integer	Integer	Specify the maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.	Integer	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.	Integer	Specify the number of milliseconds to wait for a connection to be available when the pool is exhausted. If you specify 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.
URL properties	Use this field set to define URL properties to use if any.
	String	Specify a name for the URL property if any.	String	Specify a value for the URL property name.

SQL Server Authentication

In the Account Settings, provide the Username and Password as defined for the user on the SQL Server instance. This SQL Server account must have the required privileges to run the operations seamlessly defined for the Snaps in this Snap Pack.

Windows (Active Directory-based) Authentication

Service Account Authentication

• Ensure that Snaplex is running on Windows and an AD user with required access privileges has logged into the Snaplex.

• To use the default jar budled with the Snap Pack, place the latest version of sqljdbc_auth.dll in $SL_ROOT/ldlib  (c:\opt\Snaplogic\ldlib). You do not have to add the custom jar file in the account. You can find the latest version of the DLL on the Microsoft SQL Server JDBC Driver website. 

• Service Account Authentication implies that the active AD user session on the Snaplex is leveraged to obtain access to the SQL Server instance. Therefore, you need not provide values in the Username and Password fields.

• Do not specify a port number.

• SnapLogic recommends using the default JAR. You can download the latest JAR file from here.

• Configure the following properties for JDBC under URL properties:

...

Url property name

...

Url property value

...

domain

...

Your domain name

...

integratedSecurity

...

true

Connecting to an MSSQL Instance

• You can connect to a specific instance of MSSQL by using the instance name along with the server name in Hostname <server_name>/<instance_name>. 

  Image Removed

• Alternatively, you can define the instance name by using the URL property name, instanceName.

  Image Removed

User Impersonation

In addition to providing the account properties such as Hostname, Database name, Username, and Password, ensure that the following settings/configurations are done for Active Directory Kerberos or NTLM Protocol (as the case may be) to implement User Impersonation with the SQL Server instance.

Authentication Using Active Directory Kerberos

SnapLogic supports (tested and certified) Active Directory authentication for SQL servers on the following driver JAR version for Java 11:

•  mssql-jdbc-12.2.0.jre11.jar

...

Ensure that you have installed the correct JAR file. Also, use the following configurations in Account Settings to configure Active Directory authentication:

• JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver

• JDBC Connection URL: jdbc:sqlserver://ServerNameFQDN:portNumber;databaseName=DBNAME

SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. The prerequisites are as follows:

• In the account settings, add the following to Url property name and Url property value:

...

URL property name

...

URL property value

...

IntegratedSecurity

...

True

...

AuthenticationScheme

...

JavaKerberos

...

domain

...

• In the account settings, enter your Active Directory Username and Password.

Authentication Using NTLM Protocol

There are two ways to authenticate your Windows SQL Server connection with the NTLM protocol.

Method 1: Using NTLM protocol and JAR file

...

Steps

...

Groundplex

...

Cloudplex

...

1. Download the DLL file.

...

sqljdbc_auth.dll

...

N/A

...

2. Copy/Upload Files to the desired location.

...

• JAR: To the SnapLogic shared/jar_file folder.

• DLL:  $SL_ROOT/ldlib folder.

...

JAR: To the SnapLogic shared/jar_file folder.

...

3. Configure your SQL Server account settings:

...

• JDBC Driver Class

...

com.microsoft.sqlserver.jdbc.SQLServerDriver

...

com.microsoft.sqlserver.jdbc.SQLServerDriver

...

• Url properties

...

1. Domain = <Your Active Directory domain name>

2. integratedSecurity = true

3. authenticationScheme = NTLM

...

1. Domain = <Your Active Directory domain name>

2. integratedSecurity = true

3. authenticationScheme = NTLM

...

4. Click Validate and Apply.

...

• Click Validate and select the Snaplex when prompted. The message "Account validation successful" is displayed.

• Click Apply to finish setting up NTLM-based authentication. 

...

port number of the database server to which you must connect. Remove the port number for Windows-based authentication when connecting to SQL Server account with multiple or named instances. Otherwise, SnapLogic tries to connect to the specified port without checking with the Server Browser service list. You must add the port number for Windows-based authentication when connecting to an MS SQL instance with single instance.
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. Note The SQL Server Snap Pack does not support jTDS driver. The SQL Server Snap Pack is upgraded to the Microsoft JDBC Driver 12.2.0.jre11. Existing pipelines that do not use the default driver (bundled with the SQL Server Snap Pack) are not impacted. If you create a new SQL Server Account with the default driver, you must add the trustServerCertificate URL connection property set to true in the Url properties field set. Image Added
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. Select queries are not batched. Using a large batch size could use up the JDBC placeholder limit of 2100.
Fetch size* Default value: 100 Example: 80		Integer/Expression	Specify the number of rows to fetch at a time when executing a query. Large values could cause the server to run out of memory.
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. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.
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. If you specify 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.
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.

SQL Server Authentication

In the Account Settings, provide the Username and Password as defined for the user on the SQL Server instance. This SQL Server account must have the required privileges to run the operations seamlessly defined for the Snaps in this Snap Pack.

Windows (Active Directory-based) Authentication

Service Account Authentication

• Ensure that Snaplex is running on Windows and an AD user with required access privileges has logged into the Snaplex.

• To use the default jar bundled with the Snap Pack, place the latest version of sqljdbc_auth.dll in $SL_ROOT/ldlib  (c:\opt\Snaplogic\ldlib). You do not have to add the custom jar file to the account. You can find the latest version of the DLL on the Microsoft SQL Server JDBC Driver website. 

• Service Account Authentication implies that the active AD user session on the Snaplex is leveraged to obtain access to the SQL Server instance. Therefore, you need not provide values in the Username and Password fields.

• Do not specify a port number.

• SnapLogic recommends using the default JAR. You can download the latest JAR file from here.

• Configure the following properties for JDBC under URL properties:

Connecting to an MSSQL Instance

• You can connect to a specific instance of MSSQL by using the instance name along with the server name in Hostname <server_name>/<instance_name>. 

  Image Added

• Alternatively, you can define the instance name by using the URL property name, instanceName.

  Image Added

User Impersonation

In addition to providing the account properties such as Hostname, Database name, Username, and Password, ensure that the following settings/configurations are done for Active Directory Kerberos or NTLM Protocol (as the case may be) to implement User Impersonation with the SQL Server instance.

Authentication Using Active Directory Kerberos

Prerequisites

• Set up Active Directory and SQL Server in the same domain and verify that you are able to perform Windows authentication using SQL Server Management Studio.

• On the Active Directory Server, Set the Service Principle Name(SPN) for the SQL Server. Learn more.

• Installed the correct JAR file.

Supported versions

mssql-jdbc-12.2.0.jre11.jardriver JAR version for Java 11. Refer to Microsoft JDBC Driver for SQL Serverfor details.

To configure Active Directory authentication, configure the following in the Account settings:

• JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver

• JDBC Connection URL: jdbc:sqlserver://ServerNameFQDN:portNumber;databaseName=DBNAME

SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. 

Prerequisites

In the account settings, add the following to Url property name and Url property value:

• In the account settings, enter your Active Directory Username and Password.

Authentication Using NTLM Protocol

To authenticate Windows SQL Server connection with the NTLM protocol, follow these steps:

1. Configure your SQL Server Account settings as shown below:
   JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
   Url properties

   • Domain = <Your Active Directory domain name>

...

1. • integratedSecurity = true

...

1. Domain = <Your Active Directory domain name>

2. useNTLMv2 = true

1. • authenticationScheme = NTLM
     Note: After adding the domain name as an Url property (as mentioned above), do not include it again when providing the SQL Server username.

2. Click Validate and select the Snaplex when prompted. The

...

1. "Account validation successful" message is displayed.

2. Click 

...

1. Apply to finish setting up NTLM-based authentication.

...

Linux (Active Directory-based) Authentication

...

...

• SQL Server Docs

• Accounts in SnapLogicSnapLogic Product Glossary

• Getting started with SnapLogic