Using Windows Authentication to connect to SQL Server from Linux

This article will cover how to setup Windows Authentication to connect to SQL Server from a Linux server.

This functionality is dependent on the version of your SQL Server driver. SQL Server driver version 7.4.1 (mssql-jdbc-7.4.1.jre8.jar) and newer contain the functionality for Linux servers to connect with Windows Authentication. 

Infogix Assure 9.4 uses SQL Server driver version 8.2, which has this functionality out-of-the-box. Assure version 9.3 and older will require extra steps to use a newer SQL Server driver, which are outlined within this article. 

Setting up SQL Server connection information

The sections below outline each of the properties to connect to SQL Server. These same settings are used within a Database layout in Infogix Assure.

JDBC connection string

Enabling Windows Authentication requires the following parameters within the JDBC connection string:

• authenticationScheme=NTLM
• integratedSecurity=true

Depending on your LDAP server's configuration, you may also require an domain=YOURDOMAIN parameters or specifying the user name in an email format to include the domain name. 

A sample connection string is below:

jdbc:sqlserver://sampleserver;database=sampleDB;authenticationScheme=NTLM;integratedSecurity=true;domain=INFOGIX

User credentials

User and password credentials are supplied as normal. Depending on your LDAP setup, the username may need to be in user@sampledomain.com format.

Driver class

The standard SQL Server driver class is used:

com.microsoft.sqlserver.jdbc.SQLServerDriver

Using the connection within Infogix Assure

In Assure 9.4, the Database layout can be tested via the UI and controls leveraging the layout can also be tested and run from the UI. If using Assure Client to trigger a local execution of the control, the same Assure Client instructions below can be used.

In Assure 9.3 and older, the embedded SQL Server is version 7.2 and does not contain the functionality for Linux servers to connect with Windows Authentication. The Assure Client is required in order to insert a new SQL Server driver version without overriding the default within the Assure Server. 

Setting up Assure client for local capture

Setup considerations

When downloading a newer SQL Server driver, please ensure compatibility between the SQL Server driver and the SQL Server version. A minimum of SQL Server driver version 7.4 is required for Linux servers to connect with Windows Authentication, which means a minimum of SQL Server 2012 is required.

If multiple SQL Server driver versions are needed, a unique configuration via the init-client-config script is required to keep drivers separate. When running controls via the runcp script, you can leverage the -c <config_name> as the first parameter to control which JVM configuration to use.

Adding the new SQL Server driver into Assure client

To add the driver, simply add the mssql-jdbc-X.X.X.jreX.jar into the directory below:

<assure_client_install>\igx-data\<config_name>\IA\lib\

The exact file name will vary based on the SQL Server driver version and Java version.

Running controls via Assure Client

Control execution is performed via Assure client's runcp script. The runcp script's -local flag is a requirement for the local SQL Server driver to be leveraged. 

A sample command execution is below:

./runcp.sh -local -entity linuxsqlserver -point linuxsqlserver 

If you setup multiple Assure Client configurations, then add -c <config_name> as the first parameter:

./runcp.sh -c sqlserver -local -entity linuxsqlserver -point linuxsqlserver