Failure to connect to SQl Server from Linux

Question

I am trying to connect to SQL Server 2008 on CentOS 5.8. I am using unixODBC 2.3.0 and SQL Server ODBC Driver (www.microsoft.com/en-us/download/details.aspx?id=28160).

When I try to test the connection by running:

isql -v mydsn username password

it givens me:

[S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired
[08001][unixODBC][Microsoft][SQL Server Native Client 11.0]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.
[08001][unixODBC][Microsoft][SQL Server Native Client 11.0]TCP Provider: Error code 0x2726
[ISQL]ERROR: Could not SQLConnect

The port is open, the server is accessible.

I was trying to diagnose the problem further, but got stuck here:

 strace -e trace=network isql -v mydsn username password
socket(PF_FILE, SOCK_STREAM, 0)         = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0)         = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0)         = 3
connect(3, {sa_family=AF_FILE, path="/var/run/setrans/.setrans-unix"...}, 110) = 0
sendmsg(3, {msg_name(0)=NULL, msg_iov(5)=[{"\1\0\0\0", 4}, {"\1\0\0\0", 4}, {"\1\0\0\0", 4}, {"\0", 1}, {"\0", 1}], msg_controllen=0, msg_flags=0}, MSG_NOSIGNAL) = 14
socket(PF_FILE, SOCK_STREAM, 0)         = 4
connect(4, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0)         = 4
connect(4, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)
socket(PF_INET, 0x80001 /* SOCK_??? */, IPPROTO_TCP) = -1 EINVAL (Invalid argument)

Apparently, there is something wrong going on with the establishment of connection.

Can anyone help me? Please let me know if you need any other info.

Thanks

Are you sure it's a default instance and not a named instance? Did you try to telnet to the server on port 1433? Did you try the server name or IP address instead of an antiquated DSN? How have you verified that the port is open and the server is accessible? What port? How do you know that is the right port? Details, details, details... — Aaron Bertrand, Feb 27 '13 at 02:55

score 9 · Answer 1 · edited Mar 18 '13 at 17:51

One 'gotcha' when working with linux and odbc connecting to Microsoft's SQL Server while using Microsoft's linux driver, is the string in odbc.ini for the server must contain the port as well.

 Server = [protocol:]server[,port]

as per http://msdn.microsoft.com/en-us/library/hh568455.aspx This is a different convention than most other setups that use the port = <portnumber> convention. If that is not configured, you will see a 'Could not SQLConnect' error.

Also ensure that the correct odbc files are being used.

odbcinst -j

will show configured sources and their locations.

Another gotcha you might encounter later, is the driver for SQL Server ignores user and password information in odbc.ini if it is in plain text, so make sure your application handles that.

is there any reason they won't change the behavior so it works with named instances? — Alf47, Feb 07 '18 at 21:50

score 1 · Answer 2 · edited Sep 17 '14 at 09:39

1

To debug the issue try the following steps:

telnet <1433>
Go to SQL server network configuration
TCP/IP settings
right click and open Properties
switch to the Network Properties tab
under IPAll properties set the dynamic ports to blank and port to 1433
Restart the SQL service

edited Sep 17 '14 at 09:39

Herdo

5,468
2
32
59

answered Sep 17 '14 at 09:19

marwin

11
1

score 0 · Answer 3 · answered Mar 05 '13 at 01:10

0

You can open the odbc trace and use tcpdump to catch the network package. I think the odbc trace will give a help.

answered Mar 05 '13 at 01:10

Nan Xiao

14,408
13
73
134

score 0 · Answer 4 · answered Jul 21 '13 at 15:35

0

The problem was that I was using EL6 driver with EL5. After installing the correct version, everything worked. Thanks any way for everyone's responses.

answered Jul 21 '13 at 15:35

kirylm

101
1
1
6

Failure to connect to SQl Server from Linux

4 Answers4

Linked