WinCC VBScript SQL Server Access Configuring ODBC Provider

1. Overview

Siemens WinCC supports direct database access from runtime scripts using VBScript, allowing HMIs and SCADA stations to read/write process values, alarms, audit trails, and production data to a Microsoft SQL Server backend. The connection path is always the same at the operating-system level: WinCC VBScript creates an ADODB.Connection object, which talks to an OLE DB provider, which in turn loads an ODBC driver that finally talks to SQL Server. The single most frequent failure point is the provider string itself: the wrong provider, a missing data source name (DSN), or a 32-bit/64-bit driver mismatch returns ADODB.Connection.Open error 0x800A0E7A or 0x80004005.

This reference consolidates the configuration steps documented in Siemens Entry IDs 61883659 and 61886098, adds the OLE DB/ODBC provider matrix required by current Windows and SQL Server versions, and supplies verified VBScript examples and a troubleshooting matrix for field engineers.

2. Product Family Disambiguation

If your project was created in TIA Portal, the documented behaviour of chapter 2.1 of Entry 61883659 applies directly. If you are running WinCC V7.x (classic), the same OLE DB provider strings are valid but the SQL Server Express instance installed by WinCC Setup is pre-bound to the WinCC project.

3. Prerequisites

1. SQL Server instance reachable from the HMI/PC station. Microsoft SQL Server 2014, 2016, 2017, 2019, or 2022 in any edition. SQL Server Express (free) is acceptable for non-redundant WinCC stations.
2. TCP port 1433 open between the runtime PC and the SQL host. Verify with Test-NetConnection -ComputerName <sqlhost> -Port 1433 on PowerShell or telnet sqlhost 1433.
3. Authentication method decided. Either SQL Server Authentication (login/password) or Windows Authentication (requires identical or trusted AD accounts on both hosts).
4. Matching ODBC driver installed. 32-bit drivers for 32-bit WinCC runtime, 64-bit for 64-bit. Modern SQL Server installations should use ODBC Driver 17 for SQL Server or ODBC Driver 18 for SQL Server.
5. Administrative rights on the runtime PC to register the DSN via ODBC Data Source Administrator (32-bit / 64-bit).
6. WinCC runtime running as a user with rights to read the DSN. Either run WinCC Runtime as a service account that owns the DSN, or create a System DSN visible to all users.

4. ODBC and OLE DB Provider Architecture

VBScript in WinCC is hosted by the Windows Script Host and can only consume OLE DB providers via the ADODB type library. ODBC drivers are reached through the Microsoft OLE DB-to-ODBC bridge provider (MSDASQL) or, preferably, through a native OLE DB provider such as SQLNCLI / MSOLEDBSQL. The chain is:

1. WinCC VBScript creates ADODB.Connection.
2. Connection.Provider = "…" selects the OLE DB provider.
3. Provider loads the ODBC driver identified by the DSN or the connection string.
4. Driver opens a TCP/TDS session to SQL Server.

4.1 Provider String Reference

4.2 ODBC Driver Reference

5. Step-by-Step: Creating the ODBC Data Source

The data source name (DSN) is the lookup key the VBScript Provider resolves at runtime. The DSN stores the driver name, server address, authentication, and default database.

5.1 Open the Correct ODBC Administrator

Windows ships two ODBC administrators. WinCC runtime is a 32-bit process on every currently supported release (V7 and TIA Portal Advanced/Professional), so the 32-bit ODBC administrator is the one that matters:

1. Press Win + R, type %windir%\SysWOW64\odbcad32.exe, press Enter. This is the 32-bit administrator on 64-bit Windows.
2. The 64-bit version is at %windir%\System32\odbcad32.exe. Configuring a 64-bit DSN for a 32-bit WinCC runtime is a common cause of "Data source name not found and no default driver specified" error IM002.

5.2 Add a System DSN

1. Select the System DSN tab and click Add….
2. Pick the driver ODBC Driver 18 for SQL Server (or 17 / 11.0 matching your SQL Server version).
3. In the wizard:
   • Name: WinCC_SQL (must match the VBScript connection string exactly, case-insensitive on Windows).
   • Description: WinCC runtime data source.
   • Server: SERVERNAME\INSTANCENAME or SERVERNAME,1433 for non-default ports.
4. Choose authentication: With SQL Server authentication… for username/password, or With Integrated Windows authentication… for Kerberos/NTLM.
5. Tick Change the default database to: and select your target database.
6. Leave default options. For driver 18+ tick Encrypt=Yes and choose the certificate trust option appropriate to your environment (in lab: Optional; in production: Mandatory with a real CA cert).
7. Test the data source with the Test Data Source… button. A successful test shows SQLSTATE 01000 / Microsoft SQL Server login succeeded.

5.3 Configuring SQL Server to Accept the Connection

If the test fails with SQL Server does not exist or access denied:

1. Open SQL Server Configuration Manager on the SQL host.
2. Expand SQL Server Network Configuration > Protocols for <INSTANCE> and enable TCP/IP.
3. Right-click TCP/IP > Properties > IP Addresses tab; verify the listening port (default 1433) under IPAll.
4. Restart the SQL Server service.
5. Confirm the Windows Firewall rule SQL Server (TCP-in) is enabled for the SQL Server service binary path.
6. Create a SQL login if using SQL auth: CREATE LOGIN [WinCCUser] WITH PASSWORD = 'StrongP@ssw0rd!'; then map it to a database user in your target DB.

6. WinCC VBScript: Connection Strings and Provider Names

Chapter 2.1 of Siemens FAQ 61883659 requires exactly two parameters to open a data source: the Provider and the Data Source (DSN). Additional connection string parameters are passed as required.

6.1 Connection Using Native OLE DB Provider (Recommended)

' WinCC VBScript - connect via OLE DB Driver 18 for SQL Server
Dim conn, cmd, rs, connStr
Set conn = CreateObject("ADODB.Connection")

connStr = "Provider=MSOLEDBSQL;" & _
          "Server=SQLHOST\SQLEXPRESS,1433;" & _
          "Database=ProductionData;" & _
          "UID=WinCCUser;" & _
          "PWD=StrongP@ssw0rd!;" & _
          "Encrypt=Yes;" & _
          "TrustServerCertificate=No;" & _
          "Application Name=WinCC_RT;"

conn.ConnectionTimeout = 10
conn.CommandTimeout = 30
On Error Resume Next
conn.Open connStr
If Err.Number <> 0 Then
    HMIRuntime.Trace "ADODB.Open failed: " & Err.Number & " / " & Err.Description & vbNewLine
    Err.Clear
End If
On Error Goto 0

6.2 Connection Using ODBC DSN with MSDASQL Bridge

' WinCC VBScript - connect through the ODBC bridge using a DSN
Dim conn, rs
Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = _
    "Provider=MSDASQL;" & _
    "DSN=WinCC_SQL;" & _
    "UID=WinCCUser;" & _
    "PWD=StrongP@ssw0rd!;"

conn.ConnectionTimeout = 10
conn.Open

6.3 Connection Using DSN-Less Native Client

' DSN-less connection with SQL Server Native Client 11.0 (legacy)
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = _
    "Provider=SQLNCLI11;" & _
    "Server=SQLHOST\SQLEXPRESS;" & _
    "Database=ProductionData;" & _
    "Integrated Security=SSPI;" & _
    "DataTypeCompatibility=80;" & _
    "MARS Connection=True;"
conn.Open

6.4 Parameter Reference

7. Reading and Writing Process Data

' Insert a batch record into ProductionData.dbo.Batches
Dim conn, cmd, batchID
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=MSOLEDBSQL;Server=SQL01;Database=ProductionData;" & _
                       "UID=WinCCUser;PWD=StrongP@ssw0rd!;Encrypt=Yes;"
conn.Open

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO dbo.Batches (BatchNo, StartTime, Operator) VALUES (?, ?, ?)"
cmd.Parameters.Append cmd.CreateParameter("BatchNo", 200, 1, 32, "B2024-0007")  ' adVarChar, adParamInput
cmd.Parameters.Append cmd.CreateParameter("StartTime", 135, 1, , Now)        ' adDBTimeStamp
cmd.Parameters.Append cmd.CreateParameter("Operator", 200, 1, 32, HMIRuntime.Tags("@UserName").Read)
cmd.Execute , , 128  ' adExecuteNoRecords

batchID = conn.Execute("SELECT SCOPE_IDENTITY()")(0)
HMIRuntime.Trace "Inserted BatchID=" & batchID & vbNewLine

conn.Close
Set cmd = Nothing
Set conn = Nothing

' Read the latest 50 alarms from SQL into WinCC internal tags
Dim conn, rs, i
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=MSOLEDBSQL;Server=SQL01;Database=ProductionData;" & _
                       "UID=WinCCUser;PWD=StrongP@ssw0rd!;Encrypt=Yes;"
conn.Open

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3  ' adUseClient
rs.Open "SELECT TOP 50 AlarmTime, Tag, Priority, Message FROM dbo.AlarmLog ORDER BY AlarmTime DESC", conn

i = 0
Do While Not rs.EOF And i < 50
    HMIRuntime.Tags("AlarmText_" & i).Write rs.Fields("Message").Value
    i = i + 1
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

8. Verification Procedure

1. DSN test: Open odbcad32.exe (32-bit), select WinCC_SQL, click Configure > Test Data Source. Expect Microsoft SQL Server login succeeded within 3 seconds on a LAN.
2. VBScript connection test: Run a minimal script (Section 6.1) from the WinCC script editor in runtime. Trace output ADODB.Open failed: … indicates the error code; a clean trace means Err.Number = 0 and the connection succeeded.
3. SQL-side verification: From SSMS run SELECT program_name, login_name, host_name FROM sys.dm_exec_sessions WHERE program_name = 'WinCC_RT';. Each successful WinCC connection should appear within 10 s.
4. Performance smoke test: Time a 1 000-row SELECT using Timer in VBS. Healthy LAN: < 200 ms; > 1 s indicates network latency, missing indexes, or wrong provider.
5. Process-data round trip: Insert a row from WinCC, read it back via SSMS, then update from SSMS and read back from WinCC. Confirms bidirectional access.

9. Troubleshooting Matrix

10. Security and Hardening

• Never embed passwords in scripts. Use a WinCC internal tag of type String in a restricted authorization area, or read from an encrypted file using the WinCC @ServerName mapping.
• Use least-privilege logins. Grant the WinCC user db_datareader and db_datawriter on the production database only; do not use sa.
• Force TLS. Driver 18 defaults to Encrypt=Yes; pair with a CA-signed SQL Server certificate in production.
• Restrict the SQL Server port. Bind SQL to a fixed port and add a Windows Firewall rule allowing only the WinCC subnet.
• Enable auditing. In SSMS > Server Properties > Security > set Login auditing to Failed logins only at minimum.
• Disable SA. Rename the SA account and disable it after creating named logins.
• Audit WinCC scripts centrally. Log every conn.Open and conn.Execute to a WinCC alarm for traceability.

11. Edge Cases and Field-Proven Caveats

• 32-bit vs 64-bit mismatch: WinCC runtime is 32-bit; if you install the 64-bit-only ODBC Driver 17 on a 64-bit runtime host, the driver will not be visible to the 32-bit odbcad32.exe in SysWOW64. Always install both (32- and 64-bit) driver variants on dual-platform hosts.
• Named pipes vs TCP/IP: If the connection string omits Network Library, the driver will try named pipes first on local connections. Across hosts, force TCP: Network Library=DBMSSOCN.
• MARS and lingering cursors: If you use adUseServer cursors and fire many open recordsets, exhaustion of the connection pool can manifest as Timeout expired. Switch to adUseClient (CursorLocation = 3) and disconnect the recordset from the connection with rs.ActiveConnection = Nothing.
• SQL Server Express Advanced Services: Free SQL Server Express limits the database size to 10 GB per database (2017–2022). For multi-shift production logging, plan for SQL Server Standard.
• Anti-virus interference: Some endpoint protection suites block odbcad32.exe or inject DLLs into msado15.dll. Add the WinCC install directory and %windir%\SysWOW64\odbcad32.exe to the AV exclusion list.
• Regional settings: Decimal separator mismatch between WinCC station and SQL Server locale can corrupt floating-point inserts. Force Use Settings for Visual Basic or pass parameters rather than concatenated strings.
• Always close: WinCC's VBScript host is multi-threaded across HMI screens; failing to Set conn = Nothing at the end of every script leaks connections and triggers Pool exhausted within hours.

12. Quick Reference Card