Studio Database Gateway

Studio Database Gateway is a TCP/IP server that interacts with external databases using the Microsoft .Net Framework. You can run it on the same computer that hosts the project runtime, or you can install it on a different computer and then configure it to relay communications between multiple projects and databases.

Studio Database Gateway connects to AVEVA Historian to save Trend history. It is a Windows tray application, not a Windows service. It accepts the TCP port number and enableTLS:<0|1> as command-line arguments.

The Host setting in your project's database configuration specifies the computer that hosts the gateway. If it is hosted on the same computer that hosts the project runtime, enter either localhost or 127.0.0.1. You do not need to worry about starting or stopping the gateway because that will be done automatically by the project runtime.

If the gateway is hosted on another computer, enter the host name or IP address of that computer. You need to manually install the Studio Database Gateway software on that computer, and you will need to manually run the gateway before you run your project.

The gateway can be started multiple times for different TCP/IP port numbers. The default port number is 3997, and it is changed by specifying the desired port number in the command prompt (for example, StADOSvr 1111).

While the gateway is running, its icon (StDB GTW) is displayed in the Windows notification area. To access the gateway, double-click the icon in the notification area (or system tray).

You can also right-click the icon, and then click Hide on the shortcut menu to deselect it. The Hide option controls whether or not the Studio Database Gateway window is displayed on the desktop. (The gateway runs continuously, regardless of whether the window is displayed.) The Studio Database Gateway window looks like this:

Any failure that occurs during operations with databases will be displayed both in this window and also in the Studio LogWin window. The messages are reported by exceptions generated by the ADO.NET Provider. (Please refer to Database Troubleshooting for more information about error messages in the gateway module.)

To save the Gateway log to a file, open StADOSvr.ini in the Gateway installation folder and add:

[Log]

TraceToFile=1

LogFilePath=C:\Logs\StADOSvr.log

Log menu

You can configure the output in the Studio Database Gateway window by using the Log menu:

• Show Log menu option: Shows the Database Gateway log files.

• Options menu option: Open the Configure Messages dialog.

  

  • Show Messages pane: Select Errors Only to show only error messages in the log, or select All Messages to show all database messages.

  • Additional Information pane: Configure to show additional information about each database message.

    • Message Type checkbox: Click (check) this option to show the type of the message.

    • Date/Time checkbox: Click (check) this option to show the timestamp of the message.

Show Log is off by default and is stored per Windows user.

The log window keeps the last 1000 messages. Log > Options sets the level (default: information), date and type.

Monitor menu

Also, you can directly monitor database connections using the Monitor menu:

• Connections menu option: Displays the Database Connections window.

  

• Log to File menu option: Logs the monitor output to a file named logdate.txt in the same folder as StADOSvr.exe.

Window menu

The Window menu has two standard Windows options:

1. Selecting Hide will close the Window, though the Studio Database Gateway will still be running, and it is accessible through its icon in the notification area (or system tray). Minimizing the Studio Database Gateway window will have the same effect. This is also the same Hide option available when right-clicking the icon.

2. Selecting Always on top will make the Studio Database Gateway window appear "in front of" other Windows (visible over them), even if one of those other Windows has the focus.

Data Protection (File menu)

Enable Data Protection in the Gateway with the same password as the project. If the passwords are different, the Gateway cannot connect.

Note: If more than one project runtime server will be using the same Studio Database Gateway to manage database connections, all of the projects should have the same Data Protection password.

To enable Data Protection in the Studio Database Gateway:

3. In the Studio Database Gateway window, on the File menu, click Data Protection. The Data Protection dialog box is displayed.

   

4. Select the Enable check box. The Password and Confirm Password boxes become active.

5. In the Password box, type your password, and then in the Confirm Password box, type it again. Be sure to type the same password that you used in your project.

6. Click OK.

For more information about Data Protection, see Enable Data Protection to encrypt sensitive information.

TCP/IP Secure Channel (File menu)

This is a security feature that requires you to run the Studio Database Gateway as an administrator.

TCP/IP Secure Channel protects the connection between the project runtime and Studio Database Gateway. It does not secure the separate connection between Studio Database Gateway and AVEVA Historian. The certificates that the Secure Channel script creates are used only between the project runtime and the gateway. The connection between the gateway and AVEVA Historian uses a separate certificate that the Historian server provides. For that certificate, see Trust the AVEVA Historian server certificate.

TCP/IP Secure Channel is one part of a three-part procedure that allows you to use the Secure Channel mTLS communication protocol with your TCP/IP connection. This is part of a three-part process, and all three parts must be correctly configured. To configure the other two parts, see:

• Secure Channel Communication

• In the client application, enabling (checking) the Enable Secure Channel feature in any or all of the following database configuration dialogs:

  • Historian dialog for connecting to a Historian database located on-premises

  • In the Advanced Database Configuration dialog for the Trend worksheet, Trend Control object, and the Grid object.

  • In the Database Connection (Advanced) dialog for the Database/ERP worksheet.

This feature is disabled by default. You can enable by following these steps:You can select (enable) or deselect (disable) this feature by clicking the TCP/IP Secure Channel option in the File menu. This feature is disabled by default.

7. Select File > TCP/IP Secure Channel:

   

8. A Studio Database Gateway dialog opens with the message "You must restart the Studio Database Gateway for the changes to take effect." Click OK, then close and restart the Studio Database Gateway, running it as an administrator.

   

9. If you check the File menu, you should see that the TCP/IP Secure Channel selection is now checked, showing that it is enabled:

   

10. Deselecting TCP/IP Secure Channel and repeating this process will disable this feature. If the TCP/IP Secure Channel menu item is unavailable, the current Windows account cannot write to StADOSvr.ini. Start the gateway as an administrator.

AVEVA Historian Integrated Security (File menu)

When this option is selected, Studio Database Gateway connects to AVEVA Historian using the Windows account that runs the gateway. Only Trend worksheets configured with Use Gateway Credentials can connect through that gateway.

When this option is cleared, which is the default, only Trend worksheets configured with Use Specific User Name and Password can connect.

The setting applies to all projects that use the gateway. It is stored as WWIntegratedSecurity in the [Historian] section of StADOSvr.ini, where 1 means selected and 0 means cleared. A change applies to connections opened after the change. Existing connections continue until they are closed.

If the menu item is unavailable, the current Windows account cannot write to StADOSvr.ini. Start the gateway as an administrator, or edit the file with an account that has permission.

For more information, see Configure Historian authentication.

Advanced settings

The Studio Database Gateway has Advanced Settings that are configured in the StADOSvr.ini file. If you are having problems interfacing with a specific database, you will probably need to change some of these settings or add new providers to the file. The following settings are available:

The parameters are grouped into several sections — Providers, Communication, Connection, Options, Historian, Monitor, Diagnostics, Log, and Performance — but all of the settings for configuring database providers are listed in the Providers section of the file. The default values are specified in the beginning of the file, using the prefix "Default" in each setting as shown below:

[Providers]

DefaultSaveMSec=3

DefaultAssembly=System.Data

DefaultConnectionClass=System.Data.OleDb.OleDbConnection

DefaultDataAdapterClass=System.Data.OleDb.OleDbDataAdapter

DefaultCommandBuilderClass=System.Data.OleDb.OleDbCommandBuilder

DefaultValueString=@Value

DefaultValueAddNumber=1 DefaultBoolType=INTEGER

DefaultIntegerType=INTEGER DefaultRealType=REAL

DefaultStringType=VARCHAR(255) DefaultTimeStampType=DATETIME

DefaultSingleConnection=0

The next item on the file lists the amount of providers:

Count=5

The providers are identified by the "Provider" setting followed by a number. When connecting to a database, the Provider setting in the connection string is compared to the provider's identification, in order to determine which provider will be used. If there is no provider with the value on the connection string, all the default values are assumed. Besides its identification, each provider can have its own value per each setting. Again, if no value is specified, the default is used. Below is an example with seven providers:

Count=7

Provider1=MICROSOFT.JET.OLEDB

SaveMSec1=3

ColumnDelimiterPrefix1=[

ColumnDelimiterSuffix1=]

SingleConnection1=1

Provider2=SQLOLEDB

ConnectionClass2=System.Data.SqlClient.SqlConnection

DataAdapterClass2=System.Data.SqlClient.SqlDataAdapter

CommandBuilderClass2=System.Data.SqlClient.SqlCommandBuilder

ColumnDelimiterPrefix2=[

ColumnDelimiterSuffix2=]

TableDelimiterPrefix2=[

TableDelimiterSuffix2=]

RealType2=FLOAT

Provider3=MSDASQL

ConnectionClass3=System.Data.Odbc.OdbcConnection

DataAdapterClass3=System.Data.Odbc.OdbcDataAdapter

CommandBuilderClass3=System.Data.Odbc.OdbcCommandBuilder

ValueString3=?

ValueAddNumber3=0

StringType3=VARCHAR(128)

EnableTop3=0

Provider4=ORAOLEDB

Assembly4=System.Data.OracleClient

ConnectionClass4=System.Data.OracleClient.OracleConnection

DataAdapterClass4=System.Data.OracleClient.OracleDataAdapter

CommandBuilderClass4=System.Data.OracleClient.OracleCommandBuilder

ValueString4=Value

ValueAddNumber4=1

ValueStringPrefix4=:

BoolType4=Number(1)

IntegerType4=Number(10)

RealType4=Number

StringType4=VARCHAR(255)

TimeStampType4=TIMESTAMP(0)

EnableTop4=0

Provider5=ASAPROV

Assembly5=iAnywhere.Data.AsaClient

ConnectionClass5=iAnywhere.Data.AsaClient.AsaConnection

DataAdapterClass5=iAnywhere.Data.AsaClient.AsaDataAdapter

CommandBuilderClass5=iAnywhere.Data.AsaClient.AsaCommandBuilder

ValueString5=?

ValueAddNumber5=0

ColumnDelimiterPrefix5=[

ColumnDelimiterSuffix5=]

TableDelimiterPrefix5=[

TableDelimiterSuffix5=]

Provider6=MYSQLPROV

Assembly6=ByteFX.MySqlClient

ConnectionClass6=ByteFX.Data.MySqlClient.MySqlConnection

DataAdapterClass6=ByteFX.Data.MySqlClient.MySqlDataAdapter

CommandBuilderClass6=ByteFX.Data.MySqlClient.MySqlCommandBuilder

ValueString6=@Value

ValueAddNumber6=1

StringType6=VARCHAR(128)

EnableTop6=0

Provider7=MSDAORA

Assembly7=System.Data.OracleClient

ConnectionClass7=System.Data.OracleClient.OracleConnection

DataAdapterClass7=System.Data.OracleClient.OracleDataAdapter

CommandBuilderClass7=System.Data.OracleClient.OracleCommandBuilder ValueString7=Value

ValueAddNumber7=1

ValueStringPrefix7=:

BoolType7=Number(1)

IntegerType7=Number(10)

RealType7=Number

StringType7=VARCHAR(255)

TimeStampType7=TIMESTAMP(0)

EnableTop7=0

Watchdog for localhost connections

When the AVEVA Edge project and the Studio Database Gateway are running on the same machine using the localhost (127.0.0.1) connection and both processes were started by the same user or account, you can set an optional watchdog timer to restart the Studio Database Gateway after a number of communication failures.

To enable this localhost watchdog feature:

11. Open the project APP file located at <project folder>\<project name>.APP.

12. Locate, or create at the bottom of the file, a section named [Preferences].

13. Create a setting named DatabaseFailuresBeforeRestart, followed by an equal sign (=), and then a number of communication failures after which the Studio Database Gateway will be stopped and restarted. The default setting is 0, which means that this feature is disabled.

    The result will look like this:

    [Preferences]

    DatabaseFailuresBeforeRestart=3