Unable to use Windows Integrated Authentication to access MS SQL Server from MID Server (KB0717141)

Description

Background

Customers can use Windows Integrated Authentication (also known as NTLM Authentication, Domain Authentication and so on: https://en.wikipedia.org/wiki/Integrated_Windows_Authentication) to run queries against an MS SQL Server via JDBC using a MID Server running on a Windows host.

Problem Symptoms

Some customers have seen the following error:

MID Server reported error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:f5905e6e-13c1-48da-85ef-fe1bd7e0a57d
com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:60)
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2229)
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.service_now.mid.connections.jdbc.JDBCConnection.establishConnection(JDBCConnection.java:102)
com.service_now.mid.connections.jdbc.JDBCConnection.connect(JDBCConnection.java:74)
com.service_now.mid.connections.jdbc.JDBCConnectionFactory.create(JDBCConnectionFactory.java:65)
com.service_now.mid.connections.ConnectionCachePool.getAvailableConnection(ConnectionCachePool.java:82)
com.service_now.mid.connections.ConnectionCache.get(ConnectionCache.java:94)
com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:768)
com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:116)
com.service_now.mid.probe.AProbe.process(AProbe.java:96)
com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:125)
com.service_now.mid.queue_worker.AWorkerThread.run(AWorkerThread.java:20)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)

Step 1: Verify the SNC_JVM_ARCH Variable is Correct

Ensure the in the \agent\conf\wrapper-jvm.conf file exists in the MID Server installation directory and that it contains either of the following lines (use x86-64 for a 64-bit MID Server installation, x86-32 for a 32-bit MID Server):

set.SNC_JVM_ARCH=x86-64
or
set.SNC_JVM_ARCH=x86-32

Important: This variable needs to be set to the architecture (x86-32 for 32-bit; or x86-64 for 64-bit) of the Mid Server, not the host OS. It's possible for a 32-bit MID server to be installed and working on a 64-bit host OS. If you don't know whether your MID server was installed from the 32-bit or 64-bit MID Server installer do the following to check:

Go to the folder where the MID Server is installed, go into the logs directory
Open the latest agent... log file
Search in the log file for the string AutoUpgrade.3600 Installed
You'll see something like this, note the part right at the end that indicates whether the installed MID server is x86-32 or x86-64:
2019-11-20 22:01:13 (530) AutoUpgrade.3600 Installed: [mid-core.newyork-06-26-2019__patch2-09-18-2019_09-24-2019_1701.universal.universal.zip, mid-jre.madrid-12-18-2018__patch3-04-24-2019_05-03-2019_1443.windows.x86-32.zip]
Example for 64-bit:11/12/19 05:03:00 (719) AutoUpgrade.3600 Installed: [mid-jre.madrid-12-18-2018__patch0-01-08-2019_01-17-2019_1433.linux.x86-64.zip, mid-core.madrid-12-18-2018__patch7a-10-01-2019_10-03-2019_1530.universal.universal.zip]

Step 2: Verify the Java Classpath and Library Path Variables are Correct

If the above doesn't resolve the issue try these steps:

In \agent\conf\wrapper-override.conf or wrapper.conf (preferred wrapper-override.conf) file in the MID Server installation directory make sure that you have

# Java Classpath (include wrapper.jar)
# Add class path elements as needed starting from 1
wrapper.java.classpath.1=lib/*.jar
wrapper.java.classpath.2=extlib/*.jar

# Java Library Path (location of Wrapper.DLL or libwrapper.so)
wrapper.java.library.path.1=lib
wrapper.java.library.path.2=lib/%SNC_JVM_ARCH%
Make sure that you have the correct version (32 or 64bit) of sqljdbc_auth.dll in the folder lib/x86-32 and lib/x86-64
Make sure that you restart the mid server after any config change
Verify that the SQL server is configured to allow Integrated Authentication (https://msdn.microsoft.com/en-us/library/bsz5788z.aspx)
Check the driver compatibility (https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-2017)

Step 3: Contacting Support

If the issue still hasn't been resolved please raise a Case in HI with Customer Support, provide the results of the above troubleshooting steps and the following details:

a) MID server OS
b) JRE version
c) JDBC driver jar file and version
d) Full path of all occurrence of sqljdbc_auth.dll and their version info
e) Extract of MID server log and wrapper log where the error happened.

Notifications