Thursday, August 3, 2017

Verification of SQL Server JDBC integrated authentication with sample script

SET JAVA_HOME="C:\Program Files\Java\jdk1.8.0_131"

SET CLASSPATH=.;C:\temp\sqljdbc_6.2\enu\mssql-jdbc-6.2.1.jre8.jar


C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\javac connectURL.java

C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\java connectURL
Aug 04, 2017 9:48:25 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:9f25a766-3663-4bc5-b68c-19a551cbcd20
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2435)
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:75)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3129)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:270)
        at connectURL.main(connectURL.java:43)
Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867)
        at java.lang.Runtime.loadLibrary0(Runtime.java:870)
        at java.lang.System.loadLibrary(System.java:1122)
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:50)
        ... 13 more


C:\temp\sqljdbc_6.2\enu\samples\connections>dir "C:\temp\sqljdbc_6.2\enu\auth\x6
4"
 Volume in drive C has no label.
 Volume Serial Number is D0AD-1A2D

 Directory of C:\temp\sqljdbc_6.2\enu\auth\x64

08/04/2017  09:38 AM             .

08/04/2017  09:38 AM             ..

07/14/2017  11:41 AM           310,480 sqljdbc_auth.dll
               1 File(s)        310,480 bytes
               2 Dir(s)  58,810,060,800 bytes free

C:\temp\sqljdbc_6.2\enu\samples\connections>SET PATH=%PATH%;C:\temp\sqljdbc_6\enu\auth\x64

C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\java connectURL
1 Accounting Manager
2 Assistant Sales Agent
3 Assistant Sales Representative
4 Coordinator Foreign Markets
5 Export Administrator
6 International Marketing Manager
7 Marketing Assistant
8 Marketing Manager
9 Marketing Representative
10 Order Administrator




=========================== connectURL.java ===========================
import java.sql.*;

public class connectURL {

public static void main(String[] args) {

// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://WIN-L2D9O5BHNHA:2433;" +
"databaseName=AdventureWorks2012;integratedSecurity=true;";

/* 
// Below connection string using instance name for named instance, required Browser
// Service up and running
String connectionUrl = "jdbc:sqlserver://WIN-L2D9O5BHNHA;" +
"instanceName=PROD;databaseName=AdventureWorks2012;integratedSecurity=true;";
*/

// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

        try {
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionUrl);
            
            // Create and execute an SQL statement that returns some data.
            String SQL = "SELECT TOP 10 * FROM Person.ContactType";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            
            // Iterate through the data in the result set and display it.
            while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getString(2));
            }
        }
        
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}

finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}