Connect to SQL Server using SQLAlchemy

Question

I'm trying to connect to a SQL Server Express database using SQLALchemy and pyodbc, but I'm continuously getting the error:

(pyodbc.Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

And I really don't understand if my engine url is wrong or what else. My scenario is the following:

• I'm on a Mac
• I have a docker container (based on a Debian image with unixodbc and unixodbc-dev) in which my python app tries to connect to...
• a virtualbox virtual machine running windows 8 with SQL express 2014...

I configured a user for the SQL express, with SQL Server authentication:

• user: ar_user
• password: ar_psw

...then:

• I configured TCP ports as 1433 and disabled dynamic ports (SQL Server Configuration Manager > Network Configurations > Protocols).
• I turned off Windows Firewall.
• I used an Host-only adapter for the VM running windows8

now...

The VM is accessible from the host (my mac), since a:

ping -c 3 vm-ip

succeed!

But although I tried every possible permutation of user, password, ip, server name and port:

• 'mssql+pyodbc://ar_user:ar_psw@vm-ip/master'
• 'mssql+pyodbc://ar_user:ar_psw@vm-ip:1433/master'
• 'mssql+pyodbc://IE10WIN8\\SQLEXPRESS'
• 'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS'
• 'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS:1433'
• 'mssql+pyodbc://ar_user:ar_psw@IE10WIN8\\SQLEXPRESS:1433/master'

...and many more!

I always get the "datasource not found error". What should I do?

ps: the vm is pingable even in the docker container!

UPDATE (solved but not 100%):

I solved in this way:

I configured FreeTDS driver using /etc/odbcinst.ini in this way:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
client charset = UTF-8

and in /etc/freetds/freetds.conf:

[global]
tds version = 7.3
client charset = UTF-8

Then I created the engine using the following string:

'mssql+pyodbc://my_user:my_psw@machine_ip:1433/my_db?driver=FreeTDS'

It seems to work properly, but I get this warning:

SAWarning: Unrecognized server version info '95.12.255'. Version specific behaviors may not function properly. If using ODBC with FreeTDS, ensure TDS_VERSION 7.0 through 7.3, not 4.2, is configured in the FreeTDS configuration.

I also defined the TDS version using environment variables but it doesn't fix the issue... any idea?

Cavil

Check the documentation here. You either need to supply a DSN name (as defined in your "odbc.ini" file) or you need to explicitly supply the driver= name in the connection URL. In your case a DSN connection would probably be the safest choice. – Horseweed

Gord is on right, DSN is the way to go. Also, make sure you've forwarded the appropriate ports (1433) from guest VM to host Mac. – Poised

check my updated notes ;) – Cavil

@GordThompson and FlipperPA are correct. That seems like the ideal way to go about this. Alternatively, I recommend you use pymssql: github.com/pymssql/pymssql. Basic stress testing indicates it is slightly more performant than pyodbc – Ard

I tried to connect with both "mssql+pyodbc:///?odbc_connect=" and params = parse.quote( "Driver={{FreeTDS}};Server={};Port=1433;" "Database={};UID={};PWD={};" .format(db_host, db_name, db_user, db_pass)) create_engine('mssql+pyodbc:///?odbc_connect={}'.format(params) and both times I got base.py:1586: SAWarning: Unrecognized server version info '95.12.255'. If using ODBC with FreeTDS, ensure TDS_VERSION 7.0 through 7.3, not 4.2, is configured in the FreeTDS configuration. ".".join(str(x) for x in self.server_version_info)) – Samalla

Answer 1

I wrote a tutorial here of how to do this. Essentially, you need to:

1. brew install unixodbc
2. brew install freetds --with-unixodbc
3. Add the freetds driver to odbcinst.ini
4. Add a DSN (Domain Source Name) to odbc.ini named "MY_DSN"
5. pip install pyodbc
6. e = create_engine("mssql+pyodbc://username:password@MY_DSN")

The walkthrough here does a much more thorough job of explaining this, including issues with SQL Server/FreeTDS Protocol Version Compatibility.

Gameness