IMD SolutionComputing LibraryShop main store

Troubleshooting

Citect Historian database creation fails on a named instance

Edge computing and IIoT Published 6 min read

Short answer

The error names a failed server-level command on the named instance's master database, not a broken wizard. Get the underlying SQL exception, confirm that the login the wizard presents sits in the sysadmin server role on that instance, and confirm the engine version is on the historian build's support list. Switching between Windows and SQL Server authentication fixes neither a permission gap nor a version mismatch.

On this page
  1. What the wrapper message identifies
  2. Why the default 2008 instance proves nothing
  3. Windows administrator is not SQL sysadmin
  4. Get the SQL exception under the wrapper
  5. Match the historian build to the engine
  6. The version decision, with the arithmetic
  7. Verify creation, then look downstream
  8. Questions asked

The wizard stops on the named instance because SQL Server rejected a server-level command it ran over a connection to that instance's master database, and the dialog shows only the wrapper: Creating Historian database failed, executenonquery failed for database 'master'. Fix it by reading the SQL exception underneath and by checking the server-role membership of the login the wizard presents to that instance. Two causes fit this pattern, in order: a login without sysadmin rights on the named instance — Windows administrator membership has not automatically granted sysadmin since SQL Server 2008 — and an engine version the historian build was never qualified against. Switching between Windows and SQL Server authentication changes neither.

What the wrapper message identifies #

ExecuteNonQuery is the command-execution call the wizard makes; master is the database the connection was bound to. Creating a database and its server objects is exactly the work that runs over a master connection, so the context itself is normal, and the message is not evidence that master is damaged. It tells you three things and no more:

  • The failure happened during historian database provisioning, before any tag collection or trending exists to break.
  • The failing command ran over a connection bound to master.
  • The rejection came from the database engine on the named instance.

It does not name the rejected statement, the SQL error number behind it, or the reason. Treat it as a provisioning failure scoped to one instance — not an authentication-mode problem, and not something a tag, screen or driver change can touch.

Why the default 2008 instance proves nothing #

A default instance and a named instance on the same machine are two separate database engines. Each keeps its own logins and server roles, its own service account, its own default data and log paths, and its own configuration. A run that completes on the default SQL Server 2008 instance validates that engine — it says nothing about SQLSERVER2014.

Observation What it establishes Next check
Creation completes on the default 2008 instance The wizard and its prerequisites work against one local engine Nothing; compare the target instance, not copied settings
Creation fails on the named instance The failure is instance-specific Retrieve the underlying SQL exception
Windows and SQL authentication fail identically Authentication mode is not the variable Effective permissions of the login actually used
Compatibility components installed, mixed mode on Prerequisite and reachability checks pass Neither grants provisioning rights

You have already varied the one input that does not matter. Fix one credential and work the two that do: the SQL exception and the login's server role.

Windows administrator is not SQL sysadmin #

Since SQL Server 2008, membership in the machine's local Administrators group is not automatically mapped to the sysadmin fixed server role. SQL Server carries server-wide permissions in its server-level roles, and which logins hold sysadmin is decided when each instance is set up, or granted afterwards by a login that already holds it. A named instance installed later can carry a different list from the default instance sitting beside it.

That makes "my Windows account is an administrator, so it counts as sysadmin" the first thing to test rather than assume. On the named instance, read the server-role membership of the login the wizard presents: your Windows account under Windows authentication, the SQL login under SQL authentication. It also explains your symptom — a Windows login missing the role and a low-privilege SQL login fail the same command the same way, which is why toggling modes changed nothing.

For database creation, the wizard's login needs sysadmin on the target instance; that is the role that can run server-level provisioning. If membership is missing, add the login from an account that already holds sysadmin on that instance — typically the account that installed it — then rerun the wizard.

Get the SQL exception under the wrapper #

  1. Note the clock time and rerun the wizard against the named instance until it fails.
  2. Open the historian setup output and keep the complete exception, inner exceptions included, not just the dialog line.
  3. Open that instance's SQL Server error log at the same timestamp. Not every statement error reaches it, so an empty interval clears nothing — but a matching entry carries the error number and text you need.
  4. Branch on what the underlying message says:
  • Permission denied creating the database — server-role membership of the wizard's login, as above.
  • Operating-system error while creating files — the engine's service account and the instance's data and log paths. The service writes the database files; the desktop session running the wizard does not.
  • Object already exists — remnants of an earlier failed attempt; drop them before rerunning.
  • Syntax or unsupported feature — the engine is ahead of what the historian build's scripts support.

Match the historian build to the engine #

SQLSERVER2014 is a connection label, not a version statement. Read the engine's version, edition and patch level from its own properties before reasoning about support.

Historian builds are qualified against specific SQL Server versions, and the lists are narrow. For an adjacent Citect Historian release, the documented qualification is SQL Server 2008 R2 and 2012, with SQL Server 2016 and 2017 explicitly unsupported. A newer historian release's matrix goes further and pins minimum builds: SQL 2016 SP3 build 13.0.6300.2 or higher, and SQL 2017 build 14.0.1000.169 or higher. Standing vendor guidance for these installs is to apply current cumulative updates and confirm the version pair in the supported-versions matrix for the exact build.

The decision: before spending more hours on the named instance, confirm your Citect Historian 2016 build's matrix includes that engine version and patch level. If it does not, no permission change makes creation succeed, and a forced result would be unsupported.

The version decision, with the arithmetic #

Separate the diagnostic from the hosting decision. The 2008 instance passing the wizard is a useful comparison; it is not a reason to keep plant history there.

Worked example. SQL Server 2008 left extended support in July 2019 and SQL Server 2014 left it in July 2024. The current support list holds SQL Server 2025 and 2022 in mainstream support, with 2019 and 2017 in extended support receiving security fixes only. Counting to 2026:

  • Default instance, SQL Server 2008: 2026 − 2019 = 7 years without security fixes.
  • Named instance, SQL Server 2014: 2026 − 2024 = 2 years without security fixes.

One safety note: an unpatched database engine holding plant history is an OT exposure, so treat the engine choice as a security decision, not only a compatibility one. The decision per case:

  • Permission gap on the named instance: grant sysadmin for the run, rerun, confirm clean creation — and still move the database, because 2 years unpatched fails the same test.
  • File-creation error: fix the service account's rights on the data path, drop remnants, rerun.
  • Engine not on the build's matrix: stop; stand the database up on a supported engine.

In every case the production answer is an engine that is both on the historian build's supported list and inside Microsoft support: 2017 at minimum for security fixes, practically 2019 or newer, patched to current cumulative updates.

Verify creation, then look downstream #

Verification is short: the wizard completes without the wrapper error, the historian database and its objects exist on the instance you targeted, and the historian service starts and stays running. Downstream failures are different problems. a Citect SCADA connector to the historian showing disconnected after creation is a connectivity fault, and an empty trend traced to PLC tag collection is a collection fault — work those only after the database exists.

Questions asked

Does this error mean the master database is corrupted?

No. The message says a command running over a connection bound to master failed; master is the normal connection context for creating databases and server objects. Corruption of master shows up as an engine that will not start, not as one rejected command. The rejected statement and its SQL error number live in the setup log and the SQL error log, not in the dialog.

My Windows account is a local administrator. Doesn't that make it sysadmin on the instance?

Not since SQL Server 2008. Membership in the Windows Administrators group is not automatically mapped to the sysadmin server role; each instance's setup decides which logins receive it, so a named instance installed later can have a different list. If no reachable login holds sysadmin there, start the instance in single-user maintenance mode: local administrators then connect as sysadmin, which is the documented recovery path.

Mixed mode and the backward compatibility components were already in place. Why didn't that fix it?

Because those are reachability and prerequisite checks, not grants of rights. Mixed mode only allows SQL logins to authenticate; it says nothing about what the login may do. The entry in Programs and Features proves the components are installed, not that the combination of historian build and engine version is supported. Neither item moves a rejected database-creation statement.

Which SQL Server version should host the historian database now?

Neither one on that machine. SQL Server 2008 left extended support in July 2019 and SQL Server 2014 in July 2024, so in 2026 they carry 7 and 2 years of unpatched exposure. Choose an engine that is both on the historian build's supported-versions matrix and still inside Microsoft support: 2017 at minimum (security fixes only), practically 2019 or newer, with current cumulative updates.

Keep reading

  • Troubleshooting

    PI-to-PI interface service will not start from FactoryTalk Historian

    When the PI-to-PI interface service will not start, the fault sits in the local launch, access to the two historians, or the FactoryTalk Historian license, which must permit native OSIsoft clients. Capture the exact startup error, work the three gates in fixed order, then prove the destination with values, timestamps and quality.

    Edge computing and IIoT

  • Comparison

    PLC to SQL Server: REST API or direct database access

    Choose a REST API in front of SQL Server when the data carries business rules, and an external collector that reads the controller when the database is only a repository. Keep SQL out of the PLC. The deciding test: classify each exchange as event, lookup or stream, and pick the path whose owner commits the transaction and acknowledges back to the controller.

    Edge computing and IIoT

  • Troubleshooting

    Reconnect an edge PC to SQL Server after connection loss

    Your reopen code fails because the loop still queries through the old connection reference and an incoming error can skip the open call entirely. Recover in a state machine with connect, query, cleanup and retry-wait states that replaces the stored reference: retry every 5 s to a server on the plant LAN, every 30 s across a VPN.

    Edge computing and IIoT