7

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.

  • 2
    Have you tried connecting using a port specification rather than instance name (e.g. -S "SERVERNAME,port")? – Dan Guzman Aug 18 '16 at 11:26
  • @DanGuzman - thanks! That approach works. Will I add details as an answer? – geographika Aug 18 '16 at 11:46
5

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;"
  • 1
    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. – Dan Guzman Aug 18 '16 at 12:23
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

1

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", getting following result:

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

14144 in the example above is the PID, and when tried 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;"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.