Java/JDBC – SQL Server – “Integrated Security”

Background

Wanted to go over the steps to take when one needs to connect to a Microsoft SQL Server Instance using Integrated Security.

Outline

  1. Java
    • JDBC
      • JDBC Connecting String
    • Class Path
      • Includes SQL Server JDBC Driver
  2.  System
    • System Path
      • Add [jdbc-sql]\enu\auth\[platform]\

Tasks

Java

JDBC

JDBC Connecting String

Outline
  1. JDBC URL
    • IntegratedSecurity=True
    • authenticationScheme=NativeAuthentication
Syntax

jdbc:sqlserver://{server-name}:{port};IntegratedSecurity=True;authenticationScheme=NativeAuthentication;databaseName={database};

Sample

jdbc:sqlserver://localhost:1433;IntegratedSecurity=True;authenticationScheme=NativeAuthentication;databaseName=sakila;

java Class Path

SQL Server JDBC Driver

Please include your jdbc driver as part of your classpath when you launch your app.

In our case, %_sqlServerJDBCPath%\ sqljdbc_7.0\enu\mssql-jdbc-7.0.0.jre8.jar

 

System Path

Enhance your system path to include the sqljdbc_[version]\enu\auth\x64\ folder.

We are on SQL JDBC Version 7 and our platform is 64-bit.

Our relative path is sqljdbc_7.0\enu\auth\x64.

Code

Sample


set "_libSQLServerAuth_Folder=C:\downloads\Microsoft\SQLServer\jdbc\v7.0.0.0\extract\sqljdbc_7.0\enu\auth\x64\"

set path=%path%;%_libSQLServerAuth_Folder%

Trouble Shooting

Here are a couple of areas that you may stumble upon.

WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path

Error

Error Image

Error Text


WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path: [C:\Program Files\Java\jdk-11.0.3\bin,
[WARN ] 2019-06-10 12:57:02.390 [main] JdbcEnvironmentInitiator - HHH000342: Could not obtain connection to query metadata : null
[INFO ] 2019-06-10 12:57:02.424 [main] Dialect - HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect
[WARN ] 2019-06-10 12:57:02.502 [main] SqlExceptionHelper - SQL Error: 0, SQLState: 08S01
[ERROR] 2019-06-10 12:57:02.509 [main] SqlExceptionHelper - This driver is not configured for integrated authentication.
Exception :- Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]

Error Explanation

  1. WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path

Resolution

  1. java.library.path
    • WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
  2. Please include the folder where sqljdbc_auth is located in your system path.

 

Screen Shots

Hibernate

Working

Image -01

References

  1. Microsoft
    • Docs
      • Docs / SQL / Connect / JDBC / Programming Guide for JDBC SQL Driver / Guide articles
        • Building the Connection URL
          Link
    • CSS SQL Server Engineers
      • Adam W. Saxton
        • JDBC: This driver is not configured for integrated authentication
          Link

 

Hibernate / DB Metadata

Background

In the early stages of learning Hibernate, I wanted to get some metadata about my database.

But, was lost as to how to do so.

Outline

  1. Connect to Database
  2. Get Session
    • Cast Session to sessionImpl
    • Get Connection from sessionImpl
    • Get metadata from Connection
      • Access metadata
        • getURL
        • getDriverName
        • getDatabaseProductName
        • getDatabaseProductVersion
        • getUserName
        • getDefaultTransactionIsolation

Code

void getDBMetadata(Session session)
    throws Exception
    {

        SessionImpl sessionImpl;
        Connection conn;
        DatabaseMetaData dbMetadata;

        int iDefaultTransactionIsolation;
        String strDefaultTransactionIsolation = null;

        //get Session Implementation
        sessionImpl = (SessionImpl) session;

        //Get Session Implementation Connection
        conn = sessionImpl.connection();

        //Get Metadata
        dbMetadata = conn.getMetaData();

        System.out.println
        (
              "Database URL :- "
            + dbMetadata.getURL()
        );

        System.out.println
            (
                  "Driver Name :- "
                + dbMetadata.getDriverName()
            );

        System.out.println
        ("Database Product Name :- "
            + dbMetadata.getDatabaseProductName()
        );

        System.out.println
            (
                "Database Product Version :- "
                        + dbMetadata.getDatabaseProductVersion()
            );

        System.out.println
        (
            "Database Username:- "
                + dbMetadata.getUserName()
        );          

        iDefaultTransactionIsolation
        	= dbMetadata.getDefaultTransactionIsolation();

        strDefaultTransactionIsolation
        	= getDefaultTransactionIsolationAsString
        	  (
    			  iDefaultTransactionIsolation
			  );

        System.out.println
        (
            "Default Isolation Level ( as int ):- "
                    + dbMetadata.getDefaultTransactionIsolation()
        );  

        if (strDefaultTransactionIsolation != null)
        {

	        System.out.println
	        (
	            "Default Isolation Level ( as String ) :- "
	                    + strDefaultTransactionIsolation
	        );
        }

    }

 

    String getDefaultTransactionIsolationAsString(int value)
    {

    	String strValue = null;

    	switch(value)
    	{
    	   case java.sql.Connection.TRANSACTION_NONE:
    		  strValue = "None";
    	      break; 

    	   case java.sql.Connection.TRANSACTION_READ_COMMITTED :
    		  strValue = "Read Committed";
    	      break; 

    	   case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED :
     		  strValue = "Read UnCommitted";
     	      break; 

    	   case java.sql.Connection.TRANSACTION_REPEATABLE_READ :
      		  strValue = "Repeatable Read";
      	      break; 

    	   case java.sql.Connection.TRANSACTION_SERIALIZABLE :
       		  strValue = "Serializable";
       	      break; 

    	   default :
    	      strValue = null;
    	}

    	return (strValue);

    }

Output

Output – Text


Database URL :- jdbc:sqlserver://localhost:1433;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=sakila;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
Driver Name :- Microsoft JDBC Driver 7.0 for SQL Server
Database Product Name :- Microsoft SQL Server
Database Product Version :- 14.00.3045
Database Username:- sakila
Default Isolation Level ( as int ):- 2
Default Isolation Level ( as String ) :- Read Committed

Output – Image

 

Source Code Control

GitHub

DanielAdeniji/HibernateDBMetadata
Link

 

References

  1. Oracle.com
    • java.sql
      • DatabaseMetaData
      • Connection
    • API
      • Constant Field Values
    • Sun Java System Application Server Platform Edition 8.2 Developer’s Guide
      • Using JDBC Transaction Isolation Levels
  2. jboss.org
    • org.hibernate
      • org.hibernate.impl