Using the SQL Query Engine with Security Enabled

When GENESIS Security is enabled, queries made through the SQL Query Engine will need to authenticate with a username and password. To authenticate, every SQL Query Engine stored procedure accepts an optional GENESIS Security user name and password through the @username and @password parameters. When you supply the credentials of a GENESIS Security user that is permitted to read the historical data, the SQL Query Engine performs the query as that user and applies every security setting configured for that account.

The @username and @password parameters are optional. When GENESIS Security is disabled, you can omit them. When GENESIS Security is enabled, they are required: a query that does not supply valid credentials fails with a Bad - User Access Denied error.

Before querying the SQL Query Engine with GENESIS Security enabled, verify the following:

  • The SQL Query Engine is installed, and its stored procedures are registered in SQL Server.

  • A GENESIS Security user account exists and is granted read access to the historical points you intend to query.

To query the SQL Query Engine with GENESIS Security enabled:

  1. Identify a GENESIS Security user account that has read access to the historical data you intend to query, and note its user name and password.

  2. In SQL Server Management Studio (SSMS), open a new query window and ensure that the correct database is selected.

  3. Call the stored procedure you require, supplying the account's user name and password as the @username and @password parameters. For example, to list the available points:

    EXEC QE_GetAllPointNames @username = N'myuser', @password = N'mysecret'

    For a procedure that takes other arguments, add @username and @password alongside them. When you use named parameters:

    EXEC QE_GetHistoryRawModified @startTimestamp = '2026-01-01 10:00:00', @endTimestamp = '2026-01-01 10:05:00', @username = N'myuser', @password = N'mysecret', @pointName = N'\Signals:Sine'

  4. Execute the query. If the result set returns rows, the SQL Query Engine is successfully accessing Data Historian data as the specified user.

Avoid embedding credentials in plain text in shared or saved SQL scripts, where any user with access could read them. Instead, use a secure credential mechanism such as those provided for SQL Server linked server connections. Learn More

Applying FrameWorX Server Location Changes

The SQL Query Engine reads its FrameWorX client configuration from its own directory, C:\ProgramData\ICONICS\11\Query\FwxClient.config.json, which is separate from the configuration the rest of the system uses in C:\ProgramData\ICONICS\11\Local\FwxClient.config.json. Because of this, the SQL Query Engine does not automatically pick up changes you make in the FrameWorX Server Location dialog, such as those required for a secure installation.

Whenever you change settings in the FrameWorX Server Location dialog, copy the updated configuration into the SQL Query Engine's directory so that the change is applied:

To apply FrameWorX Server Location changes to the SQL Query Engine:

  1. In Workbench, make the changes you require in the FrameWorX Server Location dialog (File menu > FrameWorX Server Location, or the Tools ribbon > FrameWorX Server Location).

  2. Copy C:\ProgramData\ICONICS\11\Local\FwxClient.config.json over C:\ProgramData\ICONICS\11\Query\FwxClient.config.json, replacing the existing file.