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
- Download SQLJDBC driver from Microsoft.
- 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).
- Declare your environment variable (click to enlarge):
- You need to declare two variables which point to the installed path of your JDBC. Example:
- Variable Name:
SQLJDBC_HOME
Variable value: D:\lib\JDBC4.1\enu (where your sqljdbc4.jar exists)
- 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.
- 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:
- Add new connections:
- Add new Driver:
- Then, click on Add button. At pop-up window, find your sqljdbc4.jar, click the file name and click Open button.
- You shall get screen as below. Rename the driver as you wish.
- Then, at the screen before, choose the newly created driver and click Next button.
- Fill in the necessary information: Host, Port, Database, Windows Username, Windows Password.
- Don't forget to add
integratedSecurity
at Connection Properties with value true
.
Test your connection and you shall have connection success/established.
Enjoy coding! :)
I’m a UiTM graduated with major in multimedia (B.Sc (hons) IT) in 1999. Prior to that, I received my Diploma in Computer Sciences (major in software engineering) from the same university in 1994. Currently, I’m pursuing Master degree in Software Security and waiting for my viva session.
In terms of experience, I’ve more than 14 years of challenging experience. I started as lecturer in IT and software engineering at Stamford College. 6 months later, to improve my skill and knowledge, I joined PKDP as MIS executives responsible on support and maintenance of logistics application running on Informix 4GL. Since then, I've joined few others company such as IB Technologies Sdn Bhd, Mesiniaga Berhad, MIMOS Berhad and Matrix Power Network Sdn Bhd.
I gain lots of experience developing various types of applications using various tools and programming language. Beside application development, I had the opportunity to become project manager and also lead a team for a few months due to departure of my department head to another cluster. In addition, I’ve serve as member of IP mining and panel in IP Disclosure part of the task is to improve an IP or evaluate the feasibility of an IP. On top of that, I’ve been presented in few security and software development conferences and published papers locally and international.