如何在Python中无需账号密码检测多个Microsoft SQL数据库是否在线

Got it, let's tackle your two needs—testing SQL Server connectivity without hardcoding credentials, and checking the online status of multiple databases—using pyodbc.

1. Test Single SQL Server Connectivity Without Explicit Credentials #

The key here is using Windows Integrated Authentication (if you're on a Windows machine joined to a domain, or accessing a local SQL Server). This lets your script use your current Windows user's permissions to attempt a connection, no need to pass a username/password explicitly.

Here's a simple script that returns a boolean indicating if the server is reachable, plus a human-readable status message:

import pyodbc

def is_sql_server_online(server_name, database_name="master"):
    try:
        # Use Trusted Connection for Windows auth (no username/password required)
        conn_str = f"""
            DRIVER={{ODBC Driver 17 for SQL Server}};
            SERVER={server_name};
            DATABASE={database_name};
            Trusted_Connection=yes;
            Connection Timeout=5;  # Short timeout to avoid hanging on unreachable servers
        """
        # We only need to attempt opening a connection—no queries required for a basic online check
        with pyodbc.connect(conn_str):
            return True, f"✅ {server_name}\\{database_name} is online"
    except pyodbc.Error as e:
        # Catch connection-related errors (server unreachable, network issues, etc.)
        return False, f"❌ {server_name}\\{database_name} is offline or unreachable: {str(e)}"

# Example usage
status, message = is_sql_server_online("MY-SERVER-NAME")
print(message)

Quick Notes: #

• Ensure the ODBC Driver 17 for SQL Server is installed on your machine (it's the most stable, widely supported version).
• We use the master database by default because it's always present on a running SQL Server—you don't need special permissions to access it for a basic connection test.
• Tweak the Connection Timeout value to balance speed and accuracy for your environment.

2. Bulk Check Multiple SQL Server Databases #

To check multiple servers or databases, wrap the above function in a loop over a list of target pairs. You can structure your targets as tuples containing server and database names:

def check_multiple_sql_servers(targets):
    results = []
    for server, db in targets:
        is_online, msg = is_sql_server_online(server, db)
        results.append({
            "server": server,
            "database": db,
            "is_online": is_online,
            "status_message": msg
        })
    return results

# Example list of targets (replace with your own servers/databases)
sql_targets = [
    ("PROD-SERVER-01", "master"),
    ("PROD-SERVER-02", "CustomerDB"),
    ("DEV-SERVER-01", "master"),
    ("TEST-SERVER-01", "InventoryDB")
]

# Run the bulk check
bulk_results = check_multiple_sql_servers(sql_targets)

# Print formatted results
for result in bulk_results:
    print(f"Server: {result['server']} | DB: {result['database']} | Online: {result['is_online']}")
    print(f"Status: {result['status_message']}\n")

What This Does: #

• Iterates through each target server/database pair
• Returns a structured list of results with boolean status and detailed messages
• Easy to extend—you could write results to a CSV, log file, or even a monitoring dashboard if needed

Important Considerations #

• If you're not on Windows, or need to use SQL Server Authentication without hardcoding credentials, you could pull credentials from environment variables (but since you asked for no credentials, Integrated Auth is the ideal approach).
• Some errors might stem from permission issues rather than the server being offline—you can add more granular error handling if you need to distinguish between these cases.

内容的提问来源于stack exchange，提问作者Jackson Reid Jenkins