In part one, I covered the basics of your Data API Builder setup. Now, it’s time to get into the real stuff :).
For the demo, I’m using the Stack Overflow 2013 database on an Azure SQL General Purpose Serverless 8-core database. It’s not a very large dataset (making it less expensive to host on Azure), but it has enough data and online examples to have some fun with.
We’re going through several steps to create the correct Azure resources, connect them, and ensure the MCP is online.
Create the Azure Container Registry (ACR)
The following code creates a resource in Azure to host your ACR. This is a resource you need to host your MCP server. The code shown here will log you in to your tenant, set your subscription to the correct one and deploy the ACR in the correct resource group.
$TENANT_ID = ""
$SUBSCRIPTION_ID = ""
$RESOURCE_GROUP = ""
az login --tenant $TENANT_ID
az account set --subscription $SUBSCRIPTION_ID
$ACR_NAME = "acrsqlmcp$(Get-Random -Minimum 1000 -Maximum 9999)"
az acr create --resource-group $RESOURCE_GROUP --name $ACR_NAME --sku Basic --admin-enabled true
Create a config file
Next, initialise your config file. This file holds all the information about your database. I found this part to be quite a lot of work, describing the tables and columns. For those looking at the full listing, you’ll observe that I may have missed tables and columns. Reason being, it’s a demo, and I got a bit fed up with adding all this metadata. Call me human.
dab init --database-type mssql --connection-string "@env('MSSQL_CONNECTION_STRING')" --host-mode Production --config dab-config.json
The first step, of course, is creating the file itself. You can find it in the C:\Users\YourName directory. Easy to find and easy to open. Spoiler alert: this is not the location you want this file to be in for later steps.
Tables
Next, you need to add information specific to the tables. This is a bit of work. You can download the full code file below. An example is this, where it declares the location of the Posts table and provides a short description. For a production system, I’d add more info to help the agent.
dab add Posts `
--source dbo.Posts `
--permissions "anonymous:read" `
--description "all non-deleted posts"
Columns
Next comes a lot of work: describing each column. You can enter the column names, a description, and whether each column is a primary key. In the descriptions, I’ve been playing around with different options to see what it will and won’t pick up on.
dab update Posts `
--fields.name Id `
--fields.description "Unique Post identifier" `
--fields.primary-key true
dab update Posts `
--fields.name PostTypeId `
--fields.description "Type of Post, links to PostTypes table"
When you choose to open the JSON config file, you’ll see something like this:
"entities": {
"Posts": {
"description": "all non-deleted posts",
"source": {
"object": "dbo.Posts",
"type": "table"
},
"fields": [
{
"name": "Id",
"description": "Unique Post identifier",
"primary-key": true
},
{
"name": "PostTypeId",
"description": "Type of Post",
"primary-key": false
},
It’s essentially the code you entered in PowerShell, just in a different format. For those of you big on automation, I can see options to create the JSON directly without the DB update command.
Basic automation
One thing you can do is create the commands by running T-SQL. I used this to make sure my tables and columns were registered correctly in the JSON. Also, note that every table needs a primary key! I tried to run a container with a table that lacked a primary key, but it refused to start, throwing an error about the missing key.
SELECT concat('dab add ', t.name, ' --source ', concat(s.name, '.', t.name), ' --permissions "anonymous:read" --description "', t.name ,'"')
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where s.name = 'dbo'
order by t.name
select concat('dab update ', t.name, ' --fields.name ', c.name ,' --fields.description ', c.name, ' --fields.primary-key ', CASE WHEN ixc.index_column_id = 1 Then 'True' else 'False' end)
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.indexes ix on t.object_id = ix.object_id
left outer join sys.index_columns ixc on t.object_id = ixc.object_id and ix.index_id = ixc.index_id and c.column_id = ixc.column_id
where s.name = 'dbo'
order by t.name
Advanced automation
If you’re already more into automation, take a good look at this blog post from the wonderful Jess Pomfret. She’s shared a very nice set of steps to create your DAB config file.
Create a Docker file
The next step was somewhat confusing for me; I had to create a Docker file. Microsoft provides some text you should use for this, and my first reaction was to run the code, expecting the Dockerfile to be created. What you need to do is create a new file named Dockerfile. No extension. Then paste the content from the quickstart Azure Container Apps page.
At the time of writing (February 2026), you need to change the image version from the shown 83 to 81. 83 kept throwing errors and issues, while 81 just worked. I will try to keep up with releases, but the numbers shown here may be outdated.
Build and push the image
The next PowerShell command will create the image and upload it to the Container registry. You can see the $ACR_NAME variable, which references the Azure Container Registry you created earlier.
az acr build --registry $ACR_NAME --image sql-mcp-server:1 .
When you start building the image to push, you might run into an error like this:
It took me way too long to realise that this was both my company laptop playing up, and also running the code in the wrong folder. The easiest fix for me was to move both files into a new folder: C:\Temp\MCP. The ACR build takes a few seconds, and you’re done.
Create the container app environment
Well, nothing much here, just the script to create the container app.
$ACR_NAME = "acrsqlmcp$(Get-Random -Minimum 1000 -Maximum 9999)"
az acr create --resource-group $RESOURCE_GROUP --name $ACR_NAME --sku Basic --admin-enabled true
$CONTAINERAPP_ENV = "sql-mcp-env"
$CONTAINERAPP_NAME = "sql-mcp-server"
az containerapp env create `
--name $CONTAINERAPP_ENV `
--resource-group $RESOURCE_GROUP `
--location $LOCATION
Create the container app
Again, just a bunch of code to run. Make sure you’re still in the same PowerShell session so you can reuse the variable values. The SQL Connection string uses a manual username and password. As far as I could tell (and test), other options do not work.
$ACR_LOGIN_SERVER = az acr show --name $ACR_NAME --query loginServer --output tsv
$ACR_USERNAME = az acr credential show --name $ACR_NAME --query username --output tsv
$ACR_PASSWORD = az acr credential show --name $ACR_NAME --query "passwords[0].value" --output tsv
$CONNECTION_STRING = "Server=tcp:sqlservername.database.windows.net,1433;Database=StackOverflow2013;User ID=[UserName];Password=P@$$w0rd;Encrypt=true;TrustServerCertificate=false;Connection Timeout=30;"
az containerapp create --name $CONTAINERAPP_NAME --resource-group $RESOURCE_GROUP --environment $CONTAINERAPP_ENV --image "$ACR_LOGIN_SERVER/sql-mcp-server:1" --registry-server $ACR_LOGIN_SERVER --registry-username $ACR_USERNAME --registry-password $ACR_PASSWORD --target-port 5000 --ingress external --min-replicas 1 --max-replicas 3 --secrets "mssql-connection-string=$CONNECTION_STRING" --env-vars "MSSQL_CONNECTION_STRING=secretref:mssql-connection-string" --cpu 0.5 --memory 1.0Gi
Since this resource now has direct access to your database (with the permissions associated with the account), you will want to ensure it is safe. Even though the output tells me the connection string is saved as a secret, better be sure.
To check how it’s saved, open the container app and select Secrets. As I’m the owner, I can see everything, including the hardcoded secret with the connection string.
When you open a secret, you can convert it to a Key Vault secret, making it much more secure. For this demo, it’s all good, but for your workload, where you’ll expose real data, go to Key Vault.
AI Foundry
Just when you think it’s all over, it isn’t. The Azure Container App you created can’t do anything; it needs something to connect to it and tell it what to do. The Agent you’re going to create will connect to the MCP service, retrieve knowledge from it, and work from there.
The steps are quite clearly documented on the Microsoft Learn pages, so I’m not going to repeat that.
For my agent, I started with the GPT 4.1 model. There are others available, and I’d recommend that you try to find the one that works best for you.
Agent Instructions
One of the things that really changes the results is the instructions you give to your agent.
In this screenshot, you can see the instructions I gave it. Since the MCP service has the relevant database information, I chose to exclude it from the instructions. After an initial test, I told it to execute statements on the database unless I told it not to. I did this because I found that the results got stuck on the MCP data; it seemed to ignore the fact that it could actually connect to the database.
The last line in the suggestion is a personal preference; the agent kept generating and executing subqueries, and I just prefer Common Table Expressions (CTEs). And it actually generates them the way I’m expecting it to.
Example prompt
I thought I’d give the agent an interesting prompt: Can you find a specific location where users are most probable to be lurkers?
The step-by-step approach gives some insight into what it’s going to write.
WITH UserStats AS (
SELECT
u.Location,
COUNT(*) AS total_users,
SUM(
CASE
WHEN (COALESCE(p.post_count,0) = 0 AND COALESCE(c.comment_count,0) = 0) THEN 1
ELSE 0
END
) AS lurker_count
FROM Users u
LEFT JOIN (
SELECT OwnerUserId, COUNT(*) AS post_count
FROM Posts
GROUP BY OwnerUserId
) p ON u.Id = p.OwnerUserId
LEFT JOIN (
SELECT UserId, COUNT(*) AS comment_count
FROM Comments
GROUP BY UserId
) c ON u.Id = c.UserId
WHERE u.Location IS NOT NULL AND u.Location != ''
GROUP BY u.Location
)
SELECT TOP 1 Location, total_users, lurker_count,
CAST(lurker_count AS FLOAT)/total_users AS lurker_ratio
FROM UserStats
WHERE total_users >= 10
ORDER BY lurker_ratio DESC, total_users DESC
This seems like a very valid result!
After the query is executed, you can see a clear representation of the results. Also, you can see the time it took to create it, the amount of tokens used (2165 in this case) and the AI Quality. Now, take this last one with a small pinch of salt; it’s AI judging itself, so it won’t be too harsh on itself.
I was misled!
The above screenshots looked really amazing, but they weren’t from my MCP. It was hallucinating like crazy. Because I was running build 83, essentially, the container never started. Something that you can see when you take a good look at the container app. What you want to see is a message that the container app is running.
When you click Show Logs, you can see what happened, either historically or in real time.
Historical log:
If you want to run your own query, that’s fine, as the data is saved in a Log Analytics Workspace. So you can add your own monitoring to these logs if you want. As far as I could tell, only errors are logged.
Next, you can check it in Foundry.
When you want to make sure you’re using the MCP, ask it this:
This will show you what it can do.
When it works
You have to be very careful about what you ask the MCP; it seems a bit limited in what it can handle. I found it very easy to run into the following error message:
The reason it’s throwing this error is that the MCP service apparently can’t handle this amount of work. For us, the query would be very easy; a select with a filter, a having clause and an order by to get the correct top 10. The MCP service must go through several steps. Getting the correct users, then counting the number of posts, putting them in order and finally getting 10.
When you change the prompt and explicitly tell it to paginate, you will get a result. So, prompting is even more important than chatting with an agent.
For example, some simple questions on the users.
If you’ve read the news around Open Claw and what it did beyond its permissions, you’ll be happy to see this.
Because I’ve set the table definitions to read only, it won’t do anything else. Which is good. Also, the suggestions start by creating SELECT query’s to see what would be changed, before suggesting update query’s.
Models to use
When you deploy your solution in Azure, you can select a large number of models to do the work. I found that the Foundry Model Router returns the best results. The model router doesn’t do anything itself; it checks your prompt and tries to find the best model to return a result. Much like the auto-setting in Visual Studio Code, GitHub Copilot. When you use the auto setting there, the Copilot will try to find the best model for your prompt and move from there. As I can’t keep up with all the models and options, it’s the best way to go.
Digging deeper
I like to see what a model is doing. On the one hand, you can use Extended Events on your SQL database to see the queries that are executed. This is all good. But it can be interesting to dig somewhere else. One thing you can do is click on the debug button at the bottom of your chat screen.
From prompt to reply
When you click on this button, it shows you the steps it took to get the answers to your prompt.
As you can see, the MCP translated my request to return the first 10 users in the database into the fields it needed to query, and the result is JSON-structured. This can then be shown in your foundry as a list of names with locations. If you want, you can try finding the live view of your container to see a lot of things happening, including the commands coming through. Or, as described in my next blog on MCP for SQL Server, you can watch it in your terminal.
Quality of the response
You’ll also see a number with AI Quality. This is clickable as well, revealing the following (depending on your settings).
It feels a bit like the agent is judging itself. As much as I like AI models and agents, I still think they should be more critical of their own work.
Token usage
You can also click on the tokens list; you’ll see the number of input and output tokens for your specific prompt and answer.
But wait, there’s more! Because when you’re using AI Foundry, you’re using a model that has a token limit. And that’s something you need to track, because using tokens also costs money.
To see how your model is being used, go to the Models tab in Foundry, select the model you’re using, and click Monitor.
In my case, this came back.
I know that I haven’t written 75 prompts, but my prompt has created 75 requests. The number of tokens is related to my prompt, the requests it generates, and the length of the output.
You can also see that, as I’ve chosen the router model, the difference in usage between the models. So far, I’ve spent 40 cents trying to get my prompts set up correctly and executed in the database.
Effective prompting will save both tokens and money. Just like writing a good query will save you CPU cycles and memory usage. I still like to think of both as skills you can hone.
Next steps?
Well, I’d advise you to give it a try. Spin up an Azure SQL database and restore the default database that you can select in the steps; probably some version of AdventureWorks. Or restore your own testing database that doesn’t have any sensitive data. Next, see what it can do and whether it matches what you’re looking for. No matter the outcome, let Microsoft know. Let them know what works and what doesn’t. At the time of writing, the MCP part of the DAB is in preview.
Thanks for reading, and see you soon in part three, where we’ll be going on-premises!
Reitse's blog 