freetds and pyodbc failing to connect

Question

I'm currently working on setting up a connection from a Linux box to a Microsoft SQL server. I have installed FreeTDS and pyodbc on the Linux box.

I have set up the following files: /etc/freetds/freetds.conf

[sqlserver]
    host = <ip address>
    port = 1433
    tds version = 8.0
    client charset = UTF-8

~/.odbc.ini

[sqlserver]
Description     = FreeTDS MSSQL
Driver          = FreeTDS
Servername      = <same ip as above> 
Database        = Reports
TDS_Version     = 8.0

/etc/odbcinst.ini

[FreeTDS]
Description             = FreeTDS MSSQL
Driver                  = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Driver64                = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup                   = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Setup64                 = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount              = 1
CPTimeout               = 
CPTimeToLive            = 
DisableGetFunctions     = 
DontDLCLose             = 
ExFetchMapping          = 
Threading               = 
FakeUnicode             = 
IconvEncoding           = 
Trace                   = 
TraceFile               = 
TraceLibrary            = 

When I attempt to run tsql -S sqlserver -U username -P password, I get the following error:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18452 (severity 14, state 1) from SYMPLECTIC03 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

I also tried connecting with pyodbc, in the following script:

import pyodbc
try:
    cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER=same_ip_as_above;DATABASE=Reports;UID=myusername;PWD=mypassword')
except pyodbc.Error, err:
    print err

which prints the following error:

('001', '[001] [nxDC[reD]SLSre]nbet onc odt ore (0) (SQLDriverConnect)')

Not exactly the most helpful error message.

Is there anything I am doing wrong when trying to connect?

As a side note, our db requires Windows authentication, not integrated. I can telnet to connect to the host, so that's not the issue either.

Answer 1

Setup looks fine. I had a similar thing working, which broke after the server upgraded to NTLMv2. This gave an "...untrusted domain..." error when testing tsql. * detail here

I only had to include use ntlmv2 = yes in my freetds.conf spec.

Answer 2

Using Windows Authentication can be tricky... and very ugly. For best practices:

• Create a SQL Authenticated user in SQL Server to connect with as limited privileges as possible.

• A side note, TDS Version 8.0 will work, but for the sake of being correct, you should use TDS Version 7.2: https://www.freetds.org/userguide/ChoosingTdsProtocol.html

• The TDS Version has changed in 2012 and 2014, but is backwards compatible. I haven't had any issues with pyodbc with 2012 or 2014 with Django.

• You will also need to change this line to include the TDS Version:

  cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER=same_ip_as_above;DATABASE=Reports;UID=myusername;PWD=mypassword;TDS_Version=7.2;')

Best of luck; I believe if you fix that, the rest of the stack should behave.

Regards,

-Tim