Connect a Local SQL Server as a Label Datasource with the zplCloud CLI

The export loop, and why it breaks

Label data lives in an ERP, a WMS or an Azure SQL database. The usual workflow is export to CSV, fix the column names, upload, print - and repeat it tomorrow, because the CSV is already stale.

The zplCloud data hub removes the export. A CLI agent inside your network holds the SQL Server connection string, the platform sends it a query description, and only the result rows come back. The database is never exposed to the internet and the credentials are never stored in the cloud.

This post is the exact mechanics: what runs where, which SQL is generated, and what the hard limits are.

Architecture in one paragraph

zplcloud proxy opens one outbound TLS connection to api.zplcloud.com (SignalR). No inbound port, no NAT rule, no VPN. When you open a datasource in the platform, the backend sends the agent a request object - base query, filter, sort, offset, limit - and the agent turns that into T-SQL, executes it against your SQL Server with your database user, and returns the rows. The connection string exists only in the agent's process memory and its local configuration.

Step 1 - start the agent with one or more servers

# Multiple --sql flags are allowed; the NAME is what you pick in the platform.
zplcloud proxy --agent "Lager" \
  --sql PROD="Server=127.0.0.1;Database=erp;User Id=zplcloud;Password=…;Encrypt=True;TrustServerCertificate=True" \
  --sql WAREHOUSE="Server=sql-wh.internal.lan,1433;Database=logistik;User Id=zplcloud;Password=…;Encrypt=True;TrustServerCertificate=True"

Equivalent without putting secrets in the command line (they would land in your shell history):

# Windows PowerShell - one variable per server, name in the middle
$env:ZPLCLOUD_SQL_PROD_CONNECTION = "Server=127.0.0.1;Database=erp;User Id=zplcloud;Password=…;Encrypt=True;TrustServerCertificate=True"
zplcloud proxy --agent "Lager"

The startup banner lists what it found: SQL servers: PROD, WAREHOUSE. Authentication against the platform uses --api-key <key> or ZPLCLOUD_API_KEY.

Persist it for unattended operation:

• Windows: setx ZPLCLOUD_SQL_PROD_CONNECTION "…", or zplcloud proxy --agent "Lager" --service-install --api-key sk_zplcloud_…
• Linux/Raspberry Pi: same --service-install flag; it writes a systemd unit zplcloud-agent.service with Restart=always
• File instead of env: sqlservers.json next to the binary or in ~/.zplcloud/, shape { "sqlServers": { "PROD": "Server=…" } }
• Docker: ZPLCLOUD_SQL_PROD_CONNECTION in docker-compose.agent.yml

Connection string notes that cost people an hour

• Encrypt=True;TrustServerCertificate=True is the pragmatic pair for an internal server with a self-signed certificate. Drop TrustServerCertificate once you have a real cert.
• A named instance needs Server=host\\INSTANCE; a non-default port is Server=host,1433 - comma, not colon.
• Use a dedicated SQL login with SELECT on exactly the tables the labels need. The agent runs everything as that user, so the database is the permission boundary - not the platform.

Step 2 - create the datasource

In Data sources the running agent shows up under Remote SQL Server with one chip per configured name. Clicking a chip pre-fills the form.

Test runs SELECT 1 and returns server · database. Fields reads the column metadata via SELECT TOP 1 * with CommandBehavior.SchemaOnly - it fetches the schema without pulling data. If a heavily nested query returns no schema, the agent retries once with SingleRow.

Step 3 - what the agent actually executes

Your base query is wrapped as a subquery. Filter, sort and paging are appended by the agent:

SELECT * FROM ( SELECT ean, name, price FROM artikel ) AS ds
WHERE ean LIKE @p0
ORDER BY name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Three things are worth reading twice:

1. Values are parameters, never string concatenation. The filter builder emits @p0, @p1, … and binds the values through SqlCommand.Parameters. There is no place where a user-entered value becomes SQL text.

2. Paging is native. limit 10 becomes FETCH NEXT 10 ROWS ONLY; SQL Server does the work and returns ten rows over the wire, not a million.

3. The row count is a separate query. When you ask for the total, the agent runs SELECT COUNT(*) FROM (<base>) AS ds with the same WHERE and the same parameters.

The hard limits (from the agent source)

If you need more than 1000 rows in one view - batch printing, a full catalog - the platform pages through the result set with increasing OFFSET. Each page is its own 1000-row request against your server, so memory stays flat regardless of the total.

A 15-second timeout is deliberate. If your base query cannot answer in 15 seconds it belongs in an indexed view or a table with the right index, not in a label datasource.

Step 4 - use it in the designer and in Print Views

• Designer → Test Data tab → Data source: pick the datasource and press Load. Field bindings render with real rows instead of placeholder text, so you see the actual field lengths before anything reaches a printer.
• Print Views → configuration → Data source (data hub): the view's preview and its printing use the live query. The operator sees current data; nobody re-uploads a CSV.

Failure modes and what they mean

Plan

The data hub (SQL Server and MongoDB datasources via the CLI agent) is part of the Pro plan. Details on the pricing page.

Related

• zplcloud CLI - commands, service install, USB, watch folders
• Agent as a Docker container
• Cloud Integration / Connections