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

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon ( (blue star) ): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon ( (blue star) ): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ( (blue star) ): Indicates that you can add fields in the fieldset.

  • Remove icon ( (blue star) ): Indicates that you can remove fields from the fieldset.

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.

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: 3306
Example: 1433

Integer/Expression

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

note

  • 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. 

  • 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.

The SQL Server Snap Pack does not support jTDS driver.

note

  • 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.

  • 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.

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

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

Specify the number of statements to execute at a time.

note

  • Select queries are not batched.

  • Using a large batch size could use up the JDBC placeholder limit of 2100.

  • 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

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

note

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

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

Max pool size*

Default value: 50
Example: 60

Integer

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

Max life time*

Default value: 30
Example: 50

Integer

Specify the maximum lifetime of a connection in the pool.

note

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.

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*

Default value: 5
Example: 8

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.

Checkout timeout*

Default value: 10000
Example: 100

Integer

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

note

If you specify 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.

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

Specify a name for the URL property if any.

URL Property Value

Default Value: cursor
Example: cursor

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.

DO NOT use the default sa account for SQL Server Authentication. Learn more about how to choose between SQL Server Authentication and Windows Authentication: SQL Docs: Security: Authentication mode

Windows (Active Directory-based) Authentication

Service Account Authentication

If you upload customized JDBC jars to the directory, the following error might occur:

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

To fix this issue, remove all JDBC jars from the SQL server account and restart the JCC node.

Url property name

Url property value

domain

Your domain name

Domain name must be added as a URL property and not along with the user name.

integratedSecurity

true

Connecting to an MSSQL Instance

  • If the Snap fails to connect to the database, it retries three more times.

  • You can Validate an account connection when creating a SQL Database Account but not when creating a SQL Server Dynamic Account because the account properties of a dynamic account are provided dynamically as pipeline parameters.

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

SQL Server Snap Pack does not support this mode of authentication on Cloudplexes.

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

Refer to Microsoft JDBC Driver for SQL Server for details.

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

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

URL property name

URL property value

IntegratedSecurity

True

AuthenticationScheme

JavaKerberos

domain

Domain name must be added as a URL property and not along with the user name.

Any version of the JAR files available in Microsoft Java and JDBC Support page later than the versions mentioned here can be used.

Authentication Using NTLM Protocol

SQL Server Snap Pack supports this mode of authentication on both Groundplexes and Cloudplexes. Authentication of SQL Server Account using NTLM Protocol is limited to Windows Snaplexes.

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

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

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

note

If you are using the default driver bundled with the SQL Server Snap Pack, you do not have to copy sqljdbc_auth.dll to the jcc ldlib folder.

If you are using the default driver bundled with the SQL Server Snap Pack, you do not have to copy sqljdbc_auth.dll to the jcc ldlib folder.

Linux (Active Directory-based) Authentication

The SQL Server Account supports Linux-based authentication in Groundplexes. The setup instructions must be configured on the Groundplex to align with the Kerberos configuration, and then updating the connection settings provides an integrated authentication. Refer to Microsoft documentation for more information: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16

Troubleshooting

Error

Reason

Resolution

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.

Snap Pack History

Related Content