What Is an MCP Server and How to Build One: Five Approaches from No-Code to .NET SQL Server

---

The Model Context Protocol has gone from an Anthropic specification published in November 2024 to a de-facto standard in under two years. Claude, GitHub Copilot, Cursor, and VS Code all support MCP clients. The SQL Server community is building MCP servers to expose database queries, stored procedures, and schema information directly to AI agents. But most of the documentation written about MCP either explains the concept without building anything practical, or jumps straight into Python examples without explaining what an MCP server actually is underneath.

This article explains what an MCP server is, what the three things it can expose are, how transport works, and then provides five concrete approaches to building one: from a no-code JSON configuration all the way through a production .NET server connecting to SQL Server using the official ModelContextProtocol SDK. All package names, version numbers, and protocol details are verified from official sources as of July 2026.

1 What an MCP Server Actually Is Beginner

The Model Context Protocol is an open standard that defines how AI applications discover and invoke external capabilities. An MCP server is a process that exposes capabilities following this standard. An MCP client is an AI application that connects to one or more MCP servers, discovers what they can do, and invokes those capabilities when generating responses.

The analogy that clarifies this best: think of MCP as USB-C for AI tools. USB-C is an open standard so any device that speaks USB-C can connect to any port that speaks USB-C, without needing a custom cable for each combination. MCP is the same idea for AI and tools. Any MCP client can connect to any MCP server. An MCP server built for Claude works identically for GitHub Copilot, Cursor, or any other MCP-compatible client. The connection is standardized. Only the capability logic inside the server is custom.

Before MCP, every AI application had its own plugin system. Claude had its own tool format. OpenAI had its own function calling format. GitHub Copilot had its own extension mechanism. A team that wanted their SQL Server tools available in all three had to implement three separate integrations. With MCP, one server implementation works everywhere.

The protocol is built on JSON-RPC 2.0. When a client connects, it asks the server what capabilities it has. The server responds with a list of its tools, resources, and prompts along with their schemas. The client presents these to the AI model. When the model decides to invoke a capability, the client sends a JSON-RPC request to the server and returns the response to the model. The developer writes only the capability logic. The protocol handling is entirely managed by the SDK.

2 The Three Primitives: Tools, Resources, and Prompts Beginner

Every MCP server exposes some combination of three primitive types. Understanding the distinction between them is the most important conceptual step before building anything. Most tutorials only use Tools and skip Resources and Prompts, which means most MCP servers in the wild are underdesigned for their use case.

Tools: things the AI can execute

A Tool is a function the AI model can invoke. It takes parameters defined by a JSON Schema, executes some logic, and returns a result. Tools are for anything with execution behavior: running a SQL query, calling an API, writing a file, inserting a record. The key characteristic is that the model decides when to call a Tool based on the conversation context. The model reads the Tool’s name and description, decides it needs that capability, and the client invokes it.

For a SQL Server MCP server, examples of Tools: execute a parameterized query and return results, call a stored procedure, check blocking sessions, get current error log entries, run a backup command.

Resources: data the AI can read

A Resource is a read-only, URI-addressable data source. Unlike a Tool which is invoked on demand, a Resource is data the client can fetch and load into the model’s context window. Resources are for stable reference data that does not change rapidly: database schemas, table column lists, stored procedure definitions, configuration settings, documentation.

The URI addressing matters. A Resource is registered with a URI like sqlserver://schema/dbo.Customers or schema://tables/list. The client fetches the resource content and provides it to the model as background context. For SQL Server, a Resource exposing the schema of a database gives the AI model the column names, data types, and relationships it needs to write accurate queries without hallucinating column names that do not exist.

Prompts: reusable templates

A Prompt is a parameterized message template the AI client can surface to the user as a shortcut or slash command. A Prompt named “health-check” might expand to a full SQL Server morning health check query template that the user can trigger with a slash command rather than typing a detailed request. Prompts are the least commonly implemented of the three primitives but are useful for standardizing common analytical workflows.

Primitive	Invoked By	Has Side Effects?	SQL Server Use Cases
Tool	AI model decides when to call it	Yes (can write, execute, modify)	Run queries, call stored procedures, check blocking, execute backups
Resource	Client fetches on request or at startup	No (read-only)	Table schemas, column lists, SP definitions, index configurations, wait stats snapshots
Prompt	User triggers via slash command or shortcut	No (template only)	Morning health check template, blocking analysis template, index fragmentation review template

3 Transport: stdio vs Streamable HTTP Beginner

Transport defines how the MCP client and server communicate. Two transports are in active use in 2026. A third (legacy SSE) is deprecated but still encountered.

stdio transport (local)

Standard input/output transport. The MCP client launches the server as a child process and communicates through stdin and stdout. This is the simplest approach for local tools running on the same machine as the AI client. Claude Desktop, VS Code Copilot, and Cursor all support stdio MCP servers by default. The server binary is configured in a JSON config file and the client manages the process lifecycle.

stdio is appropriate when: the server runs locally, the client and server are on the same machine, no network access is needed, and the server is personal or team-scoped rather than organization-wide.

Streamable HTTP transport (remote)

A single HTTP endpoint that accepts JSON-RPC requests via HTTP POST. The same endpoint optionally streams responses back via Server-Sent Events (SSE) for long-running operations. This is the standard for remote or production deployment where the MCP server runs on a server, in a container, or in a cloud function rather than on the developer’s local machine. Supports resumable sessions via Event IDs for streaming operations.

4 How AI Clients Connect to MCP Servers Beginner

Every MCP client stores its server configurations in a JSON file. The format is consistent across Claude Desktop, VS Code, Cursor, and other clients with minor variations.

Claude Desktop configuration

The configuration file lives at %APPDATA%\Claude\claude_desktop_config.json on Windows and ~/Library/Application Support/Claude/claude_desktop_config.json on Mac.

// claude_desktop_config.json
// Add MCP servers in the mcpServers object
// Each key is the server name shown in Claude Desktop
// Each value configures how to launch or connect to the server

{
  "mcpServers": {
    "sql-server-tools": {
      "command": "dotnet",
      "args": ["run", "--project", "C:\\Projects\\SqlMcpServer"],
      "env": {
        "CONNECTION_STRING": "Server=.;Database=AdventureWorks;Trusted_Connection=True;"
      }
    },
    "python-tools": {
      "command": "python",
      "args": ["C:\\Projects\\my_mcp_server.py"]
    },
    "remote-server": {
      "url": "https://my-mcp-server.company.com/mcp",
      "transport": "streamable-http"
    }
  }
}

VS Code (GitHub Copilot) configuration

Add to the workspace .vscode/mcp.json file or to VS Code user settings under github.copilot.chat.mcpServers. The structure is the same as Claude Desktop.

5 APPROACH 1 JSON Configuration Only (No Code) Beginner

The fastest way to get SQL Server data accessible to an AI client is to use an existing community MCP server with a JSON configuration file. No code is written. The server binary is already built and published. Configuration wires it to a specific SQL Server instance.

Several SQL Server MCP servers exist on GitHub and npm. The most commonly used pattern in the community is a TypeScript-based server that wraps MSSQL and exposes a query tool. Configuration in Claude Desktop or VS Code points to the package and passes the connection details.

// Claude Desktop config using an existing published MCP server
// This example uses a community SQL Server MCP server via npx
// No installation required - npx downloads on first run
// Replace connection details with actual values

{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-sqlite",
        "C:\\Databases\\analysis.db"
      ]
    }
  }
}

When to use this approach: Rapid evaluation and local development. SQL Server tools accessible to Claude Desktop in under five minutes. No control over what the server exposes. Cannot add custom business logic or additional tools. The correct starting point before committing to building a custom server.

6 APPROACH 2 Python with FastMCP Beginner

FastMCP is the Python library that powers approximately 70 percent of MCP servers across all languages, maintained by PrefectHQ. The current stable version is 3.2.4 (April 14, 2026). It wraps the official MCP Python SDK with a high-level decorator API that eliminates JSON-RPC boilerplate. Write a Python function with type annotations and a docstring, add a decorator, and FastMCP generates the JSON Schema, validates inputs, and routes invocations automatically.

# Install FastMCP
# pip install fastmcp pyodbc
# Or with uv: uv pip install fastmcp pyodbc

from fastmcp import FastMCP
import pyodbc
import os

# Create the MCP server with a name
mcp = FastMCP("SQL Server Tools")

# Connection string from environment variable - never hardcode credentials
CONNECTION_STRING = os.environ.get("SQL_CONNECTION_STRING",
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=localhost;"
    "Database=AdventureWorks;"
    "Trusted_Connection=yes;"
    "TrustServerCertificate=yes;")

def get_connection():
    """Return a SQL Server connection. Called fresh for each tool invocation."""
    return pyodbc.connect(CONNECTION_STRING)


# =============================================================
# TOOLS: things the AI can execute
# =============================================================

@mcp.tool()
def run_query(sql: str, max_rows: int = 100) -> str:
    """
    Execute a SELECT query against the SQL Server database and return results.
    Only SELECT statements are allowed. Never use this for INSERT, UPDATE, or DELETE.
    The max_rows parameter limits results to prevent large data transfers.

    Args:
        sql: A SELECT statement to execute. Must begin with SELECT.
        max_rows: Maximum number of rows to return. Default 100, maximum 1000.
    """
    # Safety: only allow SELECT statements
    if not sql.strip().upper().startswith("SELECT"):
        return "Error: Only SELECT statements are allowed through this tool."

    max_rows = min(max_rows, 1000)  # enforce ceiling

    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(sql)
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchmany(max_rows)

        # Format as a readable text table
        result = " | ".join(columns) + "\n"
        result += "-" * (len(result) - 1) + "\n"
        for row in rows:
            result += " | ".join(str(v) if v is not None else "NULL" for v in row) + "\n"
        result += f"\n({len(rows)} rows returned)"
        return result

    except Exception as e:
        return f"Query error: {str(e)}"


@mcp.tool()
def get_blocking_sessions() -> str:
    """
    Check for currently blocked SQL Server sessions.
    Returns session IDs, wait types, wait times, and the blocking chain.
    Use this when the database appears slow or unresponsive.
    """
    sql = """
        SELECT
            r.session_id,
            r.blocking_session_id,
            r.wait_type,
            r.wait_time / 1000 AS wait_seconds,
            r.status,
            DB_NAME(r.database_id) AS database_name,
            LEFT(t.text, 100) AS query_text
        FROM sys.dm_exec_requests r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
        WHERE r.blocking_session_id != 0
        ORDER BY r.wait_time DESC
    """
    return run_query(sql, max_rows=50)


@mcp.tool()
def call_stored_procedure(procedure_name: str, parameters: str = "") -> str:
    """
    Execute a SQL Server stored procedure that returns data.
    Only stored procedures in the dbo schema are allowed.
    Parameters should be provided as comma-separated values in the correct order.

    Args:
        procedure_name: Name of the stored procedure without schema prefix.
        parameters: Optional comma-separated parameter values.
    """
    # Restrict to dbo schema only
    safe_name = procedure_name.replace("[", "").replace("]", "").replace(";", "")
    sql = f"EXEC dbo.[{safe_name}]"
    if parameters:
        sql += f" {parameters}"
    return run_query(sql)


# =============================================================
# RESOURCES: read-only data the AI can fetch
# =============================================================

@mcp.resource("schema://tables")
def list_tables() -> str:
    """
    List all user tables in the database with their schemas and row counts.
    Use this resource to understand what data is available before writing queries.
    """
    sql = """
        SELECT
            SCHEMA_NAME(t.schema_id) AS schema_name,
            t.name AS table_name,
            p.rows AS row_count,
            t.create_date
        FROM sys.tables t
        JOIN sys.partitions p ON t.object_id = p.object_id
            AND p.index_id IN (0, 1)
        WHERE t.is_ms_shipped = 0
        ORDER BY schema_name, table_name
    """
    return run_query(sql, max_rows=500)


@mcp.resource("schema://table/{table_name}")
def get_table_schema(table_name: str) -> str:
    """
    Get the full column schema for a specific table including data types,
    nullability, and default values. Use before writing a query against any table.
    """
    sql = f"""
        SELECT
            c.name AS column_name,
            tp.name AS data_type,
            c.max_length,
            c.precision,
            c.scale,
            c.is_nullable,
            c.is_identity,
            dc.definition AS default_value
        FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.types tp ON c.user_type_id = tp.user_type_id
        LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
        WHERE t.name = ?
        ORDER BY c.column_id
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(sql, (table_name,))
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
        result = " | ".join(columns) + "\n" + "-" * 60 + "\n"
        for row in rows:
            result += " | ".join(str(v) if v is not None else "NULL" for v in row) + "\n"
        return result
    except Exception as e:
        return f"Error: {str(e)}"


# =============================================================
# PROMPTS: reusable templates the user can trigger
# =============================================================

@mcp.prompt()
def morning_health_check() -> str:
    """
    Template for a SQL Server morning health check analysis.
    Triggers a structured health assessment of the connected database.
    """
    return """
    Please perform a morning health check on this SQL Server instance.
    Check the following in order:
    1. Use get_blocking_sessions to check for any current blocking
    2. Use run_query to check for failed jobs in the last 24 hours using msdb.dbo.sysjobhistory
    3. Use run_query to check disk space using sys.dm_os_volume_stats
    4. Use run_query to check for databases not in ONLINE state
    5. Summarize all findings with a severity rating (CRITICAL / WARNING / OK) for each area
    """


# Run the server over stdio (for local Claude Desktop / VS Code / Cursor)
if __name__ == "__main__":
    mcp.run()   # defaults to stdio transport

# Add to Claude Desktop config to use this server:
# {
#   "mcpServers": {
#     "sql-tools": {
#       "command": "python",
#       "args": ["C:\\Projects\\sql_mcp_server.py"],
#       "env": {
#         "SQL_CONNECTION_STRING": "Driver={ODBC Driver 18 for SQL Server};Server=.;Database=AdventureWorks;Trusted_Connection=yes;TrustServerCertificate=yes;"
#       }
#     }
#   }
# }

7 APPROACH 3 Node.js and TypeScript with FastMCP-TS Intermediate

FastMCP for TypeScript is the official counterpart to FastMCP Python, built and maintained by the same PrefectHQ team. Install with npm install @prefecthq/fastmcp-ts. The API is similar to the Python version: decorators define Tools, Resources, and Prompts. TypeScript is a strong choice for teams already working in the Node.js ecosystem or when building an MCP server that also serves as an API layer for a web application.

// Install: npm install @prefecthq/fastmcp-ts mssql
// TypeScript MCP server connecting to SQL Server via mssql

import { FastMCP, tool, resource } from "@prefecthq/fastmcp-ts";
import * as sql from "mssql";
import { z } from "zod";

const mcp = new FastMCP("SQL Server Tools TS");

const sqlConfig: sql.config = {
    server       : process.env.SQL_SERVER   || "localhost",
    database     : process.env.SQL_DATABASE || "AdventureWorks",
    options: {
        trustedConnection     : true,
        trustServerCertificate: true
    }
};

// Tool: run a SELECT query
mcp.addTool({
    name       : "run_query",
    description: "Execute a SELECT query and return results. Only SELECT allowed.",
    parameters : z.object({
        sql     : z.string().describe("A SELECT statement to execute"),
        max_rows: z.number().default(100).describe("Maximum rows to return")
    }),
    execute: async ({ sql: query, max_rows }) => {
        if (!query.trim().toUpperCase().startsWith("SELECT")) {
            return "Error: Only SELECT statements are allowed.";
        }
        const pool   = await new sql.ConnectionPool(sqlConfig).connect();
        const result = await pool.request().query(query);
        await pool.close();
        const rows = result.recordset.slice(0, max_rows);
        return JSON.stringify(rows, null, 2);
    }
});

// Resource: list all tables
mcp.addResource({
    uri        : "schema://tables",
    name       : "Database Tables",
    description: "All user tables in the database with row counts",
    load: async () => {
        const pool   = await new sql.ConnectionPool(sqlConfig).connect();
        const result = await pool.request().query(
            "SELECT SCHEMA_NAME(schema_id) AS schema_name, name, create_date FROM sys.tables WHERE is_ms_shipped = 0 ORDER BY name"
        );
        await pool.close();
        return { text: JSON.stringify(result.recordset, null, 2) };
    }
});

// Start the server
mcp.run({ transport: "stdio" });

8 APPROACH 4 .NET with ModelContextProtocol Intermediate

The official C# SDK (repository: modelcontextprotocol/csharp-sdk) is co-maintained by Anthropic and Microsoft. Version 2.0 was released July 28, 2026. This is the Tier 1 recommended approach for SQL Server and .NET teams because it uses Microsoft.Data.SqlClient natively, integrates with .NET dependency injection, supports appsettings.json for configuration, and produces a self-contained executable that Claude Desktop can launch directly.

Three NuGet packages (choose based on need)

• ModelContextProtocol.Core: Minimum dependencies. Only needed for client or low-level server APIs.
• ModelContextProtocol: Main package with hosting and DI extensions. The right choice for most stdio MCP servers.
• ModelContextProtocol.AspNetCore: For HTTP-based servers deployed over Streamable HTTP. References the main package.

// Create a new .NET console application
// dotnet new console -n SqlMcpServer
// cd SqlMcpServer
// dotnet add package ModelContextProtocol
// dotnet add package Microsoft.Data.SqlClient
// dotnet add package Microsoft.Extensions.Hosting

// Program.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using ModelContextProtocol.Server;
using System.ComponentModel;

var builder = Host.CreateApplicationBuilder(args);

// Suppress console logging - MCP over stdio uses stdout/stdin for protocol
// logging to stdout would corrupt the MCP message stream
builder.Logging.ClearProviders();
builder.Logging.AddFilter(level => level >= LogLevel.Warning);

// Register the connection string from environment or appsettings
var connectionString = builder.Configuration["SQL_CONNECTION_STRING"]
    ?? "Server=.;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;";

builder.Services.AddSingleton(_ => new SqlConnectionFactory(connectionString));

// Register the MCP server with stdio transport
builder.Services
    .AddMcpServer()                 // registers the MCP server infrastructure
    .WithStdioServerTransport()     // stdio for local Claude Desktop / VS Code
    .WithTools();   // register our tool class

var host = builder.Build();
await host.RunAsync();

// SqlConnectionFactory.cs
// Simple factory to create SQL Server connections

public class SqlConnectionFactory(string connectionString)
{
    public SqlConnection CreateConnection() => new(connectionString);
}

// SqlServerTools.cs
// All MCP Tools are defined as methods with [McpServerTool] attribute
// The method summary becomes the tool description the AI model reads
// Parameter XML doc comments become parameter descriptions in the JSON Schema

using Microsoft.Data.SqlClient;
using ModelContextProtocol.Server;
using System.ComponentModel;
using System.Text;

[McpServerToolType]
public class SqlServerTools(SqlConnectionFactory connectionFactory)
{
    /// 

    /// Execute a SELECT query against SQL Server and return the results as formatted text.
    /// Use this to answer questions about data in the database.
    /// Only SELECT statements are permitted. UPDATE, INSERT, DELETE, and DDL are blocked.
    /// 
    /// The SELECT statement to execute. Must begin with SELECT.
    /// Maximum rows to return. Default 100. Maximum 500.
    [McpServerTool]
    [Description("Execute a SELECT query and return results. Only SELECT statements allowed.")]
    public async Task RunQuery(string sql, int maxRows = 100)
    {
        // Safety: block non-SELECT statements
        var trimmed = sql.TrimStart();
        if (!trimmed.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            return "Error: Only SELECT statements are permitted through this tool.";

        maxRows = Math.Min(maxRows, 500);

        try
        {
            using var conn = connectionFactory.CreateConnection();
            await conn.OpenAsync();
            using var cmd = new SqlCommand(sql, conn) { CommandTimeout = 30 };
            using var reader = await cmd.ExecuteReaderAsync();

            var sb = new StringBuilder();

            // Header row
            var columns = Enumerable.Range(0, reader.FieldCount)
                                    .Select(i => reader.GetName(i))
                                    .ToList();
            sb.AppendLine(string.Join(" | ", columns));
            sb.AppendLine(new string('-', columns.Sum(c => c.Length + 3)));

            int rowCount = 0;
            while (await reader.ReadAsync() && rowCount < maxRows)
            {
                var values = Enumerable.Range(0, reader.FieldCount)
                    .Select(i => reader.IsDBNull(i) ? "NULL" : reader.GetValue(i).ToString() ?? "")
                    .ToList();
                sb.AppendLine(string.Join(" | ", values));
                rowCount++;
            }

            sb.AppendLine($"({rowCount} rows returned)");
            return sb.ToString();
        }
        catch (SqlException ex)
        {
            return $"SQL Error {ex.Number}: {ex.Message}";
        }
        catch (Exception ex)
        {
            return $"Error: {ex.Message}";
        }
    }

    /// 

    /// List all user tables in the current database with their schemas and approximate row counts.
    /// Use this before writing any query to understand the available data.
    /// 
    [McpServerTool]
    [Description("List all tables in the database with schema names and row counts.")]
    public Task ListTables()
    {
        const string sql = """
            SELECT
                SCHEMA_NAME(t.schema_id)    AS SchemaName,
                t.name                      AS TableName,
                p.rows                      AS ApproxRows,
                t.create_date               AS CreatedDate
            FROM sys.tables t
            JOIN sys.partitions p
                ON t.object_id = p.object_id
                AND p.index_id IN (0, 1)
            WHERE t.is_ms_shipped = 0
            ORDER BY SchemaName, TableName
            """;
        return RunQuery(sql, 500);
    }

    /// 

    /// Get the column schema for a specific table including data types and nullability.
    /// Always call this before writing a query against an unfamiliar table.
    /// 
    /// The table name without schema prefix.
    /// The schema name. Defaults to dbo.
    [McpServerTool]
    [Description("Get column definitions for a specific table. Call before writing queries.")]
    public Task GetTableSchema(string tableName, string schemaName = "dbo")
    {
        var sql = $"""
            SELECT
                c.name              AS ColumnName,
                tp.name             AS DataType,
                c.max_length        AS MaxLength,
                c.precision,
                c.scale,
                c.is_nullable       AS Nullable,
                c.is_identity       AS IsIdentity
            FROM sys.columns c
            JOIN sys.tables t   ON c.object_id = t.object_id
            JOIN sys.schemas s  ON t.schema_id = s.schema_id
            JOIN sys.types tp   ON c.user_type_id = tp.user_type_id
            WHERE t.name = '{tableName}' AND s.name = '{schemaName}'
            ORDER BY c.column_id
            """;
        return RunQuery(sql, 200);
    }

    /// 

    /// Check for SQL Server blocking sessions right now.
    /// Returns the blocked session, what it is waiting for, and the blocking session chain.
    /// Use when the database appears slow or queries are hanging.
    /// 
    [McpServerTool]
    [Description("Check for current blocking in SQL Server. Use when queries are slow or hanging.")]
    public Task GetBlockingSessions()
    {
        const string sql = """
            SELECT
                r.session_id,
                r.blocking_session_id,
                r.wait_type,
                r.wait_time / 1000          AS WaitTimeSec,
                r.status,
                DB_NAME(r.database_id)      AS DatabaseName,
                LEFT(t.text, 100)           AS QueryText
            FROM sys.dm_exec_requests       r
            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
            WHERE r.blocking_session_id != 0
            ORDER BY r.wait_time DESC
            """;
        return RunQuery(sql, 50);
    }

    /// 

    /// Get the SQL Server error log entries from the last 24 hours.
    /// Shows errors, warnings, and key SQL Server events.
    /// Use when investigating unexpected behavior or recent failures.
    /// 
    [McpServerTool]
    [Description("Read recent SQL Server error log entries from the last 24 hours.")]
    public Task GetErrorLog()
    {
        const string sql = """
            CREATE TABLE #errorlog (LogDate DATETIME, ProcessInfo NVARCHAR(100), LogText NVARCHAR(4000));
            INSERT INTO #errorlog EXEC xp_readerrorlog 0, 1;
            SELECT TOP 50 LogDate, ProcessInfo, LEFT(LogText, 200) AS LogText
            FROM #errorlog
            WHERE LogDate >= DATEADD(HOUR, -24, GETUTCDATE())
            AND (LogText LIKE '%error%' OR LogText LIKE '%failed%' OR LogText LIKE '%warning%')
            ORDER BY LogDate DESC;
            DROP TABLE #errorlog;
            """;
        return RunQuery(sql, 50);
    }
}

// appsettings.json (optional - can use environment variables instead)
{
  "SQL_CONNECTION_STRING": "Server=.;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;"
}

// To build a self-contained executable for Claude Desktop:
// dotnet publish -r win-x64 --self-contained -c Release
// Points the claude_desktop_config.json args to the published .exe

9 APPROACH 5 ASP.NET Core with Streamable HTTP Transport Advanced

When the MCP server needs to be accessible remotely, run as a shared team service, or deployed to a container or cloud environment, the ModelContextProtocol.AspNetCore package adds Streamable HTTP transport to an ASP.NET Core application. Multiple AI clients can connect simultaneously over HTTPS. This is the production deployment pattern for organization-wide MCP servers.

// Create an ASP.NET Core web API project
// dotnet new webapi -n SqlMcpHttpServer
// dotnet add package ModelContextProtocol.AspNetCore
// dotnet add package Microsoft.Data.SqlClient

// Program.cs for HTTP MCP server

using ModelContextProtocol.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

// Register SQL connection factory
var connectionString = builder.Configuration.GetConnectionString("SqlServer")
    ?? throw new InvalidOperationException("SqlServer connection string required");
builder.Services.AddSingleton(_ => new SqlConnectionFactory(connectionString));

// Register MCP server with ASP.NET Core HTTP transport
builder.Services
    .AddMcpServer()
    .WithTools();   // same tool class as Approach 4

var app = builder.Build();

// Single endpoint for Streamable HTTP transport
// POST /mcp for JSON-RPC requests
// GET  /mcp with Accept: text/event-stream for SSE streaming
app.MapMcp("/mcp");

// Health check endpoint for load balancers
app.MapGet("/health", () => Results.Ok(new { status = "healthy" }));

app.Run();

// appsettings.json
// {
//   "ConnectionStrings": {
//     "SqlServer": "Server=prod-sql;Database=ProductionDB;Trusted_Connection=True;"
//   }
// }

// Connect remote clients by specifying the URL in claude_desktop_config.json:
// {
//   "mcpServers": {
//     "sql-remote": {
//       "url": "https://mcp.company.internal/mcp",
//       "transport": "streamable-http"
//     }
//   }
// }

10 Security: What Not to Expose Through an MCP Server Intermediate

• Never expose unrestricted query execution. If a Tool accepts arbitrary SQL, validate it is SELECT-only at the server level. Do not rely on the AI to avoid writing harmful queries. The server must enforce the constraint in code as shown in Approaches 2 and 4 above.
• Use Windows Authentication wherever possible. Avoids storing SQL Server credentials in config files or environment variables on developer machines.
• Use a dedicated low-privilege SQL Server login. The account the MCP server connects with should have only the permissions the Tools require. A read-only login for query tools. A separate login with specific stored procedure execute permissions for write tools.
• Do not expose production customer data through stdio MCP servers. stdio servers run on the developer machine and their outputs flow through the AI client. Sensitive customer data should only flow through organization-controlled remote servers with audit logging.
• Log all Tool invocations. In production MCP servers, log every Tool call with the session identity, the parameters passed, and the result status. This creates the audit trail required in regulated environments.
• Never expose connection strings in MCP server responses. A Tool that echoes back configuration information or error messages containing connection details is a direct credential leak. Catch exceptions and return generic error messages, not raw SQL exception messages that may contain server names or database names.

11 Choosing the Right Approach: Decision Matrix Beginner

Approach	Code Required	SQL Server Native	Remote Deploy?	Best For
1. JSON Config	None	Via community server	No	Fastest evaluation. No control over what is exposed.
2. Python FastMCP	Python	Via pyodbc	Yes, with mcp.run(“streamable-http”)	Easiest custom server. Largest community. Most tutorials.
3. TypeScript FastMCP-TS	TypeScript	Via mssql npm package	Yes	Node.js teams. Web application integration.
4. .NET ModelContextProtocol	C#	Microsoft.Data.SqlClient native	stdio only	.NET and SQL Server teams. Best type safety. DI integration.
5. ASP.NET Core HTTP	C# + ASP.NET	Microsoft.Data.SqlClient native	Yes, production HTTP	Organization-wide shared server. Multi-user. Audit logging.

12 Workshop: Complete .NET SQL Server MCP Server Intermediate

This workshop builds, tests, and connects a complete .NET SQL Server MCP server to Claude Desktop using Approach 4 from Section 8. All code is production-pattern.

# Step 1: Create the project
dotnet new console -n SqlMcpServer -f net8.0
cd SqlMcpServer

# Step 2: Add packages
dotnet add package ModelContextProtocol
dotnet add package Microsoft.Data.SqlClient
dotnet add package Microsoft.Extensions.Hosting
dotnet add package Microsoft.Extensions.Configuration.EnvironmentVariables

# Step 3: Add the SqlMcpServer.csproj properties for self-contained publish
# In SqlMcpServer.csproj, ensure the PropertyGroup has:
#   <Nullable>enable</Nullable>
#   <ImplicitUsings>enable</ImplicitUsings>

# Step 4: Copy the Program.cs and SqlServerTools.cs from Section 8 above

# Step 5: Build and test locally
# Set the connection string environment variable before running
$env:SQL_CONNECTION_STRING = "Server=.;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;"
dotnet run

# Step 6: Publish as a self-contained executable
dotnet publish -r win-x64 --self-contained -c Release -o ./publish

// Step 7: Add to Claude Desktop config
// File: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "sql-server": {
      "command": "C:\\Projects\\SqlMcpServer\\publish\\SqlMcpServer.exe",
      "env": {
        "SQL_CONNECTION_STRING": "Server=.;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;"
      }
    }
  }
}

// Step 8: Test the server in Claude Desktop
// After adding the config, restart Claude Desktop
// In Claude Desktop, open a new conversation
// The SQL Server tools should appear in the tools list (paperclip or tools icon)

// Test prompts to verify each tool:
// "List all the tables in my SQL Server database"
// "Show me the schema for the Customer table"
// "Are there any blocked sessions right now?"
// "Run this query: SELECT TOP 5 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC"
// "Check the SQL Server error log for any recent errors"

References

• Microsoft .NET Blog: Announcing v2.0 of the Official MCP C# SDK (July 28, 2026)
• GitHub: modelcontextprotocol/csharp-sdk (official C# SDK, co-maintained by Anthropic and Microsoft)
• NuGet Gallery: ModelContextProtocol package
• NuGet Gallery: ModelContextProtocol.AspNetCore package
• Model Context Protocol Specification 2025-11-25
• GitHub: PrefectHQ/fastmcp (FastMCP Python, ~1M downloads/day)
• PyPI: fastmcp package
• MCP C# SDK official documentation
• SQLYARD: Model Context Protocol for SQL Server DBAs
• SQLYARD: LLMs, RAG, Agents, and MCP for SQL Server DBAs