Connecting to SQL Server Express through JDBC

I came across this question in a decent number of forums where the subtleties of connecting to an instance of SQL Server Express using JDBC are missed out. So, thought it would be good to just document how I approached it, here for my reference and for others, if they find useful.
Once you install the SQL Server Express edition, you need to configure the TCP/IP settings for the JDBC drivers to connect to the SQLEXPRESS database instance.

Configuring the TCP/IP:

1. Configure TCP/IP communication with SQL Express
1. Open SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration -> Protocols for SQLEXPRESS
3. Set the status of TCP/IP protocol to "Enabled" (if it is already not).
4. Open Properties window for TCP/IP, go to IP Addresses section.
5. Go to the bottom of this property page and set the TCP Port under `IPAll` to 1433.
2. Connect to the SQLExpress instance using `Microsoft's JDBC driver for SQL Server`
1. JDBC URL: `jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=<your DB>;user=<your User>;password=<your Passwd>`

Using Windows Integrated Authentication:

1. Create a windows account for the application that would be used to run your programs. This account's credentials will be used to connect to the server.
2. Get Microsoft JDBC Driver for SQL Server from here.
3. Configure the JDBC URL as follows:
   
   jdbc:sqlserver://<hostname>;databaseName=<DBName>;integratedSecurity=true
4. Configure the launcher that run the Java programs from command line to include the following JVM parameter:
   
   -Djava.library.path="<jdbc driver dll location>"
   
   where the location is the directory where the JDBC driver downloaded earlier is installed or extracted. It was C:\Program Files\sqljdbc_4.0.2206.100_enu\sqljdbc_4.0\enu\auth\x64 in my case. The dll should be picked based on the JVM used for running these programs.

With the above configuration, the connection established to SQL Server would use the Windows Authentication Credentials of the domain user running the java program/process.

Sample Code:

 

public class SQLServerEx
{
    static enum Drivers
    {
        MICROSOFT(
                  SQLServerDriver.class.getName(),
                  "jdbc:sqlserver://"
                      + "localhost;"
                      + "instance=SQLEXPRESS;databaseName=vikdor_test;user=vikdor_user;password=vikdor_user"),
        MICROSOFT_INTEG(
                        SQLServerDriver.class.getName(),
                        "jdbc:sqlserver://"
                            + "localhost;"
                            + "instance=SQLEXPRESS;databaseName=vikdor_test;integratedSecurity=true");

        private String driverClass;
        private String driverURL;

        private Drivers(String driverClass, String driverURL)
        {
            this.driverClass = driverClass;
            this.driverURL = driverURL;
        }

        public String getDriverClass()
        {
            return driverClass;
        }

        public String getDriverURL()
        {
            return driverURL;
        }
    }

    public static void main(String[] args) throws Exception
    {
        Class.forName(Drivers.MICROSOFT_INTEG.getDriverClass());
        Connection con =
            DriverManager.getConnection(Drivers.MICROSOFT_INTEG.driverURL);
        PreparedStatement ps =
            con.prepareStatement(
                "insert into auto_table(name, role) values(?, ?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, "vikdor");
        ps.setString(2, "sde");
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        while (rs.next())
        {
            System.out.println(rs.getInt(1));
        }
        rs.close();
        ps.close();
        con.close();
    }
}