SQLException error when connecting with java

问题:

I have been facing a simple sql connection error, and i m tired of fixing it. I installed sql server 2014 and then sql server 2017, add rules to firewall and even turned off the firewall. services restarted multiple times. Went to configuration manager, enabled everything, added all the jar required, done clean and build, done all searches on this issue in google, still that same issue comes in again and again. System used are - Windows 10. - Sql server management studio 2017. - Netbean 8.1

code :-

    import java.sql.*;
    public class DbConnect  { 
    public static void main(String[] args) throws SQLException,ClassNotFoundException {
    String url = "jdbc:sqlserver://localhost:1433;databaseName=productlist;user=db2017;password=db2017";

//commented :-String url = "jdbc:sqlserver://DESKTOP-7CI6DU0\\NIT2017:1433;databaseName=productlist;user=db2017;password=db2017";

                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
                Connection conn = DriverManager.getConnection(url);
                System.out.println("test");
                Statement sta = conn.createStatement();
                String Sql = "select * from productlist";
                ResultSet rs = sta.executeQuery(Sql);
                while (rs.next()) {
                System.out.println(rs.getString("CatName"));                 
        }  }  }

Below is exception error comes again again, after so much troubleshooting.

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, 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 java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at DbConnect.main(DbConnect.java:11)
C:\Users\Nitish\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1

Before giving up, thought to check here...

回答1:

Your commented-out connection string suggests you are connecting to a named instance. A default SQL Server instance (not-named) listens on port 1433 unless configured otherwise. A named instance uses a dynamic port, which is not 1433, unless configured otherwise. One can have only one default instance per machine and every SQL instance must listen on a different port.

You can identify the port a SQL instance is listening on using SQL Server Configuration Manager or by viewing the SQL Server error log. The error log will include messages for each interface and port SQL Server is listening on. The message will be like "Server is listening on [ 'any' 1433]." The port can be changed using SSCM and will be effective after restarting the SQL Service.

To test port connectivity, you can use TELNET:

TELNET YourServer 1433

You'll see and empty window if the connection is successful, otherwise and error.

If you don't have TELNET installed, you can test the port connectivity with this Powershell command from a command-prompt window:

powershell -Command echo ((new-object Net.Sockets.TcpClient).Client.Connect('YourServer', 1433)) 'success'

You'll see the success message upon successful connection, otherwise a socket exception.

Note that the dynamic port is assigned during installation and the instance will attempt to use the same port upon each startup. However, be aware the port number could change if that port is unavailable at startup. One can configure a static port (including port 1433) using SSCM to avoid the port number changes of a named instance.