WinCC 7 SQL Server Integration: UserArchive and IDB Setup

1. Problem Scope and Architecture

Two-tier plant layouts frequently require a WinCC Runtime station (PC-2) to mirror or display values held in a Microsoft SQL Server instance running on a separate host (PC-1). The operator screen on PC-2 must reflect inserts, updates, and deletes performed in SQL Server without manual refresh, and without running a full WinCC Server/Client pair. The required data path is therefore:

SQL Server (PC-1) → network (TCP 1433, named instance 1434 UDP) → WinCC Runtime database / tag interface (PC-2) → WinCC picture / UA Table OCX

Three officially supported methods exist for WinCC 7.x:

For WinCC Unified Comfort Panels running V18+, the equivalent capability is provided by built-in JavaScript database functions (see WinCC Unified: Use of SQLite or Microsoft SQL databases - Support Entry 109806573).

2. Prerequisites

2.1 Network and Operating System

• Ethernet connectivity between PC-1 and PC-2, same subnet or routed with firewall rules for TCP 1433 (default SQL instance) and UDP 1434 (SQL Browser service for named instances).
• Both stations running Windows 10 LTSC 2019/2021 or Windows Server 2016/2019/2022, 64-bit, with matching regional settings (date format dd.MM.yyyy or yyyy-MM-dd is critical for ODBC).
• SQL Server 2005/2008 R2/2014/2019 native client or Microsoft ODBC Driver 17/18 for SQL Server installed on the WinCC host.

2.2 WinCC Software

• WinCC 7.0 SP3 / 7.4 SP1 / 7.5 SP2 (or current 7.5 SP2 Update 12) on PC-2.
• SQL Server 2005 Standard/Enterprise (or higher) installed and patched on PC-1. Mixed-mode authentication enabled if using SQL logins.
• WinCC/IndustrialDataBridge V7.5 license dongle if Method 1 is selected (6AV6371-1DX07-5AX0 typical order number).
• WinCC/UserArchive option installed (default WinCC 7 install includes the runtime; designer requires separate "WinCC UserArchive" component).

2.3 ODBC Data Source on the WinCC Host (PC-2)

1. Open ODBC Data Sources (64-bit) from the Windows Control Panel.
2. System DSN tab → Add → SQL Server driver (or ODBC Driver 17 for SQL Server).
3. Name: PLANT_SQL, Server: PC-1\SQLEXPRESS (or named instance), authentication set to either Windows NT or SQL Server (mixed mode).
4. Default database: select the target schema (for example PlantData).
5. Test the connection before closing. Failure to test here is the single most common cause of "Provider not found" errors at runtime.

3. Method 1 — WinCC/IndustrialDataBridge (IDB)

IDB is the recommended path when more than ~30 tags must be replicated or when the SQL source is not the same physical machine as the WinCC project. The configuration is XML-based and lives in the WinCC project under \<project>\IndustrialDataBridge\.

3.1 Define Provider (SQL Server source)

1. Open the IDB Configurator on PC-2 (Start → Siemens Automation → IndustrialDataBridge → Configuration).
2. Insert a new Provider of type SQL Server / ODBC.
3. Select the DSN created in section 2.3 (64-bit).
4. Enter the SQL statement. For full table replication use SELECT TagName, TagValue, TimeStamp FROM dbo.LiveValues; for incremental loads add a high-water mark: WHERE LastChange > :LASTSYNC.
5. Map result-set columns. The first column becomes the key.

3.2 Define Consumer (WinCC Tags)

1. Insert a Consumer of type OPC Data Access (the IDB service acts as an OPC DA server; WinCC tags connect via the internal OPC channel OPC&.WinCC&.IndustrialDataBridge).
2. Configure the OPC namespace: \<IDB service name>\ProviderName.
3. Map each provider column to a WinCC tag. Tag names follow the IDB ItemID convention, for example \PLANT_SQL\LiveValues\TagValue.

3.3 Configure the Connection and Schedule

1. In the IDB Configurator, create a Link binding the SQL Provider to the OPC Consumer.
2. Trigger mode: select Polling (default 1000 ms) for cyclic refresh, or Event-driven if the source table exposes a SQL Server trigger / Service Broker queue that writes to a file watcher.
3. Action on read error: Use last valid value and raise an alarm via the WinCC bit message system.
4. Save and activate. The IDB runtime service S7IDBSvc starts automatically under localSystem.

Reference: Siemens Support Entry 109751704 — IndustrialDataBridge configuration manual (requires Siemens Industry Online Support login).

4. Method 2 — User Archive (UA) and UA Table OCX

User Archives are the simplest method when the data is naturally tabular (recipe parameters, batch IDs, alarm history) and the dataset fits in memory. Each UA is one SQL table inside the WinCC project database CC_UserArchives_<project>_R.

4.1 Create a User Archive

1. In WinCC Explorer right-click User Archives → New User Archive.
2. Define fields matching the SQL Server table. Supported types: Number (Double/Int), Text (max 255), Date/Time, Binary. Keep the primary key in column 1.
3. Set Length on text fields. Maximum total columns per archive: 500.
4. Set Authorization if the operator must not edit.

4.2 Display in Runtime with UA Table Control

1. Open a WinCC picture in Graphics Designer.
2. Insert SmartObjects → WinCC UserArchiveTable Element (OCX WHFUA.ocx).
3. Connect the control to the archive created above. Set TimeColumn for chronological sorting.
4. Set the Refresh property to Yes; UA caches data in the project database and refreshes when the picture is loaded.

4.3 Populate the UA from External SQL

For mirroring, the WinCC UserArchive option includes the UA API (C++/COM) and an OLE-DB provider WinCC UA Provider that exposes every archive as an ODBC data source. The IDB can consume a UA source just as easily as a SQL source, so the typical pipeline is:

SQL Server (PC-1) → IDB Provider → IDB Consumer (UA Provider) → UA table → UA Table OCX in picture

This keeps the picture code identical across all operator stations while pushing the change detection into the IDB scheduler.

5. Method 3 — VBScript Direct ADO (No Additional License)

When the WinCC/UserArchive or IDB option is not licensed, scripts can read SQL directly and write to internal tags. Use this approach only for small tag counts (<50) because each poll cycle runs in the WinCC scripting host and is single-threaded.

5.1 Create the WinCC Tags

Create internal tags of matching data type: SQL_Tag1, SQL_Tag2, SQL_TS. Set update rate 250 ms in the tag properties.

5.2 Global Script for Cyclic Polling

Open Global Script → C-Script (or VBScript) and create a new action triggered every 1 second:

#include "apdefap.h"
int gscAction( void )
{
  HRESULT hr;
  _ConnectionPtr pConn = NULL;
  _RecordsetPtr pRst  = NULL;

  hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
  hr = pConn.CreateInstance(__uuidof(ADODB.Connection));
  pConn->ConnectionString =
    "Provider=SQLOLEDB;Data Source=PC-1\\SQLEXPRESS;"
    "Initial Catalog=PlantData;User ID=wincc_ro;Password=***;"
    "Connection Timeout=5;";
  pConn->Open("","","",0);

  pRst = pConn->Execute(
    "SELECT TagValue, TimeStamp FROM dbo.LiveValues "
    "WHERE TagName='Pump01_Flow'", 0, adCmdText);

  if (!pRst->EOF) {
    SetTagFloat("SQL_Tag1", (float)pRst->Fields->Item["TagValue"]->Value);
    SetTagChar("SQL_TS",   (LPCSTR)(_bstr_t)pRst->Fields->Item["TimeStamp"]->Value);
  }
  pRst->Close();
  pConn->Close();
  CoUninitialize();
  return 0;
}

For multiple rows, loop with pRst->MoveNext() and write each row to an indexed tag block (e.g. SQL_Block_01..SQL_Block_50) or pack into a string tag and parse in the picture.

6. Method 4 — WinCC Unified (V18+) Direct Database Access

WinCC Unified Comfort Panels and Unified PC Runtime ship a managed JavaScript API for native database access, eliminating IDB. Both SQLite (file-based, on-device) and Microsoft SQL Server (network) are supported, provided the server speaks the TDS protocol expected by the Microsoft.Data.Sqlite / OLE DB provider.

6.1 Enable Database Functions

1. In TIA Portal open the Unified device, Runtime settings → Services → Database.
2. Add a connection entry. Type: MSSQL. Server: tcp:PC-1,1433, database PlantData, user/password.
3. Confirm the panel can reach the server (Check connection button). If the panel uses HTTPS/REST proxy, ensure the proxy allows *.sql and TCP 1433.

6.2 Read from a Table in a Script

// In a Unified script, e.g. button "Refresh"
async function readFlow() {
  let conn = HMIRuntime.Database.CreateConnection();
  conn.ConnectionString =
    "Server=PC-1;Database=PlantData;User Id=wincc_ro;Password=***;";
  await conn.Open();
  let cmd = conn.CreateCommand(
    "SELECT TagValue, TimeStamp FROM dbo.LiveValues " +
    "WHERE TagName = @n",
    { "@n": "Pump01_Flow" });
  let rs = await cmd.ExecuteReader();
  if (await rs.Read()) {
    HMIRuntime.Tags.SysF.SetValue(
      await rs.GetValue("TagValue"));
    HMIRuntime.UI.SysF.TimeStamp = await rs.GetValue("TimeStamp");
  }
  await conn.Close();
}

Full syntax, supported providers, and known limitations (column types, datetime conversion, transaction scope) are documented in Siemens Support Entry 109806573 — WinCC Unified: Use of SQLite or Microsoft SQL databases.

7. Triggering the Refresh — Polling vs. Event

For HMI, polling at 1–2 s is almost always sufficient. Event-driven paths are reserved for fast control loops (<500 ms) where the network latency between PC-1 and PC-2 is the limiting factor.

8. Tag and Picture Wiring (Display Path)

1. Open the WinCC picture that must display the value.
2. Insert an I/O Field and link it to SQL_Tag1 (scripted) or to the OPC-DA tag exposed by IDB (e.g. IDB_PLANT_SQL_Tag1).
3. Set Update to 250 ms. WinCC polls the tag at the configured cycle; the underlying value is refreshed by IDB or script independent of the picture update rate.
4. To read on a button press, create a C-action on the Mouse click event that calls readFlow() (Unified) or a button-script VBScript that calls the same ADO code as the global action.

9. Verification and Acceptance Test

1. Insert a test row directly in SQL Server: INSERT INTO dbo.LiveValues (TagName, TagValue, TimeStamp) VALUES ('Pump01_Flow', 42.5, GETDATE()).
2. Within one polling cycle (1–2 s) the I/O Field on the WinCC Runtime picture must update to 42.5. If it does not, jump to the troubleshooting matrix.
3. Update the value: UPDATE dbo.LiveValues SET TagValue=43.7 WHERE TagName='Pump01_Flow'. Confirm Runtime reflects 43.7.
4. Delete the row and verify that the tag goes to the configured fallback (0 or last valid).
5. Disconnect the network cable from PC-1 for 10 s. Runtime must raise a "Database link down" alarm (configure in IDB error handling or in the script try/catch). Reconnect and confirm recovery within the next poll cycle.
6. Run WinCC diagnostics:

   Start → Siemens Automation → WinCC → System Information → Connections. IDB and UA states must show "OK / Running".

10. Troubleshooting Matrix

11. Performance and Sizing Notes

• Each IDB Provider query is a synchronous T-SQL call. Profile with SQL Profiler / Extended Events: target <50 ms per cycle for a 100-row table on a local 1 GbE link.
• VBScript ADO paths are limited by the WinCC scripting host: a single action blocks the scheduler for the duration of the call. Keep queries under 200 ms; if slower, use a stored procedure that returns a single denormalized row.
• WinCC UA in-memory footprint: ~16 bytes per row overhead. A 10 000-row archive is negligible (<200 KB) but a 1 000 000-row archive will degrade picture open time noticeably. Use the Filter property on the UA Table OCX to limit displayed rows.
• For WinCC Unified, the JavaScript database functions are async; however, a single connection supports one query at a time. For concurrent reads, use a connection pool of 2–4 entries.

12. Security and Hardening

1. Create a dedicated SQL login wincc_ro with the minimum permission db_datareader on the target database. Do not reuse sa.
2. Disable the SQL Server Browser service if no named instances are needed, and pin the listening port to 1433 with the Windows Firewall rule restricted to the PC-2 IP.
3. Encrypt the connection with Encrypt=yes;TrustServerCertificate=no in the ODBC DSN when the network traverses an uncontrolled segment.
4. Do not store passwords in the WinCC picture scripts. Use the Windows Credential Manager or the IDB password-encrypted field.
5. Audit wincc_ro logins with SQL Server Audit. Forward successful and failed logins to the central SIEM.

13. Field-Proven Commissioning Sequence

1. Install SQL Server and create the source database and tables. Insert a single test row.
2. Install WinCC 7 and required options. Configure the OS regional settings and disable UAC prompts for the WinCC service account.
3. Create the ODBC DSN in the correct bitness. Run the Windows test — it must succeed before continuing.
4. Configure IDB (or UA) on PC-2. Map at least one tag end-to-end and verify in the WinCC tag manager with the internal debugger.
5. Wire the I/O Field in the picture. Verify display with the acceptance test from section 9.
6. Trigger network failure and recovery. Validate the alarm path.
7. Document the architecture, credentials location, and recovery steps in the project Functional Design Specification.