Home database jdbc sql server troubleshooting

SQL Server JDBC: Configuration Secrets & Troubleshooting Tips for Seamless Connections

Updated: • 0 • min read
Table of Contents

This article delves into Java Database Connectivity (JDBC) and the troubleshooting steps encountered during its configuration, specifically focusing on JDBC for SQL Server.

Microsoft JDBC Driver for SQL Server

This guide serves as a comprehensive reference for JDBC, encompassing the driver itself, supporting documentation, installation guidelines across various operating systems (OS), and solutions to common SQL Server connection issues.

JDBC Driver Version Changes

Stay updated with the latest JDBC driver versions for SQL Server. Here’s a list of recent releases:

JDBC Requirements

Ensuring your environment meets the necessary requirements is crucial for successful JDBC connectivity.

  • The Java Runtime Environment (JRE) version must be compatible with the JDBC driver. The driver filename itself indicates the required JRE version. For instance, mssql-jdbc-9.4.1.jre8.jar necessitates JRE 1.8, while mssql-jdbc-9.4.1.jre11.jar requires JRE 11.0. Using a mismatched JRE version can lead to compatibility issues and connection failures.

  • CLASSPATH is a vital Java environment variable that specifies the directories and JAR files Java needs to execute applications. It is essential to correctly configure the CLASSPATH to include the JDBC driver and any dependent JAR files. The minimum CLASSPATH configuration should include the current working directory, denoted by .;, and the path to the JDBC driver JAR file. Without a correctly set CLASSPATH, Java will not be able to locate the JDBC driver, preventing database connections.

JDBC Configuration and Troubleshooting Steps

Let’s explore the essential steps for configuring JDBC and troubleshooting common issues.

Set the CLASSPATH Variable

The CLASSPATH variable can be defined either at the operating system level as an environment variable or within the application environment itself, such as in Tomcat. When CLASSPATH is managed within the application environment, it is typically necessary to consult with the application vendor or developer to ensure accurate configuration. Incorrect CLASSPATH settings are a common cause of JDBC connection problems.

There are two primary methods to set the CLASSPATH:

Note: Settings made via the command prompt are temporary and will be lost when the command prompt window is closed. In contrast, modifications through the graphical user interface (GUI) are permanent but necessitate a system reboot to take effect.

Command prompt example

For temporary, session-specific CLASSPATH modification, the command prompt offers a quick solution.

Set CLASSPATH=.;C:\sqljdbc_12.4\enu\mssql-jdbc-12.4.0.jre8.jar

This command sets the CLASSPATH to include the current directory and the specified JDBC driver JAR file path. Remember this setting is only active for the current command prompt session.

GUI example

For a persistent CLASSPATH setting that endures system restarts, use the graphical user interface.

  1. Open Control Panel and navigate to System and Security.
  2. Select System and then Advanced system settings.
  3. In the System Properties window, click on the Environment Variables button.
  4. Under System variables, click New to create a new system variable.
  5. Enter CLASSPATH as the Variable name.
  6. Enter .;C:\sqljdbc_12.4\enu\mssql-jdbc-12.4.0.jre8.jar as the Variable value.
  7. Click OK on all open windows to save the changes.

JDBC Configuration Troubleshooting

Connection Strings with Passed-in Credentials

A connection string with passed-in credentials directly embeds authentication information within the connection string itself. This approach allows for specifying username and password as parameters within the string. When establishing a connection to a database, providing credentials is essential for secure access and authentication.

Here are examples illustrating how to construct connection strings for different SQL Server authentication modes:

SQL Server authentication

This method utilizes standard SQL Server username and password for authentication.

String connectionUrl = "jdbc:sqlserver://<ServerName>:<PortNum>;user=<MySQLAuthAccount>;password=<MyPassword>;trustServerCertificate=true;";

Replace <ServerName>, <PortNum>, <MySQLAuthAccount>, and <MyPassword> with your actual server details, username, and password.

Windows AD authentication without integrated security

This method employs Windows Active Directory credentials for authentication without relying on integrated security features.

String connectionUrl = "jdbc:sqlserver://<ServerName>:<PortNum>;user=<MyADAuthAccount>;password=<MyPassword>;Domain=<MyDomain>;trustServerCertificate=true;javaAuthentication=NTLM";

Replace placeholders such as <ServerName>, <PortNum>, <MyADAuthAccount>, <MyPassword>, and <MyDomain> with your specific Active Directory details.

Windows AD authentication with Kerberos and without integrated security

For enhanced security, Kerberos can be used with Windows AD authentication, still without integrated security.

String connectionUrl = "jdbc:sqlserver://<ServerName>:<PortNum>;user=<MyADAuthAccount>;password=<MyPassword>;Domain=<MyDomain>;trustServerCertificate=true;javaAuthentication=JavaKerberos";

Ensure you replace <ServerName>, <PortNum>, <MyADAuthAccount>, <MyPassword>, and <MyDomain> with your Kerberos and Active Directory configuration details.

Integrated NTLM connection

Integrated NTLM authentication leverages the currently logged-in Windows user’s credentials. This approach requires the client machine to be part of a Windows domain.

The mssql-jdbc_auth-<version>-<arch>.dll file is necessary for integrated authentication and must be placed in the system’s path.

  • 64-bit DLL: %Path%;C:\sqljdbc_12.4.1.0_enu\sqljdbc_12.4\enu\auth\x64\mssql-jdbc_auth-12.4.1.x64.dll
  • 32-bit DLL: %Path%;C:\sqljdbc_12.4.1.0_enu\sqljdbc_12.4\enu\auth\x86\mssql-jdbc_auth-12.4.1.x86.dll

You can either append the path to your system’s Path environment variable or copy the DLL file into an existing directory already listed in the Path.

String connectionUrl = "jdbc:sqlserver://<ServerName>:<PortNum>;integratedSecurity=true;Domain=<MyDomain>;trustServerCertificate=true;javaAuthentication=NTLM";

Replace <ServerName>, <PortNum>, and <MyDomain> with your server and domain information.

Integrated Kerberos connections

Integrated Kerberos authentication, similar to NTLM, relies on domain membership but utilizes Kerberos for authentication.

Prerequisites for Kerberos integrated connections include:

  • Domain membership for the client machine.
  • SSSD (System Security Services Daemon) installed and configured on Linux OS (if applicable).
  • Klist (Kerberos list) installed and configured on Linux OS (if applicable).

The mssql-jdbc_auth-<version>-<arch>.dll file is also required and must be placed in the system path, similar to NTLM integrated authentication.

  • 64-bit DLL: %Path%;C:\sqljdbc_12.4.1.0_enu\sqljdbc_12.4\enu\auth\x64\mssql-jdbc_auth-12.4.1.x64.dll
  • 32-bit DLL: %Path%;C:\sqljdbc_12.4.1.0_enu\sqljdbc_12.4\enu\auth\x86\mssql-jdbc_auth-12.4.1.x86.dll

Additionally, a Jaas.conf file is necessary for Kerberos authentication. This file is not included with the driver or Java by default.

Note: The Jaas.conf file enables Java to utilize the current logged-in user’s context and cached Kerberos tickets.

You can configure Java to locate the Jaas.conf file using these methods:

  1. Modify the java.security file by adding or modifying the following line:

    # Default login configuration file
login.config.url.1=C:=\<Path to the File\>\jaas.conf

  2. Alternatively, specify the Jaas.conf file path as a parameter when launching your Java application:

    javac -Djava.security.auth.login.config=c:\myDirectory\Jaas.conf myapp.java
java -Djava.security.auth.login.config=c:\myDirectory\Jaas.conf myapp

To configure the Jaas.conf file for Kerberos integrated authentication:

SQLJDBCDriver {
com.sun.security.auth.module.Krb5LoginModule required
useTicketCache=true;
};

The connection string for Kerberos integrated authentication is:

String connectionUrl = "jdbc:sqlserver://<ServerName>:<PortNum>;integratedSecurity=true;Domain=<MyyDomain>;trustServerCertificate=true;javaAuthentication=JavaKerberos;";

Replace <ServerName>, <PortNum>, and <MyyDomain> with your server and domain details.

Sample code

JDBC drivers typically include sample code to demonstrate connection establishment. The ConnectURL.java example is commonly used and can be found in the driver’s samples directory (\sqljdbc_12.4\enu\samples\connections\ConnectURR.java).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectURL {
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://ServerName:Port;user=SQLAuthAccount;password=SomePassword;trustServerCertificate=true;";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();)
    {
                String SQL = "SELECT @@version";
        ResultSet rs = stmt.executeQuery(SQL);
                // Iterate through the data in the result set and display it.
                while (rs.next())
        {
                     System.out.println(rs.getString(1));
                }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e)
        {e.printStackTrace();    }
  }
}

This sample code demonstrates a basic connection and executes a simple SQL query to retrieve the SQL Server version.

JDBC Driver Tracing

JDBC driver tracing is a valuable tool for diagnosing connection and communication issues. Setting the tracing level to FINEST generally provides the most detailed information. There are two primary methods for enabling driver tracing: programmatically and using a logging.properties file.

If you opt for the logging.properties file method, it’s essential to locate the correct environment for this file. Common locations include $JAVA_HOME\conf\ and $JAVA_HOME\jre\lib.

Follow these steps to configure tracing using the logging.properties file:

  1. Modify the logging.properties file to resemble the following global properties configuration:

    ############################################################
#  Global properties
############################################################
# "handlers" specifies a comma-separated list of log Handler
# classes. These handlers will be installed during VM startup.
# Note that these classes must be on the system classpath.
# By default, we only configure a ConsoleHandler, which will only
# show messages at the INFO and above levels.

handlers= java.util.logging.ConsoleHandler

# To also add the FileHandler, use the following line instead.
#handlers= java.util.logging.FileHandler

# Default global logging level.
# This specifies which kinds of events are logged across
# all loggers.  For any given facility this global level
# can be overridden by a facility-specific level
# Note that the ConsoleHandler also has a separate level
# setting to limit messages printed to the console.

.level= INFO

    Handlers determine where the logging output is directed. FileHandler writes to a file, while ConsoleHandler outputs to the console. For detailed tracing, directing output to a file is generally recommended due to the potentially large volume of data.

    • Comment out ConsoleHandler:

      #handlers= java.util.logging.ConsoleHandler

    • Uncomment FileHandler:

      handlers= java.util.logging.FileHandler

    Note: Setting .level to OFF will suppress log messages from appearing in the console window.

     .level=OFF

  2. Configure specific FileHandler logging properties:

    ############################################################
# Handler specific properties.
# Describes specific configuration info for Handlers.
############################################################

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = %h/java%u.log
java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
# Default number of locks FileHandler can obtain synchronously.
# This specifies maximum number of attempts to obtain lock file by FileHandler
# implemented by incrementing the unique field %u as per FileHandler API documentation.
java.util.logging.FileHandler.maxLocks = 100
java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

# Limit the messages that are printed on the console to INFO and above.
java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

# Example to customize the SimpleFormatter output format
# to print one-line log message like this:
#     <level>: <log message> [<date/time>]
#
# java.util.logging.SimpleFormatter.format=%4$s: %5$s [%1$tc]%n

  3. Modify the following section to match or include these lines for enhanced file logging:

    java.util.logging.FileHandler.pattern = /Path/java%u.log
java.util.logging.FileHandler.limit = 5000000
java.util.logging.FileHandler.count = 20
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
java.util.logging.FileHandler.level = FINEST

  4. Adjust the java.util.logging.FileHandler.pattern = %h/java%u.log line, replacing %h/ with your desired file storage path. For example:

    java.util.logging.FileHandler.pattern = c:/Temp/java%u.log

  5. Set the JDBC driver-specific logging level by adding com.microsoft.sqlserver.jdbc.level=FINEST at the end of the “Facility-specific properties” section:

    ############################################################
# Facility-specific properties.
# Provides extra control for each logger.
############################################################

# For example, set the com.xyz.foo logger to only log SEVERE
# messages:
# com.xyz.foo.level = SEVERE
com.microsoft.sqlserver.jdbc.level=FINEST

  6. Save the modified logging.properties file.

    The complete configured file should resemble this:

    ############################################################
#  Default Logging Configuration File
#
# You can use a different file by specifying a filename
# with the java.util.logging.config.file system property.
# For example, java -Djava.util.logging.config.file=myfile
############################################################

############################################################
#  Global properties
############################################################

# "handlers" specifies a comma-separated list of log Handler
# classes.  These handlers will be installed during VM startup.
# Note that these classes must be on the system classpath.
# By default we only configure a ConsoleHandler, which will only
# show messages at the INFO and above levels.
#handlers= java.util.logging.ConsoleHandler

# To also add the FileHandler, use the following line instead.
handlers= java.util.logging.FileHandler

# Default global logging level.
# This specifies which kinds of events are logged across
# all loggers.  For any given facility this global level
# can be overridden by a facility-specific level
# Note that the ConsoleHandler also has a separate level
# setting to limit messages printed to the console.
.level= OFF

############################################################
# Handler specific properties.
# Describes specific configuration info for Handlers.
############################################################

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = c:/Temp/java%u.log
java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
# Default number of locks FileHandler can obtain synchronously.
# This specifies maximum number of attempts to obtain lock file by FileHandler
# implemented by incrementing the unique field %u as per FileHandler API documentation.
java.util.logging.FileHandler.maxLocks = 100
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter

# Limit the messages that are printed on the console to INFO and above.
#java.util.logging.ConsoleHandler.level = INFO
#java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

# Example to customize the SimpleFormatter output format
# to print one-line log message like this:
#     <level>: <log message> [<date/time>]
#
# java.util.logging.SimpleFormatter.format=%4$s: %5$s [%1$tc]%n

############################################################
# Facility-specific properties.
# Provides extra control for each logger.
############################################################

# For example, set the com.xyz.foo logger to only log SEVERE
# messages:
# com.xyz.foo.level = SEVERE
com.microsoft.sqlserver.jdbc.level=FINEST

After troubleshooting and reproducing the error, remember to revert these changes to prevent excessive log file generation.

Alternatively, you can create a new logging.properties file with the configurations above and specify its path when launching your application:

java -Djava.util.logging.config.file=c:\<Path to the file>\logging.properties myapp

This command allows you to use a custom logging.properties file located outside the default Java directories ($JAVA_HOME\conf\ and $JAVA_HOME\jre\lib).

Third-party information disclaimer:

The third-party products mentioned in this article are developed by companies independent of Microsoft. Microsoft provides no гарантии, either implied or otherwise, regarding the performance or reliability of these products.

Do you have any questions or experiences with JDBC configuration and troubleshooting for SQL Server? Share your thoughts in the comments below!

Post a Comment