Mssql Agent

PyPI version Python License: MIT

A Model Context Protocol (MCP) server that provides tools for interacting with Microsoft SQL Server databases and managing SQL Server Agent Jobs. Beyond basic database query functionality, this server supports managing stored procedures and SQL Server Agent jobs as code. Users can easily export, edit, and update these objects, and integrate them into version control systems.

Features

This MCP server provides the following tools:

Database Tools

Tool	Description
query	Execute SELECT queries and return results
execute	Execute INSERT, UPDATE, DELETE, CREATE statements
list_tables	List all tables in the database
describe_table	Get table schema including columns, types, and constraints
list_databases	List all databases on the server
get_table_sample	Get sample rows from a table
get_table_indexes	Get indexes defined on a table
get_foreign_keys	Get foreign key relationships

Stored Procedure Tools

Tool	Description
list_procedures	List all stored procedures in the current database (optionally include definitions)
list_all_procedures	List all stored procedures across all databases on the server
get_procedure_details	Get detailed info about a procedure including its full definition
get_procedure_parameters	Get parameters for a specific stored procedure
export_procedures_to_files	Export procedures to SQL files with directory structure: {db}/{schema}/{procedure}.sql
update_procedure_from_file	Update a stored procedure from an edited SQL file

SQL Server Agent Job Tools

Tool	Description
list_agent_jobs	List all SQL Server Agent jobs with status and category
get_job_steps	Get all steps for a specific job including commands and flow control
get_job_details	Get detailed settings and last run info for a job
get_job_schedules	Get schedule configurations for a job
get_job_history	Get execution history for a job
export_enabled_jobs_to_files	Export all enabled jobs and steps to SQL files
update_job_step_from_file	Update an existing job step from an edited SQL file
create_job_step_from_file	Create a new job step from a SQL file (auto-renames incorrectly named files)

🌟 What Makes This Different?

While there are several MSSQL MCP servers available, this one offers unique capabilities not found in others:

Feature	Description	Why It Matters
SQL Server Agent Job Management	Full CRUD operations for Agent jobs, steps, and schedules	Most MCP servers only handle database queries - this one lets you manage your entire automation infrastructure
Jobs-as-Code Workflow	Export enabled jobs to files, edit locally, push changes back	Enable Git version control for your SQL Agent jobs - track changes, review PRs, rollback easily
Stored Procedures-as-Code	Export/import stored procedures with metadata preservation	Manage procedures like application code with proper directory structure
Smart File Naming	Auto-renames files to {step_id}_{step_name}.sql format	Prevents conflicts and maintains consistency when creating new job steps
Syntax Validation	Uses SET PARSEONLY before applying changes	Catch SQL errors before they break your production jobs
Metadata Headers	Preserves job/procedure metadata in file comments	Never lose context about when something was created or modified
Built with FastMCP	Modern, decorator-based tool definitions	Cleaner code, automatic schema generation, better maintainability

Comparison with Other MSSQL MCP Servers

Capability	This Server	Others
Basic queries (SELECT, INSERT, UPDATE)	✅	✅
Schema inspection	✅	✅
SQL Server Agent job listing	✅	❌
Job step management	✅	❌
Job schedule viewing	✅	❌
Export jobs to files	✅	❌
Update jobs from files	✅	❌
Stored procedure export/import	✅	❌
Syntax pre-validation	✅	❌
FastMCP framework	✅	❌

Stored Procedure Management

This MCP server provides a complete workflow for managing stored procedures as code:

1. Export Procedures to Files

Export all stored procedures from specified databases to a directory structure:

Use export_procedures_to_files with output_dir: "/path/to/procedures"
Optionally specify databases: ["materialdb", "salesdb"]

This creates:

procedures/
├── materialdb/
│   ├── dbo/
│   │   ├── usp_get_facility_info_data.sql
│   │   ├── usp_update_inventory.sql
│   │   └── usp_process_orders.sql
│   └── reporting/
│       └── usp_generate_report.sql
├── salesdb/
│   └── dbo/
│       └── usp_calculate_totals.sql
└── ...

SQL files contain:

• Metadata header (database, schema, procedure name, create/modify dates)
• Full procedure definition (CREATE PROCEDURE statement)

2. Edit and Update Procedures

After editing a SQL file, push changes to SQL Server:

Use update_procedure_from_file with file_path: "/path/to/procedures/materialdb/dbo/usp_get_facility_info_data.sql"

This tool:

1. Parses the file path to extract database, schema, and procedure name
2. Also reads metadata from header comments if present
3. Automatically converts CREATE PROCEDURE to ALTER PROCEDURE
4. Validates the procedure exists before updating
5. Executes the ALTER statement to update the procedure

Example Procedure File

-- Database: materialdb
-- Schema: dbo
-- Procedure: usp_get_facility_info_data
-- Created: 2026-01-05 10:30:00
-- Modified: 2026-01-26 14:22:00
-- ============================================

CREATE PROCEDURE [dbo].[usp_get_facility_info_data]
AS
BEGIN
    -- Your procedure logic here
    SELECT * FROM facility_info
END

SQL Server Agent Job Management

This MCP server provides a complete workflow for managing SQL Server Agent jobs as code:

1. Export Jobs to Files

Export all enabled (non-deprecated) jobs to a directory structure:

Use export_enabled_jobs_to_files with output_dir: "/path/to/agent_server_jobs"

This creates:

agent_server_jobs/
├── Job_Name_1/
│   ├── job_info.json          # Job metadata (schedules, description, etc.)
│   ├── 01_first_step.sql      # Step 1 SQL command
│   ├── 02_second_step.sql     # Step 2 SQL command
│   └── 03_third_step.sql      # Step 3 SQL command
├── Job_Name_2/
│   ├── job_info.json
│   ├── 01_step_one.sql
│   └── 02_step_two.sql
└── ...

job_info.json contains:

• Job ID, name, and description
• Enabled status and category
• Owner and notification settings
• Schedule configurations (frequency, intervals, active times)
• Creation and modification dates

SQL files contain:

• Metadata header with job name, step ID, step name, subsystem, and database
• The actual SQL command

2. Edit Existing Job Steps

After editing a SQL file, push changes to SQL Server:

Use update_job_step_from_file with file_path: "/path/to/agent_server_jobs/Job_Name/02_step_name.sql"

This tool:

1. Parses the file path to extract job name and step ID
2. Reads the SQL content (skipping metadata header)
3. Validates SQL syntax using SET PARSEONLY
4. Updates the job step in SQL Server using sp_update_jobstep

3. Create New Job Steps

Create a new step by adding a SQL file to a job directory:

Use create_job_step_from_file with file_path: "/path/to/agent_server_jobs/Job_Name/my_new_step.sql"

This tool automatically:

1. Validates/renames the file - If filename doesn't match {step_id}_{step_name}.sql format, it:

   • Scans existing SQL files in the folder to find used step IDs
   • Queries the database for existing step IDs
   • Determines the next available step ID
   • Renames the file (e.g., my_new_step.sql → 03_my_new_step.sql)

2. Validates SQL syntax - Checks for syntax errors before creating the step

3. Creates the step - Uses sp_add_jobstep to create the step in SQL Server

4. Updates the file - Adds proper metadata header to the SQL file

Parameters:

• file_path (required): Path to the SQL file
• database_name (optional): Target database for the step (defaults to 'master')
• auto_rename (optional): Auto-rename incorrectly named files (defaults to true)

Filename Format

SQL files must follow this naming convention:

{step_id}_{step_name}.sql

Examples:

• 01_truncate_tables.sql
• 02_load_data.sql
• 03_update_statistics.sql
• 10_cleanup.sql

The step_id determines the execution order in the job.

Prerequisites

ODBC Driver Installation

This package requires the Microsoft ODBC Driver for SQL Server. Install it based on your operating system:

macOS:

brew install unixodbc
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew install msodbcsql18 mssql-tools18

Ubuntu/Debian:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18

Windows: Download and install from Microsoft ODBC Driver for SQL Server

Requirements

Python Version

• Requires: Python >=3.10

Core Dependencies

Package	Version	Purpose
fastmcp	>=2.0.0	High-level MCP framework with automatic schema generation
pyodbc	>=5.0.0	ODBC database connectivity for SQL Server
sqlparse	>=0.5.0	SQL parsing and formatting

Note: This project uses FastMCP for cleaner, more maintainable code. FastMCP provides automatic JSON schema generation from Python type hints and decorator-based tool definitions.

Optional Dependencies

Azure Integration ([azure])

Package	Version	Purpose
azure-identity	>=1.15.0	Azure Active Directory authentication

Development ([dev])

Package	Version	Purpose
ruff	>=0.4.0	Linting and formatting

Build System

• Uses hatchling as the build backend

Installation

This package is available on PyPI.

Using pip (Recommended)

pip install mssql-agent-mcp

With Azure AD Authentication Support

pip install mssql-agent-mcp[azure]

Using uvx (No Installation Required)

uvx mssql-agent-mcp

Development Installation

git clone https://github.com/yyinhsu/mssql-agent-mcp.git
cd mssql-agent-mcp
pip install -e .

Security Configuration

Read-Only Mode (Default)

By default, write operations are disabled for safety. The server runs in read-only mode to prevent accidental data modification.

# Default: Read-only mode enabled (safe for exploration)
MSSQL_READONLY=true

To enable write operations (required for execute, update_procedure_from_file, create_job_step_from_file, etc.):

# Enable write operations (use with caution)
MSSQL_READONLY=false

Granular Permission Control

When MSSQL_READONLY=false, you can further control which operations are allowed using these environment variables:

Variable	Default	Controls
MSSQL_ALLOW_INSERT	true	INSERT, MERGE statements
MSSQL_ALLOW_UPDATE	true	UPDATE statements
MSSQL_ALLOW_DELETE	true	DELETE, TRUNCATE statements
MSSQL_ALLOW_DDL	true	CREATE, ALTER, DROP, GRANT, REVOKE, DENY
MSSQL_ALLOW_EXEC	true	EXEC, EXECUTE (also controls job step updates)

Example: Allow only SELECT and INSERT:

MSSQL_READONLY=false
MSSQL_ALLOW_INSERT=true
MSSQL_ALLOW_UPDATE=false
MSSQL_ALLOW_DELETE=false
MSSQL_ALLOW_DDL=false
MSSQL_ALLOW_EXEC=false

Example: Allow data modifications but block schema changes:

MSSQL_READONLY=false
MSSQL_ALLOW_INSERT=true
MSSQL_ALLOW_UPDATE=true
MSSQL_ALLOW_DELETE=true
MSSQL_ALLOW_DDL=false
MSSQL_ALLOW_EXEC=false

Note: These granular settings only take effect when MSSQL_READONLY=false. When MSSQL_READONLY=true, all write operations are blocked regardless of other settings.

Environment Variables

Variable	Default	Description
MSSQL_SERVER	localhost	SQL Server hostname or IP
MSSQL_DATABASE	master	Default database
MSSQL_USER	(empty)	SQL Server username (for SQL auth)
MSSQL_PASSWORD	(empty)	SQL Server password (for SQL auth)
MSSQL_PORT	1433	SQL Server port
MSSQL_DRIVER	ODBC Driver 18 for SQL Server	ODBC driver name
MSSQL_ENCRYPT	yes	Connection encryption (yes/no)
MSSQL_TRUST_SERVER_CERTIFICATE	no	Trust self-signed certificates (yes/no)
MSSQL_AUTH_MODE	sql	Authentication mode: sql, windows, or azure
MSSQL_READONLY	true	Block all write operations (true/false)
MSSQL_ALLOW_INSERT	true	Allow INSERT/MERGE (when not readonly)
MSSQL_ALLOW_UPDATE	true	Allow UPDATE (when not readonly)
MSSQL_ALLOW_DELETE	true	Allow DELETE/TRUNCATE (when not readonly)
MSSQL_ALLOW_DDL	true	Allow DDL operations (when not readonly)
MSSQL_ALLOW_EXEC	true	Allow EXEC/EXECUTE (when not readonly)

Authentication Modes

SQL Server Authentication (default):

MSSQL_AUTH_MODE=sql
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password

Windows Authentication (Integrated Security):

MSSQL_AUTH_MODE=windows
# No username/password needed - uses current Windows credentials

Azure AD Authentication:

# Install with Azure support
pip install mssql-agent-mcp[azure]

MSSQL_AUTH_MODE=azure
# Uses DefaultAzureCredential - supports managed identity, Azure CLI, etc.

Cloud Connections (Azure SQL)

For Azure SQL Database, encryption is required:

MSSQL_SERVER=your-server.database.windows.net
MSSQL_ENCRYPT=yes
MSSQL_TRUST_SERVER_CERTIFICATE=no

Configuration

Configure your database connection:

MSSQL_SERVER=localhost
MSSQL_DATABASE=your_database
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_PORT=1433
MSSQL_READONLY=false  # Enable write operations if needed

Usage with Claude Desktop

Add to your Claude Desktop configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "mssql": {
      "command": "mssql-agent-mcp",
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password"
      }
    }
  }
}

Or using uvx (no installation required):

{
  "mcpServers": {
    "mssql": {
      "command": "uvx",
      "args": ["mssql-agent-mcp"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password"
      }
    }
  }
}

Usage with VS Code

Add to your VS Code MCP configuration (.vscode/mcp.json):

{
  "servers": {
    "mssql": {
      "command": "mssql-agent-mcp",
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password"
      }
    }
  }
}

Or using uvx:

{
  "servers": {
    "mssql": {
      "command": "uvx",
      "args": ["mssql-agent-mcp"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password"
      }
    }
  }
}

Example Tool Usage

Database Operations

Query data

Use the query tool with: SELECT TOP 10 * FROM Customers

List tables

Use the list_tables tool to see all tables in the database

Describe a table

Use the describe_table tool with table: "Customers" to see its structure

Execute statements

Use the execute tool with: INSERT INTO Customers (Name) VALUES ('John Doe')

Stored Procedure Operations

List all procedures in current database

Use list_procedures to see all stored procedures
Optionally set include_definition: true to include the full procedure code

List procedures across all databases

Use list_all_procedures to see procedures from all databases
Optionally set database_filter: "material" to filter by database name

Export procedures to files

Use export_procedures_to_files with output_dir: "/home/user/procedures"
Optionally specify databases: ["materialdb", "salesdb"]

Update a procedure after editing

Use update_procedure_from_file with file_path: "/home/user/procedures/materialdb/dbo/usp_get_data.sql"

SQL Server Agent Job Operations

List all agent jobs

Use list_agent_jobs to see all jobs with their status

Export jobs to files

Use export_enabled_jobs_to_files with output_dir: "/home/user/agent_jobs"

Update an existing step after editing

Use update_job_step_from_file with file_path: "/home/user/agent_jobs/Daily_Backup/02_backup_database.sql"

Create a new step (file will be auto-renamed)

# Create a file: /home/user/agent_jobs/Daily_Backup/new_cleanup_step.sql
# With content: DELETE FROM TempTable WHERE CreatedDate < DATEADD(day, -7, GETDATE())

Use create_job_step_from_file with file_path: "/home/user/agent_jobs/Daily_Backup/new_cleanup_step.sql"

# Result: File renamed to "03_new_cleanup_step.sql" and step created in SQL Server

Development

# Install in development mode
pip install -e .

# Run the server directly
python -m mssql_mcp.server

Requirements

• Python 3.10+
• Microsoft SQL Server (any supported version)

License

MIT