MsSQLServerMcp

A Model Context Protocol (MCP) server for Microsoft SQL Server, providing database exploration and query capabilities for AI assistants.

Overview

This MCP server enables AI assistants to interact with Microsoft SQL Server databases through three main tools:

• list_tables: List all tables and views in the database
• table_details: Get detailed information about a specific table including columns, data types, and primary keys
• run_query: Execute SELECT queries and return results (with safety limits)

Features

• 🔒 Secure by default: Only SELECT queries are allowed; DML/DDL operations are blocked
• 🔐 Flexible authentication: Supports both Windows Authentication and SQL Server Authentication
• 📊 Smart limits: Query results are limited to prevent overwhelming responses
• 🎯 MCP compliant: Built using the official ModelContextProtocol C# SDK

Installation

Prerequisites

• .NET 10.0 or higher
• Access to a Microsoft SQL Server instance

Build from Source

git clone <repository-url>
cd MsSQLServerMcp
dotnet build -c Release

The compiled binary will be available at:

• Windows: bin\Release\net10.0\MsSQLServerMcp.exe
• Linux/macOS: bin/Release/net10.0/MsSQLServerMcp

Configuration

Configure the server using environment variables:

Required Variables

| Variable | Description | Example | |----------|-------------|---------| | MSSQL_SERVER | SQL Server address | localhost or myserver.database.windows.net | | MSSQL_DATABASE | Database name | MyDatabase | | MSSQL_TRUSTED | Use Windows Authentication | true or false |

Authentication

Windows Authentication (Trusted=true):

set MSSQL_SERVER=localhost
set MSSQL_DATABASE=MyDatabase
set MSSQL_TRUSTED=true

SQL Server Authentication (Trusted=false):

set MSSQL_SERVER=localhost
set MSSQL_DATABASE=MyDatabase
set MSSQL_TRUSTED=false
set MSSQL_USERNAME=sa
set MSSQL_PASSWORD=your_password

Optional Variables

| Variable | Description | Default | |----------|-------------|---------| | MSSQL_PORT | SQL Server port | 1433 | | MSSQL_TRUST_CERT | Trust server certificate | true | | MSSQL_ENCRYPT | Enable encryption | true |

Usage with AI Assistants

OpenCode Configuration

Add the following to your opencode.json configuration file:

{
  "mcp": {
    "mssql-mcp-server": {
      "type": "local",
      "command": ["dotnet", "run", "--project", "MsSQLServerMcp/MsSQLServerMcp.csproj"],
      "enabled": true,
      "environment": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "TestDatabase",
        "MSSQL_USERNAME": "sa",
        "MSSQL_PASSWORD": "password@123"
      },
    },
  }
}

Claude Desktop Configuration

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "mssql": {
      "command": "C:\\path\\to\\MsSQLServerMcp.exe",
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_TRUSTED": "false",
        "MSSQL_USERNAME": "sa",
        "MSSQL_PASSWORD": "your_password"
      }
    }
  }
}

Testing with OpenCode

Once configured, you can test the MCP server by asking your AI assistant:

Show me all tables in the database

The AI will use the list_tables tool to retrieve and display all tables.

Get details about the Users table in dbo schema

The AI will use the table_details tool to show column information.

Run a query to get the top 10 users by created date

The AI will use the run_query tool to execute:

SELECT TOP 10 * FROM dbo.Users ORDER BY CreatedDate DESC

Available Tools

list_tables

Lists all tables and views in the database.

Parameters:

• schema (optional): Filter by schema name

Example:

{
  "schema": "dbo"
}

table_details

Gets detailed information about a specific table.

Parameters:

• schema (required): Schema name
• tableName (required): Table name

Example:

{
  "schema": "dbo",
  "tableName": "Users"
}

Returns:

• Column names
• Data types
• Max length (for string types)
• Numeric precision and scale
• Nullable flag
• Primary key indicator

run_query

Executes a SELECT query against the database.

Parameters:

• query (required): SQL query string (SELECT only)
• maxRows (optional): Maximum rows to return (default: 100, max: 1000)

Example:

{
  "query": "SELECT * FROM dbo.Users WHERE Active = 1",
  "maxRows": 50
}

Safety Features:

• Only SELECT statements are allowed
• INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE are blocked
• Results are limited to prevent overwhelming responses

Troubleshooting

Connection Issues

If you see "Configuration error" when starting:

1. Verify all required environment variables are set
2. Check that the SQL Server is accessible from the machine
3. For Windows Authentication, ensure the current user has database access
4. For SQL Authentication, verify username and password

Permission Denied

If queries fail with permission errors:

1. Ensure the database user has SELECT permissions on required tables
2. For table_details, the user needs access to system views (INFORMATION_SCHEMA)

Port Configuration

If SQL Server is not using the default port (1433):

set MSSQL_PORT=1434

Development

Project Structure

MsSQLServerMcp/
├── Program.cs                 # Entry point and MCP server setup
├── SqlServerConfiguration.cs  # Configuration management
├── DatabaseHelper.cs          # SQL Server operations
├── McpTools.cs               # MCP tool implementations
└── MsSQLServerMcp.csproj     # Project file

Building

dotnet build                    # Debug build
dotnet build -c Release        # Release build
dotnet publish -c Release      # Publish for distribution

License

[Your License Here]

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

Acknowledgments

• Built with ModelContextProtocol C# SDK
• Uses Microsoft.Data.SqlClient for database connectivity