Msphpsql: Can not connect to MS SQL from Ubuntu sqlcmd client

[CarlosRPEvertsz]

Hello,

I'm trying to connect to SQLExpress server from a Linux Ubuntu 16.04 computer. I Installed all SQL commad line utilities for the linux machine and follow instructions from Microsoft MSSQL on Linux docs.

When i run sqlcmd -S ip/SQLEXPRESS -U SA -P 1234, the command always returns the following error :

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2AF9

I was able to run the same command from a Windows10 computers and it works with no flaws.

The linux computer can communicate to the server machine and mssql 1433 port. I test this with the linux nc command (netcat).

Linux nc command results:
nc -z -v -w5 192.168.0.1 1433
Connection to 192.168.0.1 1433 port [tcp/ms-sql-s] succeeded!

Any suggestions or something missing?

Please I will really appreciate any help.

Regards,
Carlos

[david-puglielli]

Hi @CarlosRPEvertsz, it looks like you are trying to connect to a named instance. You need to supply the port number when connecting to a named instance:

sqlcmd -S <IP>\\SQLEXPRESS,1433 -U ...

For more info please see #442 and this page.

Please let us know if this fixes the problem.

[CarlosRPEvertsz]

HI David,

Now it rises other error, but it runs faster so i think we have some
progress. :-)
The new error message is:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider:
Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to
establish connection.

Regards,

Carlos

On Tue, Jul 11, 2017 at 4:16 PM, David Puglielli [e-mail]
wrote:

Hi @CarlosRPEvertsz https://github.com/carlosrpevertsz, it looks like
you are trying to connect to a named instance. You need to supply the port
number when connecting to a named instance:

sqlcmd -S \SQLEXPRESS,1433 -U ...

For more info please see #442
https://github.com/Microsoft/msphpsql/issues/442 and this page
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns
.

Please let us know if this fixes the problem.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/Microsoft/msphpsql/issues/470#issuecomment-314558810,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AYaGk39VkqPG0GfZ2JRK0wzk89N2wYA2ks5sM9gTgaJpZM4OUuOf
.

[david-puglielli]

Hi @leandroruel could you elaborate? What are you trying to do, what is failing, what is the error message(s) and what is the expected outcome? Is it similar to what @CarlosRPEvertsz is seeing?

[meet-bhagdev]

@leandroruel @CarlosRPEvertsz What version of the ODBC Driver and sqlcmd are you using?

[CarlosRPEvertsz]

Hi Meet-Bhagdev,

The ODBC version is 13 (Both Linux Ubuntu and Windows 10). The Win10 ODBC works perfectly but Linux (Ubuntu) version don't.

I wish to test previous versions to see but don't know where could i find them for Linux.

Thank you for your question and help.
Regards,
Carlos

[david-puglielli]

The latest ODBC version is 13.1, and you can also download and install 11 from this link: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server.

Also, please look at the troubleshooting section in that link - in particular, make sure you are using only one copy of the driver manager, as multiple copies can cause problems.

[CarlosRPEvertsz]

Hello David,

I read all the instructions on the link you suggest. Everything looks good
there but nothing works. I tested all versions from 13.1 to 13.0.1.0.1. (I
could not install version 11 because it is not where it supposed to be or i
could not find the tar or deb file for it).
I changed odbcinst.ini to set Trace=yes to see if this could help me with
the problem. I found that all steps that the trace shows are ok to me but
something in encoding seems to be not ok according to the troubleshooting
section. They say that the unicode problem should be because there are more
than one odbc managers installed, but this is not the case. I have the same
problem from the default version 13.1

Thanks,
Carlos

This is the odbc trace result:

[ODBC][10903][1500155582.347979][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x250c750
[ODBC][10903][1500155582.348099][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x250c750
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][10903][1500155582.348298][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.348404][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x250c750
[ODBC][10903][1500155582.348534][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x250cda0
[ODBC][10903][1500155582.348614][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x250cda0
Attribute = SQL_ATTR_LOGIN_TIMEOUT
Value = 0x8
StrLen = 4
[ODBC][10903][1500155582.348670][SQLSetConnectAttr.c][681]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.348708][SQLSetConnectAttrW.c][332]
Entry:
Connection = 0x250cda0
Attribute = SQL_ATTR_PACKET_SIZE
Value = 0x1000
StrLen = 4
[ODBC][10903][1500155582.348757][SQLSetConnectAttrW.c][616]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.348960][SQLDriverConnectW.c][290]
Entry:
Connection = 0x250cda0
Window Hdl = (nil)
Str In = [DRIVER={ODBC Driver 13 for SQL
Server};SERVER={192.168.0.1\sqlexpress,1433};UID={sa};PWD={123456789};WSID={servidor-archivos};APP={SQLC...][length
= 132 (SQL_NTS)]
Str Out = (nil)
Str Out Max = 0
Str Out Ptr = (nil)
Completion = 0
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE
'UCS-2LE'

[ODBC][10903][1500155582.366919][SQLDriverConnectW.c][699]
Exit:[SQL_ERROR]
[ODBC][10903][1500155582.367037][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 0
Diag Ident = 2
Diag Info Ptr = 0x7ffe658fd06e
Buffer Length = 2
String Len Ptr = (nil)
[ODBC][10903][1500155582.367151][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367180][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 1
Diag Ident = 5
Diag Info Ptr = 0x7ffe658fd074
Buffer Length = -6
String Len Ptr = (nil)
[ODBC][10903][1500155582.367214][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367246][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 1
Diag Ident = 6
Diag Info Ptr = 0x250e6a0
Buffer Length = 2050
String Len Ptr = 0x7ffe658fd070
[ODBC][10903][1500155582.367293][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367321][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 1
Diag Ident = 4
Diag Info Ptr = 0x7ffe658fd080
Buffer Length = 12
String Len Ptr = 0x7ffe658fd070
[ODBC][10903][1500155582.367363][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367502][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 2
Diag Ident = 5
Diag Info Ptr = 0x7ffe658fd074
Buffer Length = -6
String Len Ptr = (nil)
[ODBC][10903][1500155582.367540][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367566][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 2
Diag Ident = 6
Diag Info Ptr = 0x250e6a0
Buffer Length = 2050
String Len Ptr = 0x7ffe658fd070
[ODBC][10903][1500155582.367610][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
[ODBC][10903][1500155582.367641][SQLGetDiagFieldW.c][792]
Entry:
Connection = 0x250cda0
Rec Number = 2
Diag Ident = 4
Diag Info Ptr = 0x7ffe658fd080
Buffer Length = 12
String Len Ptr = 0x7ffe658fd070
[ODBC][10903][1500155582.367684][SQLGetDiagFieldW.c][812]
Exit:[SQL_SUCCESS]
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider:
Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to
establish connection.
[ODBC][10903][1500155582.367801][SQLGetConnectAttrW.c][211]Error:
SQL_INVALID_HANDLE

On Fri, Jul 14, 2017 at 6:01 PM, David Puglielli [e-mail]
wrote:

The latest ODBC version is 13.1, and you can also download and install 11
from this link: https://docs.microsoft.com/en-
us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-
driver-for-sql-server.

Also, please look at the troubleshooting section in that link - in
particular, make sure you are using only one copy of the driver manager, as
multiple copies can cause problems.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/Microsoft/msphpsql/issues/470#issuecomment-315478473,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AYaGk7LWFhrbKL-MwHTuWxCFKpAFxOD5ks5sN-UhgaJpZM4OUuOf
.

[leandroruel]

guys, i give up... it's really hard make this driver works properly on ubuntu. i'm trying for 3 days without success, installed ubuntu ina machine using virtualbox, installed the driver and only the php 7 driver following all instructions, after this i get:

[unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired

my odbc config:

Ubuntu 16.04
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.0
UsageCount=1

everytime i install the driver, the sqlcmd command dont work anymore, like this was uninstalled

[CarlosRPEvertsz]

I 'm trying to install in fresh ubuntu 16.04 standalone computer and it doesn't work. Complete 13.0-13.1 versions/revisions does not work. I think there is something wrong when microsoft developers built/compiled these revisions for ubuntu distro. Perhaps it works on other linux like red hat, but ubuntu 16.04 doesn't.

[leandroruel]

good luck, all my attempts fails

[meet-bhagdev]

@leandroruel @CarlosRPEvertsz I will work with you to make sure we can get sqlcmd working on Ubuntu 16.04. Are you able to re install it on a fresh machine? We can do a debugging screenshare session where I can help you fix this issue if you like.

[leandroruel]

i already made a fresh installation, but i can do another again without the drivers installed

[meet-bhagdev]

Sounds good! Send me an email at [email protected] and I will setup a screenshare session with you. 👍

[meet-bhagdev]

@CarlosRPEvertsz I worked with @leandroruel to look into this using a screenshare. I can do the same for you :) Send me an email at [email protected] and I will set something up.

[leandroruel]

yes, i was able of connect to a remote database from my ubuntu machine using the driver, but in my company network, something is blocking me of connect to the database from my VM, and the funny part is, from my PC (host) i can connect to the database normally, my (guest) VM ubuntu always return the error:

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..

[romulofrt]

Hi @meet-bhagdev , i had same problem with @leandroruel(his works with me). We were create new VM with Windows and access connection ready sucessfully, but when we try connection in VM based Linux(Ubuntu 16.04) we get the error:

All the settings for remote connections in SQL Server are enable.

[meet-bhagdev]

@romulofrt as I told @leandroruel the problem is with SQL Server not allow remote connections/firewall setup/other networking issue.

If I give you my SQL Server ip (which is what I did with @leandroruel), it works fine. Did you enable remote access for SQL Server?

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option

[romulofrt]

@meet-bhagdev Yes i do.

But the problem isn't SQL Server, because on VM based in Windows the connection has successfuly.

Do you agree if problem is with SQL Server both VM aren't connected ?

[meet-bhagdev]

@romulofrt I do not quite follow. What do you mean by the VM based in Windows has a successful connection? Is this an Ubuntu VM in Windows? I am happy to setup a screen share with you to troubleshoot this.

[romulofrt]

@meet-bhagdev Yes we do.

So you would like to do the troubleshoot ? By Skype ? If yes, i already sent invite.

[meet-bhagdev]

@romulofrt I did not get it. Can you send me an email at [email protected]? I will set something up

[leandroruel]

@meet-bhagdev thanks for all ur help, now i'm able of connect to the database of my company, the issue was... the port 1433 for any reason are blocked to connect, and the sql server uses a dinamic port so, the infrastructure department made all to me, allowing me to connect to the sql server... i don't know how to explain it to you, because, i'm from another department, but i think my issue was related with this: https://docs.microsoft.com/pt-br/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access

[romulofrt]

@meet-bhagdev You really were right! It was a SQL Server-specific port in Windows Server 2012 R2.
Thanks a lot for the help.

[JanOonk]

I had the same problem trying to connect with:
sqlcmd -S <ip>\\SQLEXPRESS,1433 -U username -P password

from a Ubuntu v16 to Windows SQL Server. Strange thing was that I could connect by using a Windows 8 Client and SQL Management Studio.
The fix for letting the Ubuntu client connect was to set "Listen to All" to No in the SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [instance name].
And under IP Address change 127.0.0.1 to the [ip] used to connect.
Don't forget to restart the SQL Server!

Other tip: check your firewall (or temporaly disable it for testing).
Also allow remote connections in SQL Server properties > Connections. See above posts.

Life safer was this command: nc -zv <ip> 1433
It should say Connection to 1433 port [tcp/ms-sql-s] succeeded!

See these online resources for more details: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port
and
https://confluence.atlassian.com/jirakb/connection-refused-when-connecting-to-sql-server-express-104300829.html

[jackalhan]

I am having the same problem. I am able to connect with FreeTDS driver by using useNTLMv2=True parameter but I can not connect with microsoft driver since it does not allow me to add that parameter.

How can I bypass that problem?

Thank you.

[lilgreenbird]

hi @jackalhan,

It sounds like you are having a problem with the ODBC driver and not PHP? Are you also using Ubuntu or other Linux platform? Please check your configuration files (ie odbc.ini, odbcinst.ini) you verify your setup using just 'isql' 1st to make sure everything is setup correctly.

[leandroruel]

@jackalhan it works very well to me every time i install this driver, maybe you're missing something, or need check if all files are correct in your respective directories, also, check the installation guide agaiin

[neilrieck]

We ran into a similar problem connecting to "SQL Server 2005" from Linux via msodbcsql. The problem here is that testing via the OpenSSL CLI from a different machine (e.g. "openssl s_client -connect ip-address:1433 -ssl2" ) proved that "SQL Server 2005" was requiring sslv2 to encyrpt/decrypt the username + password. The big problem here is that recent updates to Linux provided us with a new version of openssl which did not support sslv2.

[hktalent]

@david-puglielli @yitam @meet-bhagdev
in kali linux
me too

# sqlcmd  -S 12.68.0.5\\SQLEXPRESS,1443 -U sa -P 123456
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection due to prelogin failure.

[david-puglielli]

@hktalent Does this happen all the time or only intermittently? Which version of the MSODBCSQL driver do you have installed? Some connection bugs were fixed in the latest release (17.2), which you can install according to the instructions here. Since you're on Kali Linux you'll want to follow the Debian instructions.

[neilrieck]

It happens all the time.Everything was OK on CentOS-7.2 then everything failed with an upgrade to CentOS-7.4

I work on other platforms (like VMS and OpenVMS) where different versions of OpenSSL can coexist in harmony. This does not appear to be possible on Linux (or perhaps it is just very difficult). Anyway, the only way to connect to that old version of SQL Server is by SSL2 which is no longer available with the latest version of OpenSSL (it’s not that it has been disabled after the build; it was disabled before the build)

Neil Rieck
Waterloo, Ontario, Canada.
http://neilrieck.net

From: David Puglielli
Sent: Thursday, August 23, 2018 13:59
To: Microsoft/msphpsql
Cc: Neil Rieck ; Comment
Subject: Re: [Microsoft/msphpsql] Can not connect to MS SQL from Ubuntu sqlcmd client (#470)

@hktalent Does this happen all the time or only intermittently? Which version of the MSODBCSQL driver do you have installed? Some connection bugs were fixed in the latest release (17.2), which you can install according to the instructions here. Since you're on Kali Linux you'll want to follow the Debian instructions.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[marcostijerina]

Have you tried to adding "tcp:" before your IP Address?
Example:
sqlcmd -S tcp:12.68.0.5\SQLEXPRESS,1443 -U sa -P 123456

[neilrieck]

No, I think the server will only allow https connections via ssl2. I will check tomorrow.

Neil

From: marcostijerina
Sent: Wednesday, September 19, 2018 15:55
To: Microsoft/msphpsql
Cc: Neil Rieck ; Comment
Subject: Re: [Microsoft/msphpsql] Can not connect to MS SQL from Ubuntu sqlcmd client (#470)

Have you tried to add "tcp:" before adding your IP Address?
Example:
sqlcmd -S tcp:12.68.0.5\SQLEXPRESS,1443 -U sa -P 123456

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[cihangirbesiktas]

This seems to be connection string related problem in bash,
Try with single quotes like below.
sqlcmd -S '12.68.0.5\SQLEXPRESS,1443' -U sa -P 123456

[david-puglielli]

Closing issue because of inactivity. Please reopen if necessary.

[VHK74]

This worked for me in Raspbian.
I had to change "SQLEXPRESS" to the port of the instance. In my case it is the default port, because I have only one instance.

StringConnection 1:
"Data Source=server_name,1433;Initial Catalog=database_name;User Id=sa;Password=password;

StringConnection 2:
"Server=server_name,1433;Database=database_name;User Id=sa;Password=pass"

[agnelvishal]

https://stackoverflow.com/a/57343207/2242402 solved it for me -
Need to revert to previous sql server version

sudo apt-get install mssql-server=14.0.3192.2-2

[bmart102]

I ran into something similar (in my Ubuntu 18.04). I would get these errors:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 17 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 was able to make it work simply by adding the "login timeout" option in the sqlcmd. (ie. -l 5000)

[yitam]

@bmart102 are you connecting to Azure or SQL Server on premise?

[bmart102]

The SQL SERVER is at a remote host. To be specific, it's at an account in "https://www.smarterasp.net/". But I ran into same problem with another remote sql server, and adding "-l 5000" allowed me to finally connect to both of them.