pyodbc says the data source name was not found and no default driver was specified
pyodbc.InterfaceError: IM002 Data source name not found and no default driver specifiedShort answer
IM002 means the ODBC driver manager could not match the DRIVER or DSN in your connection string to anything it has registered. Compare the name in the string with the list from odbcinst, character for character. Then check the driver is installed and, if you use DSN=, that the DSN exists in the odbc.ini the manager reads.
Fix it fast
1Find out which cause you have
List the drivers the ODBC driver manager has registered, and where it reads its files from. If your driver name is not in the first list, the string cannot match.
odbcinst -q -d odbcinst -j2Match what you see, then jump to its fix
- 1
odbcinst -q -d prints a driver, for example [ODBC Driver 18 for SQL Server], but your string says something else such as {SQL Server} or {ODBC Driver 17 for SQL Server}The Driver= name does not match a registered driverFix - 2
odbcinst -q -d lists nothing, or the command is not found; typical in a fresh Docker image or CI runnerThe ODBC driver is not installed on this machine or imageFix - 3
The connection string starts with DSN=name, or contains no Driver at allThe string uses DSN= but no such data source is definedFix
- 1
What does the error look like?
pyodbc.InterfaceError: IM002 Data source name not found and no default driver specifiedData source name not found, and no default driver specified
Cause 1 of 3
The Driver= name does not match a registered driver
1Check
The driver manager compares the name literally. Microsoft's installer registers the driver under the name ODBC Driver 18 for SQL Server, and you can query that exact name.
odbcinst -q -d -n "ODBC Driver 18 for SQL Server"2Fix
Copy the registered name into the connection string. Braces around the name are the ODBC way to quote a value, and pyodbc hands the string to the driver manager unchanged.
import pyodbc
conn = pyodbc.connect(
"Driver={ODBC Driver 18 for SQL Server};"
"Server=localhost,1433;Database=mydb;UID=app;PWD=secret;"
"TrustServerCertificate=yes"
)3Verify
Run the connection again and fetch one row.
python -c "import pyodbc; print(pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;UID=app;PWD=secret;TrustServerCertificate=yes').execute('select 1').fetchone())"Confirmed by learn.microsoft.com
Cause 2 of 3
The ODBC driver is not installed on this machine or image
1Check
pip install pyodbc installs only the Python binding. The SQL Server driver is a separate package from Microsoft.
odbcinst -q -d -n "ODBC Driver 18 for SQL Server"2Fix
Add the Microsoft package repository and install msodbcsql18, as Microsoft documents for Ubuntu. unixodbc-dev provides the development headers, which you need only if pyodbc has to compile.
curl -sSL -O https://packages.microsoft.com/config/ubuntu/$(grep VERSION_ID /etc/os-release | cut -d '"' -f 2)/packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
rm packages-microsoft-prod.deb
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
sudo apt-get install -y unixodbc-dev3Verify
The query now prints the driver section instead of an error.
odbcinst -q -d -n "ODBC Driver 18 for SQL Server"Confirmed by learn.microsoft.com
Cause 3 of 3
The string uses DSN= but no such data source is defined
1Check
DSN, DRIVER and FILEDSN are the standard ODBC keys. A DSN must be defined in the odbc.ini files that the driver manager reads; odbcinst -j prints where those files are.
odbcinst -j2Fix
Either add the DSN section to the odbc.ini file shown by odbcinst -j, or drop the DSN and connect with a DSN-less string that names the driver, as in cause 1.
# DSN-less: no odbc.ini needed
pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;UID=app;PWD=secret")3Verify
Connect again with the new string.
python -c "import pyodbc; print(pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;UID=app;PWD=secret;TrustServerCertificate=yes').execute('select 1').fetchone())"Confirmed by github.com
How do I stop it coming back?
- Put the driver install step in the Dockerfile or CI image, not on one person's machine.
- Keep the driver name in one config value, and print the output of odbcinst -q -d in your deploy log.
- Prefer DSN-less strings in containers, so no odbc.ini has to travel with the image.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fixDatabases
manage.py test stops with 'permission denied to create database' on PostgreSQL
django.db.utils.ProgrammingError: permission denied to create database
Django creates a separate database called test_ plus your database name, using the DATABASES USER.
2 causes, each with a fixDatabases
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fix