We have a java based system which uses the SqlServerXADataTable class from sqljdbc4.jar to connect to SQL Server. Whilst migrating this app to a new server I got the error `Connection test for 'MyDatabaseName' FAILED: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Serv…

Readme.md

DBTest

Credits

Note: I based the above script on a similar one by aimtiaz11, as I've not coded in Java for almost 20 years! I tweaked it to have less dependencies, and to use SQLServerXADataSource instead of SQLServerDriver, as that seemed to be what the ported application was set to use.

How to use

(These notes assume you're running in Windows)

• Put DBTest.java in a folder for your test (e.g. c:\temp\).
• Place sqljdbc4.jar in the same folder (e.g. c:\temp\). Note: you can use other jar files so long as they contain a definition for SQLServerXADataSource that's compatible with the above code; I've only tested with the former though.
• Open a command prompt (start, run, cmd, ok)
• Ensure java and javac are available under a directory your path environment variable. A quick test for this is to run them / asking them to output their version; if you get version info, all's good; otherwise you'll see an error. javac -version. java -version.
• Navigate to the directory in which you placed the above files (e.g. pushd c:\temp\)
• Execute this one liner, amending the passed connection string to use an appropriate value for your test: javac -cp sqljdbc4.jar DBTest.java && echo "********************************" && java -cp .;sqljdbc4.jar DBTest "jdbc:sqlserver://myDatabaseServer:myPort;DatabaseName=myDatabaseCatalog;ApplicationName=DBTest;LoginTimeout=300;user=mySqlUsername;password=mySqlPassword"

Notes

The javac/java line above I've done as a one liner, though there's 2/3 statements. This is so that it's easy to amend the class (if recoding's required for your test's use case) before recompiling and rerunning just by hitting the up arrow to pull back the last run command.

The -cp tells java where to find the dependencies; for the compiler we just need to specify he sqljdbc4.jar file; whilst when running we need to include the DBTest file, so include . also.

The && statements separate each command, ensuring that those to the right are only run if those to the left succeed.

The echo statement is just there to make it simpler to tell whether any errors come from the compilation (before the *****s) or the execution (after).

Note: some versions of java allow you to execute the .java file directly, without compilation. However, the above is still compatible with these newer versions, whilst having the advantage that it's older versions. Additionally, it's clearer where issues are occurring when compilation is a separate step to execution.

Full text from this gist's description (as truncated above):

We have a java based system which uses the SqlServerXADataTable class from sqljdbc4.jar to connect to SQL Server. Whilst migrating this app to a new server I got the error Connection test for 'MyDatabaseName' FAILED: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server returned an incomplete response. The connection has been closed.". I spent ages hunting down possible TLS issues, before resorting to knocking up a test script so I could see what Java was actually doing / get a more helpful error. Eventually it transpired that a hyphen in the DB catalog name in the connection string had an em-dash where it should have had a hyphen :/. The script that helped me can be ound below though, in the hopes it helps others.

DBTest.java

import com.microsoft.sqlserver.jdbc.*;
import java.sql.*;

public class DBTest {

    public static void main(String[] args) {
        if (args.length != 1) {
            System.out.println("Please provide a connection string as an argument when calling this method; exactly 1 argument expected; received: " + args.length);
            return;
        }
        String connectionString = args[0];

        try {
            SQLServerXADataSource ds = new SQLServerXADataSource();
            ds.setURL(connectionString);
            System.out.println("##### Starting JDBC connection test with connection string: [" + connectionString + "]");

            Connection connection = ds.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT 'Command executed successfully!'");
            if (resultSet.next()) {
                System.out.println("Connection established!");
                System.out.println("You have successfully logged on: " + resultSet.getString(1));
            }
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("##### End of test");
    }
}