# MssqlMCP

> MCP Server for MSSQL (Microsoft SQL Server)

- **Type:** MCP server
- **Install:** `agentstack add mcp-mcprunner-mssqlmcp`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [MCPRUNNER](https://agentstack.voostack.com/s/mcprunner)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [MCPRUNNER](https://github.com/MCPRUNNER)
- **Source:** https://github.com/MCPRUNNER/mssqlMCP

## Install

```sh
agentstack add mcp-mcprunner-mssqlmcp
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SQL Server MCP (Model Context Protocol) Server

This is a Model Context Protocol (MCP) server that connects to one or more SQL Server databases; designed to be used by Visual Studio Code as a Copilot Agent.

## 🚨 Recent Updates (November 2025)

**Version 1.0.9.5** - Documentation Standardization & Tool Naming Consistency

**Important**: This version includes comprehensive documentation updates and tool naming standardization. All MCP tools now use consistent `mssql_` prefixed names. Key improvements include:

- **Standardized Tool Names**: All tools now use `mssql_` prefix (e.g., `mssql_execute_query`, `mssql_get_table_metadata`)
- **Updated Documentation**: All documentation files synchronized with actual implementation
- **Enhanced Examples**: C# client examples and console applications updated with correct tool names
- **Test Suite Updates**: All tests updated and passing with new tool naming conventions
- **API Reference**: Complete API documentation refresh with accurate tool specifications

**Migration Note**: While tool functionality remains unchanged, documentation and examples have been updated for consistency. See the [RELEASE_NOTES.md](./RELEASE_NOTES.md) for complete details.

---

## 🚨 Security Updates (November 2025)

**Important**: This project received significant security enhancements in November 2025. Please review the [Security Update Documentation](./Documentation/MERGE_UPDATE_NOVEMBER_2025.md) for:

- Enhanced multi-tier API key authentication system
- Role-based access control (master, admin, user keys)
- Connection-level security restrictions
- Encrypted API key storage
- Comprehensive test coverage for security features

**Migration Required**: Existing installations must configure authentication. See the [Migration Guide](./Documentation/MERGE_UPDATE_NOVEMBER_2025.md#migration-guide) for details.

## Overview

This project implements an MCP server for SQL Server database connectivity, enabling VS Code and Copilot to interact with SQL databases via the Model Context Protocol.

Features include:

- SQL query execution
- Database metadata retrieval (tables, views, stored procedures, functions)
- Detailed schema information including primary/foreign keys
- Multi-key API authentication system with master and user-specific keys
- Connection string encryption with AES-256
- Key rotation and security management
- API key usage tracking and analytics
- Async/await for all database operations
- Robust logging with Serilog
- Clean architecture with separation of concerns
- Dependency injection for testable components
- Strongly-typed models for database metadata

## How This Works

See [How This Works](./Documentation/HowItWorks.md) for an overview of communication between Copilot, the LLM and this MSSQL Model Context Protocol Server.

## API Endpoints

This server provides three types of MCP endpoints:

1. **Standard MCP Endpoints**: Accessible via the ModelContextProtocol.AspNetCore library
2. **JSON-RPC 2.0 Endpoints**: Following the MCP JSON-RPC 2.0 protocol
   - `POST /mcp`: Handles `tools/list` and `tools/call` methods
3. **Direct REST API Endpoints**: For simpler HTTP access to specific database operations

See the following documentation:

- [MCP JSON-RPC Documentation](./Documentation/McpJsonRpc.md) for details on the JSON-RPC endpoints
- [Multi-Key Authentication System](./Documentation/MultiKeyAuthentication.md) for details on the authentication system
- [Using MCP with C#](./Documentation/UsingMcpWithCSharp.md) for C# client examples
- [Testing MCP JSON-RPC Endpoints with PowerShell](./Documentation/McpJsonRpcPowershellTesting.md) for comprehensive PowerShell testing

## Getting Started

### Prerequisites

- .NET 9.0 SDK

### Runtime Variables

Set mandatory variables prior to starting MCP Server.
| Variable Name | Mandatory | Default Value | Recommended Values (Expected Format/Type) | Description |
| :-----------------------------------| :------------------------| :--------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------- |
| `MSSQL_MCP_KEY` | Yes | The `Start-MCP-Encrypted.ps1` will generate a secure random key if this variable is unset. To generate a secure encryption key you can use `Generate-MCP-Key.ps1`, to change the current key use `Rotate-Encryption-Key.ps1` | A strong, cryptographically secure random string (e.g., 32 bytes, Base64 encoded). | The master encryption key used for AES-256 encryption of connection strings stored in the `connections.db` SQLite database. |
| `MSSQL_MCP_API_KEY` | Yes | This is the master API key (Authorization Bearer token). If not set, API key authentication might be disabled or fall back to appsettings.json if configured there. The `Set-Api-Key.ps1` script generates one. | A strong, cryptographically secure random string. | The master API key required for administrative access to the MCP server and for creating additional user API keys. User API keys are stored in the SQLite database and managed through the API. |
| `MSSQL_MCP_DATA` | No | Data (A Data subdirectory in the application's root directory) | A valid file system path to a directory. | Overrides the default directory location for storing application data, most notably the `connections.db` SQLite database file. |

### TL;DR The Quick Setup Doc

[QUICK_INSTALL](./Documentation/QUICK_INSTALL.md)

## Project Architecture

This project follows a clean architecture approach with separation of concerns:

See the [full architecural documentation](./Documentation/Architecture.md) for diagrams and detailed process flow.

### Folder Structure

- **Models/**: Entity models for database metadata (TableInfo, ColumnInfo, ForeignKeyInfo, etc.)
- **Interfaces/**: Service interfaces (IDatabaseMetadataProvider, IConnectionStringProvider, ISqlServerTools)
- **Services/**: Service implementations (DatabaseMetadataProvider, ConnectionManager, ConnectionRepository)
- **Configuration/**: Configuration-related classes (ConnectionStringProvider)
- **Tools/**: MCP tool implementations (SqlServerTools, ConnectionManagerTool, SecurityTool)
- **Extensions/**: Extension methods for service registration (ServiceCollectionExtensions, ApiSecurityExtensions)
- **Middleware/**: Middleware components (ApiKeyAuthMiddleware)
- **Scripts/**: Utility and management scripts (Start-MCP-Encrypted.ps1, Rotate-Encryption-Key.ps1, Migrate-To-Encrypted.ps1, Set-Api-Key.ps1, Assess-Connection-Security.ps1, Test-Connection.ps1, Test-Security-Features.ps1, Verify-Encryption-Status.ps1, mcp.json)
- **Documentation/**: Architecture, security, usage, and API documentation
- **Examples/**: Example scripts and usage (initialize-mcp.js, test-mcp-curl.sh, test-mcp-powershell.ps1)
- **Logs/**: Log files (daily rolling logs)
- **Tests/**: Test code
- **Data/**: SQLite database for connection storage (connections.db)
- **appsettings.json / appsettings.Development.json**: Application configuration
- **Dockerfile**: Containerization support
- **mssqlMCP.sln / mssqlMCP.csproj**: .NET solution and project files
- **CopilotAgent.md / Overview.md**: Additional documentation

### Key Components

- **DatabaseMetadataProvider**: Service for retrieving database schema information
- **ConnectionStringProvider**: Service for managing database connection strings
- **ConnectionManager**: Manages connection storage and retrieval
- **ApiKeyManager**: Manages API key lifecycle and validation
- **ApiKeyRepository**: Stores and retrieves encrypted API keys
- **SqlServerTools**: MCP tools implementation for SQL Server operations
- **ConnectionManagerTool**: MCP tool for managing connections
- **SecurityTool**: MCP tool for security operations (encryption, key rotation, etc.)
- **ApiKeyManagementTool**: MCP tool for managing API keys
- **ServiceCollectionExtensions**: Extension methods for registering services with dependency injection
- **ApiKeyAuthMiddleware**: Middleware for multi-key API authentication
- SQL Server instance (local or remote)
- Visual Studio Code with Copilot extension

### Docker Image

To run via a pre-built docker image download and run via hub.docker.com [mcprunner/mssqlmcp](https://hub.docker.com/r/mcprunner/mssqlmcp)

### Installation and Setup

See [QUICK_INSTALL](./Documentation/QUICK_INSTALL.md)

## Connection Management

This project includes a robust connection management system that allows you to:

1. **Store and manage multiple database connections** using SQLite as a persistent storage backend
2. **Add, update, and remove connections** programmatically or through the MCP interface
3. **Test connection strings** before saving them
4. **Use connections across different tools** with a unified interface

### CORS Configuration

The MCP server includes built-in CORS support that can be configured through `appsettings.json`. This is particularly useful when integrating with web-based tools like Open WebUI.

CORS settings can be configured in the `Cors` section of `appsettings.json`:

```json
{
  "Cors": {
    "AllowedOrigins": ["http://localhost:3000", "http://localhost:8080"],
    "AllowedMethods": ["GET", "POST", "PUT", "DELETE", "OPTIONS", "PATCH"],
    "AllowedHeaders": [
      "Content-Type",
      "Authorization",
      "X-API-Key",
      "X-Requested-With",
      "Accept",
      "Origin",
      "Access-Control-Request-Method",
      "Access-Control-Request-Headers"
    ],
    "AllowCredentials": true,
    "ExposedHeaders": ["Content-Disposition"]
  }
}
```

- **AllowedOrigins**: List of origins that can access the API
- **AllowedMethods**: HTTP methods that are allowed
- **AllowedHeaders**: HTTP headers that can be used
- **AllowCredentials**: Whether to allow credentials (cookies, authorization headers)
- **ExposedHeaders**: Headers that browsers are allowed to access

For production environments, make sure to:

1. Replace localhost URLs with your actual domain
2. Only include necessary origins, methods, and headers
3. Consider setting AllowCredentials to false if cross-origin credentials aren't needed

### Connection Storage

Connections are stored in a Sqlite database:

- **SQLite database**: Located in the `Data/connections.db` file, providing persistent storage
- **SQLite database override location**: Override the Data directory location with Environment Variable `MSSQL_MCP_DATA`

### Connection Management Tools

The project exposes connection management features through:

1. **ConnectionManager class**: For use within the application
2. **ConnectionManagerTool**: MCP tool for client applications to manage connections
3. **Connection Security Tools**: For encrypting and managing connection string security including key rotation and encryption status verification

### Using Connection Management

#### Starting with Encryption Enabled

See [QUICK_INSTALL](./Documentation/QUICK_INSTALL.md)

This script automatically generates a cryptographically secure random key using System.Security.Cryptography.RandomNumberGenerator, sets it as an environment variable, and starts the server with encryption enabled. You can also provide your own key:

```powershell
   $env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
   $env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
./Scripts/Start-MCP-Encrypted.ps1
```

For production environments, you should store the key securely and set the environment variable externally using a secrets management solution.

#### Testing Connection Management

Use the included test script to verify connection management functionality:

```
./Scripts/test-connection-manager.ps1
```

This script demonstrates the full lifecycle of connection management including adding, testing, updating, and removing connections.

## Database Metadata Features

This MCP server provides comprehensive metadata retrieval functionality for SQL Server databases, allowing Copilot to understand and work with your database schemas effectively.

### Table Metadata

You can retrieve detailed information about database tables using the `mssql_get_table_metadata` tool:

```
#mssql_get_table_metadata connectionName="YourConnection" schema="dbo"
```

This provides complete table metadata including:

- Table names and schemas
- Column details (name, type, nullability, constraints)
- Primary keys
- Foreign key relationships

### View Metadata (New!)

As of May 2025, the MCP server now supports retrieving metadata from SQL Server views in addition to tables. This allows Copilot to understand the structure of views and use them in queries.

You can retrieve metadata for both tables and views using the new `mssql_get_database_objects_metadata` tool:

```
#mssql_get_database_objects_metadata connectionName="YourConnection" schema="dbo" includeViews=true
```

### Stored Procedure Metadata (New!)

The MCP server now also supports retrieving metadata from SQL Server stored procedures, including procedure definitions and parameters. This allows Copilot to understand and work with stored procedures in your database.

You can retrieve stored procedure metadata using the `mssql_get_database_objects_metadata` tool with the `objectType` parameter:

```
#mssql_get_database_objects_metadata connectionName="YourConnection" objectType=PROCEDURE
```

This provides detailed procedure metadata including:

- Procedure names and schemas
- Parameter details (name, type, direction)
- SQL definition (when not encrypted)

### Filtering Database Objects

You can filter database objects by type using the `objectType` parameter:

```
# Get only tables
#mssql_get_database_objects_metadata connectionName="YourConnection" objectType=TABLE

# Get only views
#mssql_get_database_objects_metadata connectionName="YourConnection" objectType=VIEW

# Get only stored procedures
#mssql_get_database_objects_metadata connectionName="YourConnection" objectType=PROCEDURE

# Get all database objects
#mssql_get_database_objects_metadata connectionName="YourConnection" objectType=ALL
```

You can also filter by schema:

```
# Get objects from a specific schema
#mssql_get_database_objects_metadata connectionName="YourConnection" schema="dbo" objectType=ALL
```

You can also filter by specific object types using the objectType parameter:

```
#mssql_get_database_objects_metadata connectionName="YourConnection" schema="dbo" objectType="VIEW"
```

Valid objectType values are:

- "TABLE" or "TABLES" - Returns only base tables
- "VIEW" or "VIEWS" - Returns only views
- "ALL" (default) - Returns all database objects

The view metadata includes:

- View names and schemas
- Column details
- SQL definition of the view
- Relationships to base tables (where applicable)

By setting `includeViews=false`, you can retrieve only table metadata, similar to the original `mssql_get_table_metadata` tool.

### Example Usage

Here's an example of retrieving both tables and views from a database:

```
User: Show me all the database objects in my AdventureWorks2022 database, including views

Copilot: I'll retrieve the metadata for all database objects in the AdventureWorks2022 database, including both tables and views.

[Tool used: mssql_get_database_objects_metadata with connectionName="AdventureWorks2022" includeViews=true]

Results:
The AdventureWorks2022 database contains 68 tables and 20 views across multiple schemas:

Tables:
1. Person.Person - Information about customers, employees, and other individuals
2. Production.Product - Products sold by the company
// ...existing tables...

Views:
1. HumanResources.vEmployee - Employee information combining various related tables
2. Sales.vSalesPerson - Sales person information with their sales data
3. Person.vAdditionalContactInfo - Additional contact information for individuals
```

A testing script is availa

…

## Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [MCPRUNNER](https://github.com/MCPRUNNER)
- **Source:** [MCPRUNNER/mssqlMCP](https://github.com/MCPRUNNER/mssqlMCP)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** yes
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/mcp-mcprunner-mssqlmcp
- Seller: https://agentstack.voostack.com/s/mcprunner
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.