Skip to content

Commit 725bf8c

Browse files
anushakolanAniruddh25Copilot
authored
Added documentation for DAB MCP and AI Foundry integration setup. (#2971)
## Why make this change? This change adds documented instructions on integrating the DAB MCP with AI Foundry using an Azure Container Instance. <img width="2398" height="2178" alt="image" src="https://github.com/user-attachments/assets/a7c1ae33-28ea-4c48-b474-12abfc3f263b" /> --------- Co-authored-by: Aniruddh Munde <[email protected]> Co-authored-by: Copilot <[email protected]>
1 parent 0e1b3c2 commit 725bf8c

1 file changed

Lines changed: 273 additions & 0 deletions

File tree

‎docs/Testing/ai-foundry-integration.md‎

Lines changed: 273 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,273 @@
1+
# Deploying SQL MCP Server implemented in Data API builder and Integrating with Azure AI Foundry
2+
3+
This document provides an end‑to‑end guide to stand up a **SQL MCP Server** with **Model Context Protocol (MCP)** tools implemented in **Data API builder (DAB)** container that also exposes **REST** and **GraphQL** endpoints, and to integrate those MCP tools with an **Azure AI Foundry Agent**.
4+
5+
## 1. Architecture Overview
6+
7+
**Components**
8+
- **Azure SQL Database** hosting domain tables and stored procedures.
9+
- **DAB container** (Azure Container Instances in this guide) that:
10+
- reads `dab-config.json` from an **Azure Files** share at startup,
11+
- exposes **REST**, **GraphQL**, and **MCP** endpoints.
12+
- **Azure Storage (Files)** to store and version `dab-config.json`.
13+
- **Azure AI Foundry Agent** configured with an **MCP tool** pointing to the SQL MCP Server endpoint.
14+
15+
**Flow**
16+
1. DAB starts in ACI → reads `dab-config.json` from the mounted Azure Files share.
17+
2. DAB exposes `/api` (REST), `/graphql` (GraphQL), and `/mcp` (MCP).
18+
3. Azure AI Foundry Agent invokes MCP tools to read/update data via DAB’s surface (tables, views and stored procedures).
19+
20+
21+
## 2. Prerequisites
22+
- Azure Subscription with permissions for Resource Groups, Storage, ACI, and Azure SQL.
23+
- Azure SQL Database provisioned and reachable from ACI.
24+
- Azure CLI (`az`) and .NET SDK installed locally.
25+
- DAB CLI version **1.7.81 or later**.
26+
- Outbound network access from ACI to your Azure SQL server.
27+
28+
29+
## 3. Prepare the Database
30+
31+
You need to create the necessary tables and stored procedures in your Azure SQL Database. Below is an example of how to create a simple `Products` table and a stored procedure to retrieve products by category.
32+
33+
**Example:**
34+
35+
1. Connect to your Azure SQL Database using Azure Data Studio, SQL Server Management Studio, or the Azure Portal's Query Editor.
36+
37+
2. Run the following SQL script to create a sample table and stored procedure:
38+
39+
```sql
40+
-- Create Products table
41+
CREATE TABLE Products (
42+
ProductID INT IDENTITY(1,1) PRIMARY KEY,
43+
Name NVARCHAR(100) NOT NULL,
44+
Category NVARCHAR(50) NOT NULL,
45+
Price DECIMAL(10,2) NOT NULL
46+
);
47+
48+
-- Create stored procedure to get products by category
49+
CREATE PROCEDURE GetProductsByCategory
50+
@Category NVARCHAR(50)
51+
AS
52+
BEGIN
53+
SET NOCOUNT ON;
54+
SELECT ProductID, Name, Category, Price
55+
FROM Products
56+
WHERE Category = @Category;
57+
END;
58+
```
59+
60+
## 4. Install DAB CLI and Bootstrap Configuration
61+
62+
```
63+
dotnet tool install --global Microsoft.DataApiBuilder --version 1.7.81
64+
export DATABASE_CONNECTION_STRING="Server=<server>.database.windows.net;Database=<db>;User ID=<user>;Password=<pwd>;Encrypt=True;"
65+
66+
dab init \
67+
--database-type "mssql" \
68+
--connection-string "@env('DATABASE_CONNECTION_STRING')" \
69+
--host-mode "Development" \
70+
--rest.enabled true \
71+
--graphql.enabled true \
72+
--mcp.enabled true \
73+
--mcp.path "/mcp"
74+
75+
```
76+
77+
## 5. Add all required entities (tables and stored procedures) to `dab-config.json` and enable MCP tools in the config
78+
79+
Here is how to add a table entity and a stored procedure to your `dab-config.json`, and ensure MCP tools are enabled:
80+
81+
1. **Open your `dab-config.json` file.**
82+
83+
2. **Add an entity (table) definition** under the `"entities"` section. For example, to expose a `Customers` table:
84+
```
85+
"entities": {
86+
"Customers": {
87+
"source": "Customers",
88+
"rest": true,
89+
"graphql": true,
90+
"mcp": true,
91+
"permissions": [
92+
{
93+
"role": "anonymous",
94+
"actions": [ "read", "create", "update", "delete" ]
95+
}
96+
]
97+
}
98+
}
99+
```
100+
101+
3. **Add a stored procedure** under the "entities" section. For example, to expose a stored procedure called GetCustomerOrders:
102+
103+
```
104+
"GetCustomerOrders": {
105+
"source": {
106+
"object": "GetCustomerOrders",
107+
"type": "stored-procedure"
108+
},
109+
"rest": true,
110+
"graphql": true,
111+
"mcp": true,
112+
"permissions": [
113+
{
114+
"role": "anonymous",
115+
"actions": [ "execute" ]
116+
}
117+
]
118+
}
119+
```
120+
121+
Note: Make sure the "entities" section is a valid JSON object. If you have multiple entities, separate them with commas.
122+
123+
4. **Ensure MCP is enabled in the "runtime" section:**
124+
125+
```
126+
"runtime": {
127+
"rest": { "enabled": true },
128+
"graphql": { "enabled": true },
129+
"mcp": {
130+
"enabled": true,
131+
"path": "/mcp"
132+
}
133+
}
134+
```
135+
136+
5. **Example dab-config.json structure:**
137+
138+
```
139+
{
140+
"data-source": {
141+
"database-type": "mssql",
142+
"connection-string": "@env('DATABASE_CONNECTION_STRING')"
143+
},
144+
"runtime": {
145+
"rest": { "enabled": true },
146+
"graphql": { "enabled": true },
147+
"mcp": {
148+
"enabled": true,
149+
"path": "/mcp"
150+
}
151+
},
152+
"entities": {
153+
"Customers": {
154+
"source": "Customers",
155+
"rest": true,
156+
"graphql": true,
157+
"mcp": true,
158+
"permissions": [
159+
{
160+
"role": "anonymous",
161+
"actions": [ "read", "create", "update", "delete" ]
162+
}
163+
]
164+
},
165+
"GetCustomerOrders": {
166+
"source": {
167+
"object": "GetCustomerOrders",
168+
"type": "stored-procedure"
169+
},
170+
"rest": true,
171+
"graphql": true,
172+
"mcp": true,
173+
"permissions": [
174+
{
175+
"role": "anonymous",
176+
"actions": [ "execute" ]
177+
}
178+
]
179+
}
180+
}
181+
}
182+
```
183+
184+
6. **Save the file.**
185+
186+
## 6. Store dab-config.json in Azure Files
187+
188+
1. **Create a Storage Account** (if you don't have one):
189+
az storage account create
190+
--name
191+
--resource-group
192+
--location
193+
--sku Standard_LRS
194+
195+
196+
2. **Create a File Share**:
197+
az storage share create
198+
--name
199+
--account-name
200+
201+
202+
3. **Upload `dab-config.json` to the File Share**:
203+
az storage file upload
204+
--account-name
205+
--share-name
206+
--source ./dab-config.json
207+
--path dab-config.json
208+
209+
210+
4. **Retrieve the Storage Account key** (needed for mounting in ACI):
211+
az storage account keys list
212+
--account-name
213+
--resource-group
214+
215+
Use the value of `key1` or `key2` as `<StorageAccountKey>` in the next step.
216+
217+
218+
## 7. Deploy DAB to Azure Container Instances
219+
220+
```
221+
az container create \
222+
--resource-group <RG> \
223+
--name dab-mcp-demo \
224+
--image mcr.microsoft.com/azure-databases/data-api-builder:1.7.81-rc \
225+
--dns-name-label <globally-unique-label> \
226+
--ports 5000 \
227+
--location <location> \
228+
--environment-variables DAB_CONFIG_PATH="/aci/dab-config.json" \
229+
--azure-file-volume-share-name <FileShareName> \
230+
--azure-file-volume-account-name <StorageAccountName> \
231+
--azure-file-volume-account-key <StorageAccountKey> \
232+
--azure-file-volume-mount-path "/aci" \
233+
--os-type Linux \
234+
--cpu 1 \
235+
--memory 1.5 \
236+
--command-line "dotnet Azure.DataApiBuilder.Service.dll --ConfigFileName $DAB_CONFIG_PATH --LogLevel Debug"
237+
```
238+
239+
## 8. Integrate with Azure AI Foundry
240+
241+
Follow these steps to connect your SQL MCP endpoint deployed in DAB to Azure AI Foundry and test the integration:
242+
243+
1. **Create or Open a Project**
244+
- Navigate to the [Azure AI Foundry portal](https://ai.azure.com/foundry) and sign in.
245+
- On the dashboard, click **Projects** in the left navigation pane.
246+
- To create a new project, click **New Project**, enter a name (e.g., `DAB-MCP-Demo`), and click **Create**.
247+
- To use an existing project, select it from the list.
248+
249+
2. **Add an Agent**
250+
- Within your project, go to the **Agents** tab.
251+
- Click **Add Agent**.
252+
- Enter an agent name (e.g., `DAB-MCP-Agent`).
253+
- (Optional) Add a description.
254+
- Click **Create**.
255+
256+
3. **Configure the MCP Tool**
257+
- In the agent's configuration page, go to the **Tools** section.
258+
- Click **Add Tool** and select **MCP** from the tool type dropdown.
259+
- In the **MCP Endpoint URL** field, enter your SQL MCP endpoint in DAB, e.g., `http://<fqdn>/mcp`.
260+
- (Optional) Configure authentication if your endpoint requires it.
261+
- Click **Save** to add the tool.
262+
263+
4. **Test in Playground**
264+
- Go to the **Playground** tab in your project.
265+
- Select the agent you created from the agent dropdown.
266+
- In the input box, enter a prompt that will trigger the MCP tool, such as:
267+
```
268+
Get all records from the Customers entity.
269+
```
270+
- Click **Run**.
271+
- The agent should invoke the MCP tool, which will call your DAB MCP endpoint and return the results.
272+
- **Expected Result:** You should see the data returned from your DAB instance displayed in the Playground output panel.
273+
- If there are errors, check the DAB container logs and ensure the MCP endpoint is reachable from Azure AI Foundry.

0 commit comments

Comments
 (0)