Not able to connect to localDB from java connection string

[satscreate]

Driver version

6.2.2.jre8

SQL Server version

Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 10.0.14393.0
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.14393.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.14393

Client Operating System

JAVA/JVM version

1.8.152

Table schema

Java code

package com.example.checkdb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBB {
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://(localDB)\\Test Instance:1433;databaseName=TestSQlCMD;user=DOMAIN\\name;password=passwrd";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
            String SQL = "select * from TestSQlCMD.dbo.Pesron";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Error

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host (localDB), port 1433 has failed. Error: "(localDB). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:284)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2435)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:635)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2010)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at com.example.checkdb.DBB.main(DBB.java:14)

Problem description

Getting above exveption. But i checked online and below protocol config is missing at the same time,

Please help and let me know how to enable port and why the protocol for sqlexpress or instance is missing.

But ,

[peterbae]

Hi @satscreate, connection to localDB requires named pipe support, and support for named pipes is in our backlog but there is no timeline for it at this time. You can refer to #769 and #1150 for the discussion that took before. I'll close this issue as duplicate.

[satscreate]

Hi @satscreate, connection to localDB requires named pipe support, and support for named pipes is in our backlog but there is no timeline for it at this time. You can refer to #769 and #1150 for the discussion that took before. I'll close this issue as duplicate.

Meantime any workaround available?? @peterbae

[peterbae]

Unfortunately, other than the suggestions made in #769, there is no workaround for this.

[satscreate]

@peterbae I mean you meant this?? This should work?

connectionUrl = "jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=myDb;integratedSecurity=true";
try (Connection conn = DriverManager.getConnection(connectionUrl)) {
System.out.println(conn.getMetaData().getDriverVersion()); // 7.0.0.0
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT TOP 5 name FROM sys.tables ORDER BY name");
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (Throwable ex) {
ex.printStackTrace(System.err);
}

[peterbae]

"jdbc:sqlserver://localhost\SQLEXPRESS..." should be "jdbc:sqlserver://localhost\\SQLEXPRESS...", but otherwise it should work.

Edit: I didn't realize two backslashes in the comment also turns into one backslash on GitHub, so you have your connection string correct.

[satscreate]

@peterbae tried below but seems getting error,

Test1:
String connectionUrl = "jdbc:sqlserver://localhost\\Test Instance:1433;databaseName=TestSQlCMD;integratedSecurity=true";

Error:

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. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:284)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2435)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:635)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2010)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at com.example.checkdb.DBB.main(DBB.java:28)

Test2:
String connectionUrl = "jdbc:sqlserver://localhost\\Test Instance;databaseName=TestSQlCMD;integratedSecurity=true";

Error:

com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance test instance failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:5241)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:1916)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1669)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at com.example.checkdb.DBB.main(DBB.java:28)

Test3:
String connectionUrl = "jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=myDb;integratedSecurity=true";

Error:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "myDb" requested by the login. The login failed. ClientConnectionId:3b35a0a3-010b-4894-9c74-bf327abef5fc
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1LogonProcessor.complete(SQLServerConnection.java:4014)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4292)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3157)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at com.example.checkdb.DBB.main(DBB.java:30)

Test4
String connectionUrl = "jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=TestSQlCMD;integratedSecurity=true";
Error

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "TestSQlCMD" requested by the login. The login failed. ClientConnectionId:5b4abad9-d2df-471a-bc01-731f24fd6047
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1LogonProcessor.complete(SQLServerConnection.java:4014)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4292)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3157)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at com.example.checkdb.DBB.main(DBB.java:30)

But when i try login to localhost\SQLEXPRESS in sql studio, and created dummy database in it,
and did a test and it works, but why not localdb not connecting????

Test5: (This works)
String connectionUrl = "jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=testdb;integratedSecurity=true";

Test6: (This not working)
String connectionUrl = "jdbc:sqlserver://localhost\\Test instance;databaseName=TestSQlCMD;integratedSecurity=true";

[ulvii]

@satscreate , you are still trying against LocalDB, which is not supported by the driver. You need to install SQL Server Express or any other SQL Server edition that supports TCP/IP.