JDBC connection Url for SQLServer express R2

Question

I wrote the below line for connection with sql server express r2:

Connection con=DriverManager.getConnection("jdbc:sqlserver://localhost/SQLEXPRESS;databaseName=abc","sa","password");

It is giving exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost/SQLEXPRESS, port 1433 has failed. Error: "null. 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.".

I followed the line to solve the exception:

TCP/IP for SQL Server Express By default, TCP/IP for SQL Server Express is disabled, so JDBC cannot connect to it and you may get the following exception …

Network error IOException: Connection refused: connect Enable TCP/IP

To enable TCP/IP, start SQL Server Configuration Manager.

Expand SQL Server 2005 Network Configuration node. In the right pane, select Protocols for SQLEXPRESS. The right pane should now show Protocols and Status columns. Select Enable from the TCP/IP context menu. Find or Configure TCP/IP Port

After enabling TCP/IP, you have to find out which port number to use. SQL Server Express allocates a port dynamically each time it is started, so to find or configure the port number, continue using SQL Server Configuration Manager …

Select Properties from the TCP/IP context menu. The TCP/IP Properties dialog should open. Select the IP Addresses tab. In the IPAll node … The TCP Dynamic Ports field shows the currently used port number. If you set that field to blank, then SQL Server Express should not automatically choose another port when it restarts. Set the desired port number in the TCP Port field. Press OK to apply your settings and close the dialog.

After that also connection giving same exception.

Answer 1

Try running some Java code to test it. Replace {computer-name}\SQLEXPRESS with the domain that you see in the root node of SQL Server Management Studio. When installing SQL Express, you had the option to change the name of "SQLEXPRESS" to something custom, so that might also be different. To get the port number, go to Windows Start Menu > SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager > expand "SQL Server Network Configuration" > Click "Protocols for SQLEXPRESS" > right click "TCP/IP" and choose Properties > click "IP Addresses" tab/menu > in Windows XP, you will see "IPAll" .. look at the port there. It should default to 1433. If you have 2 version of SQL Express, then it might be different. Also make sure your service is running (has status of "Started") in Windows Control Panel > Administrative Tools > Services > SQL Server (SQLEXPRESS). Windows 7 is slightly different, but you'll find it.

The double backslashes need to be in a Java string. That just prints a single backslash during run time because it's an escape sequence.

import java.sql.*;

public class TestConnection
{
    public static void main(String[] args)
    {
        DB db = new DB();
        db.dbConnect("jdbc:sqlserver://{computer-name}\\SQLEXPRESS:1433;databaseName=abc;integratedSecurity=true;","sa","password");
    }
}

class DB
{
    public DB() {}

    public void dbConnect(String db_connect_string, String db_userid, String db_password)
    {
        try
        {
            Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password);
            System.out.println("connected");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
};

Answer 2

Try to use your local machine's IP address instead of localhost.

Answer 3

For SQL Server > 2005 you have to start also the Sql Server Browser service.

I've tested successfully JBoss 7 with Sql Server 2014 Express. OS: Windows 7. Settings I had to do for Sql Server:

1. Enabled the TCPIP option in: SQL Server Configuration Manager -> Sql Server Network Configuration -> Protocols for servername

2. Stopped the following 2 services and started them again:

   • Sql Server (servername)
   • Sql Server Browser

I've used no port and also the local machine name in the URL.