The net has a lot of bad advice when it comes to making python on linux connect to Microsoft's
SQL Server. The following hopefully remedies that a bit.
Overview
Since you have linux on one machine and SQL Server / windows running on
another, you need something on the linux box that talks SQL Server's protocol (
Tabular Data
Stream) which is available via freetds.org. Too bad we only have pyodbc as a
python interface, which only speaks ODBC. To connect pyodbc to freetds, we need
an ODBC implementation for linux, namely unixodbc.
On CentOS, this becomes:
501$ sudo yum install freetds freetds-devel unixODBC unixODBC-devel
Setup freetds
First, we need to configure freetds by edit'ing /etc/freetds.conf and adding a stanza for
our server:
[sqlserver2k]
host = 10.1.1.10
port = 1433
tds version = 8.0
try domain login = yes
This config snippet says that the host sqlserver2k has the IP 10.1.1.10, is listening on port 1433,
uses tds version 8.0 (which corresponds to sql server 2000, [others]), and that I use domain authentication.
Now let's test the connection:
501$ tsql -S sqlserver2k -U 'mydomain\testuser'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
1> quit
502$
You can run some queries if you're not sure that you've connected appropriately, but at this point
freetds looks correctly installed.
Setup unixodbc
unixodbc relies on two files for configuration, /etc/odbcinst.ini and /etc/odbc.ini.
/etc/odbcinst.ini details the drivers available (like freetds) and odbc.ini lists the
DSNs / connections available. To add freetds, create a description file and feed it to the
odbcinst program:
501$ cat <<EOF > /tmp/obdcinst.ini
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/libtdsodbc.so
Trace = no
EOF
502$ sudo odbcinst -i -s -f /tmp/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc
503$
Note that your tds odbc driver may be located someplace else other than /usr/lib/.
While the docs indicate you can do roughly the same for /etc/odbc.ini, I couldn't get it to work
and neither could a bunch of other people, either... so just edit /etc/odbc.ini directly and
put the following attributes in it:
[sqlserver2k]
Description = db on sqlserver2k
Driver = FreeTDS
Servername = sqlserver2k
Port = 1433
Database = testdb
To test our setup, run the following:
501$ isql -v sqlserver2k 'mydomain\testuser' testpassword
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
unixodbc now looks correctly configured.
Setup pyodbc
Either 'sudo easy_install pyodbc', or download it directly and 'sudo python setup.py install'.
Let's test our setup with a script:
#!/usr/bin/python
import pyodbc
conn = pyodbc.connect('DSN=sqlserver2k;UID=mydomain\testuser;PWD=testpassword')
cursor = conn.cursor()
# Select some values and print them:
cursor.execute("select count(*) as cnt from test")
for row in cursor:
print row.cnt
You should now have a working install.
Annoyances
I want /etc/freetds.conf to have machines I can connect to, and /etc/odbc.ini to list DSNs that denote connections to databases on machines. However, it seems that I need to keep the names the same
across the ini section titles. Much debugging time was spent trying to figure out servernames versus servers versus addresses, the install goes easier if you keep the names the same across the board (note: I may be working from a broken mental model).
Shout-outs
Big thanks to developers at freetds.org, unixodbc.org and pyodbc.sf.net for making my life way easier than otherwise possible. Now to write some code and take advantage of these new-found powers. ;)