How to Connect AI to SQL Server With Managed MCP

Even as models grow more capable, enterprise AI projects still stall on the data layer underneath them, where SQL Server holds decades of operational history that no AI tool can reach without a pipeline built specifically for it. CData Connect AI closes that gap as a managed Model Context Protocol (MCP) platform, giving AI assistants and agents credentialed, live access to SQL Server and hundreds of other enterprise systems through one governed endpoint. The Model Context Protocol itself is an open standard, developed by Anthropic, for connecting AI applications directly to structured systems instead of building a custom integration for every source an assistant needs to reach. For teams evaluating SQL Server AI data integration, that's the mechanism worth understanding before anything else: one MCP endpoint replaces the one-off connector every AI tool would otherwise need. Tools like Claude, ChatGPT, Microsoft Copilot Studio, and n8n all reach SQL Server the same way through Connect AI, and the same connection extends to the rest of the data estate as new sources come online.

Setting up CData Connect AI for SQL Server AI deployment

Getting SQL Server ready for AI access doesn't require a development sprint. The setup path runs through four steps:

1. Register for CData Connect AI.

2. Access the dashboard.

3. Add SQL Server as a data source from the Sources page.

4. Review the environment and permissions the connecting account needs.

That fourth step determines what an AI tool can actually see. Connect AI authenticates against SQL Server using the credentials supplied at connection time, and access from any AI tool is bounded by whatever that account is already allowed to query. Once the connection is live, Connect AI exposes SQL Server, along with every other connected source, through a standardized SQL layer, which is what lets an LLM query a CRM and a database with the same syntax instead of learning each system's native API.

Two ways to put SQL Server and AI together

The phrase “connect AI to SQL Server” actually covers two different architectures, and picking the right one depends on which system does the reasoning.

AI-first: An AI tool, Claude, ChatGPT, Copilot Studio, or an MCP-compatible agent framework, connects to Connect AI's single MCP endpoint and queries SQL Server directly, alongside any other connected source. This is the pattern behind most SQL Server MCP server setups today, and it requires no changes inside SQL Server itself.

SQL Server-first: SQL Server pulls in external data through a linked server pointed at Connect AI's Virtual SQL Server endpoint, and existing T-SQL workflows, including in-database AI and ML calls, run against the combined result. This is the right fit for teams with reporting or scoring logic already built in T-SQL that need to reach data SQL Server doesn't natively hold.

Connecting AI tools to SQL Server through MCP

Once SQL Server is added as a source in Connect AI, any MCP-compatible client reaches it through the same URL: https://mcp.cloud.cdata.com/mcp. Authentication depends on the client, but Connect AI supports both OAuth and Basic Authentication, the latter using a Connect AI user ID and a Personal Access Token (PAT) generated from the Settings page. A PAT is a static, revocable credential, and generating a separate one per integration keeps a compromised token from exposing more than the single connection it was scoped to.

Behind that one URL, Connect AI's MCP server exposes a fixed set of tools for discovering and querying data, schema lookups followed by the query itself, and that sequence is identical no matter which source an AI tool is reaching. An AI tool that has learned to query SQL Server through Connect AI already knows how to query Salesforce, an OData feed, or any other connected source the same way.

Bringing external data into SQL Server with linked servers

For workflows that need to stay inside T-SQL, Connect AI also exposes a Virtual SQL Server endpoint at tds.cdata.com on port 14333, which any SQL Server client can register as a linked server. Once registered, an external source shows up in SQL Server the same way a remote SQL Server instance would.

To create the linked server in SQL Server Management Studio (SSMS):

1. In Object Explorer, expand the server node, right-click Linked Servers, and select New Linked Server.

2. Choose Other data source and select SQL Server Native Client 11.0 as the provider.

3. Set Data source to tds.cdata.com,14333.

4. Set Catalog to the name of the Connect AI connection you want to expose, for example ADP1.

5. On the Security page, select Be made using this security context, and enter your Connect AI email as the remote login and a PAT as the password.

Once the linked server is registered, querying it looks like querying any other remote SQL Server instance, using four-part naming:

SELECT * FROM [ConnectAI].[ADP1].[ADP].[Employees]

CData describes the resulting connection as a pure SQL Server interface: it lets SQL Server query external data as if it were a local table, with no replication and no custom sync job maintaining a copy. A single SQL Server instance can register a linked server for every connected source, ADP, an OData feed, a CRM, each queryable with the same T-SQL syntax and joinable against tables that are actually local.

Running AI models against SQL Server data

With external data reachable inside SQL Server, the next step is deciding where the model itself runs. SQL Server offers a stored procedure, sp_execute_external_script, that runs R or Python inline within T-SQL, which means a model call and a reporting query can live in the same procedure instead of two systems that have to be kept in sync manually.

The MCP path is structurally different from the other two: instead of a script running inside SQL Server, the AI tool queries SQL Server through Connect AI directly, and SQL Server never needs to know an AI model exists. For teams building new AI workflows rather than extending existing T-SQL logic, that's usually the simpler starting point.

Leveraging prebuilt and custom AI models with SQL Server

Starting with a custom model is usually the wrong first move. Prebuilt models, Azure OpenAI and Microsoft Copilot Studio among them, cut the setup a team has to own and let an AI assistant start answering questions against live SQL Server data almost immediately.

The move to a custom model tends to get triggered by three things: the data gets specific enough that a general-purpose model stops giving useful answers, the use case needs logic a prebuilt model can't express, or query volume makes a dedicated API call more practical than a shared service. None of those are reasons to start with a custom build. They're reasons to have a path to one.

Scaling and managing AI deployments with CData

The scaling question for AI and SQL Server usually isn't about the model. It's about what happens when the second team, the third data source, or the tenth AI agent shows up. Every new source in Connect AI follows the same setup path already covered, so adding one doesn't require a new integration pattern.

Governance is what keeps that expansion from turning into a liability. Connect AI's permissions and access controls apply to AI agents the same way they apply to human users, so an administrator can see which agents are querying which sources and restrict access accordingly, with audit trails covering both. Scaling to more sources, more models, or more concurrent agents doesn't require an infrastructure change. The connectivity layer that worked for one SQL Server instance and one AI assistant is the same one that supports ten.

Best practices for secure and efficient AI model integration

A few habits separate a deployment that holds up under audit from one that quietly accumulates risk:

• Generate a separate PAT per connected service rather than reusing one credential everywhere.

• Monitor usage and restrict access through Connect AI's built-in governance tools instead of ad hoc permission changes.

• Start with a no-code or prebuilt-model setup where possible and add complexity only when the use case demands it.

• Document every AI model's access points and review them on a set schedule, not just when something breaks.

An AI-driven query against production SQL Server data deserves the same security scrutiny as any other business intelligence process touching that data. Treating it as a special case, exempt from the access reviews applied everywhere else, is how governance gaps show up later.

Frequently asked questions about connecting AI to SQL Server with CData

Start querying SQL Server from your AI tools

Connecting AI to SQL Server doesn't require ETL, replication, or a custom connector for every source an assistant needs to reach. It requires one governed endpoint that resolves access and live queries before the AI tool ever runs. CData Connect AI provides that endpoint for SQL Server and hundreds of other enterprise sources, with permissions that stay tied to the systems already in place.

Explore CData Connect AI