Forums

Hello,

I am trying to use pyodbc with sql server. However I am getting the following error: pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Here is my connection string: self.conn = pyodbc.connect('DRIVER={SQL Server};SERVER=<servername>;UID=<userid>;PWD=<pwd>;DATABASE=<database>')

Any ideas? Works on my local machine.

From a free account, you can only access external resources that are on our whitelist, and only via HTTP / HTTPS. Paid accounts can access any site using any protocol.

I am having similar issue, and I have a paid account. I am able to connect on my local machine. Any ideas?

Error:

DB CONNECT ATTEMPT('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

What is the datasource that you are trying to connect to? (just want to double check that you are not trying to connect to a local source)

Is your local machine running Windows? I get this error too when I try to connect to Microsoft Azure in PythonAnywhere. There are python modules you need to use on Linux/OS X machines, but they are pretty outdated. I haven't been able to connect except on Windows machines.

Interesting, thanks for letting us know! We've got the most up-to-date ODBC libraries for our version of Linux (Ubuntu 14.04 Trusty) and I know some people are using them without problems, but it's entirely possible that they just happened to be connecting to databases that just happened to be using older MS SQL servers that were compatible with our libraries.

This is not my local machine, it is a remote Windows Server 2008 R2 running a SQL Server database. I have just made a simple script just to try and make a connection, but I still get the same error:

Not a python master by any means, not sure what is wrong here.

import pyodbc

host = "XX.XX.XXX.XX"
database = "TestDB"
username = "dtinnxx"
password = "password"

print ("DB CONNECT ATTEMPT")
try:
    cs = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (host, username, password, database)
    cnxn = pyodbc.connect(cs)
    print ("SUCCESS")
except Exception as e:
    print ("Error: " + str(e))

Always errors out:

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

[edit by admin: formatting]

I think the problem is that you're giving the host IP as the Data Source Name, so it's trying to find a DSN with a name that matches that IP address. A DSN is a pre-defined hostname and driver (and potentially other things) that's defined in a configuration file like ~/.odbc.ini.

If that's correct, the connection string should be

'DRIVER={SQL Server};SERVER=<servername>;UID=<userid>;PWD=<pwd>;DATABASE=<database>'

...where <servername> is the IP address you have in your host variable, and <userid>, <pwd> and <database> are as you already have them.

Give that a go and let us know if it works.

I am having the same issue. I have a paid account and using Python 3.5 - had to pip install pyodbc for it. I can import pyodbc but even using your format above (and even on a whitelisted site) I still get the same error as above.

import pypyodbc
cnxnMS = pyodbc.connect("""DRIVER={SQL Server};SERVER='www.xmlsoccer.com';UID='me';PWD='pass';DATABASE='me'""")

Hi,

I actually had to use pymssql to get it to work. (per PA support). Not sure why pyodbc does not work. Try the following:

From a bash console, run the following two commands (adjusting the Python version from 3.5 if appropriate):

export PYMSSQL_BUILD_WITH_BUNDLED_FREETDS=1
pip3.5 install --user pymssql

Then in your code:

import pymssql

host = <your servier ip address>
username = <your db user name>
password = <your db password>
database = <your database name>
...
 conn = pymssql.connect(host, username, password, database)
 cursor = conn.cursor()

[edit by admin: formatting]

Thanks for posting that! We should have updated this forum thread after talking to you (I think it was over email?).

You're right, pymssql is the only option that works right now -- we think the problem with pyodbc is caused by a missing system library (an operating-system-level package that can't be installed using pip), and we'll get that installed for the next system update. But in the meantime, your suggestion is definitely the best one.

Hi everyone, we pushed out our new system changes today and installed the missing system library. So pyodbc should be working now!

An update to that -- it does work, but there's some slightly fiddly setup that you need to do. We've put a full guide to connecting to Microsoft SQL Server, which includes pyodbc, on the help pages.

UPDATE: The Driver is now found and working.

I followed this guide using pyodbc, using pymssql is not an option anymore since it's not supported anymore. I seem to be having problems locating the driver [TDS driver].

Getting this error message:

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

Also when i call the pyodbc.drivers() function it returns empty.

Are you using the same data source name in your connection string as you created in odbc.ini?

The odbc.ini is declared like this:

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver

and the connection string is declared like this with my own 'database', 'user' and 'pw' :

conn = pyodbc.connect('DSN=sqlserverdatasource;Database=<database>;Uid=<user>@sf.database.windows.net;Pwd=<PW>;Encrypt=yes;Connection Timeout=30;')

But its working now, so i'm happy :)

[edit by admin: formatting]

Excellent, glad you got it working! Thanks for the heads-up about pymssql, we didn't know about that. Here's a link to the notice that the project is being discontinued if anyone else is interested in the details.

We'll update the help page so that we no longer recommend it. [edit: that's done now]