How to connect SQL Server Using Authentication in Spring based app.

Recently I had to update my Spring based app config to use Windows Authentication for connecting SQL Server Database. Previously I had datasource bean in my applicationContext as follows:

   <bean id="dataSource"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
	<property name="url" value="jdbc:sqlserver://host:port;DatabaseName=dbName;" />
	<property name="username" value="username" />
	<property name="password" value="password" />
    </bean>

As you can see this is SQL Authentication and its using Microsoft JDBC type 4 Driver verison 4.0. You can download the jdbc dependency from here.

There are two ways to do Windows Authentications: Native way through the integrated Security which will works only on Windows OS. Other way is using Kerberos protocol, a network-based authentication where you could supply the username/password. Here is the link you can checkout to learn more. I used the first way since my app was running on windows and its quickest and easiest way to do. Here are the steps:

• add integratedSecurity=true property on above configuration and remove username/password as they are not needed.

<bean id="dataSource"	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
    <property name="url" value="jdbc:sqlserver://host:port;DatabaseName=dbName;integratedSecurity=true" />
 </bean>

• find sqljdbc_auth.dll file from microsoft jdbc driver installation folder, one you installed previously from the jdbc driver link. The path would be something like: <installation directory>\sqljdbc_<version>\<language>\auth\
• copy sqljdbc_auth.dll file to your JAVA_HOME/bin folder.
• finally start your application/webservice/appserver( like tomcat) using windows credentials that you wanted to used to connect SQL Sever. Now you can inject your datasource anyway you need it to connect the db. That should do it!!