Building a Secure Foundation for AI Agent Access to SQL Server Data

Background

A major semiconductor company operating at the forefront of innovation recognized an opportunity to empower its business users with faster, more intuitive access to critical SQL Server data. With a growing interest in AI-powered tools the organization sought to bridge the gap between enterprise data stores and conversational AI interfaces with Microsoft Copilot Studio Agents.  

As a leader in the semiconductor industry, the company manages vast volumes of operational and business data across SQL Server databases. The potential to surface that data through AI agents promised significant productivity gains, but only if the path to get there was built on a foundation of security, scalability, and best practices. The company needed a trusted partner with deep expertise in .NET development, Azure architecture, and emerging AI integration patterns to chart that course. 

Challenges

Connecting SQL Server data to Copilot Studio agents through Model Context Protocol (MCP) servers introduces a set of security and technical challenges because of the nature of agentic workflows. Agents having too much access poses a security risk, while agents with too little context causes more hallucinations and poor performance.  

First, implementing authentication to Azure that also exposed fine-grained role-based access control (RBAC) was essential. Business users needed to authenticate with their own credentials and receive data access scoped precisely to their roles.  

Second, the company needed a generic, reusable approach to exposing SQL data that deliberately avoided Natural Language to SQL (NL2SQL). While NL2SQL may seem like a convenient shortcut, it introduces serious risks: prompt injection vulnerabilities, unpredictable performance, and a lack of governance over what data gets exposed and how. The company needed a deterministic, governed alternative that could scale across teams and use cases without compromising theirsecurity posture. 

Solutions

IntelliTect developed a C# command-line tool that combines Office automation with Azure-IntelliTect partnered with the semiconductor company to deliver a comprehensive, multi-tiered solution addressing infrastructure, authentication, development patterns, and testing all grounded in security-first principles. 

1. Infrastructure and Observability 

IntelliTect recommended deploying MCP servers using .NET Aspire in tandem with Azure API Management (APIM). This architecture provides full gateway-level and application-level logging, giving the organization end-to-end observability across every interaction between Copilot Studio agents and the underlying data. 

2. Secure Authentication with Entra ID and OBO Token Flow 

To ensure that every data request is authenticated and authorized at the individual user level, IntelliTect designed an Entra ID integration using the On-Behalf-Of (OBO) token flow. Business users sign in through Copilot Studio custom connectors with their own accounts, and the OBO flow ensures that downstream MCP servers honor each user’s specific RBAC permissions. This approach eliminates shared service accounts and enforces the principle of least privilege throughout the data access chain. 

3. Governed SQL Data Exposure via Data API Builder SQL MCP 

For straightforward use cases, IntelliTect recommended Microsoft’s Data API Builder SQL MCP, a configuration-driven tool that exposes SQL data through a governed plane with built-in authentication. Data access requests are processed throughGraphQL in a deterministic manner, generating SQL statements only after passing through a series of validation checks. This approach sidesteps the risks of NL2SQL entirely while still providing flexible, tool-based data interaction for AI agents. 

4. Reusable .NET Templates for Custom MCP Servers 

For scenarios requiring custom tools beyond what Data API Builder provides, IntelliTect built reusable .NET templates using the official C# MCP SDK. These templates offer options for Entity Framework Core or parameterized SQL, depending on the team’s needs, and are built on .NET Aspire to simplify local development and testing. The templates enable development teams across the organization to quickly stand up secure, standards-compliant MCP servers without starting from scratch. 

5. Non-Deterministic Testing Framework 

In the age of AI powered workflows, testing has become both more crucial and more difficult. MCP servers require a two-tiered approach to testing. The first tier is a standard combination of unit and integration tests to ensure the tools are accurate, reliable, and correct code. The second tier is the usability of the tools. IntelliTect developed a non-deterministic testing framework that allows developers to test how an AI agent interacts with MCP tools during development, validating not only tool accuracy but also agent usability, a critical consideration when the end users are business professionals, not engineers. This framework was inspired by GitHub’s published research on validating agentic behavior (“Validating agentic behavior when ‘correct’ isn’t deterministic”). 

6. Security-Focused Code Reviews 

Beyond guidelines and templates, IntelliTect performed code reviews for multiple teams already working with MCP servers. These reviews uncovered and remediated security vulnerabilities in existing MCP implementations, reinforcing the security-first posture of the entire initiative. 

Outcome

Over approximately two months, a focused two-person IntelliTect team delivered a complete package of actionable guidelines, reusable templates, and hands-on code review support. The semiconductor company now possesses a clear, security-hardened roadmap for connecting MCP servers to SQL Server databases and exposing them to Copilot Studio agents through custom connectors in Power Automate. 

The guidelines and templates have been received and incorporated into the company’s planning, providing a standardized foundation that multiple development teams can leverage as they build out AI-powered data access solutions. The code reviews conducted during the engagement delivered immediate, tangible value by identifying and remediating security issues in existing MCP work, ensuring that the organization’s current implementations meet the same rigorous standards as the new guidelines prescribe. 

Perhaps most importantly, the engagement has positioned the company to scale its AI initiatives with confidence. By establishing governed patterns for authentication, data exposure, and testing, the organization can onboard new use cases and teams without re-litigating foundational security and architecture decisions each time. The non-deterministic testing framework gives development teams a practical tool to validate agent behavior before deployment, a capability that will only grow in importance as the company’s AI footprint expands.