Service Account Authentication

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 Username and Password.

For Service account authentication, follow these steps:

1. Ensure that Snaplex is running on Windows and an AD user with required access privileges has logged into the Snaplex.
2. 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. SnapLogic recommends using the default JAR. You can download the latest JAR file from here.
   Note: If you upload custom 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.

3. Do not specify a port number.
4. Configure the following properties for JDBC under URL properties:

   URL property name	URL property value
   domain	Your domain name Note: Domain name must be added as a URL property and not along with the user 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 (<server_name>/<instance_name>) in the Hostname field.

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

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

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

User Impersonation

SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. However, in addition to providing the account properties such as Hostname, Database name, Username, and Password, ensure that the following configuration is 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 Principal Name (SPN) for the SQL Server. Learn more.
• Install the correct JAR file. Any version of the JAR files available in Microsoft Java and JDBC Support page later than the versions mentioned here can be used.

Supported versions

mssql-jdbc-12.2.0.jre11.jar driver JAR version for Java 11. Refer to Microsoft JDBC Driver for SQL Server for details.

Configuration

To authenticate Windows SQL Server connection with Active Directory authentication, follow these steps:

Steps	Configuration
1.	Enter your Active Directory Username and Password. In the account settings, enter your Active Directory Username and Password.
2.	Configure the JDBC-related settings. JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver JDBC Connection URL: jdbc:sqlserver://ServerNameFQDN:portNumber;databaseName=DBNAME
3.	Add URL-related properties. IntegratedSecurity: True AuthenticationScheme: JavaKerberos domain: The domain name must be added as a URL property and not along with the user name.

Authentication Using NTLM Protocol

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

Configuration

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

Steps	Configuration
1.	Configure the JDBC-related settings. JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver 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.
2.	Add the URL-related properties Domain: <Your Active Directory domain name> integratedSecurity: true 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.
3.	Click Validate and select the Snaplex when prompted. The "Account validation successful" message is displayed.
4.	Click Apply to complete the setting up of NTLM-based authentication.