SQL Server MCP Server: Connect Microsoft SQL Server to AI Agents

Last updated: May 2026

A SQL Server MCP server is a service that exposes Microsoft SQL Server data as tools an AI agent can call through the Model Context Protocol (MCP). Rather than handing an LLM direct database credentials, you put an MCP server between the agent and SQL Server. The agent discovers what queries it can run, calls them as named tools, and the MCP server translates those calls into safe, governed T-SQL.

The pragmatic path to a production-grade SQL Server MCP server is built on a foundation that already exists in most Microsoft-stack environments: an auto-generated REST API over the SQL Server schema. The National Institutes of Health uses exactly this pattern with DreamFactory — running on-premises in a government environment, exposing SQL Server data used for grant application storage and analytics. The deployment uses DreamFactory's REST API for governed access; the MCP server is a thin surface on top of those same endpoints. DreamFactory's framing: "turn your database into an auditable, governed API layer for AI" — the API layer is the foundation; the AI surface is the addition. This guide covers what a SQL Server MCP server is, the three common setup approaches, Microsoft-stack identity and security considerations, and how the auto-generated-API-plus-MCP pattern handles production deployment.

Quick summary

• What an MCP server does: Exposes SQL Server (on-premises, Azure SQL, or Azure SQL Managed Instance) as MCP tools an AI agent can call.
• What sits underneath: A REST API over the SQL Server schema. The MCP tools are usually the same REST endpoints exposed via MCP.
• Microsoft-stack advantage: Entra ID identity passthrough via DreamFactory; SQL Server's Row-Level Security and Dynamic Data Masking continue to apply.
• Three approaches: open-source MCP example (prototyping), custom MCP server (full control), or an AI Data Gateway like DreamFactory (auto-generated REST API + MCP server, production-ready).
• Production must-haves: Entra ID identity, RLS, Dynamic Data Masking, Always Encrypted for high-sensitivity columns, query allow-listing, audit logging.
• Named customer: National Institutes of Health — on-prem government SQL Server via DreamFactory.

What is the Model Context Protocol (MCP)?

The Model Context Protocol is an open standard, introduced by Anthropic and adopted across the LLM ecosystem, that defines how AI models discover and call external tools at runtime. An MCP server exposes tools (callable functions); an MCP client — Claude Desktop, Claude Code, the Claude API with MCP enabled, ChatGPT, Cursor, Windsurf — consumes them.

For SQL Server specifically, MCP solves the agent-data-access problem in a way that works cleanly with the existing Microsoft security model. Rather than reinventing identity and access controls, an MCP server can lean on Entra ID, Row-Level Security, and Dynamic Data Masking — features already mature in SQL Server.

The foundation: auto-generated REST APIs over SQL Server

Before discussing MCP-specific patterns, the layer underneath matters. Every production MCP server for SQL Server is, in practice, a REST API for SQL Server plus a thin MCP surface on top.

This is where DreamFactory's history matters. The product has been auto-generating REST APIs from SQL Server (and other databases) for over a decade. The National Institutes of Health uses this pattern in a government environment: SQL Server backs grant application storage and analytics, DreamFactory exposes the data via a REST API, and the application layer queries the REST API instead of writing T-SQL directly.

Connect DreamFactory to a SQL Server database (on-premises, Azure SQL, or Azure SQL Managed Instance) and it:

• Introspects the SQL Server schema (tables, views, stored procedures, relationships)
• Generates a complete REST API with CRUD operations, filtering, sorting, pagination, and bulk operations
• Generates OpenAPI / Swagger documentation automatically
• Wraps every endpoint in role-based access control at the table, endpoint, and HTTP-verb level
• Applies authentication (Microsoft Entra ID, OAuth 2.0, OpenID Connect, SAML 2.0, LDAP, AD, API keys)
• Enforces rate limits per user, role, or service
• Logs every call with calling user, timestamp, and payload for audit

That REST API is useful by itself — applications consume it, integrations connect to it, internal tools depend on it. The MCP server is the next surface for the same endpoints.

What a SQL Server MCP server does specifically

A SQL Server MCP server is an MCP server whose tools wrap T-SQL queries — typically via the auto-generated REST API. Instead of exposing a generic "run any T-SQL" tool, a well-designed server exposes named, scoped tools — "find_customer_by_email", "list_recent_orders", "get_inventory_for_sku".

The agent reads the tool descriptions, picks the right one, and calls it with structured parameters. The MCP server resolves the call to the matching REST endpoint, which translates it into a parameterised T-SQL statement against SQL Server.

Why teams expose SQL Server to AI agents

SQL Server backs an enormous share of enterprise applications — particularly in finance, healthcare, retail, manufacturing, and government. The NIH case (on-prem government SQL Server) is one named example; the broader DreamFactory customer roster includes ExxonMobil, Toyota, Saint-Gobain, PPG, Deloitte, Google Cloud, AkerBP, Netgear, Miller Industries, Vermont Agency of Transportation, D.A. Davidson, and Pillsbury Law across regulated industries.

Common AI use cases:

• Conversational analytics over enterprise data: Business users ask the agent natural-language questions and it returns answers without requiring direct T-SQL skill.
• Support and operations copilots: An AI assistant queries customer records, recent tickets, and order history.
• Agent-driven workflows on enterprise systems: Autonomous agents read from SQL Server as one step in a multi-step task.
• Self-service queries for non-technical staff: Scoped, governed SQL Server access via natural language.

The three ways to set up a SQL Server MCP server

3. DreamFactory AI Data Gateway — REST API plus MCP, auto-generated

DreamFactory's AI Data Gateway takes the API-generation foundation the product has had for years and adds the MCP surface on top. Connect to a SQL Server database (on-premises, Azure SQL, or Azure SQL Managed Instance) and:

1. DreamFactory introspects the SQL Server schema and auto-generates a full REST API.
2. The same configuration wires those endpoints into DreamFactory's built-in MCP server.
3. Authentication (including Entra ID), RBAC, field-level redaction, rate limiting, and audit logging apply at the gateway layer.
4. SQL Server's own governance (Row-Level Security, Dynamic Data Masking, Always Encrypted) continues to apply at the database layer.

For most enterprise SQL Server deployments — especially anything regulated — this is the path of least resistance.

Microsoft-stack identity: making the most of Entra ID

The cleanest production pattern for SQL Server MCP integrates with Microsoft Entra ID for identity. DreamFactory supports Entra ID natively alongside OAuth 2.0, OpenID Connect, SAML 2.0, LDAP, and Active Directory.

The flow:

1. The calling human user authenticates to the MCP-driven application via Entra ID.
2. The application acquires an access token scoped for the DreamFactory gateway.
3. DreamFactory validates the token and applies per-table, per-endpoint, per-HTTP-verb RBAC.
4. The REST endpoint (or MCP tool) executes the query against SQL Server.
5. SQL Server's permissions, RLS policies, and Dynamic Data Masking apply at the database layer.
6. DreamFactory logs the call with calling user, timestamp, and payload.

SQL Server-specific security controls

Row-Level Security

Configure RLS policies at the table level in SQL Server. They apply to every query DreamFactory issues. DreamFactory's gateway-level RBAC stacks on top.

Dynamic Data Masking

Configure masking at the SQL Server column level. DreamFactory's per-role field-level redaction adds a second layer at the API gateway.

Always Encrypted

For highly sensitive columns, Always Encrypted keeps data encrypted on the wire and inside the SQL Server engine. The decryption boundary is the column master key. DreamFactory respects this — without the key, the gateway sees ciphertext.

Stored procedure allow-lists

DreamFactory can expose specific stored procedures as REST endpoints — the auto-generated API turns each procedure into a scoped, parameterised tool.

Audit via SQL Server Audit plus DreamFactory's per-call log

SQL Server's built-in audit logs database-side activity. DreamFactory's gateway log captures calling user, timestamp, and payload for every API and MCP call. Compliance teams join the two by user and time window.

Common pitfalls when exposing SQL Server to AI agents

• Using a shared SQL login. Lose per-user enforcement. Use DreamFactory's Entra ID integration from day one.
• Granting unrestricted T-SQL access. Constrain to named tools backed by DreamFactory's REST endpoints.
• Re-implementing RLS in the MCP server. RLS belongs in SQL Server. DreamFactory's gateway adds, doesn't replace.
• Skipping query budgets and timeouts. DreamFactory's per-user / per-role rate limiting handles agent budgets.
• Exposing high-sensitivity columns without Always Encrypted. Encrypt at the column level for credentials, health records, financial PII.
• Forgetting about writes. Default to read-only. DreamFactory's per-HTTP-verb RBAC makes this trivial.

When to choose which approach

• Exploring AI access to SQL Server: Open-source MCP example.
• Specialised tools wrapping T-SQL or stored procedures: Custom MCP server via the SDK.
• You need a production REST API over SQL Server anyway, and want AI access too: DreamFactory. The REST API generation alone justifies the platform.
• Going to production on Microsoft stack with Entra ID: DreamFactory — Entra ID, RLS, Dynamic Data Masking, and Always Encrypted all integrate cleanly.
• Regulated industry on Microsoft stack: DreamFactory, almost always. NIH is the named precedent.

How DreamFactory fits in

DreamFactory's foundational capability is instant REST API generation: connect a database, get a complete, documented, role-protected REST API in minutes — no code written. The platform has been doing this for over a decade across 40+ supported data sources.

The National Institutes of Health case study is directly relevant to SQL Server MCP: NIH runs DreamFactory in an on-premises government environment, exposing Microsoft SQL Server databases used for grant application storage and analytics. The deployment eliminated the cost of building a completely new system and accelerated the agency's analytical insights — without re-platforming SQL Server.

The broader customer roster: ExxonMobil, Toyota, Saint-Gobain, PPG, Deloitte, Google Cloud, AkerBP, Netgear, Miller Industries, Vermont Agency of Transportation, D.A. Davidson, and Pillsbury Law.

For a SQL Server deployment specifically:

• Auto-generated REST API: CRUD, OpenAPI docs, related-data retrieval, filtering, sorting, pagination, bulk operations, stored procedure exposure. Works equally for on-prem SQL Server, Azure SQL Database, and Azure SQL Managed Instance.
• Built-in MCP server: same endpoints exposed to Claude Desktop, Claude Code, ChatGPT, Cursor, Windsurf.
• Authentication: Microsoft Entra ID, OAuth 2.0, OpenID Connect, SAML 2.0, LDAP, AD, API keys.
• RBAC: per-table, per-endpoint, per-HTTP-verb.
• Field-level redaction: per role at the gateway, stacking on top of SQL Server's Dynamic Data Masking.
• Audit: every call logged with user, timestamp, payload — joinable with SQL Server's audit log.
• Rate limiting: per user, role, service.
• Server-side scripting: PHP, Python, Node.js for custom business logic.
• Deployment: Linux installer, Windows (IIS / Apache), Docker, Kubernetes via Helm chart, NPX quick install. Air-gapped supported (as deployed by NIH on-premises).

G2 rates DreamFactory 4.7 with badges for "Easiest to Use," "Fastest Implementation," and "Best ROI." See the AI page or request a demo.

Frequently asked questions

What is a SQL Server MCP server?

A SQL Server MCP server is a service that exposes Microsoft SQL Server databases as tools an AI agent can call through the Model Context Protocol (MCP). The MCP server wraps T-SQL queries as named tools with semantic descriptions; an MCP client like Claude reads the available tools and calls them at runtime, letting the AI agent query SQL Server without writing raw T-SQL.

What is DreamFactory's core capability?

DreamFactory's foundational capability is instant REST API generation: connect a database (SQL Server, PostgreSQL, MySQL, Oracle, Snowflake, Databricks, MongoDB — 40+ supported), and the platform auto-generates a fully documented, role-protected REST API in minutes. Authentication, RBAC, rate limiting, audit logging, and OpenAPI documentation are built in. The AI Data Gateway and MCP server sit on top of this same auto-generated API.

Does DreamFactory work with SQL Server without using MCP at all?

Yes. The National Institutes of Health uses DreamFactory specifically to expose Microsoft SQL Server data via a secure REST API — running in an on-premises government environment, with grant application storage and analytics on top. The deployment predates the MCP integration entirely; the auto-generated REST API alone solved the problem of giving applications governed access to SQL Server without rebuilding the underlying system.

Does this work with Azure SQL and Azure SQL Managed Instance?

Yes — the same DreamFactory pattern applies to Azure SQL Database and Azure SQL Managed Instance as well as on-premises SQL Server. Azure variants gain access to additional identity options through Microsoft Entra ID, which DreamFactory supports natively as one of its authentication methods.

How do I connect Claude to SQL Server?

Configure an MCP server that connects to SQL Server, then configure Claude Desktop, Claude Code, or the Claude API with MCP enabled to launch that server. For prototyping, an open-source MCP example for SQL Server is the fastest path; for production, DreamFactory auto-generates a fully documented, secure REST API from your SQL Server schema in minutes and exposes the same endpoints to MCP clients via its built-in MCP server.

How does identity flow from an AI agent to SQL Server?

On Microsoft-stack deployments, identity flows through Microsoft Entra ID (formerly Azure AD) — one of DreamFactory's natively supported authentication methods, alongside OAuth 2.0, OpenID Connect, SAML 2.0, LDAP, and Active Directory. The user's identity drives DreamFactory's RBAC decisions at the gateway. SQL Server's own permissions, Row-Level Security policies, and Dynamic Data Masking continue to apply at the database layer.

Does SQL Server's Row-Level Security work with an MCP server?

Yes. Configure Row-Level Security (RLS) policies at the table level in SQL Server; they continue to apply to every query DreamFactory issues. Combine with DreamFactory's gateway-level RBAC (per-table, per-endpoint, per-HTTP-verb) for layered governance.

What about Always Encrypted columns?

Always Encrypted columns stay encrypted on the wire and inside the SQL Server engine — only decrypted at the client. The decryption boundary is controlled by the column master key. DreamFactory's gateway respects this: if the gateway doesn't have the key, the query returns ciphertext, providing a clean isolation pattern for highly sensitive columns.

What's the safest MCP integration pattern for production SQL Server?

Six things in combination: (1) Don't expose raw T-SQL — use allow-listed named tools backed by the auto-generated REST endpoints. (2) Authenticate via Entra ID (DreamFactory supports this natively). (3) Apply Row-Level Security and Dynamic Data Masking at the table level. (4) Use Always Encrypted for high-sensitivity columns. (5) Log every interaction in DreamFactory's per-call audit log. (6) Run the gateway inside your perimeter — Azure VNet, on-premises, or air-gapped.

Is DreamFactory used for production SQL Server workloads in regulated industries?

Yes. The National Institutes of Health runs DreamFactory on-premises in a government environment, exposing Microsoft SQL Server databases used for grant application storage and analytics. DreamFactory's broader regulated-industry customer roster includes Vermont Agency of Transportation (air-gapped Oracle), D.A. Davidson (financial services), and Pillsbury Law (legal).

Related blog posts

• PostgreSQL MCP server
• MySQL MCP server
• Snowflake MCP server
• Oracle MCP server
• What is an AI Data Gateway?
• Zero Trust for LLMs
• SQL Server vs MySQL
• What is an API?
• REST APIs: an overview