Background
A large enterprise organization wanted to make critical SQL Server data easier for business users to access through Microsoft Copilot Studio agents. The opportunity was significant: employees could interact with enterprise data through conversational AI instead of relying on traditional applications, reports, or manual data requests.
But connecting AI agents directly to enterprise data also introduced substantial security, governance, and architecture considerations. The organization needed a way to give AI agents enough context to be useful without granting excessive access or allowing unpredictable queries against sensitive data.
IntelliTect was brought in to establish a secure, scalable approach for connecting SQL Server to Copilot Studio through Model Context Protocol (MCP) servers. The goal was not simply to solve one integration, but to create reusable patterns that development teams could apply across future AI initiatives.
Challenges
Enforcing User-Level Data Access
Business users needed to authenticate with their own identities, with each request honoring their existing permissions.
The architecture therefore needed to support fine-grained role-based access control rather than relying on shared service accounts or broad application-level permissions.
Avoiding the Risks of Natural Language to SQL
Generating SQL directly from natural-language prompts can provide flexibility, but it can also introduce security and governance concerns, including prompt injection, unpredictable queries, inconsistent performance, and insufficient control over what data an AI agent can access.
For this environment, the organization wanted a more deterministic approach in which developers explicitly defined what data and operations were available to agents.
Creating Standards Multiple Teams Could Reuse
Several development teams were exploring MCP-based integrations. Without a standardized architecture, each team risked solving authentication, data access, deployment, and testing independently.
The organization needed reusable guidance and development templates that would allow teams to move faster while maintaining consistent security and governance practices.
Testing Non-Deterministic AI Behavior
Traditional unit and integration tests could validate whether individual MCP tools worked correctly, but they could not fully answer another important question: Would an AI agent use those tools effectively?
The organization needed a practical way to evaluate both deterministic software behavior and the less predictable interactions between AI agents and MCP tools.
Solution
IntelliTect developed a multi-layered architecture and set of development practices covering infrastructure, authentication, governed data access, custom MCP development, testing, and security review.
Secure Infrastructure and End-to-End Observability
IntelliTect recommended deploying MCP servers with .NET Aspire alongside Azure API Management.
This architecture provides visibility at both the application and gateway layers, allowing teams to monitor interactions between Copilot Studio agents, MCP services, and underlying enterprise data.
Centralized observability also gives development and security teams a stronger foundation for troubleshooting, auditing, and governing AI-driven data access.
User-Level Authentication with Entra ID
To maintain each user’s individual permissions throughout the request chain, IntelliTect designed an authentication architecture using Microsoft Entra ID and the On-Behalf-Of token flow.
Users authenticate through Copilot Studio custom connectors with their own identities. The downstream MCP server can then enforce access according to the permissions associated with that individual user.
This approach avoids shared service accounts and supports the principle of least privilege throughout the integration.
Governed SQL Access Without NL2SQL
For straightforward data-access scenarios, IntelliTect recommended Microsoft’s Data API Builder SQL MCP capabilities.
Rather than allowing an AI model to generate arbitrary SQL, Data API Builder provides a configuration-driven layer through which approved data and operations can be exposed to an agent.
Requests can be processed through defined GraphQL operations and validation rules, providing a more deterministic path between the AI agent and SQL Server.
This gives business users flexible conversational access while allowing development teams to retain control over what the agent can retrieve or execute.
Reusable .NET MCP Server Templates
Some use cases require business logic or tools beyond what a configuration-driven approach can provide.
For these scenarios, IntelliTect created reusable .NET templates using the official C# MCP SDK.
The templates support Entity Framework Core or parameterized SQL and use .NET Aspire to simplify development, local orchestration, and testing.
Instead of building every MCP implementation from scratch, development teams can begin with an established architecture that already incorporates the organization’s preferred authentication, deployment, and security practices.
Testing Agent Behavior, Not Just Code
AI-enabled applications require an additional testing layer beyond traditional software testing.
IntelliTect established a two-tiered approach.
The first tier uses conventional unit and integration tests to verify that MCP tools return accurate results and behave correctly.
The second evaluates how an AI agent actually discovers, selects, and interacts with those tools.
Inspired by published research into testing agentic systems where the definition of “correct” may not always be deterministic, IntelliTect created a framework that allows developers to assess tool usability and agent behavior during development.
This helps teams identify situations where an MCP tool may be technically correct but difficult for an AI agent to understand or use reliably.
Security Reviews of Existing MCP Implementations
IntelliTect also reviewed MCP server implementations already being developed by multiple teams within the organization.
These security-focused code reviews identified vulnerabilities and opportunities to strengthen existing implementations while aligning them with the new enterprise standards.
The result was both immediate risk reduction and greater consistency between current and future MCP development.
Outcome
In six months, approximately 45 applications were modernized and migrated to Azure.
The engagement delivered more than a change in hosting environment. The migrated applications benefited from modernized dependency management, standardized deployment pipelines, improved secrets management, centralized logging, and security scanning.
Just as importantly, the project helped clarify how the organization should approach cloud adoption at a larger scale.
The engagement demonstrated that migrating the company’s broader application portfolio would require participation from individual application teams rather than relying indefinitely on a centralized migration effort. The patterns, pipelines, and practices established during the project gave those teams a foundation for taking greater ownership of future application modernization.
Additional infrastructure retirement and database migration work remained for future phases, but the engagement established a repeatable technical and operational approach for continuing the organization’s cloud transformation.
Does Your Organization Need a Similar Solution?
Let’s chat about how we can help you achieve excellence on your next project!