Create a keytab file on Windows and use Java and Kerberos to connect to SQL SERVER

I currently work in a mixed environment containing box Linux and Windows computers. This can make authentication at times challenging. Below is an example java program which allows you to connect using kerberos to a SQL SERVER from a Windows or Linux client. The process involves creating a keytab file and a java login context file. This keytab file can be used to authenticate to windows resources like SQL SERVER and file servers using Java. The keytab file stores your username and password in an encrypted format.

STEP 1. Create a keytab file


cd "C:\Program Files\Java\jdk1.8.0_31\bin"
ktab.exe -a user01@LAB.NET P@ssword -k user01.keytab

STEP 2. You reference the keytab in your java login conf file.

java-login {
 com.sun.security.auth.module.Krb5LoginModule required
 useTicketCache=false
 doNotPrompt=true
 useKeyTab=true
 debug=true
 keyTab="c:/eclipse/user01.keytab"
 principal="user01@LAB.NET"
 };

STEP 3. The Java code then references the java login configuration file, then you make the SQL SERVER connection using the subject user01@LAB.NET.

import java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import javax.security.auth.login.LoginException;


public class main {


        public static void main(String[] args) {

                System.setProperty("java.security.auth.login.config", "c:/eclipse/java-login.conf");

                Subject subject = null;



                try {
                    LoginContext loginContext = new LoginContext("java-login");
                    loginContext.login();
                    subject = loginContext.getSubject();

                }
                catch (LoginException e)
                {
                    e.printStackTrace();
                }


        //  This application passes the javax.security.auth.Subject
        //  to the driver by executing the driver code as the subject
try {
                @SuppressWarnings("unchecked")
                Connection con = (Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {

                                    public Object run() {

                                        Connection con = null;
                                    try {
                                         //
                                         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                                         String url = "jdbc:sqlserver://SQLSERVER.lab.net;instanceName=sqlinstance01;database=Accounting;integratedSecurity=true;authenticationScheme=JavaKerberos";
                                         con = java.sql.DriverManager.getConnection(url);
                                        }
                                     catch (Exception except) {
                                                except.printStackTrace();
                                     //log the connection error
                                           return null;
                                        }

                                        return con;
                                    }
                });


                String SQL = "select * from dbo.table01";
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(SQL);

                while (rs.next())
                {
                        System.out.println(rs.getString(1));
                }
}
catch (Exception e)     {
        e.printStackTrace();
}



        } // end of method main



} // end of class main

Enable auditing for Windows Firewall

Recently when troubleshooting a new IIS application deployment, I realized how helpful the windows firewall auditing feature is. The IIS application was having difficulty connecting to SQL Server. I had allowed outbound connecting to the SQL Server from IIS. However, after enabling the Windows auditing on packet filtering I discovered that connections back to the IIS server on port 1434 were being blocking.

windowsfirewallaudit

Example of a failure audit
failureaudit