How to connect to SQL Server using sqlcmd on Linux?

Question

I have set up the Microsoft ODBC Driver 13 for SQL Server on an Ubuntu 16.04 machine. I am now trying to test a database connection, that includes both the server name and an instance name.

The following both work fine from a Windows machine:

sqlcmd -S "SERVERNAME\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

However none of them work on Linux - they all return the error message below. The error implies the server cannot be found:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 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..

I have also tried escaping the backslash with the same error (and with single and double quotes).

sqlcmd -S "SERVERNAME\\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

The driver is working however as I can connect to a different server with no instance name:

sqlcmd -S SERVERNAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

I am fairly certain it is due to the backslash, but cannot find a way to get around this.

• Dan Guzman: Have you tried connecting using a port specification rather than instance name (e.g. -S "SERVERNAME,port")?
• geographika: @DanGuzman - thanks! That approach works. Will I add details as an answer?

Answer 1

With the help of Dan Guzman's comment I got the connection working using a port.

I had a Windows machine with SQL Management Studio connected to the database in question, and used:

netstat -abn

Then I searched for ssms.exe for the connection details:

TCP 192.168.0.31:50777 192.168.0.78:49399 ESTABLISHED [Ssms.exe]

Alternatively log into the database server and look at the ports used by the instance in SQL Server Configuration Manager (SQL Server Network Configuration> Protocols for INSTANCENAME> TCP/IP> Properties> IPAll TCP Dynamic Ports).

The following then both worked:

sqlcmd -S 192.168.0.78,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

• Dan Guzman: In your second example, is INSTANCENAME a typo? I think it should be SERVERNAME. Although the port number specification may be an acceptable work-around for you, I think the instance name should work from Ubuntu as it does from Windows. Make sure UDP 1434 (for the SQL Server Browser service), can be reached using a utility like netcat.
• Jan: When you get an error certificate verify failed:self-signed certificate use -C commandline argument.

Answer 2

This was an issue with the microsoft odbc driver, which has been fixed with Microsoft ODBC Driver 17.4

With the new release, sqlcmd and sqlsrv driver for php are able to query UDP port 1434 to ask the MSSQL Server for the port number of the named instance

Answer 3

My windows user was not allowed to run netstat -abn as suggested in geographika's answer, but I could work around it:

By using tasklist | find /i "Ssms.exe", I got the following result:

Ssms.exe 14144 RDP-Tcp#15 3 233.384 K

14144 in the example above is the PID. Next I entered netstat -oan | find /i "14144"

This will return (probably more than one line):

TCP 192.168.100.102:60348 192.168.100.102:55600 HERGESTELLT 14144

So the Port to connect to would be 55600.

This answer gave me the hint needed, but it didn't work as expected so I had to adapt it a little.

(EDIT: Somehow forgot to put it all together.)

Now we can connect using sqlcmd the IP and the Port:

sqlcmd -S 192.168.100.102,55600 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"