Prophet 21 is an on-premise SQL Server ERP. There is no cloud endpoint to point an integration at, no tenant URL, no OAuth screen. The system holding your inventory, your pricing, and your order history sits on a server in your building, and every modern tool that wants to read it has to solve that problem first. Most stop trying. The three answers a distributor is usually offered are a VPN tunnel into the ERP, a nightly CSV dropped on a file share, or nothing at all. The first makes your security team unhappy for good reason, the second is stale by breakfast and silently breaks the day someone renames a column, and the third is why the ERP is still a system people read through a screen.
Prophet 21 itself, directly, through its own database — the tables and views you agree are in scope, and nothing outside that list. Three kinds of work run across the connection: parameterized read queries, inventory snapshots, and a price-list sync.
Parameterized reads are the general case. A question is asked with its values bound rather than pasted into the statement, executed against the ERP, and the rows come back to the application that asked. Inventory snapshots are a scheduled capture of stock position, materialized so that reporting and any AI layer above it are reading a table rather than hammering a production ERP. The price-list sync keeps item pricing aligned between the ERP and the systems downstream of it.
What we do not connect is everything else in the database. An ERP schema is large, much of it is internal bookkeeping, and a scope agreed up front is the difference between an integration your DBA signs off on and one they spend a year suspicious of.
A small bridge agent runs on your Prophet 21 server. When the application needs something from the ERP, it does not attempt to reach your network. It enqueues a job in a queue table. The agent polls that queue, sees the job, executes it locally against P21, and writes the result back.
Every connection is outbound from your network. There is no inbound firewall rule, no port opened to the database, and no VPN into the ERP. That single architectural decision is what makes the integration approvable, and it is the reason we build it this way rather than the way that would be quicker.
The agent enforces a read-only SELECT guard before anything executes. SQL is also sanitized before it reaches the agent: comments and trailing semicolons are stripped by a parser that is quote- and bracket-aware, so a string literal containing a double dash or a semicolon survives untouched and a bracketed identifier is left exactly as written. That parser exists for an unglamorous reason — an older installed agent build rejects any statement text containing a semicolon, including semicolons inside comments, and we cannot assume the executable running on a customer's server is current. Naive stripping would corrupt legitimate queries. Doing it correctly means understanding where the quotes and brackets open and close.
Snapshots use replace-and-reload with batched inserts rather than row-by-row updates. The target is rebuilt in bulk, which is both dramatically faster and, more importantly, atomic in effect: you are looking at the old snapshot or the new one, never a partially rewritten table that reporting will happily average anyway.
The price-list sync upserts by item number, then removes only the rows tagged with the P21 source that no longer appear in the pull. A deletion in the ERP therefore propagates, and pricing rows your team maintains by hand are never touched — because the sync only ever reaches for rows it put there itself. Getting that wrong is how an integration deletes work that took someone a month.
Transient backend failures retry with exponential backoff. A job that never gets picked up at all times out with an explicit error asking whether the agent is running, rather than hanging. Every completed sync writes an activity event, so there is a durable record of what ran and when.
Someone who can install and run a small service on the Prophet 21 server. This is the one thing we cannot do remotely and cannot work around; it is usually an hour of an IT administrator's time, and it is the hour the whole integration depends on.
A read-only SQL account. Give it the narrowest rights that still cover the scope, and issue it to a service identity rather than to a person, so the connection does not die when someone changes roles.
Agreement on which tables and views are in scope. Bring whoever actually knows the schema to that conversation — in most Prophet 21 installations that is one person, and their thirty minutes will save weeks of guessing which of four similarly named fields carries the real number.
No. The agent on your server makes outbound calls only. It reaches out, asks whether there is work waiting, does the work locally against the ERP, and posts the result back. Nothing from outside your network initiates a connection to the database, so there is no inbound rule to write, no port to expose, and no site-to-site tunnel for your IT team to maintain and audit. If your server can reach the internet through the same egress path your other software uses, the integration works.
The agent enforces a read-only guard: statements that are not selects are refused at the agent, on your server, before they reach the database. That guard is deliberately placed on your side of the connection rather than in the application, because a control you host is a control you can inspect. Pair it with a SQL account that has read rights only and you have two independent barriers, either of which is sufficient on its own.
A queued job that nobody picks up times out and reports an explicit error saying the agent does not appear to be running, rather than sitting in a spinner until someone gives up. That distinction matters more than it sounds: the common failure in on-premise integrations is not a crash, it is a silence that everybody interprets as slowness. Transient backend failures are separate — those retry with exponential backoff and usually resolve without anyone noticing.
Because we cannot assume the executable installed on your server is the current build. An older agent build rejects any statement text containing a semicolon, including one buried inside a comment, so queries are sanitized in transit: comments and trailing semicolons are stripped by a parser that understands quoting and bracketed identifiers. A string literal containing two dashes survives intact, and a bracketed column name with awkward characters is left alone. Writing that parser properly is unglamorous work, and skipping it produces an integration that fails on the one query that matters.
Every completed sync writes an activity event, so the record of what ran and when is in the application rather than in somebody's memory. Snapshots replace and reload their target in batched inserts, which means you are never looking at a half-updated table wondering which half is current — a snapshot is either the previous one or the new one.
Start with a complimentary industry analysis. You leave with the one project worth deploying first.
Schedule a Consultation