Test SQL Server Connection Using Python and pyodbc

A simple Python script to test connectivity to a Microsoft SQL Server database using pyodbc and ODBC Driver 18 for SQL Server.

This example uses Windows authentication (Trusted_Connection=yes), enables encryption, and verifies the connection by returning the current database and SQL Server user.

Prerequisites

Install pyodbc:

pip install pyodbc

You also need Microsoft ODBC Driver 18 for SQL Server installed on the machine running the script.

Script

import pyodbc

SERVER = "your-server-name"

DATABASE = "your-database-name"

connection_string = (

    "DRIVER={ODBC Driver 18 for SQL Server};"

    f"SERVER={SERVER};"

    f"DATABASE={DATABASE};"

    "Trusted_Connection=yes;"

    "Encrypt=yes;"

    "TrustServerCertificate=yes;"

    "Connection Timeout=10;"

)

try:

    print("Connecting...")

    conn = pyodbc.connect(connection_string)

    print("✅ Connection successful!")

    cursor = conn.cursor()

    cursor.execute("SELECT DB_NAME(), SUSER_SNAME()")

    database, user = cursor.fetchone()

    print(f"Database: {database}")

    print(f"Connected user: {user}")

    cursor.close()

    conn.close()

except pyodbc.Error as e:

    print("❌ Connection failed:")

    print(e)

Configuration

Replace these values with your SQL Server details:

SERVER = "your-server-name"

DATABASE = "your-database-name"

For a named SQL Server instance:

SERVER = "server-name\\instance-name"

For a specific port:

SERVER = "server-name,1433"

Connection Settings

Verify the Connection

The script executes:

SELECT DB_NAME(), SUSER_SNAME()

This returns the database name and SQL Server user associated with the current connection.

Example output:

Connecting...

✅ Connection successful!

Database: MyDatabase

Connected user: DOMAIN\username

Troubleshooting

If the connection fails, check the following:

1. SQL Server is running and accessible from the machine running the script.

1. The server and database names are correct.

1. The required ODBC Driver version is installed.

1. Your Windows account has permission to access the database.

1. The SQL Server firewall allows the required port.

1. TCP/IP is enabled for SQL Server if connecting remotely.