MS SQL Server: Java DB Driver Tracing

Product: MS SQL Server with Java
Version: Any version

Microsoft docs.Microsoft.com homepage has a page regarding diagnosing problems with JDBC driver, but it is not comprehensive.

https://docs.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver15

This post is going to provide additional information to supplement Microsoft's doc.  I am going to use TLSv1.2 with MS SQL Server cluster (currently called AlwaysOn) as typically troubleshooting involves both.

Some critical information that Microsoft failed to state:

1. Every different OS vendor and bit could provide different output
2. Every different Java version could provide different output
3. Every different Java vendor's, e.g. Oracle Java, Open Java, IBM Java, SAP Java, will provide different output
4. Every different JDBC driver version could provide different output
5. Every different Java's lib/security/java.security could produce different output. To be technically correct, the cipher could be different
6. Every different Java's lib/security/local_policy.jar could produce different output
7. Every different Java's lib/security/US_export_policy.jar could produce different output
8. If MS SQL Server driver log is configured to output to console (via java.util.logging), then it can only capture INFO log level, but not FINE.  This does not able to help in troubleshooting. It must output to log file
9. MS SQL Server certificate must be created as per requirement (intermediate SSL cert doesn't need to follow this requirement)

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

When troubleshooting JDBC connectivity issue, always ask for:

1. OS version, and bit, e.g. Windows 7 64-bit
2. "java -version" output to confirm the vendor, and version
3. A copy of the JDBC driver file to simulate across diff env, including reproduce locally
4. Check JDBC driver version, if don't have the JAR file
5. Copy of file jre/lib/security/java.security
6. Copy of file jre/lib/security/local_policy.jar
7. Copy of file jre/lib/security/US_export_policy.jar
8. Copy of file jre/lib/security/cacerts, which is the Java keystore
9. Copy of SSL certificate of MS SQL Server
10. Copy of intermediate SSL certificate of MS SQL Server

Location of logging.properties File

Depending on the Java application, or J2EE application that you are troubleshooting the issue, you might be able to modify the Java option to specify different location of the file, or not.  This section going to show various options to keep the logging.properties file

1. Create logging.properties in jre/lib/java.util.logging - use this if you cannot specify alternate location of java.util.logging
2. Create file in current directory, and specify Java option -Djava.util.logging.config.file=logging.properties
3. Create file in specific directory, e.g. D:\temp\, and specify Java option -Djava.util.logging.config.file=D:\temp\logging.properties

Example 1: Run Java program with logging.properties in D:\temp\logging.properties with -jar option

java -Djava.util.logging.config.file=D:\temp\logging.properties -jar [java_application].jar [class name]