Connect to SQL Server using Windows Authentication

Introduction

When you are trying to establish a 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.)

What is the easiest or simple solution to this? There are many solutions/discussions which will drag you to an endless issues/discussion. But there are good site sharing possible solutions too. However, to ease you, here are 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 use. 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 variables which point to the installed path of your JDBC. Example:
   1. Variable Name: SQLJDBC_HOME

      Variable value: D:\lib\JDBC4.1\enu (where your sqljdbc4.jar exists)

   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 is 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 the following folder:
   • D:\[JDK_INSTALLED_PATH]\bin
   • D:\[JDK_INSTALLED_PATH]\jre\bin
   • D:\[JDK_INSTALLED_PATH]\jre\lib
   • D:\[JDK_INSTALLED_PATH]\lib

     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;

     The next step 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 the steps below after completing 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.

Enjoy coding! :)