# Directory Structure
```
├── .github
│ └── copilot-instructions.md
├── .gitignore
├── appsettings.json
├── install-service.cmd
├── install-service.ps1
├── Interfaces
│ ├── IConnectionFactory.cs
│ ├── IQueryExecutor.cs
│ ├── ISchemaRepository.cs
│ └── ISqlQueryValidator.cs
├── LICENSE
├── MsSqlMCP.csproj
├── MsSqlMCP.sln
├── Program.cs
├── Properties
│ └── launchSettings.json
├── README.md
├── sample-tools-response.json
├── SchemaTool.cs
├── Services
│ ├── ReadOnlySqlQueryValidator.cs
│ ├── SafeQueryExecutor.cs
│ ├── SchemaRepository.cs
│ └── SqlConnectionFactory.cs
├── test-endpoint.ps1
└── Tests
└── ReadOnlySqlQueryValidatorTests.cs
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
## Ignore Visual Studio temporary files, build results, and
## files generated by popular Visual Studio add-ons.
##
## Get latest from https://github.com/github/gitignore/blob/main/VisualStudio.gitignore
# User-specific files
*.rsuser
*.suo
*.user
*.userosscache
*.sln.docstates
# User-specific files (MonoDevelop/Xamarin Studio)
*.userprefs
# Mono auto generated files
mono_crash.*
# Build results
[Dd]ebug/
[Dd]ebugPublic/
[Rr]elease/
[Rr]eleases/
x64/
x86/
[Ww][Ii][Nn]32/
[Aa][Rr][Mm]/
[Aa][Rr][Mm]64/
bld/
[Bb]in/
[Oo]bj/
[Ll]og/
[Ll]ogs/
# Visual Studio 2015/2017 cache/options directory
.vs/
# Uncomment if you have tasks that create the project's static files in wwwroot
#wwwroot/
# Visual Studio 2017 auto generated files
Generated\ Files/
# MSTest test Results
[Tt]est[Rr]esult*/
[Bb]uild[Ll]og.*
# NUnit
*.VisualState.xml
TestResult.xml
nunit-*.xml
# Build Results of an ATL Project
[Dd]ebugPS/
[Rr]eleasePS/
dlldata.c
# Benchmark Results
BenchmarkDotNet.Artifacts/
# .NET Core
project.lock.json
project.fragment.lock.json
artifacts/
# ASP.NET Scaffolding
ScaffoldingReadMe.txt
# StyleCop
StyleCopReport.xml
# Files built by Visual Studio
*_i.c
*_p.c
*_h.h
*.ilk
*.meta
*.obj
*.iobj
*.pch
*.pdb
*.ipdb
*.pgc
*.pgd
*.rsp
# but not Directory.Build.rsp, as it configures directory-level build defaults
!Directory.Build.rsp
*.sbr
*.tlb
*.tli
*.tlh
*.tmp
*.tmp_proj
*_wpftmp.csproj
*.log
*.tlog
*.vspscc
*.vssscc
.builds
*.pidb
*.svclog
*.scc
# Chutzpah Test files
_Chutzpah*
# Visual C++ cache files
ipch/
*.aps
*.ncb
*.opendb
*.opensdf
*.sdf
*.cachefile
*.VC.db
*.VC.VC.opendb
# Visual Studio profiler
*.psess
*.vsp
*.vspx
*.sap
# Visual Studio Trace Files
*.e2e
# TFS 2012 Local Workspace
$tf/
# Guidance Automation Toolkit
*.gpState
# ReSharper is a .NET coding add-in
_ReSharper*/
*.[Rr]e[Ss]harper
*.DotSettings.user
# TeamCity is a build add-in
_TeamCity*
# DotCover is a Code Coverage Tool
*.dotCover
# AxoCover is a Code Coverage Tool
.axoCover/*
!.axoCover/settings.json
# Coverlet is a free, cross platform Code Coverage Tool
coverage*.json
coverage*.xml
coverage*.info
# Visual Studio code coverage results
*.coverage
*.coveragexml
# NCrunch
_NCrunch_*
.*crunch*.local.xml
nCrunchTemp_*
# MightyMoose
*.mm.*
AutoTest.Net/
# Web workbench (sass)
.sass-cache/
# Installshield output folder
[Ee]xpress/
# DocProject is a documentation generator add-in
DocProject/buildhelp/
DocProject/Help/*.HxT
DocProject/Help/*.HxC
DocProject/Help/*.hhc
DocProject/Help/*.hhk
DocProject/Help/*.hhp
DocProject/Help/Html2
DocProject/Help/html
# Click-Once directory
publish/
# Publish Web Output
*.[Pp]ublish.xml
*.azurePubxml
# Note: Comment the next line if you want to checkin your web deploy settings,
# but database connection strings (with potential passwords) will be unencrypted
*.pubxml
*.publishproj
# Microsoft Azure Web App publish settings. Comment the next line if you want to
# checkin your Azure Web App publish settings, but sensitive information contained
# in these scripts will be unencrypted
PublishScripts/
# NuGet Packages
*.nupkg
# NuGet Symbol Packages
*.snupkg
# The packages folder can be ignored because of Package Restore
**/[Pp]ackages/*
# except build/, which is used as an MSBuild target.
!**/[Pp]ackages/build/
# Uncomment if necessary however generally it will be regenerated when needed
#!**/[Pp]ackages/repositories.config
# NuGet v3's project.json files produces more ignorable files
*.nuget.props
*.nuget.targets
# Microsoft Azure Build Output
csx/
*.build.csdef
# Microsoft Azure Emulator
ecf/
rcf/
# Windows Store app package directories and files
AppPackages/
BundleArtifacts/
Package.StoreAssociation.xml
_pkginfo.txt
*.appx
*.appxbundle
*.appxupload
# Visual Studio cache files
# files ending in .cache can be ignored
*.[Cc]ache
# but keep track of directories ending in .cache
!?*.[Cc]ache/
# Others
ClientBin/
~$*
*~
*.dbmdl
*.dbproj.schemaview
*.jfm
*.pfx
*.publishsettings
orleans.codegen.cs
# Including strong name files can present a security risk
# (https://github.com/github/gitignore/pull/2483#issue-259490424)
#*.snk
# Since there are multiple workflows, uncomment next line to ignore bower_components
# (https://github.com/github/gitignore/pull/1529#issuecomment-104372622)
#bower_components/
# RIA/Silverlight projects
Generated_Code/
# Backup & report files from converting an old project file
# to a newer Visual Studio version. Backup files are not needed,
# because we have git ;-)
_UpgradeReport_Files/
Backup*/
UpgradeLog*.XML
UpgradeLog*.htm
ServiceFabricBackup/
*.rptproj.bak
# SQL Server files
*.mdf
*.ldf
*.ndf
# Business Intelligence projects
*.rdl.data
*.bim.layout
*.bim_*.settings
*.rptproj.rsuser
*- [Bb]ackup.rdl
*- [Bb]ackup ([0-9]).rdl
*- [Bb]ackup ([0-9][0-9]).rdl
# Microsoft Fakes
FakesAssemblies/
# GhostDoc plugin setting file
*.GhostDoc.xml
# Node.js Tools for Visual Studio
.ntvs_analysis.dat
node_modules/
# Visual Studio 6 build log
*.plg
# Visual Studio 6 workspace options file
*.opt
# Visual Studio 6 auto-generated workspace file (contains which files were open etc.)
*.vbw
# Visual Studio 6 auto-generated project file (contains which files were open etc.)
*.vbp
# Visual Studio 6 workspace and project file (working project files containing files to include in project)
*.dsw
*.dsp
# Visual Studio 6 technical files
*.ncb
*.aps
# Visual Studio LightSwitch build output
**/*.HTMLClient/GeneratedArtifacts
**/*.DesktopClient/GeneratedArtifacts
**/*.DesktopClient/ModelManifest.xml
**/*.Server/GeneratedArtifacts
**/*.Server/ModelManifest.xml
_Pvt_Extensions
# Paket dependency manager
.paket/paket.exe
paket-files/
# FAKE - F# Make
.fake/
# CodeRush personal settings
.cr/personal
# Python Tools for Visual Studio (PTVS)
__pycache__/
*.pyc
# Cake - Uncomment if you are using it
# tools/**
# !tools/packages.config
# Tabs Studio
*.tss
# Telerik's JustMock configuration file
*.jmconfig
# BizTalk build output
*.btp.cs
*.btm.cs
*.odx.cs
*.xsd.cs
# OpenCover UI analysis results
OpenCover/
# Azure Stream Analytics local run output
ASALocalRun/
# MSBuild Binary and Structured Log
*.binlog
# NVidia Nsight GPU debugger configuration file
*.nvuser
# MFractors (Xamarin productivity tool) working folder
.mfractor/
# Local History for Visual Studio
.localhistory/
# Visual Studio History (VSHistory) files
.vshistory/
# BeatPulse healthcheck temp database
healthchecksdb
# Backup folder for Package Reference Convert tool in Visual Studio 2017
MigrationBackup/
# Ionide (cross platform F# VS Code tools) working folder
.ionide/
# Fody - auto-generated XML schema
FodyWeavers.xsd
# VS Code files for those working on multiple tools
.vscode/*
!.vscode/settings.json
!.vscode/tasks.json
!.vscode/launch.json
!.vscode/extensions.json
*.code-workspace
# Local History for Visual Studio Code
.history/
# Windows Installer files from build outputs
*.cab
*.msi
*.msix
*.msm
*.msp
# JetBrains Rider
*.sln.iml
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# MsSqlMCP
MCP Server for SQL Server database schema inspection and read-only query execution.
## Features
- **Read-only access**: All queries are validated to prevent data modification (INSERT, UPDATE, DELETE, DROP, EXEC, etc. are blocked)
- **Schema discovery**: Tables, columns, relationships, and stored procedures
- **SQL execution**: Safe SELECT queries only
- **Dual transport**: Supports both stdio and HTTP/SSE protocols
- **Windows Service**: Can run as a Windows Service for production deployments
- **MCP Protocol**: Compatible with VS Code Copilot, Claude Desktop, and other MCP clients
## Prerequisites
- .NET 10 (or .NET 9 with minor adjustments)
- SQL Server
## Architecture
The project follows SOLID principles with dependency injection:
```
MsSqlMCP/
├── Program.cs # Entry point with DI and dual transport
├── SchemaTool.cs # MCP tool definitions
├── Interfaces/
│ ├── IConnectionFactory.cs # SQL connection abstraction
│ ├── IQueryExecutor.cs # Query execution abstraction
│ ├── ISchemaRepository.cs # Schema queries abstraction
│ └── ISqlQueryValidator.cs # Query validation abstraction
├── Services/
│ ├── SqlConnectionFactory.cs # Connection management
│ ├── SafeQueryExecutor.cs # Validated query execution
│ ├── SchemaRepository.cs # Schema query implementation
│ └── ReadOnlySqlQueryValidator.cs # Security validation (27 blocked keywords)
└── Tests/
└── ReadOnlySqlQueryValidatorTests.cs # 42 security tests
```
## Configuration
### Connection String
Edit `appsettings.json`:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=(local);Initial Catalog=YourDatabase;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Urls": "http://localhost:5000"
}
```
## Running
### Console Mode (Development)
```bash
# Run with both stdio and HTTP transports
dotnet run
# Run with HTTP transport only (for debugging)
dotnet run -- --http-only
```
### Run Tests
```bash
dotnet test --filter "FullyQualifiedName~Tests"
```
## MCP Client Configuration
### Option 1: stdio Transport (VS Code)
Add to your VS Code `settings.json`:
```json
{
"mcp": {
"servers": {
"MsSqlMCP": {
"type": "stdio",
"command": "dotnet",
"args": ["run", "--project", "c:\\path\\to\\MsSqlMCP.csproj"]
}
}
}
}
```
### Option 2: HTTP Transport (VS Code)
First, start the server:
```bash
dotnet run -- --http-only
```
Then add to your VS Code `settings.json`:
```json
{
"mcp": {
"servers": {
"MsSqlMCP": {
"type": "http",
"url": "http://localhost:5000/sse",
"autoApprove": [
"get_tables",
"get_columns",
"get_relationships",
"execute_sql",
"get_store_procedure"
]
}
}
}
}
```
### Option 3: Claude Desktop
Add to `claude_desktop_config.json`:
```json
{
"mcpServers": {
"MsSqlMCP": {
"command": "dotnet",
"args": ["run", "--project", "c:\\path\\to\\MsSqlMCP.csproj"]
}
}
}
```
## Available Tools
| Tool | Description | Required Parameters |
|------|-------------|---------------------|
| `GetTables` | Get all table names in the database | None |
| `GetColumns` | Get columns (fields) for a specific table | `tableName` |
| `GetRelationships` | Get foreign key relationships between tables | None |
| `GetStoreProcedure` | Get stored procedure definition | `spName` |
| `ExecuteSql` | Execute a read-only SELECT query | `sqlQuery` |
All tools accept an optional `databaseName` parameter to query different databases in the same SQL Server instance.
### Security
The `ExecuteSql` tool only allows SELECT queries. The following statements are blocked:
- **DML**: INSERT, UPDATE, DELETE, MERGE, TRUNCATE
- **DDL**: CREATE, ALTER, DROP
- **DCL**: GRANT, REVOKE, DENY
- **Execution**: EXEC, EXECUTE, SP_EXECUTESQL, XP_
- **Others**: BACKUP, RESTORE, BULK, OPENROWSET, OPENQUERY, OPENDATASOURCE
## Windows Service Installation
### 1. Publish the Application
On your development machine:
```bash
cd c:\path\to\MsSqlMCP
dotnet publish -c Release -r win-x64 --self-contained true
```
This creates files in: `bin\Release\net10.0\win-x64\publish\`
### 2. Copy to Server
Copy the contents of the `publish` folder to the server:
```
Source: bin\Release\net10.0\win-x64\publish\*
Destination: C:\Services\MsSqlMCP\
```
### 3. Configure on Server
Edit `C:\Services\MsSqlMCP\appsettings.json` with your SQL Server connection string:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=YOUR_SQL_SERVER;Initial Catalog=YOUR_DATABASE;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Urls": "http://localhost:5000"
}
```
### 4. Install the Service
Open **PowerShell as Administrator** and run:
```powershell
# Create the Windows Service
sc.exe create MsSqlMCP binPath= "C:\Services\MsSqlMCP\MsSqlMCP.exe --http-only" start= auto DisplayName= "MsSql MCP Server"
# Add description
sc.exe description MsSqlMCP "Model Context Protocol server for SQL Server database inspection"
# Create logs directory
mkdir C:\Services\MsSqlMCP\logs -Force
# Start the service
net start MsSqlMCP
# Verify status
sc.exe query MsSqlMCP
```
### 5. Verify Installation
```powershell
# Check service status
Get-Service -Name MsSqlMCP
# Test the endpoint
Invoke-RestMethod -Uri "http://localhost:5000/sse/tools"
```
### Service Management Commands
```powershell
# Stop service
net stop MsSqlMCP
# Start service
net start MsSqlMCP
# Restart service
net stop MsSqlMCP; net start MsSqlMCP
# Uninstall service
net stop MsSqlMCP
sc.exe delete MsSqlMCP
```
### Firewall Configuration (if accessing remotely)
```powershell
# Allow inbound traffic on port 5000
New-NetFirewallRule -DisplayName "MsSqlMCP" -Direction Inbound -Port 5000 -Protocol TCP -Action Allow
```
## HTTP API Endpoints
When running in HTTP mode, the following endpoints are available:
| Endpoint | Method | Description |
|----------|--------|-------------|
| `/sse` | GET | SSE stream for MCP protocol |
| `/sse/tools` | GET | List all available tools |
| `/sse/invoke` | POST | Invoke a tool |
### Example: Invoke Tool via HTTP
```bash
curl -X POST http://localhost:5000/sse/invoke \
-H "Content-Type: application/json" \
-d '{"Tool": "GetTables", "Params": {}}'
```
```powershell
Invoke-RestMethod -Uri "http://localhost:5000/sse/invoke" -Method POST -ContentType "application/json" -Body '{"Tool": "GetTables", "Params": {}}'
```
## Troubleshooting
### Service won't start
1. Check logs in `C:\Services\MsSqlMCP\logs\`
2. Verify connection string in `appsettings.json`
3. Ensure SQL Server is accessible from the service account
4. Run manually to see errors: `C:\Services\MsSqlMCP\MsSqlMCP.exe --http-only`
### Connection issues
1. Verify SQL Server is running
2. Check firewall rules for SQL Server port (1433)
3. If using Windows Authentication, ensure the service account has database access
### Port already in use
Change the port in `appsettings.json`:
```json
{
"Urls": "http://localhost:5001"
}
```
## License
MIT
```
--------------------------------------------------------------------------------
/Properties/launchSettings.json:
--------------------------------------------------------------------------------
```json
{
"profiles": {
"MsSqlMCP": {
"commandName": "Project",
"launchBrowser": true,
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
},
"applicationUrl": "https://localhost:51615;http://localhost:51616"
}
}
}
```
--------------------------------------------------------------------------------
/Interfaces/IConnectionFactory.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Data.SqlClient;
namespace MsSqlMCP.Interfaces;
/// <summary>
/// Factory for creating and managing SQL Server connections.
/// </summary>
public interface IConnectionFactory
{
/// <summary>
/// Creates and opens a new SQL connection, optionally switching to a specific database.
/// </summary>
/// <param name="databaseName">Optional database name to switch to after connecting.</param>
/// <returns>An open SqlConnection ready to use.</returns>
Task<SqlConnection> CreateOpenConnectionAsync(string? databaseName = null);
}
```
--------------------------------------------------------------------------------
/Interfaces/IQueryExecutor.cs:
--------------------------------------------------------------------------------
```csharp
namespace MsSqlMCP.Interfaces;
/// <summary>
/// Executes validated SQL queries against the database.
/// </summary>
public interface IQueryExecutor
{
/// <summary>
/// Executes a read-only SQL query and returns formatted results.
/// </summary>
/// <param name="sqlQuery">The SQL query to execute (must be SELECT-only).</param>
/// <param name="databaseName">Optional database name to query.</param>
/// <returns>Formatted query results as a string.</returns>
Task<string> ExecuteReadOnlyQueryAsync(string sqlQuery, string? databaseName = null);
}
```
--------------------------------------------------------------------------------
/appsettings.json:
--------------------------------------------------------------------------------
```json
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=<su servidor e instancia>;Initial Catalog=<su base de datos>;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning",
"ModelContextProtocol": "Information"
},
"File": {
"Path": "logs/mssqlmcp-.log",
"RollingInterval": "Day",
"RetainedFileCountLimit": 7
}
},
"Service": {
"ServiceName": "MsSqlMCP",
"DisplayName": "MsSql MCP Server",
"Description": "Model Context Protocol server for SQL Server database inspection"
},
"Urls": "http://localhost:5000"
}
```
--------------------------------------------------------------------------------
/Interfaces/ISqlQueryValidator.cs:
--------------------------------------------------------------------------------
```csharp
namespace MsSqlMCP.Interfaces;
/// <summary>
/// Validates SQL queries to ensure they are safe to execute.
/// </summary>
public interface ISqlQueryValidator
{
/// <summary>
/// Validates a SQL query and returns the result.
/// </summary>
/// <param name="sqlQuery">The SQL query to validate.</param>
/// <returns>A ValidationResult indicating if the query is valid and any error message.</returns>
ValidationResult Validate(string sqlQuery);
}
/// <summary>
/// Represents the result of a SQL query validation.
/// </summary>
/// <param name="IsValid">Whether the query passed validation.</param>
/// <param name="ErrorMessage">Error message if validation failed, null otherwise.</param>
public record ValidationResult(bool IsValid, string? ErrorMessage = null);
```
--------------------------------------------------------------------------------
/Interfaces/ISchemaRepository.cs:
--------------------------------------------------------------------------------
```csharp
namespace MsSqlMCP.Interfaces;
/// <summary>
/// Repository for querying database schema information.
/// </summary>
public interface ISchemaRepository
{
/// <summary>
/// Gets all table names in the database.
/// </summary>
Task<IReadOnlyList<string>> GetTablesAsync(string? databaseName = null);
/// <summary>
/// Gets column information for a specific table.
/// </summary>
Task<IReadOnlyList<string>> GetColumnsAsync(string tableName, string? databaseName = null);
/// <summary>
/// Gets all foreign key relationships in the database.
/// </summary>
Task<IReadOnlyList<string>> GetRelationshipsAsync(string? databaseName = null);
/// <summary>
/// Gets the definition of a stored procedure.
/// </summary>
Task<IReadOnlyList<string>> GetStoredProcedureDefinitionAsync(string spName, string? databaseName = null);
}
```
--------------------------------------------------------------------------------
/test-endpoint.ps1:
--------------------------------------------------------------------------------
```
# Script para probar el endpoint /tools
Write-Host "Probando el endpoint /tools..." -ForegroundColor Cyan
try {
$response = Invoke-RestMethod -Uri "http://localhost:5000/tools" -Method Get -ErrorAction Stop
Write-Host "`nRespuesta recibida:" -ForegroundColor Green
$response | ConvertTo-Json -Depth 10 | Write-Host
Write-Host "`n`nNúmero de herramientas: $($response.Count)" -ForegroundColor Yellow
foreach ($tool in $response) {
Write-Host "`n--- $($tool.Name) ---" -ForegroundColor Magenta
Write-Host "Description: $($tool.Description)"
if ($tool.Parameters) {
Write-Host "Parameters: " -NoNewline
$tool.Parameters | ConvertTo-Json -Compress | Write-Host
}
}
}
catch {
Write-Host "Error: $_" -ForegroundColor Red
Write-Host "Asegúrate de que el servidor esté corriendo con: dotnet run" -ForegroundColor Yellow
}
```
--------------------------------------------------------------------------------
/Services/SqlConnectionFactory.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using MsSqlMCP.Interfaces;
namespace MsSqlMCP.Services;
/// <summary>
/// Factory for creating SQL Server connections with optional database switching.
/// </summary>
public class SqlConnectionFactory : IConnectionFactory
{
private readonly string _connectionString;
private readonly ILogger<SqlConnectionFactory> _logger;
public SqlConnectionFactory(IConfiguration configuration, ILogger<SqlConnectionFactory> logger)
{
_connectionString = configuration.GetConnectionString("DefaultConnection")
?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found in configuration.");
_logger = logger;
}
/// <inheritdoc />
public async Task<SqlConnection> CreateOpenConnectionAsync(string? databaseName = null)
{
var connection = new SqlConnection(_connectionString);
try
{
await connection.OpenAsync();
_logger.LogDebug("SQL connection opened successfully");
if (!string.IsNullOrWhiteSpace(databaseName))
{
var sanitizedName = SanitizeDatabaseName(databaseName);
using var cmd = new SqlCommand($"USE {sanitizedName};", connection);
await cmd.ExecuteNonQueryAsync();
_logger.LogDebug("Switched to database {DatabaseName}", databaseName);
}
return connection;
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to open SQL connection");
await connection.DisposeAsync();
throw;
}
}
/// <summary>
/// Sanitizes database name to prevent SQL injection.
/// Wraps in brackets and escapes internal bracket characters.
/// </summary>
private static string SanitizeDatabaseName(string name) => $"[{name.Replace("]", "]]")}]";
}
```
--------------------------------------------------------------------------------
/sample-tools-response.json:
--------------------------------------------------------------------------------
```json
[
{
"name": "GetTables",
"description": "Get tables name of database. Optionally, specify a database name to query a different database in the same instance.",
"parameters": {
"type": "object",
"properties": {
"databaseName": {
"type": "string"
}
}
}
},
{
"name": "GetColumns",
"description": "Get the columns (fields) of a database table. Optionally, specify a database name to query a different database in the same instance.",
"parameters": {
"type": "object",
"properties": {
"tableName": {
"type": "string"
},
"databaseName": {
"type": "string"
}
},
"required": ["tableName"]
}
},
{
"name": "GetRelationships",
"description": "Get the relationships between tables in the database. Optionally, specify a database name to query a different database in the same instance.",
"parameters": {
"type": "object",
"properties": {
"databaseName": {
"type": "string"
}
}
}
},
{
"name": "ExecuteSql",
"description": "Execute a SQL query against the database. Does not allow DROP statements. Optionally, specify a database name to query a different database in the same instance.",
"parameters": {
"type": "object",
"properties": {
"sqlQuery": {
"type": "string"
},
"databaseName": {
"type": "string"
}
},
"required": ["sqlQuery"]
}
},
{
"name": "GetStoreProcedure",
"description": "Get the definition of a stored procedure by name. Optionally, specify a database name to query a different database in the same instance.",
"parameters": {
"type": "object",
"properties": {
"spName": {
"type": "string"
},
"databaseName": {
"type": "string"
}
},
"required": ["spName"]
}
}
]
```
--------------------------------------------------------------------------------
/install-service.cmd:
--------------------------------------------------------------------------------
```
@echo off
REM MsSqlMCP Windows Service Installation Script (CMD wrapper)
REM Run as Administrator
echo ============================================
echo MsSqlMCP Windows Service Installer
echo ============================================
echo.
REM Check for admin rights
net session >nul 2>&1
if %errorLevel% neq 0 (
echo ERROR: This script must be run as Administrator.
echo Right-click and select "Run as administrator"
pause
exit /b 1
)
REM Get the directory where this script is located
set SCRIPT_DIR=%~dp0
set INSTALL_PATH=C:\Services\MsSqlMCP
set SERVICE_NAME=MsSqlMCP
echo Script Directory: %SCRIPT_DIR%
echo Install Path: %INSTALL_PATH%
echo.
REM Check if service already exists
sc query %SERVICE_NAME% >nul 2>&1
if %errorLevel% equ 0 (
echo Service %SERVICE_NAME% already exists.
echo.
choice /C YN /M "Do you want to reinstall"
if errorlevel 2 goto :end
echo Stopping and removing existing service...
net stop %SERVICE_NAME% >nul 2>&1
sc delete %SERVICE_NAME%
timeout /t 3 >nul
)
REM Check if published files exist
set PUBLISH_DIR=%SCRIPT_DIR%bin\Release\net10.0\win-x64\publish
if not exist "%PUBLISH_DIR%" (
echo Publishing application...
cd /d "%SCRIPT_DIR%"
dotnet publish -c Release -r win-x64 --self-contained true
if errorlevel 1 (
echo ERROR: Failed to publish application.
pause
exit /b 1
)
)
REM Create installation directory
if not exist "%INSTALL_PATH%" (
echo Creating installation directory...
mkdir "%INSTALL_PATH%"
)
REM Copy files
echo Copying files to %INSTALL_PATH%...
xcopy "%PUBLISH_DIR%\*" "%INSTALL_PATH%\" /E /Y /Q
REM Create logs directory
if not exist "%INSTALL_PATH%\logs" mkdir "%INSTALL_PATH%\logs"
REM Create the service
echo Creating Windows Service...
sc create %SERVICE_NAME% binPath= "\"%INSTALL_PATH%\MsSqlMCP.exe\" --http-only" start= auto DisplayName= "MsSql MCP Server"
sc description %SERVICE_NAME% "Model Context Protocol server for SQL Server database inspection"
echo.
echo ============================================
echo Installation Complete!
echo ============================================
echo.
echo Service Name: %SERVICE_NAME%
echo Install Path: %INSTALL_PATH%
echo Service URL: http://localhost:5000/sse
echo.
echo Next steps:
echo 1. Review configuration: %INSTALL_PATH%\appsettings.json
echo 2. Start service: net start %SERVICE_NAME%
echo 3. Check logs: %INSTALL_PATH%\logs\
echo.
:end
pause
```
--------------------------------------------------------------------------------
/Services/SafeQueryExecutor.cs:
--------------------------------------------------------------------------------
```csharp
using System.Text;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using MsSqlMCP.Interfaces;
namespace MsSqlMCP.Services;
/// <summary>
/// Executes validated read-only SQL queries against the database.
/// </summary>
public class SafeQueryExecutor : IQueryExecutor
{
private readonly IConnectionFactory _connectionFactory;
private readonly ISqlQueryValidator _validator;
private readonly ILogger<SafeQueryExecutor> _logger;
public SafeQueryExecutor(
IConnectionFactory connectionFactory,
ISqlQueryValidator validator,
ILogger<SafeQueryExecutor> logger)
{
_connectionFactory = connectionFactory;
_validator = validator;
_logger = logger;
}
/// <inheritdoc />
public async Task<string> ExecuteReadOnlyQueryAsync(string sqlQuery, string? databaseName = null)
{
// Validate the query before execution
var validationResult = _validator.Validate(sqlQuery);
if (!validationResult.IsValid)
{
_logger.LogWarning("Query validation failed: {ErrorMessage}", validationResult.ErrorMessage);
return validationResult.ErrorMessage!;
}
_logger.LogDebug("Executing validated query on database: {DatabaseName}", databaseName ?? "default");
try
{
await using var connection = await _connectionFactory.CreateOpenConnectionAsync(databaseName);
var resultBuilder = new StringBuilder();
if (!string.IsNullOrWhiteSpace(databaseName))
{
resultBuilder.AppendLine($"Database: {databaseName}");
resultBuilder.AppendLine();
}
using var command = new SqlCommand(sqlQuery, connection);
using var reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
// Build header
var headers = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
headers.Add(reader.GetName(i));
}
resultBuilder.AppendLine(string.Join("\t|\t", headers));
resultBuilder.AppendLine(new string('-', Math.Min(headers.Count * 20, 120)));
// Build rows
var rowCount = 0;
while (await reader.ReadAsync())
{
var values = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
values.Add(reader.IsDBNull(i) ? "NULL" : reader[i]?.ToString() ?? "NULL");
}
resultBuilder.AppendLine(string.Join("\t|\t", values));
rowCount++;
}
resultBuilder.AppendLine();
resultBuilder.AppendLine($"({rowCount} row(s) returned)");
}
else
{
resultBuilder.AppendLine("No rows returned from the query.");
}
_logger.LogDebug("Query executed successfully");
return resultBuilder.ToString();
}
catch (SqlException ex)
{
_logger.LogError(ex, "SQL error executing query");
return $"SQL Error: {ex.Message}";
}
catch (Exception ex)
{
_logger.LogError(ex, "Error executing query");
return $"Error executing SQL query: {ex.Message}";
}
}
}
```
--------------------------------------------------------------------------------
/Services/ReadOnlySqlQueryValidator.cs:
--------------------------------------------------------------------------------
```csharp
using System.Text.RegularExpressions;
using MsSqlMCP.Interfaces;
namespace MsSqlMCP.Services;
/// <summary>
/// Validates SQL queries to ensure only read-only (SELECT) operations are allowed.
/// This protects the database from modifications through the MCP server.
/// </summary>
public partial class ReadOnlySqlQueryValidator : ISqlQueryValidator
{
/// <summary>
/// SQL keywords that can modify data or schema - all blocked in read-only mode.
/// </summary>
private static readonly HashSet<string> BlockedKeywords = new(StringComparer.OrdinalIgnoreCase)
{
// DML statements
"INSERT",
"UPDATE",
"DELETE",
"MERGE",
"TRUNCATE",
// DDL statements
"DROP",
"ALTER",
"CREATE",
// DCL statements
"GRANT",
"REVOKE",
"DENY",
// Execution statements
"EXEC",
"EXECUTE",
"SP_EXECUTESQL",
// Bulk operations
"BULK",
"OPENROWSET",
"OPENDATASOURCE",
// Backup/Restore
"BACKUP",
"RESTORE",
// Other dangerous operations
"SHUTDOWN",
"KILL",
"RECONFIGURE",
"DBCC"
};
/// <inheritdoc />
public ValidationResult Validate(string sqlQuery)
{
if (string.IsNullOrWhiteSpace(sqlQuery))
{
return new ValidationResult(false, "SQL query cannot be empty.");
}
var normalizedQuery = sqlQuery.Trim();
// Check that query starts with allowed read operations
if (!IsReadOnlyQueryStart(normalizedQuery))
{
return new ValidationResult(false,
"Only SELECT queries are allowed. This is a read-only MCP server. " +
"Queries must start with SELECT or WITH (for CTEs).");
}
// Check for blocked keywords anywhere in the query
foreach (var keyword in BlockedKeywords)
{
if (ContainsKeywordAsWord(normalizedQuery, keyword))
{
return new ValidationResult(false,
$"Error: {keyword} statements are not allowed. This is a read-only MCP server.");
}
}
// Check for multiple statements (semicolon followed by another statement)
if (ContainsMultipleStatements(normalizedQuery))
{
return new ValidationResult(false,
"Multiple SQL statements are not allowed. Please execute one SELECT query at a time.");
}
return new ValidationResult(true);
}
/// <summary>
/// Checks if the query starts with allowed read-only operations.
/// </summary>
private static bool IsReadOnlyQueryStart(string query)
{
return query.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) ||
query.StartsWith("WITH", StringComparison.OrdinalIgnoreCase) || // CTEs
query.StartsWith("SET", StringComparison.OrdinalIgnoreCase) || // SET statements for session config
query.StartsWith("--", StringComparison.Ordinal) || // Comments
query.StartsWith("/*", StringComparison.Ordinal); // Block comments
}
/// <summary>
/// Checks if a keyword appears as a complete word in the query.
/// Uses word boundaries to avoid false positives (e.g., "UPDATED_DATE" shouldn't match "UPDATE").
/// </summary>
private static bool ContainsKeywordAsWord(string query, string keyword)
{
var pattern = $@"\b{Regex.Escape(keyword)}\b";
return Regex.IsMatch(query, pattern, RegexOptions.IgnoreCase);
}
/// <summary>
/// Detects if query contains multiple statements separated by semicolons.
/// </summary>
private static bool ContainsMultipleStatements(string query)
{
// Look for semicolon followed by another statement keyword
var dangerousPatterns = new[]
{
@";\s*(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|EXEC|EXECUTE|TRUNCATE|GRANT|REVOKE|DENY)\b",
@";\s*--.*\r?\n\s*(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|EXEC|EXECUTE)\b"
};
foreach (var pattern in dangerousPatterns)
{
if (Regex.IsMatch(query, pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline))
{
return true;
}
}
return false;
}
}
```
--------------------------------------------------------------------------------
/.github/copilot-instructions.md:
--------------------------------------------------------------------------------
```markdown
# MsSqlMCP - Copilot Instructions
## Project Overview
This is a **Model Context Protocol (MCP) server** that exposes SQL Server database schema inspection tools to AI assistants. It uses the `ModelContextProtocol` SDK with stdio transport for MCP protocol communication.
**IMPORTANT: This is a READ-ONLY server.** The `ExecuteSql` tool only allows SELECT queries. All modifying statements (INSERT, UPDATE, DELETE, DROP, etc.) are blocked by the `ReadOnlySqlQueryValidator`.
### Architecture Pattern
- **Entry point**: `Program.cs` uses top-level statements with `Host.CreateApplicationBuilder`
- **Tool registration**: Tools are defined in `SchemaTool.cs` with `[McpServerTool]` attributes
- **Dependency Injection**: Services are registered directly in Program.cs
- **Interfaces**: Located in `Interfaces/` folder for SOLID compliance
- **Services**: Located in `Services/` folder with implementations
- **Transport**: Stdio transport using `AddMcpServer().WithStdioServerTransport().WithToolsFromAssembly()`
### Project Structure
```
MsSqlMCP/
├── Program.cs # Entry point with DI configuration (top-level statements)
├── SchemaTool.cs # MCP tool definitions
├── Interfaces/
│ ├── IConnectionFactory.cs # SQL connection abstraction
│ ├── IQueryExecutor.cs # Query execution abstraction
│ ├── ISchemaRepository.cs # Schema queries abstraction
│ └── ISqlQueryValidator.cs # Query validation abstraction
├── Services/
│ ├── SqlConnectionFactory.cs # Connection management
│ ├── SafeQueryExecutor.cs # Validated query execution
│ ├── SchemaRepository.cs # Schema query implementation
│ └── ReadOnlySqlQueryValidator.cs # Security validation
└── Tests/
└── ReadOnlySqlQueryValidatorTests.cs # Security tests (42 tests)
```
## Key Conventions
### Dependency Injection Pattern
All services are registered in `Program.cs`:
```csharp
builder.Services.AddSingleton<IConnectionFactory, SqlConnectionFactory>();
builder.Services.AddSingleton<ISqlQueryValidator, ReadOnlySqlQueryValidator>();
builder.Services.AddScoped<ISchemaRepository, SchemaRepository>();
builder.Services.AddScoped<IQueryExecutor, SafeQueryExecutor>();
builder.Services.AddScoped<SchemaTool>();
```
### Tool Definition Pattern
Tools in `SchemaTool.cs` use constructor injection:
```csharp
[McpServerToolType]
public class SchemaTool
{
private readonly ISchemaRepository _schemaRepository;
private readonly IQueryExecutor _queryExecutor;
public SchemaTool(ISchemaRepository schemaRepository, IQueryExecutor queryExecutor)
{
_schemaRepository = schemaRepository;
_queryExecutor = queryExecutor;
}
[McpServerTool, Description("Tool description for AI")]
public async Task<string> ToolName(string? databaseName = null)
{
// Use injected services
}
}
```
### Security: Read-Only Query Validation
The `ReadOnlySqlQueryValidator` enforces read-only access by:
1. **Whitelist approach**: Only queries starting with `SELECT` or `WITH` are allowed
2. **Blocked keywords**: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, EXEC, TRUNCATE, MERGE, etc.
3. **Multiple statement detection**: Prevents SQL injection via semicolons
### Database Name Security
`SqlConnectionFactory.SanitizeDatabaseName()` prevents SQL injection:
- Wraps database names in `[]` brackets
- Escapes internal `]` characters as `]]`
### Optional Database Parameter
All tools accept `string? databaseName = null` to query different databases in the same SQL Server instance.
### Configuration Pattern
Connection string is loaded from `appsettings.json` via IConfiguration:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=(local);Initial Catalog=ia_oc;..."
}
}
```
## Development Workflows
### Building and Running
```bash
# Run the MCP server (stdio mode)
dotnet run
# Run tests (filter required due to shared project)
dotnet test --filter "FullyQualifiedName~Tests"
```
### VS Code MCP Integration
Configure in `settings.json`:
```json
"mcp": {
"servers": {
"MsSqlMCP": {
"type": "stdio",
"command": "dotnet",
"args": ["run", "--project", "c:\\path\\to\\MsSqlMCP.csproj"]
}
}
}
```
### Adding New Tools
1. Add async method to `SchemaTool` class
2. Decorate with `[McpServerTool, Description("...")]`
3. Use injected services (avoid creating dependencies with `new`)
4. Accept `string? databaseName = null` for consistency
5. Return `Task<string>` (MCP requirement)
### Adding New Services
1. Create interface in `Interfaces/` folder
2. Create implementation in `Services/` folder
3. Register in `Program.cs`
4. Inject via constructor in consuming classes
### Logging
All logs go to **stderr** via `LogToStandardErrorThreshold = LogLevel.Trace` to avoid polluting stdio transport.
## Dependencies
- **Microsoft.Data.SqlClient 6.0.1**: SQL Server connectivity
- **ModelContextProtocol 0.4.0-preview.1**: MCP SDK
- **ModelContextProtocol.AspNetCore 0.4.0-preview.1**: MCP ASP.NET Core integration
- **xUnit 2.9.2**: Unit testing framework
- **.NET 9.0**: Target framework
```
--------------------------------------------------------------------------------
/install-service.ps1:
--------------------------------------------------------------------------------
```
# MsSqlMCP Windows Service Installation Script
# Run as Administrator
param(
[Parameter(Mandatory=$false)]
[string]$ServiceName = "MsSqlMCP",
[Parameter(Mandatory=$false)]
[string]$DisplayName = "MsSql MCP Server",
[Parameter(Mandatory=$false)]
[string]$Description = "Model Context Protocol server for SQL Server database inspection",
[Parameter(Mandatory=$false)]
[string]$InstallPath = "C:\Services\MsSqlMCP",
[Parameter(Mandatory=$false)]
[ValidateSet("Install", "Uninstall", "Reinstall", "Status")]
[string]$Action = "Install"
)
$ErrorActionPreference = "Stop"
# Check if running as Administrator
$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if (-not $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
Write-Error "This script must be run as Administrator. Right-click PowerShell and select 'Run as Administrator'."
exit 1
}
function Get-ServiceStatus {
$service = Get-Service -Name $ServiceName -ErrorAction SilentlyContinue
if ($service) {
Write-Host "Service '$ServiceName' exists with status: $($service.Status)" -ForegroundColor Cyan
return $true
} else {
Write-Host "Service '$ServiceName' does not exist." -ForegroundColor Yellow
return $false
}
}
function Install-MsSqlMCPService {
Write-Host "Installing MsSqlMCP as Windows Service..." -ForegroundColor Green
# Check if service already exists
if (Get-ServiceStatus) {
Write-Host "Service already exists. Use -Action Reinstall to reinstall." -ForegroundColor Yellow
return
}
# Get the script directory (where the published files are)
$scriptDir = Split-Path -Parent $MyInvocation.ScriptName
$publishDir = Join-Path $scriptDir "bin\Release\net10.0\win-x64\publish"
if (-not (Test-Path $publishDir)) {
Write-Host "Published files not found at: $publishDir" -ForegroundColor Yellow
Write-Host "Publishing the application..." -ForegroundColor Cyan
Push-Location $scriptDir
dotnet publish -c Release -r win-x64 --self-contained true
Pop-Location
if (-not (Test-Path $publishDir)) {
Write-Error "Failed to publish the application."
exit 1
}
}
# Create installation directory
if (-not (Test-Path $InstallPath)) {
Write-Host "Creating installation directory: $InstallPath" -ForegroundColor Cyan
New-Item -ItemType Directory -Path $InstallPath -Force | Out-Null
}
# Copy files to installation directory
Write-Host "Copying files to: $InstallPath" -ForegroundColor Cyan
Copy-Item -Path "$publishDir\*" -Destination $InstallPath -Recurse -Force
# Create logs directory
$logsPath = Join-Path $InstallPath "logs"
if (-not (Test-Path $logsPath)) {
New-Item -ItemType Directory -Path $logsPath -Force | Out-Null
}
# Get the executable path
$exePath = Join-Path $InstallPath "MsSqlMCP.exe"
if (-not (Test-Path $exePath)) {
Write-Error "Executable not found at: $exePath"
exit 1
}
# Create the Windows Service
Write-Host "Creating Windows Service..." -ForegroundColor Cyan
$service = New-Service -Name $ServiceName `
-BinaryPathName "`"$exePath`" --http-only" `
-DisplayName $DisplayName `
-Description $Description `
-StartupType Automatic
Write-Host "Service '$ServiceName' installed successfully!" -ForegroundColor Green
Write-Host ""
Write-Host "Next steps:" -ForegroundColor Yellow
Write-Host " 1. Review configuration at: $InstallPath\appsettings.json"
Write-Host " 2. Start the service: Start-Service -Name $ServiceName"
Write-Host " 3. Check logs at: $InstallPath\logs\"
Write-Host ""
Write-Host "Service URL: http://localhost:5000/sse" -ForegroundColor Cyan
}
function Uninstall-MsSqlMCPService {
Write-Host "Uninstalling MsSqlMCP Windows Service..." -ForegroundColor Yellow
# Check if service exists
$service = Get-Service -Name $ServiceName -ErrorAction SilentlyContinue
if (-not $service) {
Write-Host "Service '$ServiceName' does not exist." -ForegroundColor Yellow
return
}
# Stop the service if running
if ($service.Status -eq 'Running') {
Write-Host "Stopping service..." -ForegroundColor Cyan
Stop-Service -Name $ServiceName -Force
Start-Sleep -Seconds 2
}
# Remove the service
Write-Host "Removing service..." -ForegroundColor Cyan
sc.exe delete $ServiceName | Out-Null
Write-Host "Service '$ServiceName' uninstalled successfully!" -ForegroundColor Green
Write-Host ""
Write-Host "Note: Installation files remain at: $InstallPath" -ForegroundColor Yellow
Write-Host "To remove files: Remove-Item -Path '$InstallPath' -Recurse -Force" -ForegroundColor Yellow
}
function Reinstall-MsSqlMCPService {
Write-Host "Reinstalling MsSqlMCP Windows Service..." -ForegroundColor Cyan
Uninstall-MsSqlMCPService
Start-Sleep -Seconds 2
Install-MsSqlMCPService
}
# Execute based on action
switch ($Action) {
"Install" { Install-MsSqlMCPService }
"Uninstall" { Uninstall-MsSqlMCPService }
"Reinstall" { Reinstall-MsSqlMCPService }
"Status" { Get-ServiceStatus }
}
```
--------------------------------------------------------------------------------
/SchemaTool.cs:
--------------------------------------------------------------------------------
```csharp
using System.ComponentModel;
using ModelContextProtocol.Server;
using MsSqlMCP.Interfaces;
namespace MsSqlMCP;
/// <summary>
/// MCP Server tools for querying SQL Server database schema and executing read-only queries.
/// All methods use dependency injection for better testability and separation of concerns.
/// </summary>
[McpServerToolType]
public class SchemaTool
{
private readonly ISchemaRepository _schemaRepository;
private readonly IQueryExecutor _queryExecutor;
public SchemaTool(ISchemaRepository schemaRepository, IQueryExecutor queryExecutor)
{
_schemaRepository = schemaRepository;
_queryExecutor = queryExecutor;
}
[McpServerTool, Description("Get tables name of database. Optionally, specify a database name to query a different database in the same instance.")]
public async Task<string> GetTables(string? databaseName = null)
{
var tables = await _schemaRepository.GetTablesAsync(databaseName);
var dbInfo = FormatDatabaseInfo(databaseName);
return $"Tables{dbInfo}:\n\n{string.Join("\n", tables)}";
}
[McpServerTool, Description("Get the columns (fields) of a database table. Optionally, specify a database name to query a different database in the same instance.")]
public async Task<string> GetColumns(string tableName, string? databaseName = null)
{
if (string.IsNullOrWhiteSpace(tableName))
{
return "Please specify the table name to query its fields.";
}
// Extract table name if it contains extra text
var cleanTableName = ExtractTableName(tableName);
if (string.IsNullOrEmpty(cleanTableName))
{
return "Please specify the table name to query its fields.";
}
var columns = await _schemaRepository.GetColumnsAsync(cleanTableName, databaseName);
var dbInfo = FormatDatabaseInfo(databaseName);
if (columns.Count == 0)
{
return $"No columns found for table '{cleanTableName}'{dbInfo}. Verify the table name is correct.";
}
return $"Columns in the table {cleanTableName}{dbInfo}:\n\n{string.Join("\n", columns)}";
}
[McpServerTool, Description("Get the relationships between tables in the database. Optionally, specify a database name to query a different database in the same instance.")]
public async Task<string> GetRelationships(string? databaseName = null)
{
var relationships = await _schemaRepository.GetRelationshipsAsync(databaseName);
var dbInfo = FormatDatabaseInfo(databaseName);
if (relationships.Count == 0)
{
return $"No foreign key relationships found{dbInfo}.";
}
return $"Relationships between tables{dbInfo}:\n\n{string.Join("\n", relationships)}";
}
[McpServerTool, Description("Execute a read-only SQL query (SELECT only). INSERT, UPDATE, DELETE and other modifying statements are blocked for security. Optionally, specify a database name to query a different database in the same instance.")]
public async Task<string> ExecuteSql(string sqlQuery, string? databaseName = null)
{
return await _queryExecutor.ExecuteReadOnlyQueryAsync(sqlQuery, databaseName);
}
[McpServerTool, Description("Get the definition of a stored procedure by name. Optionally, specify a database name to query a different database in the same instance.")]
public async Task<string> GetStoreProcedure(string spName, string? databaseName = null)
{
if (string.IsNullOrWhiteSpace(spName))
{
return "Stored procedure name cannot be empty.";
}
var procedures = await _schemaRepository.GetStoredProcedureDefinitionAsync(spName, databaseName);
var dbInfo = FormatDatabaseInfo(databaseName);
if (procedures.Count == 0)
{
return $"Stored procedure '{spName}' not found{dbInfo}.";
}
return $"Stored procedure '{spName}'{dbInfo}:\n\n{string.Join("\n\n", procedures)}";
}
/// <summary>
/// Formats database name for display in output messages.
/// </summary>
private static string FormatDatabaseInfo(string? databaseName) =>
string.IsNullOrWhiteSpace(databaseName) ? string.Empty : $" (database '{databaseName}')";
/// <summary>
/// Extracts a clean table name from input that may contain additional text.
/// Handles cases like "table Users" or "tabla Customers".
/// </summary>
private static string ExtractTableName(string input)
{
var words = input.Split(new[] { ' ', '\t', '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < words.Length; i++)
{
var word = words[i].ToLowerInvariant();
// If we find "table" or "tabla" keyword, return the next word
if ((word == "tabla" || word == "table") && i + 1 < words.Length)
{
return CleanTableName(words[i + 1]);
}
}
// If no keyword found, return the first word (assumed to be the table name)
return words.Length > 0 ? CleanTableName(words[0]) : string.Empty;
}
/// <summary>
/// Removes common punctuation from table names.
/// </summary>
private static string CleanTableName(string name) =>
name.Trim(',', '.', ':', ';', '?', '!', '"', '\'', '[', ']');
}
```
--------------------------------------------------------------------------------
/Services/SchemaRepository.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using MsSqlMCP.Interfaces;
namespace MsSqlMCP.Services;
/// <summary>
/// Repository implementation for querying SQL Server schema information.
/// </summary>
public class SchemaRepository : ISchemaRepository
{
private readonly IConnectionFactory _connectionFactory;
private readonly ILogger<SchemaRepository> _logger;
public SchemaRepository(IConnectionFactory connectionFactory, ILogger<SchemaRepository> logger)
{
_connectionFactory = connectionFactory;
_logger = logger;
}
/// <inheritdoc />
public async Task<IReadOnlyList<string>> GetTablesAsync(string? databaseName = null)
{
_logger.LogDebug("Getting tables list for database: {DatabaseName}", databaseName ?? "default");
await using var connection = await _connectionFactory.CreateOpenConnectionAsync(databaseName);
const string sql = @"
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME";
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
var tables = new List<string>();
while (await reader.ReadAsync())
{
var schema = reader.GetString(0);
var tableName = reader.GetString(1);
tables.Add($"{schema}.{tableName}");
}
_logger.LogDebug("Found {Count} tables", tables.Count);
return tables;
}
/// <inheritdoc />
public async Task<IReadOnlyList<string>> GetColumnsAsync(string tableName, string? databaseName = null)
{
var (schema, table) = ParseTableName(tableName);
_logger.LogDebug("Getting columns for table: {Schema}.{Table}", schema, table);
await using var connection = await _connectionFactory.CreateOpenConnectionAsync(databaseName);
const string sql = @"
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY,
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) as IS_PRIMARY_KEY
FROM
INFORMATION_SCHEMA.COLUMNS c
WHERE
TABLE_SCHEMA = @schema
AND TABLE_NAME = @tableName
ORDER BY
ORDINAL_POSITION";
using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@schema", schema);
command.Parameters.AddWithValue("@tableName", table);
using var reader = await command.ExecuteReaderAsync();
var columns = new List<string>();
while (await reader.ReadAsync())
{
var columnName = reader.GetString(0);
var dataType = reader.GetString(1);
var charMaxLength = reader.IsDBNull(2) ? null : reader.GetValue(2)?.ToString();
var isNullable = reader.GetString(3);
var isIdentity = reader.GetInt32(4);
var isPrimaryKey = reader.GetInt32(5);
var lengthInfo = charMaxLength != null ? $"({charMaxLength})" : "";
var nullableInfo = isNullable == "YES" ? "NULL" : "NOT NULL";
var identityInfo = isIdentity == 1 ? " IDENTITY" : "";
var pkInfo = isPrimaryKey > 0 ? " PRIMARY KEY" : "";
columns.Add($"{columnName} | {dataType}{lengthInfo} | {nullableInfo}{identityInfo}{pkInfo}");
}
_logger.LogDebug("Found {Count} columns for table {Table}", columns.Count, tableName);
return columns;
}
/// <inheritdoc />
public async Task<IReadOnlyList<string>> GetRelationshipsAsync(string? databaseName = null)
{
_logger.LogDebug("Getting relationships for database: {DatabaseName}", databaseName ?? "default");
await using var connection = await _connectionFactory.CreateOpenConnectionAsync(databaseName);
const string sql = @"
SELECT
fk.name AS ForeignKey,
OBJECT_NAME(fk.parent_object_id) AS TableName,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName,
OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumnName
FROM
sys.foreign_keys AS fk
INNER JOIN
sys.foreign_key_columns AS fkc
ON fk.OBJECT_ID = fkc.constraint_object_id
ORDER BY
TableName, ReferencedTableName";
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
var relationships = new List<string>();
while (await reader.ReadAsync())
{
var foreignKey = reader.GetString(0);
var tableName = reader.GetString(1);
var columnName = reader.GetString(2);
var referencedTableName = reader.GetString(3);
var referencedColumnName = reader.GetString(4);
relationships.Add($"{tableName}.{columnName} -> {referencedTableName}.{referencedColumnName} (FK: {foreignKey})");
}
_logger.LogDebug("Found {Count} relationships", relationships.Count);
return relationships;
}
/// <inheritdoc />
public async Task<IReadOnlyList<string>> GetStoredProcedureDefinitionAsync(string spName, string? databaseName = null)
{
_logger.LogDebug("Getting stored procedure definition: {SpName}", spName);
await using var connection = await _connectionFactory.CreateOpenConnectionAsync(databaseName);
const string sql = @"
SELECT name, object_definition(object_id)
FROM sys.procedures
WHERE name = @spName";
using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@spName", spName);
using var reader = await command.ExecuteReaderAsync();
var procedures = new List<string>();
while (await reader.ReadAsync())
{
var name = reader.GetString(0);
var definition = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
procedures.Add($"{name}:\n{definition}");
}
_logger.LogDebug("Found {Count} procedures matching name {SpName}", procedures.Count, spName);
return procedures;
}
/// <summary>
/// Parses a table name that may include schema prefix.
/// </summary>
private static (string Schema, string Table) ParseTableName(string tableName)
{
if (tableName.Contains('.'))
{
var parts = tableName.Split('.', 2);
return (parts[0], parts[1]);
}
return ("dbo", tableName);
}
}
```
--------------------------------------------------------------------------------
/Tests/ReadOnlySqlQueryValidatorTests.cs:
--------------------------------------------------------------------------------
```csharp
using MsSqlMCP.Interfaces;
using MsSqlMCP.Services;
using Xunit;
namespace MsSqlMCP.Tests;
/// <summary>
/// Unit tests for ReadOnlySqlQueryValidator to ensure security.
/// </summary>
public class ReadOnlySqlQueryValidatorTests
{
private readonly ISqlQueryValidator _validator = new ReadOnlySqlQueryValidator();
#region Valid SELECT Queries
[Theory]
[InlineData("SELECT * FROM Users")]
[InlineData("select id, name from products")]
[InlineData("SELECT TOP 10 * FROM Orders ORDER BY CreatedDate DESC")]
[InlineData("SELECT COUNT(*) FROM Customers WHERE IsActive = 1")]
[InlineData("SELECT a.*, b.Name FROM TableA a JOIN TableB b ON a.Id = b.AId")]
public void Validate_SimpleSelectQueries_ReturnsValid(string query)
{
var result = _validator.Validate(query);
Assert.True(result.IsValid, $"Query should be valid: {query}. Error: {result.ErrorMessage}");
}
[Theory]
[InlineData("WITH cte AS (SELECT 1 as Id) SELECT * FROM cte")]
[InlineData("WITH Orders_CTE AS (SELECT * FROM Orders) SELECT * FROM Orders_CTE")]
public void Validate_CTEQueries_ReturnsValid(string query)
{
var result = _validator.Validate(query);
Assert.True(result.IsValid, $"CTE query should be valid: {query}. Error: {result.ErrorMessage}");
}
[Theory]
[InlineData("SELECT * FROM Users WHERE UpdatedDate > '2023-01-01'")] // Column named UpdatedDate
[InlineData("SELECT DeletedAt, InsertedBy FROM AuditLog")] // Column names containing keywords
[InlineData("SELECT * FROM CREATE_LOG")] // Table name containing keyword
public void Validate_QueriesWithKeywordLikeNames_ReturnsValid(string query)
{
var result = _validator.Validate(query);
Assert.True(result.IsValid, $"Query with keyword-like names should be valid: {query}. Error: {result.ErrorMessage}");
}
#endregion
#region Invalid DML Queries - All should be blocked
[Theory]
[InlineData("INSERT INTO Users (Name) VALUES ('Test')")]
[InlineData("insert into products values (1, 'test', 10.99)")]
public void Validate_InsertQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"INSERT query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("UPDATE Users SET Name = 'Test' WHERE Id = 1")]
[InlineData("update products set price = 20.00")]
public void Validate_UpdateQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"UPDATE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("DELETE FROM Users WHERE Id = 1")]
[InlineData("delete from orders")]
public void Validate_DeleteQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"DELETE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("TRUNCATE TABLE Users")]
[InlineData("truncate table logs")]
public void Validate_TruncateQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"TRUNCATE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("MERGE INTO Target USING Source ON Target.Id = Source.Id WHEN MATCHED THEN UPDATE SET Name = Source.Name")]
public void Validate_MergeQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"MERGE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
#endregion
#region Invalid DDL Queries
[Theory]
[InlineData("DROP TABLE Users")]
[InlineData("drop database TestDb")]
[InlineData("DROP INDEX IX_Users_Name ON Users")]
public void Validate_DropQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"DROP query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("ALTER TABLE Users ADD Email VARCHAR(255)")]
[InlineData("alter table products drop column description")]
public void Validate_AlterQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"ALTER query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("CREATE TABLE NewTable (Id INT PRIMARY KEY)")]
[InlineData("create index IX_Test on Users(Name)")]
[InlineData("CREATE PROCEDURE sp_Test AS SELECT 1")]
public void Validate_CreateQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"CREATE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
#endregion
#region Invalid Execution Queries
[Theory]
[InlineData("EXEC sp_DeleteAllUsers")]
[InlineData("execute sp_DropDatabase")]
[InlineData("EXEC('DELETE FROM Users')")]
public void Validate_ExecQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"EXEC query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
#endregion
#region SQL Injection Attempts
[Theory]
[InlineData("SELECT * FROM Users; DELETE FROM Users")]
[InlineData("SELECT 1; DROP TABLE Users")]
[InlineData("SELECT * FROM Users; INSERT INTO Logs VALUES ('hacked')")]
public void Validate_MultipleStatements_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"Multiple statements should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("SELECT * FROM Users WHERE Id = 1; --\nDELETE FROM Users")]
public void Validate_CommentInjection_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"Comment injection should be blocked: {query}");
}
#endregion
#region Edge Cases
[Theory]
[InlineData("")]
[InlineData(" ")]
[InlineData(null)]
public void Validate_EmptyOrNullQueries_ReturnsInvalid(string? query)
{
var result = _validator.Validate(query!);
Assert.False(result.IsValid);
Assert.Contains("empty", result.ErrorMessage!, StringComparison.OrdinalIgnoreCase);
}
[Theory]
[InlineData("GRANT SELECT ON Users TO TestUser")]
[InlineData("REVOKE ALL ON Database TO TestUser")]
[InlineData("DENY INSERT ON Users TO TestUser")]
public void Validate_DCLQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"DCL query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
[Theory]
[InlineData("BACKUP DATABASE TestDb TO DISK = 'C:\\backup.bak'")]
[InlineData("RESTORE DATABASE TestDb FROM DISK = 'C:\\backup.bak'")]
public void Validate_BackupRestoreQueries_ReturnsInvalid(string query)
{
var result = _validator.Validate(query);
Assert.False(result.IsValid, $"BACKUP/RESTORE query should be blocked: {query}");
Assert.NotNull(result.ErrorMessage);
}
#endregion
}
```
--------------------------------------------------------------------------------
/Program.cs:
--------------------------------------------------------------------------------
```csharp
// MCP Server Entry Point - Supports both stdio and HTTP transports
// Can run as Console App or Windows Service
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System.ComponentModel;
using System.Reflection;
using System.Text.Json;
using ModelContextProtocol.Server;
using MsSqlMCP;
using MsSqlMCP.Interfaces;
using MsSqlMCP.Services;
// Detect if running in HTTP-only mode (for debugging)
bool httpOnly = args.Contains("--http-only");
// Configure content root to the application directory (important for Windows Service)
var options = new WebApplicationOptions
{
Args = args,
ContentRootPath = AppContext.BaseDirectory
};
var builder = WebApplication.CreateBuilder(options);
// Enable Windows Service support (context-aware: works as console or service)
builder.Host.UseWindowsService();
// Configure logging
builder.Logging.ClearProviders();
builder.Logging.AddConsole(consoleLogOptions =>
{
// Log to stderr to avoid polluting stdio transport
consoleLogOptions.LogToStandardErrorThreshold = LogLevel.Trace;
});
// Ensure logs directory exists for Windows Service mode
var logsPath = Path.Combine(AppContext.BaseDirectory, "logs");
Directory.CreateDirectory(logsPath);
// Add timestamped console logging
builder.Logging.AddSimpleConsole(options =>
{
options.TimestampFormat = "[yyyy-MM-dd HH:mm:ss] ";
});
// Register application services
builder.Services.AddSingleton<IConnectionFactory, SqlConnectionFactory>();
builder.Services.AddSingleton<ISqlQueryValidator, ReadOnlySqlQueryValidator>();
builder.Services.AddScoped<ISchemaRepository, SchemaRepository>();
builder.Services.AddScoped<IQueryExecutor, SafeQueryExecutor>();
builder.Services.AddScoped<SchemaTool>();
// Configure MCP Server
var mcpBuilder = builder.Services.AddMcpServer();
if (!httpOnly)
{
mcpBuilder.WithStdioServerTransport();
}
mcpBuilder.WithHttpTransport()
.WithToolsFromAssembly();
var app = builder.Build();
// Map MCP endpoints (including /sse for SSE transport)
app.MapMcp();
// Endpoint to get the list of available tools
app.MapGet("/sse/tools", () =>
{
var tools = DiscoverMcpTools();
return Results.Ok(tools);
});
// Endpoint to invoke tools via HTTP
app.MapPost("/sse/invoke", async (HttpContext context, IServiceProvider serviceProvider) =>
{
ToolInvokeRequest? request;
try
{
request = await context.Request.ReadFromJsonAsync<ToolInvokeRequest>();
}
catch
{
return Results.BadRequest("Invalid JSON request");
}
if (request == null || string.IsNullOrEmpty(request.Tool))
{
return Results.BadRequest("Invalid request: Tool name is required");
}
try
{
// Find the tool method
var toolTypes = AppDomain.CurrentDomain.GetAssemblies()
.SelectMany(a => a.GetTypes())
.Where(t => t.GetCustomAttribute<McpServerToolTypeAttribute>() != null);
foreach (var type in toolTypes)
{
var method = type.GetMethods(BindingFlags.Public | BindingFlags.Instance)
.FirstOrDefault(m => m.Name == request.Tool &&
m.GetCustomAttribute<McpServerToolAttribute>() != null);
if (method != null)
{
// Create instance using DI
using var scope = serviceProvider.CreateScope();
var instance = scope.ServiceProvider.GetRequiredService(type);
// Convert request parameters to method parameters
var parameters = method.GetParameters();
var methodArgs = new object?[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
var param = parameters[i];
if (request.Params != null && request.Params.TryGetValue(param.Name!, out var value))
{
if (value is JsonElement jsonElement)
{
methodArgs[i] = jsonElement.Deserialize(param.ParameterType);
}
else
{
methodArgs[i] = Convert.ChangeType(value, param.ParameterType);
}
}
else
{
methodArgs[i] = param.HasDefaultValue ? param.DefaultValue : null;
}
}
// Invoke the method
var result = method.Invoke(instance, methodArgs);
// If async, await the result
if (result is Task task)
{
await task;
var resultProperty = task.GetType().GetProperty("Result");
result = resultProperty?.GetValue(task);
}
return Results.Ok(result);
}
}
return Results.NotFound($"Tool '{request.Tool}' not found");
}
catch (Exception ex)
{
return Results.Problem($"Error invoking tool: {ex.Message}");
}
});
await app.RunAsync();
// Helper methods for tool discovery
static List<McpToolDescriptor> DiscoverMcpTools()
{
var tools = new List<McpToolDescriptor>();
var toolTypes = AppDomain.CurrentDomain.GetAssemblies()
.SelectMany(a => a.GetTypes())
.Where(t => t.GetCustomAttribute<McpServerToolTypeAttribute>() != null);
foreach (var type in toolTypes)
{
var methods = type.GetMethods(BindingFlags.Public | BindingFlags.Instance)
.Where(m => m.GetCustomAttribute<McpServerToolAttribute>() != null);
foreach (var method in methods)
{
var descriptor = new McpToolDescriptor
{
Name = method.Name,
Description = method.GetCustomAttribute<DescriptionAttribute>()?.Description,
Parameters = BuildParametersSchema(method)
};
tools.Add(descriptor);
}
}
return tools;
}
static JsonElement? BuildParametersSchema(MethodInfo method)
{
var parameters = method.GetParameters();
if (parameters.Length == 0)
{
return null;
}
var properties = new Dictionary<string, object>();
var required = new List<string>();
foreach (var param in parameters)
{
var paramType = Nullable.GetUnderlyingType(param.ParameterType) ?? param.ParameterType;
var isNullable = param.ParameterType != paramType || param.HasDefaultValue;
var paramSchema = new Dictionary<string, object>
{
["type"] = GetJsonSchemaType(paramType)
};
var description = param.GetCustomAttribute<DescriptionAttribute>()?.Description;
if (description != null)
{
paramSchema["description"] = description;
}
properties[param.Name!] = paramSchema;
if (!isNullable && !param.HasDefaultValue)
{
required.Add(param.Name!);
}
}
var schema = new Dictionary<string, object>
{
["type"] = "object",
["properties"] = properties
};
if (required.Count > 0)
{
schema["required"] = required;
}
var json = JsonSerializer.Serialize(schema);
return JsonSerializer.Deserialize<JsonElement>(json);
}
static string GetJsonSchemaType(Type type)
{
if (type == typeof(string)) return "string";
if (type == typeof(int) || type == typeof(long)) return "integer";
if (type == typeof(double) || type == typeof(float) || type == typeof(decimal)) return "number";
if (type == typeof(bool)) return "boolean";
if (type.IsArray || (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List<>))) return "array";
return "object";
}
// Types for HTTP endpoints
record ToolInvokeRequest(string Tool, Dictionary<string, object>? Params);
sealed class McpToolDescriptor
{
public string Name { get; set; } = default!;
public string? Description { get; set; }
public JsonElement? Parameters { get; set; }
}
```