Sql-server – Problem connecting to SQL Server 2005 via FreeTDS on FreeBSD

freetdssql server

I am trying to connect to SQL Server 2005 from a FreeBSD server with the following command:

tsql -H DEV -p 1433 -U ****

This results in the following error message:

locale is "C"
locale charset is "US-ASCII"
Password: 
Msg 20004, Level 9, State 0, Server OpenClient, Line 0
Read from SQL server failed.
Msg 20014, Level 9, State 0, Server OpenClient, Line 0
Login incorrect.
There was a problem connecting to the server

Since the error message reports "Login incorrect", my first step was to verify that I am indeed typing in the correct username and password. It is a SQL Server login (i.e. not Windows authentication) and I have ensured that it's correctly input with case sensitivity. Note that I am able to connect using the same login from a Windows client without any issues.

My next step was to dump a log from FreeTDS:

util.c:288:Starting log file for FreeTDS 0.64
        on 2011-04-12 16:29:57 with debug flags 0x4fff.
iconv.c:195:names for ISO-8859-1: ISO-8859-1
iconv.c:195:names for UTF-8: UTF-8
iconv.c:195:names for UCS-2LE: UCS-2LE
iconv.c:195:names for UCS-2BE: UCS-2BE
iconv.c:361:iconv to convert client-side data to the "US-ASCII" character set
iconv.c:514:tds_iconv_info_init: converting "US-ASCII"->"UCS-2LE"
net.c:168:Connecting to 10.5.5.226 port 1433.
net.c:673:Sending packet

...snipped...

net.c:673:Sending packet

...snipped...

token.c:310:tds_process_login_tokens()
util.c:119:Changing query state from IDLE to DEAD
token.c:2252:tds_client_msg: #20004: "Read from SQL server failed.".  Connection state is now 4.  
token.c:314:looking for login token, got  0()
token.c:105:tds_process_default_tokens() marker is 0()
token.c:108:leaving tds_process_default_tokens() connection dead
util.c:119:Changing query state from DEAD to DEAD
token.c:2252:tds_client_msg: #20014: "Login incorrect.".  Connection state is now 4.  
mem.c:519:tds_free_all_results()

One other thing I've verified is that the SQL Server instance accepts remote connections via TCP/IP. I can also telnet to the SQL Server on port 1433 from the FreeBSD machine, so there does not seem to be a general connectivity issue.

So, at the moment, I'm dead in the water. Any assistance with diagnosing these error messages or suggesting other things to try would be very much appreciated.

Best Answer

"DB-Lib error message 20004, severity 9: Read from SQL server failed" error appears.

On Linux/*nix you may encounter the following behaviour: import _mssql c=_mssql.connect('hostname:portnumber','user','pass') Traceback (most recent call last): File "", line 1, in _mssql.DatabaseException: DB-Lib error message 20004, severity 9: Read from SQL server failed. DB-Lib error message 20014, severity 9: Login incorrect.

It may happen when one of the following is true:

  • freetds.conf file cannot be found
  • tds version in freetds.conf file is not 7.0 or 4.2
  • any character set is specified in freetds.conf
  • an unrecognized character set is passed to _mssql.connect() or pymssql.connect() method.

"Login incorrect" following this error is spurious, real "Login incorrect" messages has code=18456 and severity=14.

ref: http://pymssql.sourceforge.net/faq.php