When using pyodbc + sqlalchemy to connect to the SQLServer database, an error occurred: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect).
After consulting the sqlalchemy mssql+pyodbc database documentation, I found that:
You need to install the ODBC driver. Additionally, if you are connecting to the database using the hostname method, you also need to specify the driver name. However, there are many types of drivers, and one driver can have different names. So I did some research.
Why are there so many drivers?#
I would like to quote and re-edit a clear description from Driver history for Microsoft SQL Server - SQL Server | Microsoft Docs:
There are three generations of Microsoft ODBC Driver for SQL Server.
- The first generation "SQL Server" ODBC driver is still provided as part of the Windows Data Access Components. It is not recommended to use this driver for new development.
- Starting from SQL Server 2005, the SQL Server Native Client includes an ODBC interface, and it is the ODBC driver that comes with SQL Server 2005 to SQL Server 2012. This driver is also not recommended for new development.
- After SQL Server 2012, the Microsoft ODBC Driver for SQL Server driver is updated with the latest server features.
SQL Server Native Client#
SQL Server 2005 introduced support for SQL Server Native Client, which can be used for ODBC connections.
SQL Server Native Client (commonly abbreviated as SNAC) was developed from SQL Server 2005 to SQL Server 2012. SQL Server Native Client 10.0 was released with SQL Server 2008. SQL Server Native Client 11.0 was released with SQL Server 2012.
You can download all historical versions here: Download SQL Server Native Client - ConnectionStrings.com
If you want to use it, just find the latest version for installation; the drivers are forward compatible. It is important to note the driver names set for different SQLServer versions:
Driver={SQL Server Native Client}(SQL Server 2005)Driver={SQL Server Native Client 10.0}(SQL Server 2008)Driver={SQL Server Native Client 11.0}(SQL Server 2012 and later)
However, Microsoft announced a new version of the ODBC Driver in 2013, which is the one below. In 2017, development on SNAC was discontinued and replaced by this driver, which has better performance and supports new features of databases after SQLServer 2012.
Microsoft ODBC Driver for SQL Server#
Latest driver download link | Historical driver download link
The usage method is also to install the driver and set the driver name.
Driver name format:
Driver={ODBC Driver XX for SQL Server}(XX is the name of the driver version you installed)
| Database Version → ↓ Driver Version | Azure SQL Database | Azure Synapse Analytics | Azure SQL Managed Instance | SQL Server 2019 | SQL Server 2017 | SQL Server 2016 | SQL Server 2014 | SQL Server 2012 | SQL Server 2008 R2 | SQL Server 2008 | SQL Server 2005 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 17.6 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | |||
| 17.5 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | |||
| 17.4 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | |||
| 17.3 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | |
| 17.2 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | ||
| 17.1 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | ||
| 17.0 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | ||
| 13.1 | Yes | Yes | Yes | Yes | Yes | Yes | |||||
| 13 | Yes | Yes | Yes | Yes | Yes | ||||||
| 11 | Yes | Yes | Yes | Yes | Yes |
SQL Version Compatibility | Table from Microsoft
Summary#
The ODBC Driver for SQL Server has better performance, with many optimizations and new features in the new version, making it the choice for development in 2021.
Different driver versions support different database versions. Please make sure to check your SQLServer version, install the corresponding driver version, and configure the driver name correctly.
I also discovered a fantastic website: ConnectionStrings
ConnectionStrings.com helps developers connect software to data. It's a straight to the point reference about connection strings, a knowledge base of articles and database connectivity content and a host of Q & A forums where developers help each other finding solutions.
ConnectionStrings.com helps developers connect software to databases. It is a direct reference about connection strings, a knowledge base of articles and database connectivity content, and a Q&A forum where developers can help each other find solutions.
Reference Links#
- sql server - Differences Between Drivers for ODBC Drivers - Stack Overflow
- ODBC Driver for SQL Server Release Notes on Windows - SQL Server | Microsoft Docs
- Installing - SQL Server Native Client | Microsoft Docs
- Driver history for Microsoft SQL Server - SQL Server | Microsoft Docs
- System Requirements, Installation, and Driver Files - SQL Server | Microsoft Docs