Versions Compared

Old Version 47

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 48

changes.mady.by.user Kalpana Malladi

Saved on

Key

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

In this article

Table of Contents
maxLevel3
absoluteUrltrue

...

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.

...