Monday, January 5, 2015

JAVA - Connecting to SQL Server database using Windows Authentication or Active Directory

When you are trying to establish connection to SQL Server database using windows authentication or Active Directory (which users were not created in database by DBA via normal/traditional ways), you might get one of these errors:
  • Cannot establish a connection to jdbc:sqlserver://localhost:1433;databaseName=[databaseName] using com.microsoft.sqlserver.jdbc.SQLServerDriver (Login failed for user '[Windows User]'. ClientConnectionId:ae9be66b-830a-45a9-9317-5806e13167ba)
  • Cannot establish a connection to jdbc:sqlserver://localhost:1433;databaseName=[databaseName];integratedSecurity=true using com.microsoft.sqlserver.jdbc.SQLServerDriver (Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.)
  • Cannot establish a connection to jdbc:sqlserver://localhost:1433;databaseName=[databaseName] using com.microsoft.sqlserver.jdbc.SQLServerDriver (This driver is not configured for integrated authentication. ClientConnectionId:b595d819-4588-4003-b9bb-34da21984a1b)
  • Cannot establish a connection to jdbc:sqlserver://localhost:1433;databaseName=[databaseName] using com.microsoft.sqlserver.jdbc.SQLServerDriver (Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.)
So, how to resolved? There are many solutions/discussion which will drag you to endless issues/discussion.. but there are good site sharing possible solutions too. Here is the steps you need to do/check to avoid lengthy discussion and searching :)

Steps:

  1. Download SQLJDBC driver from Microsoft.
  2. Install the driver to any locations that you wish to used. Please use the easiest and simplified location/path (if you had it done, just skip this)
  3. Declare your environment variable (click to enlarge)
  4. You need to declare two variable which pointed to installed path of your JDBC. Eg:
    1. Variable Name: SQLJDBC_HOME
      Variable value: D:\lib\JDBC4.1\enu (where your sqljdbc4.jar exist)
    2. Variable Name: SQLJDBC_AUTH_HOME
      Variable value: D\lib\JDBC4.1\enu\auth\x86 (if you are running 32bit OS) or D\lib\JDBC4.1\enu\auth\x64 (if you are running 64bit OS). This is where your sqljdbc_auth.dll located.
  5. Copy sqljdbc_auth.dll to folder where your JDK/JRE is running. You may copy to lib folder, bin folder, etc. I copied to following folder:
    • D:\[JDK_INSTALLED_PATH]\bin
    • D:\[JDK_INSTALLED_PATH]\jre\bin
    • D:\[JDK_INSTALLED_PATH]\jre\lib
    • D:\[JDK_INSTALLED_PATH]\lib
  6. Then, in your source code, you may add integratedSecurity=true as part of the parameter like below:
    • jdbc:sqlserver://[DB_URL]:[DB_PORT];databaseName=[DB_NAME];integratedSecurity=true;
  7. The next steps is to ensure that in your jdbc library folder, you only have SQLJDBC4.jar. Please remove other sqljdbc*.jar file from that folder (or copy to other folder). If you are adding the driver as part of your program, please ensure that you add only SQLJDBC4.jar as driver to use.
If you use Netbeans.. follow steps below after complete the above steps:
  1. Add new connections
  2. Add new Driver
  3. Then, click on Add button. At pop-up window, find your sqljdbc4.jar, click the file name and click Open button.
  4. You shall get screen as below. Rename the driver as you wish.
  5. Then, at the screen before, choose the newly created driver and click Next button.
  6. Fill in the necessary information: Host, Port, Database, Windows Username, Windows Password.
  7. Don't forget to add integratedSecurity at Connection Properties with value true
Test your connection and you shall have connection success/established.

Have a enjoyable coding :)

0 comments :

Share It

Gadget

This content is not yet available over encrypted connections.

Popular Posts