WinCC Runtime Advanced V17 SQL Authentication via ODBC: Root Cause and Workarounds

Siemens WinCC Runtime Advanced V17 (TIA Portal V17) supports logging of data and alarms to relational databases through ODBC. Officially, both Windows Authentication and SQL Server Authentication are documented as supported logon procedures when configuring the ODBC data source. In field practice, however, configuring SQL Authentication frequently results in a connection that fails at runtime, even though the ODBC administrator test (via odbcad32.exe) reports success. This article documents the root cause, the underlying driver behaviour, and the supported workarounds for production deployments.

1. Problem Description

A typical failing deployment looks like this:

1. The user creates a System DSN in odbcad32.exe (32-bit on 32-bit RT, 64-bit on 64-bit RT) pointing to a Microsoft SQL Server instance.
2. During the DSN configuration, the user selects SQL Server Authentication and enters a valid Login ID and Password.
3. The "Test Data Source" button returns SQL_SUCCESS; the DSN is saved.
4. In the TIA Portal project, the user opens Runtime Settings > Logs > Data Logs / Alarm Logs and selects the new DSN as the destination.
5. The project is compiled and downloaded to the RT Advanced runtime.
6. At runtime, the log writer either silently discards records, raises a database alarm 120001 / 120002 (database cannot be reached) or logs the SQL state 28000 (login failed).
7. Inspecting the SQL Server errorlog shows the connection attempt was made using NT AUTHORITY\SYSTEM (or the runtime's service account), not the configured SQL login.

Operators and engineers see the ODBC test pass, then see a connection failure in runtime. Siemens' official documentation states SQL Authentication is supported; the gap between documentation and runtime behaviour is the source of most support tickets on this topic.

2. Root Cause Analysis

WinCC Runtime Advanced does not implement a native SQL client. All database access from the runtime is performed through the Windows ODBC driver manager (odbc32.dll). To use a connection, the runtime calls SQLDriverConnect with a connection string that omits UID and PWD and instead uses the DSN name as the only identifier.

The Microsoft ODBC Driver for SQL Server (sqlsrv32.dll / msodbcsql17.dll) honours the following resolution order when no credentials are present in the connection string:

1. Credentials cached in the credential store for the DSN.
2. Credentials stored in the registry under HKLM\SOFTWARE\ODBC\ODBC.INI\<DSN> (for System DSNs).
3. Integrated Windows Authentication using the calling process token.

Since SQL Server 2012, the Microsoft SQL Server ODBC driver deliberately does not persist the SQL Authentication UID/PWD pair in the registry DSN definition. Microsoft documents this behaviour in the SQL Server Native Client and ODBC Driver 17/18 release notes under the topic "Persistent Security Information". The registry keys LastUser and LastPassword are intentionally suppressed because the encrypted password storage model is no longer considered secure.

When the runtime opens the DSN with no credentials, the driver falls through to step 3 above and attempts a Windows-integrated logon. On a typical engineering PC, this succeeds against the local SQL Server because the user is a domain admin. In production, where the runtime runs as LOCAL SYSTEM or a dedicated service account, the logon fails with:

SQL State: 28000
Native Error: 18456
Message: Login failed for user 'NT AUTHORITY\SYSTEM'.

The ODBC administrator test (Step 5 of the DSN wizard) passes because the test is performed by the interactive user who did type the credentials into the wizard and is therefore authenticated for the lifetime of that process. The runtime, executing as a different identity, sees no credentials and falls back to Windows Authentication.

3. Why the Documentation Says SQL Authentication Is Supported

Siemens documentation, specifically the manual "WinCC Runtime Advanced - Logging" (entry ID 57434982), Section 3.2 "SQL Database User Data Management", describes the logon procedure selection inside the ODBC Data Source Administrator. That description refers to the SQL Server ODBC driver's capability, not to the runtime's behaviour. The runtime simply hands the DSN to the driver; whether the resulting connection uses SQL or Windows auth depends on what the driver can extract from the DSN. Because the driver cannot extract persisted SQL credentials, only Windows auth is feasible from the runtime.

4. Engineering Workarounds

Three patterns are used in production. Each has trade-offs.

4.1 Pattern A: Windows Authentication (Recommended)

Grant the runtime host's machine account (or the dedicated service account that starts the RT Advanced process) a SQL Server login with the required db_datareader / db_datawriter rights on the target database.

Steps:

1. Open SQL Server Management Studio on the database server.
2. Connect to the instance hosting the log database.
3. Security > Logins > New Login. Set Login name to the machine or service account, choose Windows authentication.
4. On User Mapping, map the login to the log database and tick db_datareader, db_datawriter and db_ddladmin (the latter is needed if WinCC creates tables on first run).
5. Confirm the account is not blocked by an explicit DENY CONNECT SQL.
6. On the runtime host, open the ODBC administrator and configure the System DSN with With Windows NT authentication using the network login ID.
7. Click Test Data Source; the test will succeed if the SQL login is correctly mapped.
8. Compile and download the TIA project. The runtime will connect under the configured identity.

4.2 Pattern B: ODBC DSN with Trusted_Connection in the Connection String

Where the application permits, set the DSN connection string explicitly to use Windows auth by appending Trusted_Connection=Yes; in the advanced options. This removes ambiguity regardless of how the DSN was created.

Driver={ODBC Driver 17 for SQL Server};
Server=sql-prod-01\WINCC;
Database=WinCCLogs;
Trusted_Connection=Yes;

4.3 Pattern C: Persisted Credentials in Credential Manager (Field Proven)

The Microsoft ODBC driver will use credentials saved in the Windows Credential Manager under the generic credential entry ODBC:dsn=<DSN_NAME>. This is the only Microsoft-supported mechanism to inject a SQL login into a runtime that does not embed UID/PWD. Configure it via cmdkey on each runtime host:

cmdkey /generic:ODBC:dsn=WinCC_LogDB /user:wincc_app_login /pass:<password>

Restrictions:

• The credential is stored per-user; LOCAL SYSTEM cannot read user-mode credential vaults. Use a dedicated service account and start the RT Advanced runtime under that account via sc config or the Services MMC.
• On panel runtime (Comfort Panels) this pattern is not available; use Pattern A.
• Password rotation requires a process to rewrite the credential; build a small scheduled task using cmdkey /generic:ODBC:dsn=WinCC_LogDB /pass:<new> after rotating the SQL password.

4.4 Pattern D: Automated DSN Deployment at Scale

For fleets of engineering stations and runtime PCs, hand-configuring each DSN is impractical. Microsoft documents a deployment pattern using PowerShell and the ODBCConf.exe utility, summarised in Configure the ODBC Driver for SQL Server and the related Microsoft Q&A Automating ODBC Data Source Deployment with Embedded Credentials. A production-grade script registers the DSN under HKLM and writes a LastUser value that the runtime reads at startup:

# Deploy-Dsn.ps1
param(
  [string]$DsnName = "WinCC_LogDB",
  [string]$SqlServer = "sql-prod-01\WINCC",
  [string]$Database = "WinCCLogs",
  [string]$Driver = "ODBC Driver 17 for SQL Server"
)

$reg = "HKLM:\SOFTWARE\ODBC\ODBC.INI\$DsnName"
New-Item -Path $reg -Force | Out-Null
Set-ItemProperty -Path $reg -Name "Driver"  -Value "C:\Windows\System32\msodbcsql17.dll"
Set-ItemProperty -Path $reg -Name "Server"  -Value $SqlServer
Set-ItemProperty -Path $reg -Name "Database" -Value $Database
Set-ItemProperty -Path $reg -Name "Trusted_Connection" -Value "Yes"
Set-ItemProperty -Path $reg -Name "LastUser" -Value "wincc_app_login"

$odbcinst = "HKLM:\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
Set-ItemProperty -Path $odbcinst -Name $DsnName -Value $Driver

Distribute via Group Policy, Intune, or a configuration management tool. The LastUser value is read by older Microsoft drivers but the password is not persisted; pair this with Pattern C for full automation.

5. SQL Server-Side Verification

Once a pattern is in place, confirm the runtime is connecting with the intended identity. From SQL Server Management Studio, run:

SELECT login_name, host_name, program_name, login_time, client_net_address
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('WinCCLogs')
  AND program_name LIKE '%WinCC%';

You should see exactly one row per RT Advanced instance, with the login_name matching the Windows account provisioned in Pattern A. If multiple rows accumulate, a misbehaving runtime is reconnecting rapidly, which indicates credential or network trouble rather than an authentication mismatch.

6. Troubleshooting Matrix

7. Security Considerations

SQL Authentication in this context pushes the problem of credential storage down to the deployment layer. The recommended secure pattern is:

1. Use a least-privilege dedicated SQL login for the application, with password complexity enforced by SQL Server policy.
2. Run the WinCC RT Advanced runtime as a dedicated service account (e.g. DOMAIN\svc_wincc_rt) rather than LOCAL SYSTEM or an interactive engineer account.
3. Store the password in Windows Credential Manager scoped to the service account, not in clear text on disk or in scripts.
4. Enable TLS on the SQL Server instance and configure the ODBC DSN with Encrypt=Yes;TrustServerCertificate=No; when using ODBC Driver 18.
5. Audit logins with SQL Server Audit and forward to your SIEM. Failed logons for the WinCC account should be alerted on, as they often indicate credential rotation drift.

8. Version-Specific Notes

9. Verification Checklist

Run through the following before declaring the issue resolved:

• SQL Server login exists for the runtime's Windows identity.
• Login is mapped to the target database with db_datareader, db_datawriter, and (on first run) db_ddladmin.
• DSN bitness (32/64) matches the runtime bitness.
• ODBC administrator Test Data Source succeeds under the runtime service account (use runas /user:svc_wincc_rt odbcad32.exe).
• TIA project log configuration references the DSN by name, not by a free-form connection string.
• Runtime started, sys.dm_exec_sessions shows one session with the expected login_name.
• A test alarm written via the runtime appears in the SQL target table within the configured log cycle.
• No 120001 / 120002 alarms in the WinCC alarm log for at least one full log cycle.

10. Field-Proven Caveats

• Changing the runtime service account after a successful DSN configuration requires re-running the ODBC test under the new identity; existing DSNs do not magically rebind.
• Microsoft's Always Encrypted column encryption is not supported by WinCC RT Advanced; the runtime cannot supply the column master key.
• SQL Server contained databases work with Windows auth but require the contained user to be granted connect per database; do not mix contained and server-level logins.
• On a Comfort Panel, ODBC is only available on 4"+ devices and only over Ethernet; the panel acts as the ODBC client.
• If the runtime host is in a different domain than the SQL Server (common with DMZ architectures), you must use a SQL login even with Trusted_Connection=Yes; the cross-domain trust is rarely the path of least surprise.