Connecting with hibernate to a Microsoft SQL 2008 server

Question

I am trying to connect to a Microsoft SQL 2008 server via hibernate. The following is my hibernate.cfg.xml file:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
        <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="connection.url">jdbc:sqlserver://127.0.0.1:1433;databaseName=myDBName;instanceName=myInstanceName;</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.connection.password">pass</property>

        <mapping resource="Obj.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

And here is the code I use to try and establish an connection and do a query :

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class SessionsTest {
    private static SessionFactory sessionFactory;
    private static ServiceRegistry serviceRegistry;

    /**
     * @param args
     */
    @SuppressWarnings({ "unchecked"})
    public static void main(String[] args) {
        sessionFactory = configureSessionFactory();

        Session session = sessionFactory.openSession();
        session.beginTransaction();

        List<Obj> result = (List<Obj>) session.createQuery("FROM Obj").list();

        for (Obj obj : result ) {
            System.out.println(obj.getObjID());
        }

        session.getTransaction().commit();
        session.close();

        if ( sessionFactory != null ) {
            sessionFactory.close();
        }
    }

    private static SessionFactory configureSessionFactory() throws HibernateException {
        Configuration config = new Configuration();
        configuration.configure();
        serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();            
        sessionFactory = config.buildSessionFactory(serviceRegistry);
        return sessionFactory;
    }
}

The stacktrace I get :

2013-04-13 15:02:03,449 [main] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08S01
2013-04-13 15:02:03,449 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1425)
    at com.test.test.ObjTest.main(ObjTest.java:24)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:204)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
    ... 5 more

I have tried using a different driver(JTDS). I have tried changing the URL string in various ways. I have tried changing my dialect to org.hibernate.dialect.SQLServerDialect. I also tried using windows authentication at one point by adding ;IntegratedSecurity=true to the end of the url string. Other than this I have been poking around in the server properties to make sure the the instance I am providing is correct aswell as the port.I have tried : telnet localhost 1433 and can't connect that way,but I can connect using SQL Server Management Studio. Further I used NetStat -o in cmd and TaskList /FI "PID eq 4072" /FO LIST /V to try and track down the sql server to confirm the port aswell.The weird thing is I couldn't track down sql server this way.It does not turn up in the NetStat list,but if I use the PID of the server directly it does show details about it except the Status is Unknown,Session# is 0 and User Name is N/A.

I use Hibernate 4.2.0 and SQLJDBC4,when I used JTDS it was 1.2.7. The output of java -version : java version "1.6.0_30" Java(TM) SE Runtime Environment (build 1.6.0_30-b12) Java HotSpot(TM) 64-Bit Server VM (build 20.5-b03, mixed mode)

Please tell me if any other info is needed,first time posting here.

Answer 1

Really, really, really check if the TCP/IP protocol is enabled in your SQL Server instance.

Follow these steps (tested for SS2012) to make sure:

• Open "Sql Server Configuration Manager" in "Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools\"
• Expand "SQL Server Network Configuration"
• Go in "Protocols for <YourInstance>"
• Enable TCP/IP

If you have any problem, check this blog post for details, as it contains screenshots and much more info.

Also check if the "SQL Server Browser" windows service is activated and running:

• Go to Control Panel -> Administrative Tools -> Services
• Open "SQL Server Browser" service and enable it (make it manual or automatic, depends on your needs)
• Start it.

That's it.

After I installed a fresh local SQL Server, all I had to do was to enable TCP/IP and start the SQL Server Browser service.

Below a code I use to test the connection to a SQLEXPRESS local instance. Of course, you should change the IP, DatabaseName and user/password as needed.:

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

public class JtdsSqlExpressInstanceConnect {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        String url = "jdbc:jtds:sqlserver://127.0.0.1;instance=SQLEXPRESS;DatabaseName=master";
        String driver = "net.sourceforge.jtds.jdbc.Driver";
        String userName = "user";
        String password = "password";
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Connected to the database!!! Getting table list...");
            DatabaseMetaData dbm = conn.getMetaData();
            rs = dbm.getTables(null, null, "%", new String[] { "TABLE" });
            while (rs.next()) { System.out.println(rs.getString("TABLE_NAME")); }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            rs.close();
        }
    }
}

And if you use Maven, add this to your pom.xml:

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.2.4</version>
</dependency>

Answer 2

I think your SQL Server instance is not serving at 1433 for TCP connections. To identify the port:

• Go to SQL Server configuration manager.
• Choose SQL Server Network Configuration
  • Go for Protocols for Your Instance
  • Click on TCP IP (Enable it if not enabled, then clients could connect with TCP/IP)
  • On the popup which results, Choose IP Addresses Tab
  • Scroll Down
  • You'll see TCP Dynamic ports in Section IPAll
  • Grab that value and that's the port you should be using