Versions Compared

Old Version 46

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 47

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

...

  • Ensure that you provide SQL JDBC drivers to connect to the database.

    • SQL Server Snap Pack does not support jTDS driver.

    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.

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

    Integer/Expression

    Specify the 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

    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.

    Info

    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.

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

    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.

    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.

    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.

    ...

    Note

    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.

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

    ...

    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

    Info

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

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

    •  mssql-jdbc-12.2.0.jre11.jar

    Refer to Microsoft JDBC Driver for SQL Serverfor details.

    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:

    ...

    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

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

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

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

    Tip

    After adding the domain name as a Url property (as mentioned above), do not include it again when entering the SQL Server username.

    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/

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

    1. Configure yourSQL Server account settings

    • JDBC Driver Class

    net.sourceforge.jtds.jdbc.Driver

    net.sourceforge.jtds.jdbc.Driver

    • Url properties

    1. Domain = <Your Active Directory domain name>

    2. useNTLMv2 = true

    1. Domain = <Your Active Directory domain name>

    2. useNTLMv2 = true

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

    Tip

    After adding the domain name as a URL property (as mentioned above), do not include it again when entering the SQL Server username.

    ...

    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.

    ...