How to Resolve JDBC Connection Errors to SQL Server 2012

Why am I encountering a TCP/IP connection error when trying to connect my Java application to SQL Server 2012?

When connecting your Java application to SQL Server 2012 using JDBC, you may encounter an error indicating that the TCP/IP connection to the specified host has failed. This commonly occurs when either the SQL Server is not running, the connection properties are incorrect, or network issues such as firewalls blocking the connection are present. In this guide, we will explore the steps to diagnose and resolve this connection issue.

// Connect to SQL Server 2012 using JDBC
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=aysha;user=sa;password=admin;";
Connection con = DriverManager.getConnection(url); 
Statement stmt = con.createStatement(); 
String sql = "SELECT * FROM employee";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    System.out.println(rs.getInt(1));
}

Causes

• SQL Server is not running on the specified host.
• Incorrect JDBC connection string properties (URL, username, or password).
• The SQL Server instance is not configured to accept TCP/IP connections.
• The specified port (default is 1433) is blocked by a firewall.

Solutions

• Ensure SQL Server is running. You can check this using SQL Server Management Studio or by running 'services.msc' on the host machine.
• Verify the connection string format. For SQL Server 2012, use: `jdbc:sqlserver://127.0.0.1:1433;databaseName=yourDatabase;user=yourUser;password=yourPassword;`
• Check SQL Server Configuration Manager to ensure TCP/IP protocol is enabled under SQL Server Network Configuration.
• Examine firewall settings to ensure that inbound and outbound connections on port 1433 are allowed.

Mistake: Using the wrong IP address or hostname in the connection string.

Solution: Always use '127.0.0.1' or 'localhost' if connecting to a local SQL Server instance.

Mistake: Forgetting to include the correct port number in the connection string.

Solution: Default SQL Server port is 1433, ensure it is specified in the format: `jdbc:sqlserver://127.0.0.1:1433;...`.

Mistake: Not enabling TCP/IP protocol in SQL Server Configuration.

Solution: Open SQL Server Configuration Manager, navigate to SQL Server Network Configuration, and ensure TCP/IP is enabled.

Mistake: Incorrectly formatted JDBC URL.

Solution: Follow the correct JDBC URL format: `jdbc:sqlserver://host:port;databaseName=dbName;user=userName;password=password;`.