Power Platform Community Forum Thread Details

Hi All. New here.
I've spent this weekend getting my MCP server to work.
Followed this GOAT guide: https://github.com/microsoft/mcsmcp
mostly...

1) Backstory.
Work for specialist retail. We buy stuff, we sell stuff, we know a lot about the stuff we sell. It's specialised stuff.
I've played around with MCP pretty much since it was announced as it seemed a great idea at the time and super simple to get going with using their typescript sdk. I've set out to create a set of tools for myself to help me and my staff with daily work. A pocket full of tools.

I think it's worth mentioning my environment. It's a bunch of VMs running SQL server within azure. Old school but that's what the platform is on so when in Rome...

There wasn't a specific use case I had in mind. The general idea was I'd like the MCP equipped chat agent to be able to find and do everything a person could after a little training. Look up stock levels and pricing, check order status, find out ETAs, look for compatible products and accessories... etc etc. This worked well for the initial concept so I've added more tools, then separated related tools into multiple MCP servers (accounts need different toolset than warehouse and different than sales) Tuned prompts, tool descriptions, response context size, to get it aligned to use cases, database schemas, and business/industry lingo.

Did some sandboxed testing on the tools actually making changes to the records with great results. I could actually not only find stuff out, but actually make things happen pretty reliably (Got even more reliable once gpt4.1 got released, we are truly living in the future) .

It worked quite well but one thing stopped me from rolling it out:

2) I need to know who's asking.

So I had this great toolset but I couldn't really give it to everyone. Before I could my tools needed to know who is using them.

Are they in the right security group to see the data, and which data
Are they in the right security group to change the data, and which data
Should they have access to the tool in the first place.

Didn't really want to create my own system to mange this as I already have this in entra. While I probably could get some auth going through third party MCP clients like claude, my staff is already logged into copilot. I was looking for the missing link that the guide provided, in a way

3) "So you saying I can run my node as container in azure and it can serve mcp tools, SIGN ME UP"
The guide is amazing and really easy to follow. I do not have any experience with azd and Container apps so it was my first foray into this world which I enjoyed as a weekend project. Demo just worked. It was amazing.

4) BOLT IT DOWN.
While demo worked out of the box I was concerned about security aspect of running my apps in the same way. I decided that:

I need to have the container environment internal to my azure vnet. Lock it out from external access. NSGs everywhere
It needs to have access to my sql servers within the same vnet NSGs everywhere
I am going to let the request from copilot come over on premise gateway. (gateway access managed)
I am going to make sure the copilot itself is only available to logged in users.

This took me on a deep dive into how to set up container apps within above context, which was quite the adventure.

5) IT WORKS.
There were a few non obvious things like three separate access controls each for gateway / custom connector / connection itself... The modal within copilot to authenticate is taking users to power apps page which is bad user experience as it should be your typical app login and authorise screen. But I am getting user ids coming with the requests. Which is all I need a this stage

6) Are there better ways of doing this?

I don't know, I hope so. Couldn't find much about how oauth is supposed to work in this scenario and oauth doesn't look like available with connector going through on premise gateway.

Maybe I should have exposed it on azurecontainerapps.io but don't have much experience securing it so decided to keep it all as much internal as possible.

7) Why this post?
Genuinely need to share, I'm chuffed it worked.

Community site session details

IT's ALIVE - MCP server in copilot studio story

Helpful resources

News and Announcements

Quick Links

Michael Gernaey – Community Spotlight

Congratulations to the May Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard > Copilot Studio

Featured topics

Product updates