SQL Server 管理与查询 MCP 服务

SqlServerMcp

SqlServerMcp is a .NET 10 Model Context Protocol (MCP) server for SQL Server administration and querying. It supports both HTTP and stdio transports and exposes a focused toolset for metadata inspection, read queries, guarded data changes, and selected administrative operations.

What It Does

• Lists database metadata such as schemas, tables, and object definitions
• Exposes runtime instance details through get_server_info
• Executes read-only SQL through query_sql
• Supports guarded UPDATE and DELETE flows with preview tokens
• Provides typed helpers for common DDL and security operations
• Returns consistent JSON-shaped responses across tools

MCP Tools

• get_database_info
• get_server_info
• list_schemas
• list_tables
• get_table_details
• get_object_definition
• query_sql
• preview_data_change
• execute_data_change
• create_table
• alter_table
• create_login
• create_user
• grant_role_membership
• execute_admin_sql

Operational Rules

• Every tool call must include a connectionString
• get_server_info is the exception and does not require any SQL connection
• Responses are normalized and include fields such as ok, operation, data, target, paging, metrics, and error when applicable
• query_sql accepts only a single SELECT or WITH statement
• preview_data_change and execute_data_change accept only simple single-target UPDATE or DELETE statements
• Statements without WHERE require allowAffectAllRows=true
• execute_data_change requires a previewToken by default unless confirmation is explicitly disabled
• Administrative tools (create_table, alter_table, create_login, create_user, grant_role_membership, execute_admin_sql) require Confirm = true in the request body when SqlServerMcp:RequireAdminConfirmation is enabled (the default)
• See docs/preview-execute-flow.md for the full preview/execute sequence diagram

Requirements

• .NET 10 SDK
• Access to a SQL Server instance
• An MCP client such as Codex CLI, ChatGPT Apps tooling, or another MCP-compatible host

Configuration

appsettings.json

{
  "SqlServerMcp": {
    "HttpPath": "/mcp",
    "SkipDmlConfirmation": false,
    "PreviewSampleLimit": 10,
    "RequireAdminConfirmation": true,
    "HttpApiKey": null,
    "RateLimit": {
      "Enabled": true,
      "PermitsPerMinute": 240,
      "MutatingPermitsPerMinute": 60
    }
  }
}

Equivalent environment variables (ASP.NET double-underscore convention):

• SqlServerMcp__HttpPath
• SqlServerMcp__SkipDmlConfirmation
• SqlServerMcp__PreviewSampleLimit
• SqlServerMcp__RequireAdminConfirmation
• SqlServerMcp__HttpApiKey
• SqlServerMcp__RateLimit__Enabled
• SqlServerMcp__RateLimit__PermitsPerMinute
• SqlServerMcp__RateLimit__MutatingPermitsPerMinute

Example curl call against the HTTP transport when an API key is configured:

curl -sS -X POST http://localhost:6191/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: $SQLSERVERMCP_API_KEY" \
  --data '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'

Run Locally

HTTP transport:

dotnet run --project ./SqlServerMcp/SqlServerMcp.csproj

stdio transport:

dotnet run --project ./SqlServerMcp/SqlServerMcp.csproj -- --transport stdio

MCP Client Examples

HTTP server:

{
  "servers": {
    "SqlServerMcp": {
      "type": "http",
      "url": "http://localhost:6191/mcp"
    }
  }
}

stdio server:

{
  "servers": {
    "SqlServerMcp": {
      "type": "stdio",
      "command": "dotnet",
      "args": [
        "run",
        "--project",
        "/absolute/path/to/SqlServerMcp/SqlServerMcp.csproj",
        "--",
        "--transport",
        "stdio"
      ]
    }
  }
}

Codex CLI Example

[mcp_servers.SqlServerMcp]
command = "dotnet"
args = [
  "run",
  "--project",
  "/absolute/path/to/SqlServerMcp/SqlServerMcp.csproj",
  "--",
  "--transport",
  "stdio"
]
startup_timeout_sec = 30.0

If you need environment variables for the stdio process, set them before launching Codex or register the server with codex mcp add --env:

codex mcp add SqlServerMcp --env SqlServerMcp__SkipDmlConfirmation=false --env SqlServerMcp__PreviewSampleLimit=10 -- dotnet run --project D:/absolute/path/to/SqlServerMcp/SqlServerMcp.csproj -- --transport stdio

For an already running HTTP instance:

[mcp_servers.SqlServerMcp]
url = "http://localhost:6191/mcp"
startup_timeout_sec = 30.0

The HTTP root endpoint returns the same payload exposed by get_server_info:

curl http://localhost:6191/

Prompt Example: Inspect the Active Server Instance

Use get_server_info before debugging a reported issue to confirm which server instance is actually responding.

Natural-language prompt:

Call get_server_info and show me which SqlServerMcp instance is responding, including transport, environment, build version, content root, and process id.

Expected response shape:

{
  "ok": true,
  "operation": "get_server_info",
  "data": {
    "server": "SqlServerMcp",
    "assemblyVersion": "1.0.0.0",
    "informationalVersion": "1.0.0",
    "environment": "Production",
    "transport": "http",
    "httpPath": "/mcp",
    "skipDmlConfirmation": false,
    "previewSampleLimit": 10,
    "contentRoot": "D:/MCP/sqlserver-mcp",
    "processId": 21588
  },
  "target": null,
  "paging": null,
  "metrics": {
    "durationMs": 3
  }
}

Prompt Example: List All Tables

Use list_tables when you want the server to return the tables available in a database.

Natural-language prompt:

List all user tables in this SQL Server database. Use this connection string:
Server=tcp:sql-demo.example.net,1433;Database=SalesDb;User ID=app_reader;Password=ReplaceWithStrongPassword123!;Encrypt=True;TrustServerCertificate=False;

Do not include views or system objects. Return the first page with the default page size.

Equivalent MCP payload:

{
  "connectionString": "Server=tcp:sql-demo.example.net,1433;Database=SalesDb;User ID=app_reader;Password=ReplaceWithStrongPassword123!;Encrypt=True;TrustServerCertificate=False;",
  "includeViews": false,
  "includeSystemObjects": false,
  "page": 1,
  "pageSize": "25"
}

If you want all tables in a single response, request all rows explicitly:

{
  "connectionString": "Server=tcp:sql-demo.example.net,1433;Database=SalesDb;User ID=app_reader;Password=ReplaceWithStrongPassword123!;Encrypt=True;TrustServerCertificate=False;",
  "includeViews": false,
  "includeSystemObjects": false,
  "page": 1,
  "pageSize": "*"
}

Optional filters for list_tables:

• schema: restrict results to a schema such as "dbo"
• includeViews: set to true to include views
• includeSystemObjects: set to true to include system-shipped objects
• page and pageSize: control pagination

Expected response shape:

{
  "ok": true,
  "operation": "list_tables",
  "data": [
    {
      "schema": "dbo",
      "name": "Customers",
      "objectType": "USER_TABLE",
      "approximateRowCount": 1204
    },
    {
      "schema": "sales",
      "name": "Orders",
      "objectType": "USER_TABLE",
      "approximateRowCount": 45218
    }
  ],
  "target": {
    "server": "sql-demo.example.net",
    "database": "SalesDb"
  },
  "paging": {
    "page": 1,
    "pageSize": "25",
    "returnedRows": 2,
    "isUnbounded": false
  },
  "metrics": {
    "durationMs": 18
  }
}

Do not publish real credentials in prompts, screenshots, or shared documentation. Keep public examples on realistic placeholders like the one above.

Troubleshooting / Deployment Alignment

If a tool behaves differently from what the repo code suggests, call get_server_info first.

• Confirm contentRoot, transport, environment, and processId match the instance you intended to update.
• Compare assemblyVersion and informationalVersion before and after a redeploy or restart.
• If get_server_info still points to an old deployment path or process after you publish new code, fix the deployment or restart first.
• Do not assume a functional bug in the repo until the active instance identity matches the build you expect.

Security Notes

• The HTTP transport does not implement authentication by itself
• Treat this service as internal unless protected by network controls, a reverse proxy, or external authentication
• Effective permissions are determined by the connectionString supplied by the client
• Connection strings are used per request and are not persisted by the server

License

This project is licensed under the MIT License. See LICENSE.