Access MSSQL named instances without having to specify their port number

[oreadflex]

Introduction

I cannot reach a connection to the MSSQL Server. I get a 10061 connection refused always

To Reproduce

Connect to a sqlserver with a named instance rather than host or port

Actual behavior

In trying using sqlx-oldapi and also sqlpage i fail to make a connection. My details are correct as i can connect to the database using sqlcmd

Screenshots

N/A

Expected behavior

The connection should establish

Version information

• OS: W11
• MsSql

[lovasoa]

Hi ! Could you check whether your issue is not a duplicate of https://github.com/sqlpage/SQLPage/issues/92 or https://github.com/sqlpage/SQLPage/issues/86 ? If so, could you comment directly on the original issue ?

Also, we recently added support for odbc, so you can also connect to your sql server database on windows using the native odbc driver that supports all SQL server connection methods.

[oreadflex]

I see now. So, when providing a named instance, it will not interrogate for a given port.

https://github.com/sqlpage/SQLPage/issues/86#issuecomment-1718461453

It was my understanding given this that we do not need to provide the port as it will be found automatically but based on #92 this is not implemented.

[oreadflex]

I'm not really in a position to submit a pull request but for possible future upstreaming or whoever else encounters this issue you can just query the port using this

use std::net::UdpSocket;
use std::time::Duration;
pub fn lookup(server: &str, instance: &str) -> Result<u16, Box<dyn std::error::Error>> {
    let udp_port = 1434;
    let udp_message = [&[0x04u8][..], instance.as_bytes()].concat();
    let socket = UdpSocket::bind("0.0.0.0:0")?;
    socket.set_read_timeout(Some(Duration::new(5, 0)))?;
    socket.send_to(&udp_message, (server, udp_port))?;
    let mut buf = [0u8; 1024];
    let (n, _) = socket.recv_from(&mut buf)?;
    let payload = String::from_utf8(buf[3..n].to_vec())?;
    let parts: Vec<&str> = payload.split(';').collect();
    let mut map = std::collections::HashMap::new();
    let mut i = 0;
    while i + 1 < parts.len() {
        map.insert(parts[i], parts[i + 1]);
        i += 2;
    }
    println!("{:?}",map);
    let tcp = map.get("tcp").ok_or("tcp not found")?;
    Ok(tcp.parse::<u16>()?)
}

[lovasoa]

Interesting, thanks for the snipper ! Is there a reference document where this is documented ?

The file to update is probably: https://github.com/sqlpage/sqlx-oldapi/blob/main/sqlx-core/src/mssql/connection/stream.rs

A pull request would be very welcome !

[oreadflex]

You can read more about it here

1. https://pypi.org/project/sqlserverport/
2. https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-wpo/c67adffd-2740-435d-bda7-dc66fb13f1b7

I figured out the problem by actually checking the network traffic on sqlcmd with wireshark.

Not sure if that's the right place for it. It also might be nice to cache the active port somewhere for use in cargo check with comptime query validation

[lovasoa]

Would you be available to test it if I implemented it ? There is no support for named instances in the official sqlserver docker image we use on ci.

[lovasoa]

I had cursor attempt an implementation here: https://github.com/sqlpage/sqlx-oldapi/pull/43

[lovasoa]

Could you please test the new feature and give feedback on https://github.com/sqlpage/SQLPage/pull/1085 ?