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

 

R – Error: package or namespace load failed for “rJava”

Background

Users opens up a ticket regarding issues running an R Script on a new machine.

Error Message

Image

errorloadingpackage.rjava.20190109.0251pm

Textual


Error: package or namespace load failed for "rJava":
 .onLoad failed in loadNamespace() for 'rJava', details:
  call: fun(libname, pkgname)
  error: No CurrentVersion entry in Software/JavaSoft registry!
  Try re-installing Java and make sure R and Java have matching architectures.

Explanation

Unable to load rJava module.

Troubleshooting

java

Is java Installed?

Outline

  1. OS Platform
    • OS :- Windows
      • GUI
        • Control Panel
      • Console
        • java.exe

OS Platform

MS Windows
GUI – Control Panel

Accessed Control Panel, but no Java Applet.controlpanel.20190109.0355pm

Console – java.exe

Syntax


java -version

Syntax – Check specifically for 64-bit


java -d64 -version

Output

Image

java.-d64.20190109.0407pm

Textual


>java -d64 -version
Unrecognized option: -d64
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

 

Remediation

java

Download Java Runtime Environment ( JRE)

Download 64-bit Java JRE from here :-

Java SE Runtime Environment 8 Downloads
Link

java.artifact.20190109.0412pm

Install Java

Install the download artifacts

Validate Java

Validate Java ( x64 )

Syntax

java -d64 -version

Output
Output – Before – Image

java.-d64.DoesNotSupportA64bitJVM.20190109.0419PM.PNG

Output – Before – Textual

Error : This Java instance does not support a 64-bit JVM.
Please install the desired version.

Output – After

java.-d64.Supports64bitJVM.20190109.0420PM.PNG

 

R

Access System

In R, issue call to system.

The call is to validate that the R Script can access the system and invoke the java executable.

System calls

System Call – java
Syntax

system('java -version")

Output
system.java.-version.20190109.0424pm

 

 

 

 

 

ElasticSearch (v6) – Installation ( on Windows )

Background

Trying to review ElasticSearch.

Our choices is to search the Net for Web Hosting Services that avail it or “Install it locally“.

Installation Material Choices

There are a couple of options for installing ElasticSearch on MS Windows.

ElasticSearch is packaged via zip and MSI.

If you will like to install to run it as a Windows Service and have it registered as a Windows Program, please choose the MSI route.

If you will to simply test things out, please proceed down the ZIP path, extract it, and run it from the command line, please choose the ZIP package.

Installation Material Location

The Install Materials are available here.

Installation Choice

We chose to go down the MSI Package.

Prerequisite

The lone Prerequisite is Java’s JDK.

Java’s JDK

Version

Our targeted ElasticSearch’s Version is 6.

That minimum supported version is JDK 9.  As JDK 10 is now available and supported by ES 6, we chose to go with JDK 10.

Installed Vejava_version_20180729_0123AM.PNGrsion

To determine the Version of Java JDK installed, please launch a command prompt and run java passing along the -version option.

Code


java -version

Output

java_version_20180729_1258AM.PNG

Explanation

We can see that java JDK is not installed.

Download Location

JDK v10 is available here.

installMaterial_20180729_0102AM

Installing

GUI

Welcome

welcome_20180729_0107AM.PNG

Options
Options – Before

optionalFeatures_20180729_0108AM.PNG

Options – After

Changed Install Location from C:\Program Files\Java\jdk-10.0.2\ to E:\Program Files\Java\jdk-10.0.2\

optionalFeatures_20180729_0109AM.PNG

Status
Status -1

status_20180729_0109AM.PNG

Status -2

successfullyInstalled_20180729_0120AM.PNG

Complete

status_20180729_0111AM.PNG

Validate Version

Let us go back and validate that we have the right Java Installed.

Code


java -version

Output

java_version_20180729_0123AM.PNG

Explanation

Confirmed that we Java JDK 10.0.2 installed.

Bitness is 64-Bit.

Install Elastic Search…

GUI

Locations

Locations – Original

locations_20180729_0124AM.PNG

Locations – Create Folder

We don’t want to target the system drive and thus will create folders on another drive; in our case drive E:


setlocal 

set "_base=E:\Program Files\Elastic\Elasticsearch\6.3.2"

if not exist "%_base%\data" mkdir "%_base%\data"

if not exist "%_base%\config" mkdir "%_base%\config"

if not exist "%_base%\logs" mkdir "%_base%\logs"

endlocal
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Locations – Revised

locations_20180729_0134AM.PNG

 

Services

Revised

Have the following:

  1. Account Information from Local System to Network Service
  2. Chose to have Service start upon completion of Installation
  3. Opted not to have Service automatically during OS Starts

Service_20180729_0135AM.PNG

Configuration

Original

Configuration_20180729_0136AM.PNG

Revised

Have the following:

  1. Changed Memory  from 2 GB to 256 MB
  2. Opted not to have “Lock JVM memory

Configuration_20180729_0137AM.PNG

 

Plugins

Original

plugins_20180729_0138AM.PNG

 

Revised

Chose to enable the following plugins:

  1. Ingest Attachment Processor
  2. Ingest GeoIP Processor
  3. Store SMB
Revised -01
  1. plugins_20180729_0147AM_A.PNG
Revised -02

plugins_20180729_0147AM_B.PNG

XPack

Chose to enable the following plugins:

  1. License :- BASIC

XPack_20180729_0148AM.PNG

 

Installing

Installing – 01

Installing_20180729_0149AM.PNG

 

Installing – “Downloading Plugins….”

Installing_Plugins_20180729_0151AM.PNG

Installing – “Downloading Plugins…store-smb”

Installing_Plugins_StoreSMB_20180729_0151AM.PNG

 

Start Elasticsearch Service

startingService_20180729_0152AM.PNG

 

Installed Successful

ElasticSearchInstalledSuccessfully_20180729_0153AM.PNG

Validation

Launch a browser and access the following URL http://localhost:9200

Image

browser_20180729_0154AM.PNG

Explanation

  1. Version
    • number :- 6.3.2
    • Build Date :- 2018-07-20
  2. Lucene Version
    • 7.3.1

 

References

  1. Elastic
    • Installation
      • Elasticsearch Reference [6.3] » Set up Elasticsearch » Installing Elasticsearch » Install Elasticsearch with Windows MSI Installer
        Link
      • Elasticsearch Reference [2.4] » Getting Started » Installation
        Link
    • Artifacts
      • Download ElasticSearch
        Link
    • Plugin
      • Store SMB
        • Store SMB Plugin
          Link
        • Elasticsearch Plugins and Integrations [6.3] » Store Plugins » Store SMB Plugin » Working around a bug in Windows SMB and Java on windows
          Link
  2. Apache.Org
    • Issues
      • Lucene – CoreLUCENE-6176
        • Modify FSIndexOutput in FSDirectory to open output steam for Write and Read
          Link