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