How does ml-connector authenticate Sage 300 if it only uses HTTP Basic Auth?

ml-connector stores the Sage 300 username and password encrypted and encodes them to base64 with every request, satisfying the mandatory Authorization header. Both credentials must be uppercase as required by Sage 300, and the API user must have Web API security group permissions assigned in Administrative Services, not the built-in Admin account.

Can ml-connector write data back into Sage 300 after it lands in BigQuery?

No. This integration is read-only from Sage 300 into BigQuery. ml-connector polls Sage 300 and streams the records as a one-way sync into BigQuery tables, where they can be queried and analyzed. Writes are never sent back to Sage 300, keeping the ERP safe from downstream modifications.

How does the integration handle the Sage 300 server being on-premise and self-hosted?

ml-connector requires the customer to expose their Sage 300 IIS server over HTTPS and provide the full base URL (for example, https://sage300.customerdomain.com/Sage300WebApi). The poll requests are made directly to that URL with HTTP Basic Auth credentials, so the Sage 300 instance must be internet-accessible or connected via a private network bridge that ml-connector can reach.

Sage 300Google BigQuery

Sage 300 and Google BigQuery integration

Sage 300 runs procurement, payables, and general ledger operations. Google BigQuery provides a query engine and data lake for analytics. Connecting the two moves transactions from your ERP into a cloud warehouse where finance teams can query, audit, and analyze spending, vendor performance, and GL reconciliation at scale. ml-connector polls your Sage 300 instance on a schedule you control and appends every invoice, payment, and GL entry into BigQuery datasets, ready for immediate BI analysis.

How Sage 300 works

Sage 300 is an on-premise ERP running on Windows IIS with a SQL Server backend. It exposes vendors, invoices, payments, GL accounts, GL batches, purchase orders, and items through REST and OData APIs at a customer-hosted URL with HTTP Basic Authentication. All API calls require a base64-encoded username and password in the Authorization header, with both credentials in uppercase. Sage 300 has no webhooks or change-data-capture system, so all data reads are pull-based via OData $filter and $skip/$top pagination, typically on time or date filters to detect new or modified records. The API is self-hosted and requires the customer to expose their IIS server over HTTPS.

How Google BigQuery works

Google BigQuery is a fully-managed, serverless cloud data warehouse on Google Cloud Platform. It authenticates via OAuth 2.0 with a service account JWT Bearer flow and provides REST APIs to create and populate datasets and tables. BigQuery has no native webhook or event system, so data flows are pull-only, but records can be streamed in as soon as Sage 300 data is fetched. The service account must have bigquery.dataEditor and bigquery.jobUser roles. Access tokens expire after one hour and must be refreshed on each request cycle. BigQuery stores data in datasets organized by the customer, with no pre-defined schemas, so the customer and connector together define table structures to match Sage 300 entities.

What moves between them

Data flows from Sage 300 into Google BigQuery. ml-connector polls Sage 300 at regular intervals, pulling new and modified invoices, payments, vendors, GL accounts, GL journal entries, and purchase orders via OData filters, then streams those records into BigQuery tables. Each poll cycle checks for records modified since the last successful sync using date or timestamp filters, ensuring no duplicates are re-inserted. Read-only analysis queries in BigQuery can reference Sage 300 data without ever writing back to the ERP, so the sync is one-way and safe for financial data.

How ml-connector handles it

ml-connector stores the Sage 300 HTTP Basic Auth credentials (username and password) encrypted and encodes them to base64 on each request, satisfying Sage 300's mandatory authorization header requirement. On the BigQuery side, it stores the service account JSON key encrypted and exchanges it for an OAuth 2.0 access token at the start of each sync, refreshing the token if it expires during a long poll cycle. Because Sage 300 is pull-only with no webhooks, ml-connector polls on a fixed schedule (daily, weekly, or as configured) using OData $filter expressions with date/time comparisons to fetch only records created or modified since the last sync. The records are then batch-inserted into BigQuery using the tabledata.insertAll endpoint, which supports deduplication via insertId to prevent double-counting if a sync is retried. If a BigQuery request fails due to a transient error or permission issue, ml-connector backs off and retries, and every record inserted carries a timestamp and source batch identifier so downstream queries can trace the data lineage. Because Sage 300 user credentials are required at the API level, ml-connector validates that the configured user has Web API security group permissions in Administrative Services, not just the built-in Admin account, which has no API access.

A real-world example

A mid-sized construction or manufacturing company runs Sage 300 on-premise for accounts payable, procurement, and general ledger. Finance and accounts payable teams manually export reports from Sage 300 each month to analyze vendor spend, payment trends, and cost allocations, then paste the data into spreadsheets for month-end close and vendor performance reviews. With Sage 300 and Google BigQuery connected, every invoice, payment, and GL entry flows automatically into BigQuery each night. Finance teams now query vendor invoices by date range, GL account, or cost code in real time, run spend analytics across departments, and validate month-end GL balances without manual export or re-entry, cutting the close cycle by days.

What you can do

Stream Sage 300 invoices, payments, and GL entries into BigQuery on a scheduled poll cadence, with automatic deduplication via insertId.
Query Sage 300 vendor master, GL accounts, and purchase order data in BigQuery alongside transaction data for spend analysis and reconciliation.
Store Sage 300 HTTP Basic Auth credentials encrypted and manage OAuth 2.0 token refresh for BigQuery without manual intervention.
Audit every record inserted into BigQuery with source batch identifiers and timestamps so downstream queries can trace data lineage.
Detect new and modified records in Sage 300 via OData $filter with date/time comparisons, ensuring only fresh data is polled on each cycle.

Questions

How does ml-connector authenticate Sage 300 if it only uses HTTP Basic Auth?: ml-connector stores the Sage 300 username and password encrypted and encodes them to base64 with every request, satisfying the mandatory Authorization header. Both credentials must be uppercase as required by Sage 300, and the API user must have Web API security group permissions assigned in Administrative Services, not the built-in Admin account.
Can ml-connector write data back into Sage 300 after it lands in BigQuery?: No. This integration is read-only from Sage 300 into BigQuery. ml-connector polls Sage 300 and streams the records as a one-way sync into BigQuery tables, where they can be queried and analyzed. Writes are never sent back to Sage 300, keeping the ERP safe from downstream modifications.
How does the integration handle the Sage 300 server being on-premise and self-hosted?: ml-connector requires the customer to expose their Sage 300 IIS server over HTTPS and provide the full base URL (for example, https://sage300.customerdomain.com/Sage300WebApi). The poll requests are made directly to that URL with HTTP Basic Auth credentials, so the Sage 300 instance must be internet-accessible or connected via a private network bridge that ml-connector can reach.

Connect Sage 300 and Google BigQuery

Free to use. Add your credentials, ping your real systems, and see if we fit.

Get started