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

Answer 1

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.

Answer 2

To debug the issue try the following steps:

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