Archive

Archive for April, 2012

Connecting to Ms SQL Server 2005 using JDBC in Windows Authentication Mode

April 16, 2012 2 comments

Hi All! There are 2 modes of connecting to the Ms SQL Server
1) Windows Authentication Mode
2) SqlServer Authentication Mode

I will be discussing the Windows authentication mode here. Actually i was first using SqlServer authentication mode but that wasn’t required at the deployment site so had to work with Windows Authentication. I am discussing only from implementation point of view.

First you have to download the SqlJdbc 4.0 jar libraries (google it and you’ll find it from Microsoft). You are concerned with following two files in that downloaded SqlJdbc 4.0 folder:

a) sqljdbc4.jar
b) sqljdbc_auth.dll (please note that you will find two sqljdbc_auth.dlls for x86 and x64 so carefully choose according to your platform)

Add both of these in your class path. An important thing is here that if you are using an IDE (like eclipse which i have used here) it may not provide you with an option to add a dll to the path so a Simple Solution for that is to place the dll (sqljdbc_auth.dll) in the your JRE’s bin (i placed it at C:\Program Files\Java\jre6\bin).

Now when the configurations are done lets come to the code.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class SqlServerConnect {

	/**
	 * @author Hassan Ali
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */

	public SqlServerConnect() throws ClassNotFoundException, SQLException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		Connection connection = DriverManager
				.getConnection("jdbc:sqlserver://localhost:1433;database=BioStar;integratedSecurity=true;");
		System.out
				.println("Connected to MS SQL 2005 using windows authentication");
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		new SqlServerConnect();
	}

}

having this working example now i will explain it a bit, i’ll highlight the important points:

a) The Micrsoft JDBC driver for MS SQL server is: com.microsoft.sqlserver.jdbc.SQLServerDriver so loaded it using Class.formName()
b) The Connection String for connecting to MS Sql Server using Windows Authentication Mode is: jdbc:sqlserver://localhost:1433;database=BioStar;integratedSecurity=true;

(Note that using windows authentication you have to give no username or password simply have to mention this additional integratedSecurity=true.)

Simply copying this connection string and using it in you application as far as you change the “database=BioStar” to “database=yourDbName” would be working.
In windows authentication you don’t need to mention user name and password for connecting is because you are connecting as a windows user to your MS Sql Server instance.

I only tried to mention some important points relating the connection string and sqljdbc_auth.dll etc things because people (i too) get more confused and irritated on such issues. Any questions or comments are always welcomed. Cheers 🙂

Categories: Java