Re: Cannot get tsql to connect

On Wed, 8 Jul 2015 12:17:58 -0400
Chuck Roberts <[email protected]> wrote:

> - Machine: Ubuntu 14.04.1 LTS 64-bit, it's a virtual machine.
...
> - tsql command and result. (IP, domain, user and password are fake)
> chuck@ubuntucomp:~/perl/gilson/jimv/fedex$ tsql -S 10.20.30.40 -U
> "DOMAIN\user" -P passwd
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> using default charset "UTF-8"
> Msg 18452 (severity 14, state 1) from EDISON Line 1:
>         "Login failed for user ''. The user is not associated with a
> trusted SQL Server connection."
...
> It looks like the user "DOMAIN\user" needs to be associated with a
> trusted SQL server connection. I'm not the IT guy, so what do I tell
> my IT guy? Other people on Windows are already using this domain
> \username to log in without a password.

Bourne shells interpret double-quoted strings.  I recommend using
single quotes, e.g. 'DOMAIN\user' just in case the first letter in the
username is interpolated.  

You don't have to tell your IT guy anything.  The MS-CHAP
authentication protocol involves only the two ends of the connection,
client and server.  The primary domain controller is not consulted.
All that's required is that the username you're providing does
indeed belong to that domain, meaning you can log on that way to a
Windows machine.  

> Also, specifying the command line with tsql means I bypassed the
> freetds.conf, right?

No, when you use the -S option, you're naming a servername entry in
freetds.conf.  If that lookup fails, IIRC the library will try to
convert the name by DNS lookup or by converting the string to an IP
address.  If freetds.conf is not used, the TDS version comes from the
value of TDSVER or the compiled-in default.  

I suspect what's happening is that you're not supplying a servername
that appears in freetds.conf, and consequently the connection is made
by decoding the -S argument as an IP address, and the TDS version used
is 4.2.  In that scenario, the username is not treated as a domain
login, and SQL Server rejects it as invalid because no such user is
defined to it.  You can confirm that by setting TDSDUMP and looking at
the top of the file.  

For the record: I have this "decode the name as address" filed under
Not Helpful.  It would be better if the library returned a name-lookup
failure, because that's what the user specified with -S.  

> Even though I'm using a domain logon, I don't have SSPI with FreeTDS.
> Do I need SSPI? If so, how do I enable it?

You would use SSPI if you had a Windows client; it's the API that lets
an application log in to SQL Server without a password by using the fact
that the user has already logged on to the Windows machine.  The
underlying technology is Kerberos, and FreeTDS on a Linux machine
configured with Kerberos can also log in to SQL Server without a
password.  

The difference is this: with Kerberos or SSPI, the application can log
in to SQL Server without a password.  Authentication credentials are
provided by a third machine (that Kerberos calls a KDC and Microsoft
calls a PDC).  Without them, the user provides a username & password,
and SQL Server authenticates the client all by itself.  

HTH.  

--jkl