Connect an Industrial SQL Database to Copilot
You're building a Copilot agent that should answer questions about production — and the data lives in a SQL database on a plant or corporate network: batch records, quality results, downtime logs, production counts. SQL databases are everywhere in manufacturing, but the SQL Server or SQL Express instance that holds your operational data is rarely in a position where Microsoft's cloud can query it safely. Getting a Copilot agent to surface live production data from a SQL source requires solving problems that go beyond writing a good Custom Connector.
The barriers below are what makes this integration harder than it looks.
Why it's hard
Seven reasons connecting a Copilot agent to an industrial SQL database is harder than expected.
Copilot agents call HTTP — SQL Server doesn't expose a REST API
Copilot Studio agents retrieve data through Custom Connectors making HTTP calls to REST endpoints. A SQL Server instance exposes a TDS protocol port — not an HTTP interface. Reaching data from a Copilot agent requires an intermediary REST API that runs queries against SQL Server and exposes results over HTTP to Microsoft's cloud. That layer doesn't exist by default and has to be built and maintained.
SQL databases sit on restricted plant or corporate networks — Microsoft's cloud can't reach them
Production SQL databases are rarely internet-accessible. They sit behind firewalls on plant or corporate LANs. Microsoft's Power Platform makes outbound HTTP calls from cloud infrastructure — it has no direct path to a SQL Server on a restricted network. Getting there requires either a relay service or a network path change — both of which involve IT and often a security review.
Shared or service credentials — risky to expose through a connector
Industrial SQL databases often use shared credentials or a single service account for application access. Wiring these credentials into a Custom Connector definition means they're stored in Power Platform configuration and used for every agent request. If the connector is misconfigured, cloned, or shared broadly, those credentials are exposed. Row-level security and least-privilege access are rarely configured in OT-adjacent databases.
The agent doesn't know which tables or columns are relevant
Industrial SQL databases evolved organically — table names like tblBatchHdr, T_QualEvents, or PROD_LOG_01 are meaningful only to the team that built them. The agent has no schema map or business context. Without a curated semantic layer — descriptions of what each table means, which columns matter for operational questions, what joins are required — the agent can't query meaningfully even if it has database access.
Raw query results are too large and too unstructured for action responses
A raw SELECT against a production or batch database can return thousands of rows. Action responses in Copilot Studio have payload size limits — large query results will exceed them. Even where payload fits, the agent receives a flat row-and-column result set that it has to interpret without knowing what the data represents. Aggregation, filtering, and context-adding logic has to live in the endpoint layer.
Agent queries can add unexpected load to production databases
Industrial SQL databases often run on shared or under-resourced infrastructure — the same server that writes production data is the one being queried. Poorly optimised agent-triggered queries, repeated calls from multiple users, or inefficient aggregation over large tables can add unexpected load to a server that's already doing production work. DBA teams reasonably push back on any new query source they don't control.
Power Platform Custom Connectors require admin approval
Any Copilot Studio agent accessing external data needs a Custom Connector with an approved OpenAPI spec. In enterprise Microsoft 365 environments, connectors that touch operational databases go through Power Platform admin review, security assessment, and sometimes change management. The approval timeline regularly runs to weeks before a connector reaches production use.
What teams usually try
REST API wrapper on the same network as the database
Build a service on the plant or corporate network that queries SQL Server and exposes a REST API. Define a Custom Connector against this endpoint and handle query results in the API layer.
The catch: The service needs an internet-accessible path for Microsoft's cloud to call it — a firewall change that IT has to approve. Credentials still have to be managed in the service. Without a semantic layer, query results still lack business context. The service adds ongoing infrastructure overhead.
Replicate to Azure SQL or Dataverse
Set up replication or ETL from the on-premise SQL database to Azure SQL or Dataverse. Point the Custom Connector at the cloud target. Microsoft's cloud can reach it directly.
The catch: The agent answers questions about a replicated snapshot — not live production data. Replication adds infrastructure complexity and introduces latency. Schema mapping between source and target tables requires design and ongoing maintenance.
Export to SharePoint lists or files
Schedule SQL exports to SharePoint lists or CSV files. Configure the Copilot agent to read these as its knowledge source for production questions.
The catch: The agent answers questions about the last export. Large result sets and flat files perform poorly for operational queries like time-range filtering or cross-table lookups. The export cadence determines how stale the data is.
How TrendOps solves it
TrendOps Edge connects to SQL databases on the plant or corporate network — querying production tables, applying the context mapping, and aggregating results — then forwards structured data to TrendOps Platform in the cloud over a single outbound connection. Your Copilot Studio agent calls TrendOps Web API through a single Custom Connector. No direct SQL Server access from Microsoft's cloud, no credential exposure, no raw query results overflowing action payloads.
The agent calls TrendOps Web API — a cloud-accessible HTTPS endpoint. No SQL Server credentials in the connector, no database port open to the internet, no direct connection from Power Platform to production infrastructure.
TrendOps normalises SQL query results into structured, labelled responses the agent can present. Action responses include field names and values the end user can understand — not cryptic column identifiers from an organically-grown production schema.
TrendOps handles result aggregation server-side. Action responses return summarised, filterable data — not raw row dumps that overflow payload limits or leave the agent to interpret a flat result set.
TrendOps executes defined queries — the agent calls parameterised endpoints, not freeform SQL. DBAs retain control over which queries run, preventing unexpected load or inadvertent access to sensitive tables.
TrendOps Edge queries SQL Server continuously. The agent returns current production values — not a snapshot from a replication job that ran on a schedule.
TrendOps normalises SQL data alongside AVEVA PI, FactoryTalk, DeltaV, and MQTT sources. One connector gives the agent a unified view across production data sources.
What you end up with
No SQL Server connection from Microsoft's cloud — the agent calls a cloud API.
Current production values — the agent knows what's happening right now.
Context-rich, aggregated responses — not raw table rows with cryptic column names.
One connector, every OT and production source — not just SQL.
Don't want to build your own AI agent integration?
TrendGuru — TrendOps's built-in AI agent — already has access to your plant data. Ask questions about your production SQL database in plain language and get answers instantly, deployed directly in Microsoft Teams. No Custom Connector to configure, no OpenAPI spec to write, no Power Platform admin approval to wait for.
Learn about TrendGuru →Your Copilot agent shouldn't need direct SQL Server access to answer a question about production data
TrendOps gives your Copilot agent a clean, cloud-accessible API for your industrial SQL database — contextualised, aggregated, and available without opening database ports to the internet.
Book a Demo