0

I am using freetds version 1.00.91 from HomeBrew. I can successfully connect when I bypass the freetds.conf, e.g. using the following command

tsql -H {my_hostname} -p {port_no} -U {username} -P {password}

However, when I tried to 

tsql -S MYMSSERVER -U {username} -P {password}

it failed with the following error:

Msg 40532 (severity 20, state 1) from MYSERVER:
Cannot open server "MYSERVER" requested by the login. The login failed.

Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server

The freetds.conf content is the following:

[MYSERVER]
        host = {my_hostname}
        port = {port_no}
        tds version = 7.3

I checked the output of tsql -C and confirmed that 7.3 is also the version used by the -H -p command.

Here's some relevant info from freetds.log file about this error:

15:49:43.391174 3380 (token.c:313):looking for login token, got  aa(ERROR)
15:49:43.391215 3380 (token.c:132):tds_process_default_tokens() marker is aa(ERROR)
15:49:43.391240 3380 (mem.c:653):tds_free_all_results()
15:49:43.391258 3380 (token.c:2384):tds_process_info() reading message 40532 from server
15:49:43.391308 3380 (token.c:2456):tds_process_info() calling client msg handler
15:49:43.391391 3380 (token.c:2473):tds_process_info() returning TDS_SUCCESS
15:49:43.391418 3380 (token.c:313):looking for login token, got  fd(DONE)
15:49:43.391442 3380 (token.c:132):tds_process_default_tokens() marker is fd(DONE)
15:49:43.391471 3380 (token.c:2082):tds_process_end: more_results = 0

Thanks for all that help!

Allen Lin
  • 1,179
  • 4
  • 13
  • 23

2 Answers2

0

I made it work, more specifically made it work with unixODBC ultimately in a hacky way.

Here's my freetds.conf file

[my_hostname]
        host = {my_hostname}
        port = {port_no}
        tds version = 7.3

And the following is my odbc.ini file

[Foo]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = {the_same_my_hostname_as_in_freetds.conf}
Database            = BingMigration

isql Foo {username} {password} then works like a charm.

Why it works:

According to the tsql man, the tsql will treat the -S value as hostname if no servername is found in freetds.conf. That's the reason why the above freetds.conf works. I need to name the freetds.conf section using the my_hostname because the isql seems to require a DSN that matches a section in freetds.conf in its 1st argument. Simply using a hostname that does not have corresponding section in freetds.conf failed when I tried.

Allen Lin
  • 1,179
  • 4
  • 13
  • 23
0

There's a bit of confusion here. tsql use libTDS directly, so freetds.conf while isql is a ODBC program, mainly uses odbc.ini (on Windows this is stored as registry entries). isql does not require a section name, the terminology is a DSN name. There's no server_url, the name is usually an host name, like a DNS name. An environment which can be useful for debugging these issue is TDSDUMPCONFIG. The freetds.conf in the initial question seems correct, the fact that server return an aa message (ERROR) seems to indicate there has been a TCP connection. According to https://learn.microsoft.com/en-us/azure/sql-database/sql-database-develop-error-messages error 40532 is Cannot open server "%.*ls" requested by the login. The login failed.

Frediano Ziglio
  • 310
  • 1
  • 6