SQL Server SQLCMD Named Instance Login Timeout Expired

Question

I am getting an error from sqlcmd on linux when I try to connect to a named instance. I am running all this inside a docker container defined below.

FROM microsoft/mssql-server-linux:2017-latest
RUN apt-get update
RUN DEBIAN_FRONTEND=noninteractive apt-get install -y krb5-user
RUN apt-get install -y vim-common netcat

CMD ["/bin/bash"]

I then configure krb5.conf and run kinit for the account I want to use. That all works fine.

I read here: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-connect-to-the-database-engine?view=sql-server-2017

That one of these should work to connect to the named instance:

/opt/mssql-tools/bin/sqlcmd -S 'np:\\server\pipe\MSSQLi1\sql\query' -E -C
/opt/mssql-tools/bin/sqlcmd -S 'np:\\server\pipe\MSSQL$i1\sql\query' -E -C
/opt/mssql-tools/bin/sqlcmd -S 'lpc:server\i1' -E -C
/opt/mssql-tools/bin/sqlcmd -S 'server\i1' -E -C

None of them work. The error is the same for all these commands. It is:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : MAX_PROVS: Cannot open a Shared Memory connection to a remote SQL Server instance [87]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

sqlcmd is clearly trying to connect to the wrong port.

So, I was able to use:

echo '020000' | xxd -r -p | nc -u server 1434

To query SQL Server for its list of named instances, like I would expect sqlcmd to do on its own. SQL Server happily replied with its list, and in that list was the instance I was looking for, located on port 60655.

Subsequently, I tried the following SUCCESSFUL command:

/opt/mssql-tools/bin/sqlcmd -S 'server,60655\i1' -E -C

Now, my question is: how do I get sqlcmd to resolve the port of the named instance and connect to it? I'm able to connect correctly so long as I explicitly provide the port of the named instance.

Update with more info:

I looked into the specs for SQL Server Browser and learned I could do this to request instance i1(6931 is hex for ascii 'i1'):

echo '046931' | xxd -r -p | nc -w 1 -u server 1434

From the docker container, this prints:

RServerName;SERVER;InstanceName;I1;IsClustered;No;Version;13.2.5026.0;tcp;60655;;

So the SQL Server Browser is working perfectly.

I then tried running this on the host:

nc -l -u 1434

And this in the docker container:

/opt/mssql-tools/bin/sqlcmd -S 'host\i2'

And nothing came through to netcat, which means that sqlcmd is not even attempting to talk to the sql server browser.

Jack Davidson Jack Davidson · Accepted Answer · 2018-12-05T01:05:20

As a work-around I defined a bash function for now that queries the sql server browser:

function resolve()
{
  SRV=$(echo $1 | tr '\\' '\n' | head -1)
  INS=$(echo $1 | tr '\\' '\n' | tail -1)
  PORT=`echo -n '04'$(echo -n $INS | xxd -ps -c 200) | xxd -r -p | nc -u $SRV 1434 -w 1 \
  | perl -nle 'm/tcp;(.*?);.*/; print $1'`
  echo ${SRV},${PORT}\\${INS} 
}

Then I can connect like this:

SRV='Server\i1'
/opt/mssql-tools/bin/sqlcmd -S $(resolve $SRV)

SQL Server SQLCMD Named Instance Login Timeout Expired

1 Answers