7

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?

daveoncode
  • 18,900
  • 15
  • 104
  • 159
  • 1
    Check the documentation [here](http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#connecting-to-pyodbc). 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. – Gord Thompson Jan 10 '16 at 12:58
  • 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. – FlipperPA Jan 12 '16 at 19:58
  • check my updated notes ;) – daveoncode Jan 13 '16 at 08:56
  • 2
    @GordThompson and FlipperPA are correct. That seems like the ideal way to go about this. Alternatively, I recommend you use pymssql: https://github.com/pymssql/pymssql. Basic stress testing indicates it is slightly more performant than pyodbc – meet-bhagdev Feb 24 '16 at 17:24
  • 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)) – Andrei Sura Mar 22 '16 at 07:44

1 Answers1

4

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.

The Aelfinn
  • 13,649
  • 2
  • 54
  • 45