Connect to MSSQL Server 2008 on linux

linux, pyodbc, python, sql-server, sql-server-2008

Solution

Do you have all the software you need? This is what you need for Ubuntu 12.04:

sudo apt-get install php5-odbc php5-sybase tdsodbc

Have you configured your these files on your Linux server? (These are taken from an Ubuntu 12.04 server)

/etc/odbc.ini

# Define a connection to the MSSQL server.
# The Description can be whatever we want it to be.
# The Driver value must match what we have defined in /etc/odbcinst.ini
# The Database name must be the name of the database this connection will connect to.
# The ServerName is the name we defined in /etc/freetds/freetds.conf
# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
[mssql]
Description             = MSSQL Server
Driver                  = freetds
Database                = MyDatabase
ServerName              = mssql
TDS_Version             = 8.0

/etc/odbcinst.ini

# Define where to find the driver for the Free TDS connections.
[freetds]
Description     = MS SQL database access with Free TDS
Driver          = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount      = 1

/etc/freetds/freetds.conf

# The basics for defining a DSN (Data Source Name)
# [data_source_name]
#       host = <hostname or IP address>
#       port = <port number to connect to - probably 1433>
#       tds version = <TDS version to use - probably 8.0>

# Define a connection to the MSSQL server.
[mssql]
        host = mssql_server_ip_or_domain_name
        port = 1433
        tds version = 8.0

I've read several accounts of the tds version causing problems. It seems like 8.0 words best but I've also seen people say they got things working with 7.5 and 7.0.

Then test your connection:

isql mssql username password

Depending on your environment your username might have to be in the format: domain\username

After issuing the command you should see something like:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

And here's what I think your connect command should look like (NOTE: I don't know Python):

cnxn = pyodbc.connect('DRIVER=freetds;SERVER=FOOBAR;PORT=1433;DATABASE=T2;UID=FOO;PWD=bar;TDS_Version=8.0;')

Problem

I am wondering how to connect to a MSSQL Server 2008 from a linux machine. I do currently have FreeTDS installed - I haven't had any luck getting bsqldb working, however. I've currently been able to connect to this database using the following python code (in Windows): ``` import pyodbc cnxn = pyodbc.connect("DRIVER={SQL Server};" +"SERVER=something.example.com;" +"DATABASE=exampledatabase;" ``` I believe my Windows credentials are being passed here. Does anyone have any recommendations for what to use in linux?

Original source