Unable to connect to MSSQL Server from Linux VM using FreeTDS

Question

I've set up FreeTDS + UnixODBC on my RHEL VM and have a MS SQL Server that uses Windows Auth be default.

I'm trying to connect to this MS SQL server using windows auth.

Following are my config files for freeTDS and ODBC

freetds.conf

[global]
        tds version = 7.2
        dump file = /tmp/freetds.log
        debug flags = 0xffff
        timeout = 10
        connect timeout = 10
        text size = 64512

[mssql_db1_freetds]
    host = {ip}
    port = 1433
    timeout = 600
    connect timeout = 600
    tds version = 7.2
    text size = 20971520

odbc.ini

[mssql_db1_freetds]
Description             = MSSQL Server
Driver                  = freetds
Database                = {DB_Name}
ServerName              = mssql_db1_freetds
TDS_Version             = 7.2
Trace = Yes
TraceFile       = /tmp/mstest.log

odbcinst.ini

[FreeTDS]
Description=v0.91 with protocol v7.2
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount=1
FileUsage=1

I'm using this to pass my windows auth through tsql:

/usr/bin/tsql -S {ip} -U "{domain}\{username}" -I /etc/freetds/freetds.conf

I get the following error:

locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
Error 100 (severity 11):
        unrecognized msgno
Msg 18452 (severity 14, state 1) from {Server_Name} Line 1:
        "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

This is my freetds log:

09:03:25.326729 25546 (log.c:196):Starting log file for FreeTDS 0.91
        on 2019-08-26 09:03:25 with debug flags 0xffff.
09:03:25.326925 25546 (util.c:331):tdserror(0x239dbb0, 0x239dcd0, 100, 0)
09:03:25.326961 25546 (util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
09:03:25.326966 25546 (util.c:384):tdserror: returning TDS_INT_CANCEL(2)
09:03:25.326972 25546 (iconv.c:330):tds_iconv_open(0x239dcd0, ISO-8859-1)
09:03:25.327102 25546 (iconv.c:187):local name for ISO-8859-1 is ISO-8859-1
09:03:25.327110 25546 (iconv.c:187):local name for UTF-8 is UTF-8
09:03:25.327113 25546 (iconv.c:187):local name for UCS-2LE is UCS-2LE
09:03:25.327115 25546 (iconv.c:187):local name for UCS-2BE is UCS-2BE
09:03:25.327117 25546 (iconv.c:349):setting up conversions for client charset "ISO-8859-1"
09:03:25.327120 25546 (iconv.c:351):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
09:03:25.327128 25546 (iconv.c:391):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
09:03:25.327136 25546 (iconv.c:394):tds_iconv_open: done
09:03:25.327146 25546 (net.c:207):Connecting to {ip} port 1433 (TDS version 7.1)
09:03:25.327344 25546 (net.c:272):tds_open_socket: connect(2) returned "Operation now in progress"
09:03:25.329401 25546 (net.c:312):tds_open_socket() succeeded
09:03:25.329418 25546 (util.c:156):Changed query state from DEAD to IDLE
09:03:25.329427 25546 (net.c:743):Sending packet
.
.
.
.
09:03:25.345470 25546 (token.c:337):looking for login token, got  aa(ERROR)
09:03:25.345473 25546 (token.c:122):tds_process_default_tokens() marker is aa(ERROR)
09:03:25.345478 25546 (token.c:2588):tds_process_msg() reading message 18452 from server
09:03:25.345487 25546 (token.c:2661):tds_process_msg() calling client msg handler
09:03:25.345501 25546 (token.c:2674):tds_process_msg() returning TDS_SUCCEED
09:03:25.345504 25546 (token.c:337):looking for login token, got  fd(DONE)
09:03:25.345506 25546 (token.c:122):tds_process_default_tokens() marker is fd(DONE)
09:03:25.345509 25546 (token.c:2339):tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
09:03:25.345513 25546 (token.c:2355):tds_process_end() state set to TDS_IDLE
09:03:25.345515 25546 (token.c:2370):                rows_affected = 0
09:03:25.345518 25546 (token.c:438):tds_process_login_tokens() returning TDS_FAIL
09:03:25.345520 25546 (login.c:466):login packet accepted

Can anyone guide as to what am I doing wrong in configuration or otherwise. FreeTds returns version as 4.2 on running "-C" command. Not sure if this is the problem as I'm passing the "-I" param for config file

Answer 1

/usr/bin/tsql -S {ip} -U "{domain}\{username}" -I /etc/freetds/freetds.conf

I believe, while you using this, it would recognize as SQL Authentication. Usually, Windows Authentication recognized as currently logged-in user, which required server to be joined to domain where the domain controller manages kerberos authentication. Otherwise, it required full-set of configuration kerberos authentication from unix/linux .

If you don't have any security issues, i would suggest go with SQL Authentication which you can change it via SSMS -> Server Properties -> Security (tab) -> SQL Server and Windows Authentication - (required service restart).

Answer 2

This is really annoying, and I've kicked myself for it before, but you need to use single quotes:

/usr/bin/tsql -S {ip} -U '{domain}\{username}' -I /etc/freetds/freetds.conf

For example, I just tested:

$ tsql -S myserver.example.com -p 1433 -U "MYDOMAIN\myuser"
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18452 (severity 14, state 1) from CDW-SQL0101 Line 1:
        "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Error 20002 (severity 9):
        Adaptive Server connection failed
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server
$ tsql -S myserver.example.com -p 1433 -U 'MYDOMAIN\myuser'
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> SELECT TOP 1 name, object_id FROM sys.tables;
2> GO
name    object_id
spt_fallback_db 117575457
(1 row affected)
1>

This was on CentOS 7.6 with FreeTDS 0.95, but should work on your setup as well. Good luck!