Using Windows Authentication with SQL Server Accounts

[ebarner]

The topic of "providing windows ID / Password to SQL Server has come up a few different times so below is a solution that I found to work in most instances.

Scenario:

• Service accounts with Native SQL Server authentication is NOT supported (internal security rules, regulatory compliance etc.)
• Running SnapLogic using “IntegratedSecurity = true” would require every database to grant access to an individual robot / service account and didn’t allow specific user tracking

Solution:
3rd party JDBC driver called jtds (http://jtds.sourceforge.net/) allows you to specify URL Properties and pass in account credentials.

You will need to upload this jar file into the SLDB and reference that within the account settings (see screen caps below).

On the SQL Server, you can see Windows Authentication selection for ID “SQLSvr_TESTING”

Account settings within SnapLogic for “SQL Server Account” type:

Account settings that need to be defined

1. Referencing the JAR files uploaded to the SLDB
2. The JDBC Driver Class should be set to net.sourceforge.jtds.jdbc.Driver
3. URL Properties Defined:
   useNTLMv2 = true
   domain = [YourDomain]
   instance = [InstanceName-OPTIONAL]

NOTE :
It’s important to stipulate that if the users windows password changes, they will need to come into SnapLogic and adjust the password in the account as well. By default, the SQL Server snaps will attempt to reconnect 3 times so you may get into a situation where the ID becomes locked out.

[ncrouch]

The other thing to know about SQL Server accounts (that often comes up along with Windows Authentication), is the ability to connect to a specific instance of MSSQL running on the same server as another (multiple or named instances).

In this case, the hostname should be specified as:

servername/instancename

Alternatively, the instance name can be specified in the url properties:

URL Property Name: instanceName
URL Property Value: <instanceName>

Most crucial in either case is to not specify a port. If a port is specified, SnapLogic will simply connect to that port without checking with the Server Browser service first.

[RogerSramkoski]

Please note, the latest release of the jTDS driver on SourceForge was version 1.3.1, released in June 2013, supporting Microsoft SQL Server (6.5 up to 2012). The previous posts are not a recommendation or requirement to use jTDS and this post is not a recommendation against it. Whenever you consider using a third-party JDBC driver please make sure to research the documentation and open issues if public, and please reach out to SnapLogic Support if you encounter specific issues.