Adding a Private AI Layer to Dynamics GP On-Prem
how to add AI to Dynamics GP on-prem
Also searched as
- Dynamics GP AI integration without cloud
- Dynamics GP local LLM SQL Server
- chat with Dynamics GP data privately
- GP private AI architecture eConnect
Short answer
Dynamics GP is an easy system to read from and a delicate one to write to. Read from curated SQL views on a replica of the company database, search documents with a local retrieval index, serve an open-weight model on your own GPU, and send any write through eConnect or another supported integration after a person approves it.
Applies to: Dynamics GP 2016 to GP 18.x on SQL Server, single or multi-company, with a SQL replica available
How to stand up a private AI layer on GP
- 1Pick the first questions: open receivables and payables, inventory by site, open purchase orders, trial balance, and who approved what.
- 2Create a read-only copy of the DYNAMICS system database and each company database (for example TWO) using AlwaysOn, log shipping or restores.
- 3Build curated views over the master, open, work and history tables so the assistant sees one clean shape per business object.
- 4Create a dedicated SQL login with SELECT only on those views; never reuse a GP user login or DYNSA.
- 5Index attachments, item notes, vendor documents and any scanned files kept on file shares in a local retrieval store.
- 6Expose named read actions via a tool server with parameter validation and row limits, and write every call to an audit log.
- 7Serve the model on local GPUs and pilot read-only questions with finance and purchasing before enabling drafts.
Real data access paths in GP
GP keeps a system database named DYNAMICS and one database per company, named by the company id. Tables follow a letters-and-numbers convention: RM00101 customer master, PM00200 vendor master, RM20101 open receivables, PM20000 open payables, IV00101 item master, IV00102 item quantities, POP10100 and POP10110 purchase order header and lines, SOP10100 and SOP10200 sales documents in work, GL00100 account master, GL10000 unposted batches and GL20000 and GL30000 open and history journal transactions. Open, work and history tables are separate, so one question often needs a union.
For supported integration, eConnect and the Integration Manager are the usual paths for writing documents, while read-only reporting typically uses views, SmartList and SSRS. Some GP versions also offer service-based interfaces, but check what your build supports. Reading SELECT-only views from a replica is simple and robust; writing with SQL is not supported.
-- Read-only: open receivables by customer from the open table
SELECT c.CUSTNMBR, c.CUSTNAME,
SUM(CASE WHEN o.RMDTYPAL IN (7,8,9) THEN -o.CURTRXAM ELSE o.CURTRXAM END) AS OpenBalance
FROM RM20101 o
JOIN RM00101 c ON c.CUSTNMBR = o.CUSTNMBR
WHERE o.VOIDSTTS = 0
GROUP BY c.CUSTNMBR, c.CUSTNAME
HAVING SUM(CASE WHEN o.RMDTYPAL IN (7,8,9) THEN -o.CURTRXAM ELSE o.CURTRXAM END) <> 0;
-- RMDTYPAL document types differ by build and setup; confirm the sign logic against an aging report.Reference architecture and air-gapped option
The architecture is a read-only replica or reporting copy, change data capture or timestamp-based incremental extracts feeding a retrieval index, a tool server such as an MCP server exposing approved read actions, an open-weight model on vLLM or Ollama, and a local audit log. GP environments often run on a modest virtual host, so the replica and the model server should be separate machines to avoid starving the ERP.
An air-gapped option is straightforward because GP itself needs no internet: bring in model weights and packages by controlled transfer, refresh the replica from backups inside the enclave and keep logs local. You own model upgrades and evaluation, which is the cost of full isolation.
Permissions and write-back
GP creates a SQL login for each GP user and manages access with security tasks and roles in the DYNAMICS database, plus company-level access. Mirroring this means reading the user's roles and companies at question time and restricting the tool server to the companies and object types they may see. Many GP shops have broad roles; use the migration to tighten them and avoid exposing payroll or bank data by default.
Keep write-back as a later, narrow phase. The assistant may draft a payables invoice or a sales order and show the proposed values; after a person approves, the document is submitted through eConnect or a vetted integration so GP validation and posting numbers apply. Never let the assistant hold a login with write rights to company tables.
Sizing, rollout and honest limits
A 30 to 150 user GP site usually needs one modest inference GPU for an 8B to 14B class model, or a quantised larger model, plus a small server for the index. Document sets are often small, so an index of a few gigabytes is common. Phase one is read-only questions, phase two is drafts, phase three is a handful of approved actions.
Honest limits: GP is a mature product with a finite roadmap, so check your support and migration plans before investing in write-back. SmartList, SSRS and Power BI already answer fixed questions well; an assistant earns its keep on ad hoc questions and documents. Third-party add-ons with their own tables need separate mapping, and answers that depend on them should be tested before release.
Try it here, free
Dynamics GP Private AI Layer Blueprint Generator
Enter your GP environment and get a plan for a private AI assistant: curated views, tool server, model serving, controls and rollout.
Common pitfalls
- !Querying only open tables and missing history. Unioning RM20101 with RM30101 or PM20000 with PM30200 changes the answer.
- !Reusing a GP user login for the assistant. GP users are SQL logins with broad rights in many installs.
- !Treating document type codes as universal. Confirm them against a known report for your build.
- !Ignoring multi-currency amounts and originating versus functional columns in the tables.
- !Writing to company tables with SQL because it seems quick. It bypasses GP validation and breaks posting integrity.
How an ERP-grounded AI assistant handles this
A grounded GP assistant answers from curated views, shows the query behind each figure and respects the asking user's company access. Netray builds this as a custom private layer using open-weight models on your hardware, with approval before anything is written.
Frequently asked questions
Can we use SmartList and SSRS instead?
For fixed, repeated questions, yes, and they are cheaper. An assistant adds value for ad hoc questions across modules and for searching documents. Many sites keep SmartList and add the assistant on top.
Is eConnect the only way to write to GP?
It is the long-standing developer path, and Integration Manager is common for imports. Which options are available depends on your version, so confirm in your documentation. Direct table writes are not supported.
Does this work with GP 2016 or older?
Read-only SQL access works across versions with table-level differences. Older builds may lack newer interfaces, so keep the tool server on views and test per version.
How is multi-company handled?
Build one view set per company database or a parameterised tool that selects the database from the user's allowed list. Never let the model supply a database name freely.
Related
Adding a Private AI Layer to Dynamics NAV On-Prem
Dynamics NAV can feed a private AI assistant through two safe doors: a read-only SQL Server copy for analytical questions and published web services (OData and SOAP) for anything that must respect NAV logic and permissions. Put an open-weight model and a retrieval index beside them on your own hardware and keep writes behind human approval and codeunit-based services.
AdvancedAdding a Private AI Layer to Business Central On-Premises
Business Central on-premises has a clean, supported read path: API v2.0, custom API pages written in AL, and OData web services, all enforcing the calling user's permissions. Put a tool server on top of those, add a retrieval index and an open-weight model on your own GPUs, and keep write-back behind human approval and the same APIs.
AdvancedAdding a Private AI Layer to an SSRS Report Estate
An SSRS estate holds years of business logic in RDL files, shared datasets and subscriptions. A private AI layer can make that estate searchable (which report answers this question), run reports with parameters on the user's behalf, and summarise the output, all on your own servers while keeping SSRS folder security and data source credentials in force.
How-toCalling and extending the Business Central API v2.0
The Business Central API v2.0 is a REST/OData endpoint at api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/v2.0, authenticated with Azure AD OAuth 2.0. It exposes standard entities like customers and items, and lets you publish your own with an AL API page.
Error fixFixing "Cannot create a record in (table). The record already exists" in D365 F&SCM data entity imports
This is the standard kernel duplicate-key exception, thrown when the Data Management Framework tries to insert a staging row into a target table whose unique or alternate key already matches an existing record. It almost always means the entity's insert-vs-update logic did not recognize the target row as an update, not that you have a true accidental duplicate in your source file.
Error fixFixing "There are currently no batch servers available for processing" in D365 F&SCM
This message means the batch framework cannot find any AOS instance flagged as a batch server and available for the batch group the job is assigned to. The fix is almost always in server configuration (System administration > Servers) or batch group assignment, not in the batch job itself.
AI for ERPAI for Dynamics GP and NAV, Before the Business Central Decision
Dynamics GP mainstream support is ending and NAV is long retired from active development. Add a private LLM over GP or NAV data now, on-prem, without a forced Business Central move.
AI for ERPAI Agents for ERP, Running On-Prem
A practical guide to on-prem AI agents for ERP: what they can safely automate, where human approval belongs, and how to design the guardrails.
Stuck on Microsoft Dynamics GP?
Talk to engineers who work inside Microsoft Dynamics GP every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- Dynamics GP Private AI Layer Blueprint GeneratorEnter your GP environment and get a plan for a private AI assistant: curated views, tool server, model serving, controls and rollout.
- Dynamics GP AI Assistant Sizing and Value CalculatorSize a private AI assistant for Dynamics GP: GPUs, index and monthly value from users and questions per day.
- Dynamics GP End of Support Migration AssessmentTen-question readiness score for Dynamics GP customers who need a decision on what comes after GP.
ERP fixes and how-tos
- Adding a Private AI Layer to Dynamics AX 2012 On-PremAdd AI to Dynamics AX 2012 on-prem without moving data: SQL replica, AIF services, retrieval index, local LLM, mirrored AX roles, approved write-back.
- Adding a Private AI Layer to Dynamics NAV On-PremAdd AI to Dynamics NAV on-prem without moving data: SQL replica, web services, retrieval index, local LLM, mirrored NAV permission sets, approved write-back.
- Adding a Private AI Layer to Business Central On-PremisesAdd AI to Business Central on-prem without cloud: API v2.0, custom API pages, SQL replica, retrieval, local LLM, BC permission sets, approved write-back.
Integrations
- How to add AI agents to Microsoft Dynamics GPHow to add AI agents to Microsoft Dynamics GP: a read-only tool layer over SQL views, eConnect for approved writes, and human sign-off, on-prem if needed.
- How to add a mobile app to Microsoft Dynamics GPBuild or buy a mobile app for Dynamics GP: barcode receiving, inventory moves, approvals and lookups using SQL Server views and eConnect, with offline handling.