From Prototype to Production: Building a Secure MCP Server for Team-Wide AI Data Access

A walkthrough of taking a proof-of-concept Model Context Protocol (MCP) server — built to let an AI assistant safely query a company's data warehouse — and deploying it as an authenticated, team-shared connector on Azure infrastructure.

The problem

A data analyst on the team had built a working MCP (Model Context Protocol) server — using the official SDK, with real, callable tools — that let Claude search a business data catalog, match natural-language questions to pre-approved SQL query patterns, validate that SQL against a strict read-only policy, and — only if everything checked out — run it against the company's Azure Synapse data warehouse.

The catch: it only ran locally, launched as a direct subprocess on his own machine (a transport mode called "stdio"). It worked great for him, one person at a time — but it had no way to run as a standing, network-reachable service, and no authentication of any kind, since "you're on my machine" was the entire security model.

The task was to take that local prototype and make it available to a six-person team, securely, without each person needing their own copy of the code or their own database credentials.

Architecture

The key design decision was separating two layers of identity that are easy to conflate:

• Who can use the tool at all — enforced by each person's own Microsoft login, checked against a dedicated Entra security group. Someone outside that group is blocked before the AI assistant ever sees a tool.
• How the server talks to the database — handled entirely by the virtual machine's own Azure Managed Identity, never the end user's personal credentials. No database password lives in any config file anywhere.

What I actually did

1. Stood up the initial secure foundation: configured a reverse proxy with an auto-renewing TLS certificate, and built the OAuth 2.0 authentication layer using Microsoft Entra ID — including scoping access to a dedicated security group so only approved users could reach the server at all. Replaced an earlier, less secure static-token access method with this proper per-user login flow, and verified the full OAuth handshake independently using a protocol inspector tool before considering it production-ready.
2. Reviewed a handed-off prototype and discovered the deployment package was missing a whole module the entry point depended on — an easy thing to overlook when a project gets zipped up for handoff. Diagnosed this by unzipping and diffing the actual file tree against what the code's own imports expected, rather than trusting the README.
3. Diagnosed that the prototype was built for local stdio transport (an assistant launching the script directly as a subprocess on the same machine) — which cannot be shared across a team or exposed over the internet. Rewrote the server around the same persistent HTTP transport and authentication layer already proven in step one, swapping in the prototype's real business logic in place of placeholder test tools.
4. Diagnosed and resolved a break in the expected chain of trust for database access: found that the VM had no managed identity assigned yet, enabled it, and verified it could obtain a real access token. Then worked with the data warehouse's owner to explicitly scope that identity to read-only access at the database level — deliberately requesting the minimum permission needed rather than defaulting to broader admin rights, since this identity would be shared across every query the team's AI assistant ever ran.
5. Verified the entire pipeline end-to-end with a live query against production data — including debugging a template placeholder in the query pattern library that didn't match a real column name, and substituting a correct date-parsing expression to get a query fully executing.
6. Documented the full environment (topology, configuration, known issues, and open follow-ups) so the work is maintainable by someone other than me, and handed it back to the team with a clear, appropriately cautious rollout note.

Team members interact with the finished system entirely through natural-language conversations with Claude — asking a business question is enough to trigger the catalog search, query-contract matching, validation, and (once approved) execution steps automatically.

Security posture

Every layer of this system was built around least privilege: the AI assistant can only reach the server if the person's own login is approved; the server can only read data, never write it; and every individual SQL statement is checked against a strict allow-list of tables and rejected outright if it contains anything beyond a single read-only SELECT. No layer trusts the one above it more than it has to.

Infrastructure as code

This deployment was built manually — Portal clicks and SSH — to move quickly for an initial proof-of-concept aimed at demonstrating feasibility to leadership. Below is representative Terraform for how the core Azure and Entra resources would be defined and version-controlled if this becomes a permanent service, rather than left as click-ops.

terraform {
  required_providers {
    azurerm = { source = "hashicorp/azurerm", version = "~> 3.100" }
    azuread = { source = "hashicorp/azuread", version = "~> 2.47" }
  }
}

# --- Entra ID app registration used for connector OAuth ---
resource "azuread_application" "mcp_connector" {
  display_name = "governed-data-agent-mcp"
}

resource "azuread_application_password" "mcp_connector_secret" {
  application_id = azuread_application.mcp_connector.id
  end_date       = timeadd(timestamp(), "4380h") # ~6 months, rotate on schedule
}

resource "azuread_service_principal" "mcp_connector" {
  client_id = azuread_application.mcp_connector.client_id
}

# --- Security group gating who can reach the connector ---
resource "azuread_group" "mcp_users" {
  display_name     = "mcp-authorized-users"
  security_enabled = true
}

resource "azuread_group_member" "mcp_users" {
  for_each         = toset(var.authorized_user_object_ids)
  group_object_id  = azuread_group.mcp_users.object_id
  member_object_id = each.value
}

# --- Network security group: least-privilege inbound rules ---
resource "azurerm_network_security_group" "mcp_server" {
  name                = "nsg-mcp-server"
  location            = var.location
  resource_group_name = var.resource_group_name

  security_rule {
    name                       = "AllowHTTPS"
    priority                   = 100
    direction                  = "Inbound"
    access                     = "Allow"
    protocol                   = "Tcp"
    source_port_range          = "*"
    destination_port_range     = "443"
    source_address_prefix      = "*"
    destination_address_prefix = "*"
  }

  security_rule {
    name                       = "AllowSSHFromAdminSubnet"
    priority                   = 110
    direction                  = "Inbound"
    access                     = "Allow"
    protocol                   = "Tcp"
    source_port_range          = "*"
    destination_port_range     = "22"
    source_address_prefix      = var.admin_subnet_cidr
    destination_address_prefix = "*"
  }
  # Note: no direct inbound rule for the app port (e.g. 8000) -- only the
  # reverse proxy on 443 is internet-facing; the app port stays internal.
}

# --- The VM hosting the MCP server, with a managed identity ---
resource "azurerm_linux_virtual_machine" "mcp_server" {
  name                = "vm-mcp-server"
  resource_group_name = var.resource_group_name
  location            = var.location
  size                = "Standard_B2ms"
  admin_username      = var.admin_username
  network_interface_ids = [azurerm_network_interface.mcp_server.id]

  admin_ssh_key {
    username   = var.admin_username
    public_key = var.admin_ssh_public_key
  }

  os_disk {
    caching              = "ReadWrite"
    storage_account_type = "Standard_LRS"
  }

  source_image_reference {
    publisher = "Canonical"
    offer     = "ubuntu-24_04-lts"
    sku       = "server"
    version   = "latest"
  }

  # Enables the managed identity used to authenticate to Synapse,
  # with no credentials stored anywhere in code or config.
  identity {
    type = "SystemAssigned"
  }
}

# --- Grant that identity read-only access at the Azure resource level ---
# (Database-level permissions -- CREATE USER / db_datareader inside the
# target SQL pool -- still require a SQL statement run against the
# database itself; Terraform can trigger this via a null_resource +
# local-exec provisioner calling sqlcmd, but it is not a native
# Terraform resource.)
resource "azurerm_role_assignment" "mcp_identity_reader" {
  scope                = var.synapse_workspace_id
  role_definition_name = "Reader"
  principal_id         = azurerm_linux_virtual_machine.mcp_server.identity[0].principal_id
}

variable "authorized_user_object_ids" { type = list(string) }
variable "resource_group_name"        { type = string }
variable "location"                   { type = string }
variable "admin_username"             { type = string }
variable "admin_ssh_public_key"       { type = string }
variable "admin_subnet_cidr"          { type = string }
variable "synapse_workspace_id"       { type = string }

Note: the database-level grant (CREATE USER ... FROM EXTERNAL PROVIDER) that actually authorizes queries inside the Synapse SQL pool isn't a native Terraform resource — it can be triggered via a null_resource with a local-exec provisioner calling sqlcmd, but that's a workaround, not idiomatic Terraform. In a fully productionized setup, the application code deployment (the MCP server itself) would also move out of manual scp/SSH and into a CI/CD pipeline, and secrets would move from a flat environment file into Azure Key Vault.

Notable issues along the way

Skills demonstrated

Outcome

The connector now lets any authorized team member ask plain-language business questions and get back governed, validated, read-only results from the company's data warehouse — with every query checked against a strict SQL policy before it ever reaches the database, and full auditability of who has access. What started as one analyst's local prototype is now a shared, authenticated production service.

Extending the system: an observability pipeline

Once the connector was live, a stakeholder asked for something new: structured logging on the catalog-search tools specifically — capturing which questions users asked, how confidently the system matched them, and which records the match touched — to help spot gaps in the underlying data catalog and prioritize what to build next.

This meant designing and shipping a second, independent telemetry pipeline into an already-running production system: a dedicated log stream on the server, a new cloud analytics workspace, and the infrastructure connecting the two — without disrupting the connector already in daily use.

The result: the stakeholder now has self-serve access to query this data directly, along with a short written guide (aimed at someone with no query-language experience) covering how to navigate the tool and a handful of ready-to-use queries for the exact questions they cared about.

Full system diagram

A more detailed view of every component and how a request actually flows through the system, end to end:

The two identity checks are shown separately on purpose: a human login gates access at the top, and a completely different machine identity gates the database at the bottom — a request has to pass both before it ever reaches real business data, and even then it's capped at read-only.