How to build an MCP server for Sage 100
Short answer
An MCP server for Sage 100 is a small service that exposes named, read-only tools such as item lookup, order status and inventory, implemented over the Sage 100 ODBC driver or SQL Server. Any MCP client, including Claude and ChatGPT, can then call those tools with permissions you control and logs you keep.
The Model Context Protocol gives AI clients a standard way to call tools and read resources. For Sage 100, which has no modern hosted API story that you should rely on across versions, an MCP server is a pragmatic bridge. It hides ODBC, ProvideX and BOI details behind a clean list of functions.
The design work is mostly about restraint. A good Sage 100 MCP server has a dozen well-named tools, not a generic query function. Each tool maps to a business question, returns trimmed results, and cannot do more than the Sage role behind it allows.
Because the same server can serve Claude, ChatGPT, an internal chatbot and a local model, building it once usually pays back faster than building separate integrations.
What teams use it for
Item and availability lookup
A tool returns description, unit of measure, on-hand, committed and on-order quantities by warehouse for an item code.
Sales order status
Given an order number, return header status, ship dates and open lines, with a flag for prices being hidden for some roles.
Purchase order tracking
List open PO lines by vendor or item with promise dates and quantities received to date.
Customer snapshot
Return terms, credit limit, aging buckets and last payment, scoped to the requesting user's allowed customers.
BOM and where-used
For Bill of Materials users, expose components for an assembly and the reverse lookup for a component.
Draft transaction tools
Optional write tools that create a pending proposal, not a posted document, for a person to approve.
Sage 100 integration surface
- Sage 100 ODBC driver: a Windows ODBC data source usable from Python, .NET or Node ODBC bindings; suitable for modest read volume.
- SQL Server in Premium: native SQL access with row and column controls through a dedicated login or views you create for the server.
- Business Object Interface through COM or ProvideX: the only supported write path; wrap it behind proposal tools that a human approves.
- Table families to expect: customer (AR), item and warehouse (CI and IM), sales orders (SO), purchase orders (PO), BOM (BM) and work orders in some editions. Names and columns vary by version, so inspect your own schema.
- MCP transports: stdio for local development and desktop clients; streamable HTTP for a shared server behind TLS and authentication.
How it works
- 1
Choose the language and SDK
Use an official MCP SDK in TypeScript or Python. Python has easy ODBC support; .NET suits COM and BOI work.
- 2
Map tools to questions
Write each tool with a clear name, a one-sentence description, typed inputs and a bounded output. Avoid a run_sql tool.
- 3
Create database views
In Premium, create read-only views that rename cryptic columns and drop sensitive ones, then query only those views.
- 4
Implement authentication
For HTTP, validate a bearer token tied to a user or service identity and map it to allowed customers, warehouses or companies.
- 5
Add limits and timeouts
Cap rows per call, set query timeouts, and rate-limit per user to protect the ODBC driver and interactive Sage users.
- 6
Add audit logging
Record user, tool, arguments, row count and duration to an append-only store.
- 7
Test with real clients
Use the MCP Inspector, then Claude and any other client you expect, and tune tool descriptions until the model picks the right tool.
Example flows
Availability check from a chat client
Trigger: A user asks if 200 units of an item can ship Friday.
- Client calls get_item_availability with the item and warehouse.
- Server queries the warehouse quantities view and open PO receipts due.
- It returns on-hand, committed, available and due-in by date.
- The model states the answer and the assumption it used.
Result: A grounded answer with the numbers visible.
Proposal write tool
Trigger: A user asks to create a reorder PO for three items.
- Client calls propose_purchase_order with vendor and lines.
- Server validates vendor and item codes and stores a pending proposal.
- A buyer opens the approval screen and accepts or edits.
- On approval, a worker calls the PO business object through BOI.
Result: A PO is created only after human sign-off, with an audit record.
Keeping data on-prem
The MCP server should run on a host that can reach Sage 100 over the local network, ideally the application server or a small VM beside it. With streamable HTTP, bind it to the internal network and publish it through your VPN or reverse proxy if remote staff need it. Pair it with a locally hosted open-weight model on vLLM or Ollama when prompts must not leave the building. Because MCP is model-agnostic, switching between a cloud client and a local model is a configuration change, not a rebuild.
Security checklist
- Use a dedicated Sage 100 role and database login limited to the tables or views the tools need.
- Enforce authorization in the server per user: customers, warehouses, companies and whether prices are visible.
- Do not expose a raw query tool; every query should be parameterized and named.
- Terminate TLS, require tokens on HTTP, and keep stdio servers off shared machines.
- Keep audit logs with retention that matches your financial record policy.
Ways to get it done
Build it yourself
Effort: One to three weeks for a read-only server.
Good if you have a developer comfortable with ODBC or SQL and a clear tool list.
Generic database MCP server
Effort: Hours to set up, more to harden.
Acceptable for experiments, but it exposes tables rather than business questions and is hard to secure.
Partner build with Netray
Effort: A scoped pilot of a few weeks.
Good when you want hardened auth, audit, BOI write proposals and a local model.
Pitfalls
- !Exposing every table; the model gets lost and security gets harder.
- !Ignoring ODBC concurrency, which can slow down interactive Sage users.
- !Hardcoding one admin credential in the server config.
- !Returning cryptic column names; rename them in views so models choose correctly.
- !Skipping versioning of tool schemas, which breaks clients after changes.
Frequently asked questions
What is an MCP server in plain terms?
It is a small program that tells an AI client what tools are available, such as get order status, and runs them when asked. The AI never connects to your database directly. It sees tool names, supplies parameters and receives the results your code chooses to return.
Should the first version have write tools?
No. Start with read-only tools, prove accuracy and security, then add proposal tools that need human approval. Direct posting by an AI client into financial or inventory records is rarely worth the risk early on.
Which MCP clients will work?
Any client that supports the protocol, including Claude clients and others that have added MCP support. Support varies by product and plan, so test with the clients your team actually uses rather than assuming.
Stdio or HTTP transport?
Use stdio for a developer or single-user desktop setup. Use streamable HTTP for a shared server, with TLS, authentication and a network boundary. Many projects prototype on stdio and move to HTTP once more than a few people rely on it.
Do we need SQL Server for this?
No, the ODBC driver is enough to start. SQL Server in Premium makes views, permissions and performance easier, so it helps as usage grows. Plan around your edition and ask your reseller about version specific limits.
Want MCP server working on your Sage 100?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment