In this article, we will look into the details of connecting to SQL Server with JDBC Driver using NTLM authentication. At Bobcares, with our Server Management Services, we can handle your SQL Server issues.

SQL Server-JDBC Driver NTLM Authentication

An application can specify that it wants to connect to a database using NTLM v2 Authentication by utilizing the authenticationScheme connection property with the Microsoft JDBC Driver for SQL Server.

What is an NTLM Authentication? NTLM credentials consist of a domain name, a user name, and a one-way hash of the user’s password and are based on information gathered during the interactive logon process. Without transmitting the user’s password over the wire, NTLM authenticates a user using an encrypted challenge/response protocol. Rather, the system will do a calculation seeking authentication to demonstrate that it has access to the secured NTLM credentials.

In the Windows Challenge/Response(NTLM) protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password, and the client sends a response to the server. If it is a local user account, the server validates the user’s response by looking into the Security Account Manager; if it is a domain user account, the server forwards the response to the domain controller for validating and retrieving the user account’s group policy.

Requirements of NTLM Authentication

NTLM requires the user’s password in order to generate a challenge-response, and the client can establish its identity without providing the password to the server.

Connecting to SQL Server with NTLM authentication

All the properties, except domainname, are mandatory for NTLM Authentication. When the NTLM authenticationScheme property is used, the JDBC driver will throw an error if any are missing.

• domain = domainName
• user = userName
• password = password
• integratedSecurity = true

Logging: The Microsoft JDBC Driver for SQL Server offers the use of tracing (or logging) to assist in the resolution of flaws and errors with the JDBC driver in the application. The JDBC driver leverages the logging of the APIs in java.util.logging to facilitate the usage of tracing, which provides a collection of classes for constructing Logger and LogRecord objects.

In order to use NTLM Authentication to connect to SQL Server, A new logger has been added to support NTLM authentication: com.microsoft.sqlserver.jdbc.internals.NTLMAuthentication.

DataSource: We can set the NTLM properties programmatically while using a datasource to create connections with setAuthenticationScheme, setDomain, and setServerSpn.

SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName("<server>");
ds.setPortNumber(1433); // change if necessary
ds.setIntegratedSecurity(true);
ds.setAuthenticationScheme("NTLM");
ds.setDomain("<domainName>");
ds.setUser("<userName>");
ds.setPassword("<password>");
ds.setDatabaseName("<database>");
ds.setServerSpn("<serverSpn");

try (Connection c = ds.getConnection(); Statement s = c.createStatement();
ResultSet rs = s.executeQuery(“select auth_scheme from sys.dm_exec_connections where session_id=@@spid”)) {
while (rs.next()) {
System.out.println(“Authentication Scheme: ” + rs.getString(1));
}
}

Service principal names: SPN is the name through which a client uniquely identifies a service instance. We can specify the SPN using the serverSpn connection property. The property is in the form:

"MSSQLSvc/fqdn:port@REALM" where:

• fqdn- Fully qualified domain name
• port- Port number
• REALM- The realm of the SQL Server

With the 6.2 release, the driver is also able to build the serverSpn by default.

[Searching for a solution to another query? We’re available 24/7.]

Conclusion

In this article, we have provided a brief description from our Support team of the NTLM Authentication method. We also came to know how to connect to SQL Server with JDBC Driver using NTLM authentication.