MSSQL Agent MCP

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

Stored Procedure Tools

SQL Server Agent Job Tools

🌟 What Makes This Different?

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

Comparison with Other MSSQL MCP Servers

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)

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

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

1. 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

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])

#### Development ([dev])

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:

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

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