PYODBC--Data source name not found and no default driver specified

by Michael Szul on

No ads, no tracking, and no data collection. Enjoy this article? Buy us a ☕.

Python has been an amazing language for a very long period of time, supplanting Perl after the failure of Perl 6 to materialize into the promised revamp it was supposed to be. With the rise of data science and machine learning, Python's stock has continued to skyrocket as the language of choice for many programmers in data science thanks to packages like Pandas and Numpy.

Sometimes though, Python can still feel a bit cobbled together when trying to use it on a Windows machine. Ever move from one machine to another, and then suddenly get the following error:

PYODBC--Data source name not found and no default driver specified

Everything was working fine on your machine, but a production deployment is suddenly blowing up.

The problem is that there are multiple ODBC drivers available for SQL Server, and a driver name could be different on your production machine than your local machine.

For example, maybe you have this:

DATABASE='DRIVER={SQL Server Native Client 11.0};SERVER=database.example.com;DATABASE=MYDB;UID=MYUSER;PWD=HulkSad!'

In this example, the driver is named {SQL Server Native Client 11.0}, but in production, it might be {SQL Server}.

In fact, there are numerous drivers:

  • {ODBC Driver 11 for SQL Server} for SQL Server 2005 - 2014
  • {ODBC Driver 13 for SQL Server} for SQL Server 2005 - 2016
  • {ODBC Driver 13.1 for SQL Server} for SQL Server 2008 - 2016
  • {ODBC Driver 17 for SQL Server} for SQL Server 2008 - 2017

The ones above are the ones recommended by Microsoft moving forward; however, there are a handful of deprecated drivers that you'll likely need to consider:

  • {SQL Server} for SQL Server 2000
  • {SQL Native Client} for SQL Server 2005
  • {SQL Server Native Client 10.0} for SQL Server 2008
  • {SQL Server Native Client 11.0} for SQL Server 2012

The one you need depends on the version of SQL Server that you're running. You can get this information in the Administrative Tools section of the Control Panel in Windows. Just click on Data Sources and look for the Drivers section. Alternatively, you could always use trial-and-error.