WinCC 7.0 SP3 SQL Database Read Write VBScript ADO Guide

Overview

SIMATIC WinCC V7.0 SP3 offers two production-ready paths for exchanging process data with Microsoft SQL Server:

1. The licensed User Archive option, which creates, manages, and persists structured tables inside the WinCC runtime database and exposes them to graphics runtime through the UserArchiveTable ActiveX control.
2. Direct VBScript access through ADO (ActiveX Data Objects) with the SQLOLEDB or SQLNCLI provider, which can connect to any local or remote SQL Server instance and read or write from a WinCC Global Action, a button event, or a scheduled C-script action.

User Archive is the right answer when the dataset lifetime equals the project lifetime and no external BI tool needs to query the data directly. Direct ADO is the right answer when you must share data with MES, SAP, an existing SCADA historian, or any third-party application that already owns the table schema. This article covers both paths, supplies working VBScript examples for connect / read / write, and documents the SQL Server authentication vulnerability published by CISA as ICSA-12-205-01 that affects WinCC V7.0 SP3 and earlier.

Prerequisites

Architecture: WinCC RT Database vs. External SQL

WinCC V7.0 SP3 stores its own runtime state in a Microsoft SQL Server instance installed as part of the WinCC setup (default instance name WINCC). All process tags, alarms, and tag logging rows are persisted in this embedded instance. The User Archive option simply adds additional tables to the same instance under the database CC_UserArchive_<ProjectName>_<ServerPrefix>.

An external SQL Server (typically used by MES, ERP, or a corporate historian) is a separate instance. The connection topology is:

Method 1 - WinCC User Archive (Built-in)

The User Archive editor in WinCC Explorer defines the schema, indexes, and primary key of the table. The runtime populates the rows; the UserArchiveTable ActiveX displays them in a grid that is fully configurable at runtime (column width, filter, sort, multi-select). Refer to the WinCC Information System section Options > User Archive in the WinCC V7.0 SP3 help and the Siemens KB article 10095491 for the full reference.

Step-by-step: Create a User Archive

1. Open WinCC Explorer, right-click User Archive, choose New Archive.
2. Define columns: ID (Integer, primary key, auto-increment), Timestamp (Date/Time), TagName (String, 50), TagValue (Float, double precision), Quality (String, 8).
3. Enable Allow only one client for writing for safety when multiple WinCC clients can write.
4. Set Update cycle for read-only archives that should be reloaded on a timer.
5. Compile and activate the project. The table is created automatically on first start in CC_UserArchive_<ProjectName>_<Prefix>.

Step-by-step: Display the User Archive in a picture

1. Open the Graphics Designer. From the Smart Objects palette insert UserArchiveTable control.
2. In the control properties, link the field Source to the configured archive name.
3. Configure column properties: visibility, width, alignment, format string.
4. Use the uaAppend, uaDelete, uaRead, uaWrite VBScript methods on the HMIRuntime object to populate rows from process tags. See the VBScript methods reference in the WinCC V7.0 SP3 help under Programming VBS > User Archive.

VBScript example - Write process tag value to User Archive

Dim ua, op
Set ua = HMIRuntime.UserArchives("ProductionData")
op = ua.uaAppend
If op = 0 Then
    Dim row
    Set row = HMIRuntime.UserArchives("ProductionData").uaGetFieldCollection
    row.Item("TagName").Value  = "Motor_Speed"
    row.Item("TagValue").Value = HMIRuntime.Tags("Motor_Speed").Read
    row.Item("Timestamp").Value = Now
    row.Item("Quality").Value   = HMIRuntime.Tags("Motor_Speed").Quality
    HMIRuntime.UserArchives("ProductionData").uaSetFieldCollection row
End If

Method 2 - Direct ADO / OLE DB from VBScript

Use the ADO model when the target table lives in a SQL Server instance outside the WinCC project database, or when a third-party tool owns the schema and you only have INSERT and SELECT rights.

Connection String Reference

Helper procedure - Open connection

Function OpenSqlConn(serverName, dbName, userName, password)
    Dim conn, cs
    Set conn = CreateObject("ADODB.Connection")
    cs = "Provider=SQLNCLI10;Server=" & serverName & ";Database=" & dbName & ";Uid=" & userName & ";Pwd=" & password & ";"
    conn.ConnectionTimeout = 10
    conn.CommandTimeout = 30
    conn.Open cs
    Set OpenSqlConn = conn
End Function

Step-by-step: Write a WinCC tag to an external SQL table

1. In Graphics Designer place a button. Configure the event Mouse > Left Click as a VBScript action.
2. Create the destination table in the external SQL instance:

   CREATE TABLE ProductionData (
       ID         INT IDENTITY(1,1) PRIMARY KEY,
       TagName    NVARCHAR(50)  NOT NULL,
       TagValue   FLOAT         NULL,
       Quality    NVARCHAR(8)   NULL,
       StampUtc   DATETIME      NOT NULL DEFAULT (GETUTCDATE())
   )

3. Click event script:

   Sub OnClick(ByVal Item)
       Dim conn, cmd, val
       Set conn = OpenSqlConn("MYSERVER\SQLEXPRESS", "MesData", "WinCCUser", "P@ssw0rd!")
       Set cmd = CreateObject("ADODB.Command")
       cmd.ActiveConnection = conn
       cmd.CommandText = "INSERT INTO ProductionData (TagName, TagValue, Quality) VALUES (?,?,?)"
       cmd.CommandType = 1   ' adCmdText
       cmd.Parameters.Append cmd.CreateParameter("p1", 200, 1, 50, "Motor_Speed")
       val = HMIRuntime.Tags("Motor_Speed").Read
       cmd.Parameters.Append cmd.CreateParameter("p2", 5,   1,   8, val)
       cmd.Parameters.Append cmd.CreateParameter("p3", 200, 1,   8, HMIRuntime.Tags("Motor_Speed").Quality)
       cmd.Execute , , 128   ' adExecuteNoRecords
       conn.Close
       Set cmd = Nothing
       Set conn = Nothing
   End Sub

4. Use a Global Action to write the tag value to SQL on every change. Configure a Standard cycle of 1 s and compare against the previous value to avoid flooding the table.

Step-by-step: Read from external SQL into a WinCC tag

1. Create the destination WinCC internal tag DB_Motor_Speed of type Signed 32-bit or Float.
2. On a button click or scheduled Global Action, run:

   Sub OnRead(ByVal Item)
       Dim conn, rs
       Set conn = OpenSqlConn("MYSERVER\SQLEXPRESS", "MesData", "WinCCUser", "P@ssw0rd!")
       Set rs = CreateObject("ADODB.Recordset")
       rs.CursorType = 3  ' adOpenStatic
       rs.LockType   = 3  ' adLockOptimistic
       rs.Open "SELECT TOP 1 TagValue FROM ProductionData WHERE TagName='Motor_Speed' ORDER BY StampUtc DESC", conn
       If rs.EOF Then
           HMIRuntime.Trace "No row for Motor_Speed" & vbCrLf
       ElseIf IsNull(rs.Fields("TagValue").Value) Then
           HMIRuntime.Trace "NULL value" & vbCrLf
       Else
           HMIRuntime.Tags("DB_Motor_Speed").Write rs.Fields("TagValue").Value
       End If
       rs.Close
       conn.Close
       Set rs = Nothing
       Set conn = Nothing
   End Sub

3. Verify in WinCC Explorer with Tag Simulation or in Graphics Runtime by adding an I/O field bound to DB_Motor_Speed.

Displaying Query Results in a WinCC Table Control

To display arbitrary result sets (not just User Archives) inside a WinCC picture, push the rows into a MSFlexGrid or MSHFlexGrid control, or use the DataGrid ActiveX with a bound ADODB recordset. The simplest path is the legacy MSFlexGrid COM control, which WinCC still ships and supports in V7.0 SP3.

Sub FillGrid()
    Dim conn, rs, g, i
    Set conn = OpenSqlConn("MYSERVER\SQLEXPRESS", "MesData", "WinCCUser", "P@ssw0rd!")
    Set rs = conn.Execute("SELECT StampUtc, TagName, TagValue FROM ProductionData ORDER BY StampUtc DESC")
    Set g = ScreenItems("GridResults")
    g.Rows = rs.RecordCount + 1
    g.Cols = rs.Fields.Count
    g.TextMatrix(0,0) = "Time" : g.TextMatrix(0,1) = "Tag" : g.TextMatrix(0,2) = "Value"
    i = 1
    Do While Not rs.EOF
        g.TextMatrix(i,0) = rs.Fields(0).Value
        g.TextMatrix(i,1) = rs.Fields(1).Value
        g.TextMatrix(i,2) = rs.Fields(2).Value
        rs.MoveNext
        i = i + 1
    Loop
    rs.Close : conn.Close
    Set rs = Nothing : Set conn = Nothing
End Sub

Error Handling and Diagnostics

Every ADODB call in VBScript can raise a runtime error. Wrap database code with On Error Resume Next and read Err.Number / Err.Description for diagnostics. Common ADO error codes:

For deeper diagnostics set the provider's SQL Server Profiler trace and run a VBScript test using cscript.exe on the WinCC station. Use the WinCC APDiag and DiagMonitor tools to confirm that the runtime process is reaching the VBScript at all.

Security: CISA ICSA-12-205-01

In July 2012, CISA (then ICS-CERT) published ICSA-12-205-01, a vulnerability advisory covering an insecure default SQL Server authentication configuration shipped with SIMATIC WinCC V7.0 SP3 and earlier. The advisory states that the WinCC installer configures the bundled SQL Server with a well-known administrative account whose credentials are documented and reachable from the network, allowing an unauthenticated remote attacker to gain full read/write access to the WinCC runtime database, modify process values, and disrupt operations.

Performance Considerations

• Use parameterised commands (cmd.Parameters.Append ...) instead of string concatenation. Parameterised commands are precompiled by the server and are immune to SQL injection.
• Throttle writes to one row per 250 ms or slower. WinCC Global Actions run in the same thread as the picture update, so excessive database traffic will stall the runtime UI.
• Use a connection pool by keeping a single ADODB.Connection object as a module-level reference; do not open and close a connection on every event.
• For high-volume logging (thousands of rows per second) use the WinCC Tag Logging fast option and configure the database swap path rather than ADO inserts.
• Index the columns you filter on. For a typical TagName + Timestamp query a composite index CREATE INDEX IX_PD_Tag_Time ON ProductionData(TagName, StampUtc DESC) will keep the read latency under 10 ms even on a multi-million-row table.

Troubleshooting Matrix

Verification Checklist

1. In WinCC Explorer, open Tools > Cross Reference and confirm the VBScript action is referenced from the picture.
2. Start Graphics Runtime, click the write button, then run SELECT TOP 10 * FROM ProductionData ORDER BY StampUtc DESC in SQL Management Studio.
3. Modify the value in SQL directly with an UPDATE, click the read button, and verify the I/O field updates.
4. Watch the APDiag output for any VBScript exception with On Error Resume Next traces.
5. Confirm firewall rules: Test-NetConnection -ComputerName MYSERVER -Port 1433 from PowerShell on the runtime station must return TcpTestSucceeded : True.
6. Confirm the SQL Server login can be reached: from the WinCC station run sqlcmd -S MYSERVER\SQLEXPRESS -U WinCCUser -P P@ssw0rd! -Q "SELECT 1".

Field-Proven Tips

• Always store timestamps in UTC. The local time on the SQL Server and the WinCC station can drift by hours, and daylight saving changes will break chronological reports.
• Use HMIRuntime.Trace in the VBScript and tail the WinCC trace file in C:\Program Files\Siemens\Automation\WinCC\Diagnose for rapid diagnosis in the field.
• When a single value must be both written and read, build a small DBManager COM component once and call it from every picture, rather than duplicating ADO code in each event.
• For redundant WinCC servers, configure the User Archive on the master only and let the standby replicate with WinCC Redundancy; do not run direct ADO from both servers to the same external table or you will get duplicate inserts on failover.

Related Siemens Documentation

• Siemens KB 10095491 - User Archive configuration basics
• Siemens KB 11925601 - User Archive scripting samples
• Siemens KB 2463816 - Displaying User Archive data in runtime
• CISA ICS Advisory ICSA-12-205-01 - WinCC SQL Server authentication