Accessing SQL Server Configuration Manager with Python

Question

I'm using Python with the 'wmi' and 'pyodbc' libraries in order to automate a server inventory of a list of VMs. Per VM, I'm trying to get a list of all MS SQL Server products, the SQL Server database instance name, number of databases, databases on that engine, engine version, etc. and will eventually put it on a excel spreadsheet. For now, I'm simply line printing on the console with information from just one machine.

I'm able to get most of my information by running a T-SQL query using metadata function SERVERPROPERTY, and then running a cursor and printing the information by line. For example:

SERVERPROPERTY('MachineName')
SERVERPROPERTY('ProductVersion')
SERVERPROPERTY('Edition')

The only hiccup that I have is gathering all of the installed SQL Server Services that are displayed in SQL Server Configuration Manager, specifically the SQL Server Services tab as shown below: SQL Server Configuration Manager

Is there another library, or pyodbc, that is able to access SQL Server Configuration Manager in order for me to get this list of services?

As a side note, I used wmi's win32_Product class to try to retrieve anything related to SQL Server, but it returns too much much unwanted returns, like language packs, drivers, etc.

server_connection = wmi.WMI(server, user=username, password=password)
task = server_connection.Win32_Product()
`for service in task:`
`if 'SQL Server' in service.Caption:`
`print(f"Service Name: {service.Caption}")`

Answer 1

If you're trying to retrieve a list of SQL Server services (as seen in SQL Server Configuration Manager) using Python, the best approach is to use WMI (Windows Management Instrumentation). The win32_Product class retrieves too many irrelevant results (e.g., drivers, language packs), so instead, you should use win32_service.

import wmi

server = "your_server_name"  # Change this to your target server
username = "your_username"  # If needed
password = "your_password"  # If needed

# Connect to the machine
server_connection = wmi.WMI(server, user=username, password=password)

# Get SQL Server-related services
for service in server_connection.Win32_Service():
    if "SQL" in service.Name or "SQL" in service.DisplayName:
        print(f"Service Name: {service.Name}")
        print(f"Display Name: {service.DisplayName}")
        print(f"State: {service.State}")
        print(f"Start Mode: {service.StartMode}")
        print("-" * 50)

SQL Server services typically includes MSSQLSERVER (Default instance), MSSQL$InstanceName (Named instances), SQLSERVERAGENT, SQLBrowser, SQLWriter and so on.

If you need all installed SQL Server instances (even if they are not currently running), query the Windows Registry:

import winreg

def get_sql_instances():
    sql_instances = []
    reg_path = r"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"

    try:
        reg_key = winreg.OpenKey(winreg.HKEY_LOCAL_MACHINE, reg_path)
        i = 0
        while True:
            try:
                instance_name, service_name, _ = winreg.EnumValue(reg_key, i)
                sql_instances.append((instance_name, service_name))
                i += 1
            except OSError:
                break
    except FileNotFoundError:
        print("SQL Server registry path not found.")

    return sql_instances

# Example usage
for instance, service in get_sql_instances():
    print(f"Instance Name: {instance} - Service Name: {service}")