Search code examples
sql-servermacosodbcfreetds

Connect to MS SQL Server Database Using isql on Mac

Summary: tsql connects and isql doesn't.

Details: I have tried every official installation guide and Stack Overflow question I can find, but none of them produce joy. I'm trying to connect to a MS SQL Server database from my Mac (Mohave), and ultimately a Xubuntu 16.04 virtual machine as well, using pyodbc. Before getting into the Python, I just want to trouble-shoot the connection. The problem is, most of the documentation I'm seeing is missing important details such as, "Should I use quotes in usernames and passwords?" Or, "Should I include the domain in the username?" And if I should include the domain the username, "Should I escape the backslash character?"

Here is my current setup: tsql -C produces 

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.11
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes

The contents of /usr/local/etc/freetds.conf are:

#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
    tds version = auto

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    # To reduce data sent from server for BLOBs (like TEXT or
    # IMAGE) try setting 'text size' to a reasonable limit
;   text size = 64512

    # If you experience TLS handshake errors and are using openssl,
    # try adjusting the cipher list (don't surround in double or single quotes)
    # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

# A typical Sybase server
[egServer50]
    host = symachine.domain.com
    port = 5000
    tds version = 5.0

# A typical Microsoft server
[egServer73]
    host = ntmachine.domain.com
    port = 1433
    tds version = 7.3

[my_server]
    host = my_server
    port = 1433
    tds version = 7.3

The my_server is the server to which I'm trying to connect. The command tsql -S my_server -U domain\\username -P password, with no quotes anywhere, connects just fine, with the result

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

So much for FreeTDS. For the ODBC side, first note that FreeTDS is apparently already set up for ODBC, as it says unixodbc: yes in the results of tsql -C. My /etc/odbcinst.ini file contains:

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

My /etc/odbc.ini file contains

[my_server]
Description = "The Location of the EOAM_RA Database"
Driver = FREETDS
Trace = Yes
TraceFile = /tmp/sql.log
Database = my_database
Servername = my_server
UserName = domain\\username
Password = password
Port = 1433
Protocol = 7.3
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No

But when I try any version of isql -v my_server username password, I get the dreaded and incredibly unhelpful

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

So, while I certainly would LOVE to have the right settings for everything so that I can connect via isql and ultimately pyodbc, it would also help greatly if I could have the answers to the following questions:

  1. For every place where I should have my username typed in, should it have quotes around it?
  2. For every place where I should have my username typed in, should I have it prefixed with the domain?
  3. For every place where I should have my username typed in, if I do need my domain included, should I escape the backslash in-between?
  4. For servers, should I include quotes?

Many thanks for your time!

Solution

  • Have you tried connecting after using the fully qualified domain name (fqdn) for your SQL Server in the freetds.conf file:

    [my_server]
host = my_server.<yourdomain.example>
port = 1433
tds version = 7.3