Sql-server – How to connect to SQL Server using sqlcmd on Linux

linuxsql serversqlcmdUbuntu

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.

Best Answer

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;"
Related Question