Connecting to MSSQL server using sqlcmd and odbc driver on RHEL 6 fails

Question

I spent the last hour trying to connect to a mssql server using sqlcmd through odbc driver. I was talking to the db guy but he doesn't seem to have a clue what is going on.. Maybe you can help me out to find some questions I could ask that guy..

I am using the official odbc driver from Microsoft. Aparently we are using some sort of dynamic ports.

There is no firewalls in between.

# odbcinst -q -d -n
[PostgreSQL]
[MySQL]
[ODBC Driver 11 for SQL Server]

I tried it different ways

Error code 0x2AF9:

sqlcmd -S hostname\DEV04 -U username -P password
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 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: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: Error code 0x2AF9.

and

Error Locating Server/Instance Specified [xFFFFFFFF].

sqlcmd -S hostname\\DEV04 -U username -P password
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 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: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Using perl

DBI connect('DASHBOARD','username',...) failed: [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired (SQL-HYT00) [state was HYT00 now 08001]
[unixODBC][Microsoft][ODBC Driver 11 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. (SQL-08001)
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (SQL-08001) at ./test.pl line 4
Can't connect to : [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired (SQL-HYT00) [state was HYT00 now 08001]
[unixODBC][Microsoft][ODBC Driver 11 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. (SQL-08001)
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (SQL-08001) at ./test.pl line 4.

did you found a way to resolve this ? I am in trouble too :) — v0d1ch, Sep 04 '15 at 12:25
Additional information on this question: https://twitter.com/jlangdon/status/673342905898020864 — , Dec 06 '15 at 09:40
If you check the `errorlog` for the instance, what port and IP does it say it is listening on (https://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/). — , Dec 06 '15 at 09:46

score 1 · Answer 1 · answered Apr 19 '18 at 07:45

After reading this thread: https://github.com/Microsoft/msphpsql/issues/190

I had to find out another portnumber SQL Server was listening on by executing

USE MASTER 
GO 
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

Result

2018-04-12 03:19:57.830 Server       Server is listening on [ 'any' <ipv6> 49155].                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
2018-04-12 03:19:57.830 Server       Server is listening on [ 'any' <ipv4> 49155].

add a Rule for the Windows Firewall on SQL Server host: - enabling specific Port 49155 (I think this can vary)

Then this command succeeded on my Ubuntu machine:

sqlcmd -Smyhost,49155 -U sa -P sa_pwd -d mydb -Q "select @@servername"

score 0 · Answer 2 · answered Oct 27 '16 at 04:23

I was hitting a similar issue. It turns out that the \DEV04 part of the hostname\DEV04 is used to indicate that the client should be connecting to the server on a different port from the default. In the Microsoft world, using DEV04 automatically ensures that the client connects to the correct port... but that doesn't seem to work with the Linux sqlcmd.

Ask you SQL Server admin which port the \DEV04 instance is listening on and connect using hostname,PORT

Or just make sure the SQL Browser server service is running and listening. — SqlACID, Jul 08 '17 at 13:24

score 0 · Answer 3 · answered Apr 13 '18 at 19:55

Based on a blurb from here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/36b8956e-0218-44aa-b717-725fe1d601f9/error-in-sqlserver-odbc-dirver-for-linux?forum=sqldataaccess

The UDP protocol just won't work in Linux using the Microsoft provided ODBC tools and sqlcmd. The UDP portion is used initially to probe the server to find the TCP port the requested database is listening on.

The solution use at my work is to switch to pymssql which will work with named instance and the UDP port requests.

Connecting to MSSQL server using sqlcmd and odbc driver on RHEL 6 fails

3 Answers3