How to connect to Azure SQL Database in Python using pyodbc?

I would like to connect to an Azure SQL database via pyodbc Python package. I obtained the connection string by navigating to the Azure SQL database resource and going to the Connection strings menu under the Settings group. After, I selected the ODBC tab and copied the connection string that contains the username and password authentication.

The following code tries to connect to the database:

import pyodbc


driver = "{ODBC Driver 18 for SQL Server}"
server = "<server>"
database = "<database>"
username = "<user_name>"
password = "<password>"

conn_str = str.format(
    "Driver={0};Server={1},1433;Database={2};Uid={3};Pwd={4}",
    driver,
    server,
    database,
    username,
    password,
)

connection = pyodbc.connect(conn_str)

connection.close()

However, I got the following error:

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

Solution

Before connecting to the database, you need to download a proper Microsoft ODBC Driver for SQL Server and then install it:

In Windows, download the appropriate executable and install it. For x64 architecture: https://go.microsoft.com/fwlink/?linkid=2249006
On Linux, select the proper distro then download and install the package. For example on Debian 12:

curl https://packages.microsoft.com/debian/12/prod/pool/main/m/msodbcsql18/msodbcsql18_18.3.2.1-1_amd64.deb -o msodbcsql18.deb
ACCEPT_EULA=Y dpkg --install msodbcsql18.deb

After that, you can connect to the Azure SQL database:

connection = pyodbc.connect(conn_str)

# Print out the connection information
print(connection)
print(connection.getinfo(pyodbc.SQL_DBMS_NAME))
print(connection.getinfo(pyodbc.SQL_DBMS_VER))

connection.close()

The example output would be:

<pyodbc.Connection object at 0x0000025E9F50F910>
Microsoft SQL Server
12.00.5349