Cannot connect to specific database instance

[phunsberger]

Bug Report

For a traditional JDBC connection to a SQL Server instance instance the connection string (at least on Windows) would resemble:

jdbc:sqlserver://host\instanceName

with r2dbc if I include the instance as part of the database host, the host it isn't found. If I try to include it as part of the database name I get the error:

Connection refused: no further information: localhost/127.0.0.1:1433

I've tried about a dozen different ways to format it, all but give the same error. If I change the credentials I get the expected authentication error so they seem to be getting passed properly. From what I can tell this either not implemented or a bug?

Versions

io.r2dbc: 0.8.0.RELEASE
spring-data-r2dbc: 1.0.0.RELEASE
spring-framework-cloud: 2.2.0

• Database:
  MSSQL Server 2017

• Java:
  Kotlin on JVM 1.8

• OS:
  Windows 10 Enterprise 10.0.17134

Current Behavior

Connection refused

Caused by: io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: no further information: localhost/127.0.0.1:1433 Caused by: java.net.ConnectException: Connection refused: no further information at sun.nio.ch.SocketChannelImpl.checkConnect(Native Method) ~[na:1.8.0_191] at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:717) ~[na:1.8.0_191] at io.netty.channel.socket.nio.NioSocketChannel.doFinishConnect(NioSocketChannel.java:330) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.channel.nio.AbstractNioChannel$AbstractNioUnsafe.finishConnect(AbstractNioChannel.java:334) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:688) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:635) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:552) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:514) ~[netty-transport-4.1.43.Final.jar:4.1.43.Final] at io.netty.util.concurrent.SingleThreadEventExecutor$6.run(SingleThreadEventExecutor.java:1050) ~[netty-common-4.1.43.Final.jar:4.1.43.Final] at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) ~[netty-common-4.1.43.Final.jar:4.1.43.Final] at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) ~[netty-common-4.1.43.Final.jar:4.1.43.Final] at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_191]

N/A

Open class TestServiceConfig @Autowired constructor(private val properties: TestServiceProperties) : AbstractR2dbcConfiguration() {

@Bean
override fun connectionFactory(): ConnectionFactory {
      return MssqlConnectionFactory(
            builder().host( properties.host!! )
                    .database( properties.database!! )
                    .username( properties.username!!)
                    .password( properties.password!!).build() )
}

}

Steps to reproduce

Any connection attempt causes the error

Expected behavior/code

A connection to the database

[mp911de]

The R2DBC driver supports only direct TCP connections. There’s no SRV or instance lookup implemented. We would need some help to add features like these.

[mp911de]

Specifying an instance requires a UDP lookup using the browser service. The browser service is reachable at <server>:1434. The server message has the format:

0x04 followed by the instance name encoded to bytes using the default charset. The max response size can be up to 4096 bytes. The response must contain tcp; followed by the port number terminated by semicolon (tcp;1433;). If the instance isn't configured for TCP, then the port lookup and the connect phase must fail.

See also https://www.bobpusateri.com/archive/2010/09/a-look-at-the-sql-server-browser-service/ for further detail.

[ninja-]

👍

[ninja-]

here's relevant code that could be copied over
https://github.com/microsoft/mssql-jdbc/blob/18db69cb8c7372c6bf5ba3fc883278436d48e26a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java#L7401

[ninja-]

here's a simplified standalone version of that code I used to get the job done:

class MSSqlBrowserException(msg: String, cause: Exception? = null) : IOException(msg, cause)

private const val BROWSER_PORT = 1434

/**
 * Resolves MSSql port using Browser protocol.
 *
 * @throws MSSqlBrowserException
 */
fun getInstancePort(server: String, instanceName: String): Int {
    var browserResult: String? = null
    var datagramSocket: DatagramSocket? = null
    var lastErrorMessage = "Failed to determine instance for the : $server instance:$instanceName"

    try {
        // First we create a datagram socket
        try {
            datagramSocket = DatagramSocket()
            datagramSocket.soTimeout = 1000
        } catch (socketException: SocketException) {
            // Errors creating a local socket
            // Log the error and bail.
            lastErrorMessage = "Unable to create local datagram socket"
            throw MSSqlBrowserException(lastErrorMessage, socketException)
        }

        try {
            // If instance name is not specified along with multiSubnetFailover, we resolve only the first IP
            // for server name
            val inetAddr = InetAddress.getByName(server)!!
            // Send the UDP request
            try {
                val sendBuffer = " $instanceName".toByteArray()
                sendBuffer[0] = 4
                val udpRequest = DatagramPacket(
                    sendBuffer, sendBuffer.size, inetAddr,
                    BROWSER_PORT
                )
                datagramSocket.send(udpRequest)
            } catch (ioException: IOException) {
                lastErrorMessage = ("Error sending SQL Server Browser Service UDP request to address: "
                        + inetAddr + ", port: " + BROWSER_PORT)
                throw MSSqlBrowserException(lastErrorMessage, ioException)
            }
        } catch (unknownHostException: UnknownHostException) {
            lastErrorMessage = "Unable to determine IP address of host: $server"
            throw MSSqlBrowserException(lastErrorMessage, unknownHostException)
        }

        // Receive the UDP response
        try {
            val receiveBuffer = ByteArray(4096)
            val udpResponse = DatagramPacket(receiveBuffer, receiveBuffer.size)
            datagramSocket.receive(udpResponse)
            browserResult = String(receiveBuffer, 3, receiveBuffer.size - 3)
        } catch (ioException: IOException) {
            // Warn and retry
            lastErrorMessage = "Error receiving SQL Server Browser Service UDP response from server: $server"
            throw MSSqlBrowserException(lastErrorMessage, ioException)
        }
    } finally {
        datagramSocket?.close()
    }

    checkNotNull(browserResult)

    // If the server isn't configured for TCP then say so and fail
    val p = browserResult.indexOf("tcp;")
    if (-1 == p) {
        throw MSSqlBrowserException(lastErrorMessage)
    }
    // All went well, so return the TCP port of the SQL Server instance
    val p1 = p + 4
    val p2 = browserResult.indexOf(';', p1)
    return browserResult.substring(p1, p2).toInt()
}

[mp911de]

Thanks a lot. That should be sufficient to rewrite the code to a non-blocking form using Reactor Netty.