Remote Databases (SQL Server, MySQL, PostgreSQL, Oracle, ODBC, MongoDB)

Last updated

License feature: Remote Databases

A remote database connection lets Scada.Ink read from — and, if you allow it, write to — a database elsewhere on the network: an ERP's SQL Server, a MySQL quality database, an Oracle MES schema, a MongoDB collection.

Once a connection exists you can:

• Include tables so they appear in Data Tables next to local tables, for dashboards, forms and scripts (see Data Tables).
• Run named queries against it from buttons, widgets, scripts and workflows (see Named Queries).
• Query it from the SQL runner in Data Tables (see SQL Query Mode).
• Store tag history in it with the SQL historian (see Store & Forward and Data Infrastructure).

Where and who

System → Remote DBs (/admin/remote-db, title Remote Database Connections). Needs the remote_databases licensed feature. Creating, editing, testing, browsing and including tables need admin; any signed-in user with the feature can see the list (the buttons are shown to everyone but refuse non-admins with Insufficient permissions).

Supported engines

Database Type	Default port	Notes
MSSQL / SQL Express	1433	SQL authentication only. Encryption is off unless you tick Encrypt the connection; the server certificate is trusted without being checked
MySQL / MariaDB	3306
PostgreSQL	5432	Pure JavaScript driver, nothing to install. Table and column names are case-sensitive when they were created quoted
Oracle	1521	Database Name is the service name (host:port/service). Thin mode, no Oracle client needed. Browsing included tables needs Oracle 12c or later
ODBC	—	Uses Connection String only; the ODBC driver for the target must be installed on the appliance's operating system
MongoDB	27017	Pure JavaScript driver, nothing to install. Not SQL: see MongoDB below

PostgreSQL is its own connection type: pick PostgreSQL rather than going through ODBC.

MongoDB

A MongoDB connection uses Host, Port, Database Name, Username and Password, or a Connection string (optional) such as mongodb+srv://cluster0.example.mongodb.net/plant for MongoDB Atlas, which is used instead of the host and port when it is set. Tick Use TLS for a server that requires it (a mongodb+srv:// address turns TLS on by itself). Other driver options, such as authSource or tlsAllowInvalidCertificates, go in the connection's options through the API. Test Connection answers Connected successfully to MongoDB (database "<name>").

What works on a MongoDB connection:

• Include tables: Browse Tables lists the database's collections. A collection has no fixed columns, so its columns are the top-level fields found in a sample of up to 200 documents; a field missing from some documents is nullable. Rows are identified by _id (shown as text), nested objects and arrays appear as JSON text, and dates as ISO times. Values typed in the table editor are stored with the column's type, so a number stays a number and a date becomes a real date. Search looks in the text fields.
• Transaction groups: pick the connection as a remote target and give the collection as the table. Each run inserts one document with the mapped columns as fields, plus the timestamp field if one is set (as a real date).
• The query box in Data Tables takes one JSON command instead of SQL. To read: {"find": "orders", "filter": {"status": "open"}, "sort": {"_id": -1}, "limit": 50}, {"aggregate": "orders", "pipeline": [...]} or {"count": "orders", "filter": {...}}. To change data (admin, with SQL runner on): {"insert": "orders", "documents": [...]}, {"update": "orders", "filter": {...}, "set": {...}} or {"delete": "orders", "filter": {...}} (add "many": true for more than one document). Extended JSON works, so {"$date": "2026-10-19T08:00:00Z"} is a date and {"$oid": "<id>"} an ObjectId. $out and $merge stages are refused.
• Store & forward: writes from the table editor, scripts and transaction groups are buffered while the server is unreachable, exactly as for SQL databases, and replayed in order.
• Tag history: to send tag changes to MongoDB, use a MongoDB data connector with Every tag that records history. MongoDB cannot be the SQL historian's database, and named queries cannot use a MongoDB connection (Named queries run SQL. A MongoDB connection cannot be their datasource…).

Create a connection

1. Click New.
2. Fill in Name (for example ERP), Database Type, Host, Port, Database Name, Username and Password — or, for ODBC, the Connection String.
3. For SQL Server, tick Encrypt the connection if the server requires encryption (Azure SQL, Force Encryption).
4. Click Test Connection. It tests what is in the form right now, even on a saved connection you have not saved again yet. A success message such as Connected successfully. Server responded with test query. appears; otherwise the driver's own error.
5. Decide what may modify data (below). Leave all three off for a read-only link.
6. Click Create.
7. In the detail view, click Browse Tables, tick the tables you need, give each a Display name and choose Read Only or Read/Write, then click Include Selected.

Tip: Create a database login for Scada.Ink with only the rights it needs — SELECT on the included tables, plus INSERT/UPDATE where you allow writes. Scada.Ink's own switches are a second line of defense, not a substitute for database permissions.

Connection fields

Field	What it does	Default	Notes
Name	How the connection is shown and chosen elsewhere	—	Required; unique (A connection with that name already exists)
Description	Free text	empty
Database Type	Engine	MSSQL / SQL Express	Changing it sets the default port on a new connection
Host	Server name or IP	—	Hidden for ODBC
Port	TCP port	per engine	Hidden for ODBC
Database Name	Database (Oracle: service name)	—
Username / Password	Database login	—	The password is encrypted on the appliance and never sent back to the browser. When editing, leave it blank to keep the current one
Encrypt the connection	Encrypts traffic to SQL Server (SQL Server only)	Off	Stored as encrypt in the connection's options; Test Connection uses the same setting
Connection String	Full ODBC connection string (ODBC only)	—	Encrypted on the appliance; any Pwd=/Password= value it carries is masked in the API and the page. When editing, the masked password stands for the stored one unless you type a new one

What may modify data here — all off by default, each for this connection only:

Switch	Allows
Named queries	An admin can save an INSERT/UPDATE/DELETE as a named query and run it from a button, script or workflow
Data Tables editor	Rows in included tables set to Read/Write can be added, edited and deleted in the table editor
SQL runner	An admin can type and execute INSERT/UPDATE/DELETE in the SQL runner — the broadest of the three

Connection pool size (0–10 connections by default), other driver options (such as trustServerCertificate) and enabling/disabling a connection are not on the form; they can be set through PUT /api/remote-db/connections/:id (poolMin, poolMax, options, enabled). Test Connection applies the same options as the saved connection.

Included tables

• An included table appears in Data Tables with the display name you gave it and the columns discovered from the database (mapped to integer, real, boolean, datetime or text).
• Read Only tables cannot be changed from Scada.Ink. Read/Write tables can, but only through the table editor if Data Tables editor is also on.
• A display name that matches a local table is refused ("X" conflicts with a local table name).
• Discovery lists: SQL Server — every base table in the database; MySQL — the connected database; PostgreSQL — base tables in every schema except pg_catalog and information_schema; Oracle — tables owned by the login's own schema only.
• If the database's columns change, remove and re-include the table (there is no refresh button; POST /api/remote-db/tables/:tableId/refresh does it through the API).
• Removing the connection removes its included tables from Scada.Ink (not from the database).

From scripts

• db.query('Display name', …), db.insert, db.update and db.delete find a table by display name — local tables first, then included remote tables. Scripts respect Read Only/Read/Write but not the Data Tables editor switch.
• db.transaction(...) covers local tables only.
• For anything else — joins, aggregates, stored logic — use namedQueries.run('name', params). See Scripting API Reference.

When the database is unreachable

Writes to included tables from the table editor and from scripts go through store & forward when that feature is licensed: a failed write is kept on the appliance and replayed every 20 seconds (details in Store & Forward). Named-query writes are not buffered — they fail with the database's error.

Warning: A buffered write reports success to the caller with zero rows changed. This includes writes that failed for a reason retrying will not fix (a constraint violation, a wrong column). Such a row blocks the rows behind it for that connection until it has failed ten times. If store_forward is not licensed, a failed write is discarded and the caller still sees success. Check Data Infrastructure → Remote DB S&F after any outage.

Known limits

• MySQL connections show Connected as soon as they are saved, before a real connection is made. Use Test.
• Search in the table editor skips binary columns (for example SQL Server varbinary/image, Oracle BLOB/RAW).
• Browsing included Oracle tables needs Oracle 12c or later. Inserts into Oracle tables do not report the new row's id.
• The Schema tab of an included table only lists its columns. Change columns in the database itself, then remove and re-include the table.
• From scripts, orderBy and where on a remote table must name columns the table has; anything else fails with Unknown column "X" in table "Y".
• Remote reads in named queries fetch the whole result and then apply the row limit — add a TOP/LIMIT/FETCH FIRST clause yourself on large tables.
• If the appliance's JWT secret is changed, stored passwords can no longer be decrypted; re-enter them.
• The Password, the Connection String and the connection's options can each hold a reference to the Secrets store, written ${secret:DB_PW} — including inside a connection string, as in Pwd=${secret:DB_PW};. A reference is a name rather than a credential, so it reads back as you typed it instead of masked, and the value is looked up each time a pool opens and on every Test Connection. Replace the secret and the next connection uses the new password, with nothing to re-enter here. A name that matches no secret fails with Secret "DB_PW" is not in the Secrets store instead of being sent to the database.

API

Method	Path	Role	Purpose
GET	/api/remote-db/connections	viewer	List connections (each password masked as ••••••••, or shown as its ${secret:NAME} reference)
POST	/api/remote-db/connections	admin	Create
PUT	/api/remote-db/connections/:id	admin	Update
DELETE	/api/remote-db/connections/:id	admin	Delete with its included tables
POST	/api/remote-db/connections/test	admin	Test unsaved settings
POST	/api/remote-db/connections/:id/test	admin	Test a saved connection and update its status
GET	/api/remote-db/connections/:id/tables	admin	Discover tables
POST	/api/remote-db/connections/:id/include	admin	Include tables
GET	/api/remote-db/tables	viewer	Included tables
GET	/api/remote-db/tables/:tableId/data	viewer	Rows (limit ≤ 1000)
POST	/api/remote-db/query	editor	Run one SELECT (writes: admin + SQL runner); up to 10,000 rows

Troubleshooting

Symptom	Cause	Fix
Login failed for user	Wrong credentials, or SQL Server set to Windows authentication only	Enable SQL Server and Windows authentication mode; check the login
Failed to connect to <host>:1433 in 10000ms / ETIMEDOUT	Host/port unreachable, firewall, SQL Browser instance port	Use the instance's fixed TCP port; open it in the firewall
Server requires encryption, set 'encrypt' config option to true.	SQL Server forces encrypted connections	Tick Encrypt the connection on the connection, or relax the server setting
ODBC driver not available: …	ODBC library missing on the appliance	Install unixODBC and the vendor driver
Oracle driver not available: …	Oracle driver package not installed	Contact support
Remote connection "X" is disabled	Connection disabled through the API	Re-enable it
That database does not allow named queries to modify data. Enable it on the connection under Remote Databases first.	Named queries switch off	Turn it on
This connection does not allow Data Tables to modify data. Enable it on the connection first.	Data Tables editor switch off	Turn it on
Table is read-only	Table included as Read Only	Remove and re-include as Read/Write
Edits "succeed" but rows never appear	Write buffered by store & forward	See Data Infrastructure → Remote DB S&F

See also

• Custom Data Tables
• Named Queries
• Data Tables: SQL Query Mode
• Store & Forward and Data Infrastructure
• Transaction Groups