How-to
Connect EcoStruxure Automation Expert to SQL Server
Short answer
The runtime cannot own the SQL connection: standard FCT functions execute Structured Text only, and the 24.1 release closed the build-your-own service block route. Run a gateway on a PC that reaches both networks, exchange typed data over OPC UA or MQTT, and let the gateway alone own the SQL Server connection on TCP port 1433.
On this page
- Why the runtime cannot own the SQL connection
- Choose the gateway that fits the job
- Commission the SQL Server endpoint first
- Define the data contract before writing code
- Build the EAE side of the handshake
- Configure the gateway service
- Worked example: sizing the recipe-read timeout for a 220 ms round trip
- Prove it end to end before release
- Questions asked
You will not load a SQL client into the EcoStruxure Automation Expert runtime, and version 24.1 offers no supported route to build your own Service Function Block. Standard FCT functions execute Structured Text only, so System.Data.SqlClient has nowhere to load. The working architecture is a gateway: a service on a PC that reaches both the controller network and the database, exchanging typed data with EAE over OPC UA or MQTT while it alone owns the SQL Server connection on TCP port 1433. When the goal is archiving rather than recipes and reporting, the EAE Archive Database is the vendor-documented shortcut.
Why the runtime cannot own the SQL connection #
Three facts close the direct route. First, FCT functions are Structured Text: IEC 61131-3 defines the programming languages of the control runtime, Structured Text among them, and a function in that world cannot load an external .NET assembly such as System.Data.SqlClient. Second, Service Function Blocks exist, but they are implemented in C and delivered finished inside libraries; you consume them, you do not author database clients in them. Third, 24.1 no longer provides FM_DLL and BM_DLL, so the build-your-own route that material for older releases may describe is gone at that version.
The C# you already wrote is not wasted. It belongs in the gateway service, where the full .NET stack is available and a failed connection cannot stall a control task. That split is correct engineering anyway: database calls block, retry and time out in ways a deterministic machine sequence must never wait on.
Choose the gateway that fits the job #
- An OPC UA link (IEC 62541) when the controller must answer requests. The gateway reads and writes typed, exposed EAE variables in a session-based request-response pattern, which suits recipe reads. On the controller side this means putting the handshake variables behind EAE's OPC UA server; deploying EAE OPC UA device configuration is its own setup task.
- MQTT when the design favors brokered, asynchronous publication with store-and-forward, which suits process logging on unreliable links. Recipe exchange over MQTT works but stacks the same handshake discipline on top of topic design; bridging OPC UA PLC data to MQTT with an edge PC covers that pattern.
- A custom C# service when you need SQL transactions, stored procedures, validation, bounded retries and distinct error categories, which is exactly the recipe-read case. The service speaks OPC UA or MQTT to EAE and owns the database connection.
Run the gateway on a fanless mini PC in the control cabinet, on a network path that reaches both the controller and the database server. If reporting is the only destination and the plant already runs a historian, using AVEVA Historian with EcoStruxure Automation Expert can take the custom SQL layer out of scope. For your two goals the split is clean: recipe reads and parameter writes go through the custom service over OPC UA, and routine logging rides the OPC UA or MQTT path, where logging PLC values from an OPC server into SQL Server shows the route end to end.
Commission the SQL Server endpoint first #
- Get from the database administrator the host name, instance and database names, the authentication mode, and the exact tables or stored procedures the integration will touch.
- Confirm the port the instance listens on. The design uses TCP port 1433, the conventional listener for a default instance; verify the configured value instead of assuming it.
- From the gateway host, test name resolution and open one TCP connection to that host and port.
- Log in with the identity the gateway will use, not your own account, and grant it only the read, insert, update or execute permissions the integration needs.
- Run one representative read and one write outside EAE. A successful TCP connection proves reachability only; authentication, database selection, schema rights and transaction success stay unproven until this step passes.
If the site requires encrypted transport to the database, configure it in the gateway's SQL client, not in controller logic. Move on only when the gateway identity completes the required operation and nothing beyond it.
Define the data contract before writing code #
Use a handshake instead of letting the gateway infer intent from drifting process values, and separate commands from acknowledgements so a reconnect cannot silently repeat an operation.
| Data group | Typical contents | Required behavior |
|---|---|---|
| Request | Operation type plus a request identifier that changes monotonically | The gateway executes each identifier once |
| Payload | Recipe key, parameters, batch context, process values | The gateway snapshots the whole payload before touching SQL |
| Response | Completion state, returned values, the matching identifier | EAE accepts only the response for the active request |
| Diagnostics | Connection state, result category, readable error text | An operator can tell protocol, SQL and validation failures apart |
For logging writes, carry a unique business or request key and make the database operation idempotent where a duplicate row would mislead a report. For recipe reads, validate ranges and required fields before any returned value reaches a control parameter. Never place passwords or connection strings in exposed EAE variables; credentials live in the gateway's protected configuration.
Build the EAE side of the handshake #
- Create the handshake variables, illustrative names with IEC 61131-3 types:
ReqID : UDINT := 0incremented by one per request, an operation selector, the payload variables,RespID : UDINT := 0, a completion flag, returned data and a diagnostic string. - Expose only what the chosen OPC UA or MQTT interface needs; keep internal sequence states off the integration surface.
- Implement the sequence in Structured Text: capture the request, wait for the response whose identifier equals the active request, validate it, then acknowledge to the machine sequence.
- Set timeout and fallback from the process requirement. A recipe-dependent sequence waits in a controlled state or faults when data is late; routine logging never stops deterministic control.
- Test variable access with a protocol client before SQL Server is connected: write a test request and confirm the gateway reads the exact value and type, then write a test response and confirm EAE receives it.
Configure the gateway service #
- Wait for a request identifier you have not seen, then read the whole payload into a local snapshot.
- Reject missing fields, out-of-range values and mismatched types before opening a transaction.
- Execute a parameterized command or a stored procedure, and never build SQL text by concatenating values that arrived from the control application; parameterized commands close that injection path.
- For a recipe read, map the returned row into the response payload; for logging, commit the transaction before reporting success.
- Write the matching response identifier and final state, and report authentication, network, validation and SQL failures as distinct categories.
- Retry transient connection failures a bounded number of times, never block the control task while retrying, and never report success before the commit completes.
Connection pooling, transactions and retry scheduling belong in this service because they are nondeterministic operating-system services.
Worked example: sizing the recipe-read timeout for a 220 ms round trip #
Inputs:
- Platform: an EAE 24.1 application reading recipes through a custom C# gateway over OPC UA, the architecture chosen above.
- Database: SQL Server reached on TCP port 1433, the design port, confirmed with the database administrator in endpoint step 2.
- Handshake:
ReqID : UDINT := 0, incremented by one per request, withRespIDreturned on completion. - Timing inputs for the arithmetic, example values rather than site measurements: worst-case SQL read 150 ms, gateway snapshot and validation 50 ms, network round trip 20 ms.
Rule in words: the handshake timeout must cover the worst-case round trip five times over, rounded up, so a slow but working path completes and only a genuinely lost response faults the sequence.
The arithmetic:
- Worst-case round trip = 150 ms + 50 ms + 20 ms = 220 ms.
- Timeout = 5 × 220 ms = 1100 ms, rounded up to 1500 ms.
Proof of the transaction rule: set ReqID from 0 to 1 with the recipe key loaded. The gateway executes once, returns RespID = 1, and the log shows exactly one committed row for identifier 1. Resend ReqID = 1: that identifier was already processed, so the row count stays one.
Decision: with the timeout at 1500 ms, a response arriving at the 220 ms worst case completes the machine sequence, and anything past 1500 ms faults it into a controlled wait with one bounded gateway retry. If your measured worst case exceeds 220 ms, rescale the timeout to five times that figure, rounded up.
Prove it end to end before release #
- Request a known recipe record from EAE and confirm the gateway returns the same identifier with every field in the right type.
- Submit one known log record and confirm exactly one committed row appears.
- Resend the same request identifier and confirm no second row appears.
- Stop the database service, then repeat with a wrong password; EAE must report the correct diagnostic category and never set the completion flag.
Release when one request produces one committed transaction and a failed transaction never acknowledges success.
Questions asked
Is there an official way to run SQL inside the EcoStruxure Automation Expert runtime?
Not for code you write yourself. Service Function Blocks are implemented in C and shipped in libraries, and 24.1 no longer provides FM_DLL and BM_DLL, so there is no supported kit for a custom SQL block. A vendor knowledge base article, EcoStruxure Automation Expert - Getting Database Values into HMI with C#, shows the C# route on the HMI side, which runs on a PC rather than in the control runtime.
Does the EAE Archive Database replace the gateway?
Only for archiving. The vendor documents installing and configuring the Archive Database, and the EcoStruxure Automation Expert catalog lists SQL compatibility with Windows Server 2019 and Server 2022, Windows 10, Windows 11 and Linux. It stores EAE data on the schedule you configure; it is not a route for recipe reads, stored procedures or your own transactions, so keep the gateway for those.
What does a successful TCP connection to port 1433 actually prove?
Only that the gateway host can reach the listener. Authentication, database selection, schema rights and transaction success stay unproven until you log in with the gateway's own identity and run one representative read and one write. Also confirm the port itself: 1433 is the conventional default-instance listener, and a named instance can be configured on a different port.
Where should the SQL credentials live?
In the gateway's protected configuration store, never in variables exposed to the controller. Grant that identity only what the integration needs, for example execute permission on the specific stored procedures it calls, so a compromised gateway cannot reach unrelated tables. Keep the connection string out of any code the control engineers maintain.