Friday, September 02, 2011

TCP/IP connection to the host 127.0.0.1, port 1433 has failed

I encountered this annoying error today when attempting to make a JDBC connection to MS SQL Server 2005 Express Edition (SP 4).

Exception: Could not create connection; - nested throwable: (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.".); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (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.".))

The problem was that TCP/IP connections were not enabled. To fix this, open SQL Server Configuration Manager, select SQL Server 2005 Network Configuration (32bit), select Protocols for SQLEXPRESS, mark TCP/IP as Enabled. Also, restart MS SQL Server to ensure the changes take effect.

For future reference, MS SQL JDBC connection strings look like this: jdbc:sqlserver://host\instanceName;DatabaseName=TheDbName - optionally include username and password with jdbc:sqlserver://host\instanceName;DatabaseName=TheDbName;user=xxx;password=xxx. You can find out host and instance name through Microsoft SQL Server Management Studio Express: right click on the DB and select Properties.

Also, MS SQL Server runs on port 1433 by default, but the connection string doesn't specify this. Why?