MS SQL Server MCP Server

This project provides an MCP server for interacting with MS SQL Server databases via AI assistants using the Model Context Protocol (MCP).

It uses a secure profile management system leveraging the system's native credential store (via the keyring library) to avoid sending passwords over MCP.

Prerequisites

For Direct Python Execution:

Python 3.10+
Pip (Python package installer)
ODBC Driver for SQL Server installed on the machine running the server.
keyring library dependencies might require system packages (e.g., dbus-launch, gnome-keyring or kwallet on Linux, see keyring documentation for details).

For Docker Execution:

Docker installed and running.
The Dockerfile is configured to use the keyrings.cryptfile backend for secure password storage within the container's filesystem. This requires mounting a volume for persistence.

Installation

Option 1: Direct Python Execution

Clone the repository (if applicable):

git clone <repository_url>
cd mcp-server-mssql

Create and activate a Python virtual environment:

python -m venv venv
# Activate (Windows PowerShell): .\venv\Scripts\Activate.ps1
# Activate (macOS/Linux): source venv/bin/activate

Install dependencies:
```
pip install -r requirements.txt
```

Option 2: Docker Execution

Clone the repository (if applicable).
Build the Docker image:
```
docker build -t mcp-mssql-server .
```
(Note: The Dockerfile includes ODBC driver installation and configures the keyrings.cryptfile backend.)

Configuration: Profile Management

This server uses connection profiles to manage database credentials securely.

Profiles File (profiles.json): Stores non-sensitive details (driver, server, database, username) locally in the server's directory. This file is created automatically if it doesn't exist. Ensure this file is added to your .gitignore.
System Keyring: Passwords are stored securely in your operating system's credential manager (like Windows Credential Manager, macOS Keychain) using the keyring library under the service name mcp-mssql-server.
Adding Profiles: Use the add_connection_profile tool. When first adding a profile, you will be prompted in the terminal where the server is running to securely enter the password. This password is never sent via MCP.
Managing Profiles: Use list_connection_profiles and remove_connection_profile tools.

Usage

Option 1: Direct Python Execution (Stdio)

Activate the virtual environment.

Configure in MCP Client Settings (e.g., mcp_settings.json):

{
  "mcpServers": {
    // ... other servers ...
    "mssql": {
      "command": "python", // Or full path to venv python
      "args": ["D:/path/to/your/mcp-server-mssql/server.py"],
      "cwd": "D:/path/to/your/mcp-server-mssql",
      "alwaysAllow": [
        "add_connection_profile",
        "list_connection_profiles",
        "remove_connection_profile",
        "read_table_rows",
        "create_table_records",
        "update_table_records",
        "delete_table_records",
        "get_table_schema",
        "list_tables"
      ]
    }
    // ... other servers ...
  }
}

Adjust paths accordingly.

Restart your MCP Client/IDE Extension. Add profiles using the add_connection_profile tool (requires console interaction for password).

Option 2: Docker Execution (Stdio)

Ensure the Docker image mcp-mssql-server is built.

Configure in MCP Client Settings:

{
  "mcpServers": {
    // ... other servers ...
    "mssql-docker": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "--env-file", "./mcp-server-mssql/.env_docker", // Example: Store KEYRING_CRYPTFILE_PASSWORD here
        "-v", "mssql_keyring_data:/keyring_data", // Mount volume for keyring data
        "mcp-mssql-server"
      ],
      "alwaysAllow": [
        "add_connection_profile", // Note: Password prompt will appear in container logs
        "list_connection_profiles",
        "remove_connection_profile",
        "read_table_rows",
        "create_table_records",
        "update_table_records",
        "delete_table_records",
        "get_table_schema",
        "list_tables"
      ]
    }
    // ... other servers ...
  }
}

Restart your MCP Client/IDE Extension.
Important:
- The Dockerfile is configured to use the keyrings.cryptfile backend. You MUST provide a strong password for encrypting the keyring file via the KEYRING_CRYPTFILE_PASSWORD environment variable when running the container. It is highly recommended to use a .env file (like the example .env_docker in the args) or Docker secrets, rather than passing the password directly on the command line.
- A Docker volume (e.g., mssql_keyring_data in the example) MUST be mounted to /keyring_data inside the container to persist the encrypted keyring file (keyring_pass.cfg) and the profiles.json file across container restarts.
- The add_connection_profile password prompt will still appear in the container's logs/terminal.

Available Tools

Profile Management:

add_connection_profile(profile_name: str, driver: str, server: str, database: str, username: str)
list_connection_profiles()
remove_connection_profile(profile_name: str)

Database Operations (Requires profile_name):

read_table_rows(profile_name: str, table_name: str, ...)
create_table_records(profile_name: str, table_name: str, records: List[Dict])
update_table_records(profile_name: str, table_name: str, updates: Dict, filters: Dict)
delete_table_records(profile_name: str, table_name: str, filters: Dict)
get_table_schema(profile_name: str, table_name: str)
list_tables(profile_name: str, schema: Optional[str] = None)

Refer to the docstrings within server.py for detailed argument descriptions.

Example Tool Usage (Conceptual)

Add a profile (requires console interaction on server): "Using the mssql server, call add_connection_profile with name SalesDB_UK, driver {ODBC Driver 17 for SQL Server}, server myserver.example.com, database SalesDB, username report_user." (Then enter password in server console).
Use the profile: "Using the mssql server, call read_table_rows using profile SalesDB_UK for the table Customers. Filter where Country is 'UK'."

The AI assistant would construct the second call like:

// Conceptual MCP Tool Call
{
  "tool_name": "read_table_rows",
  "arguments": {
    "profile_name": "SalesDB_UK",
    "table_name": "Customers",
    "filters": {
      "Country": "UK"
    }
  }
}