Sql Connections Configuration

The required ADO.NET provider must be installed on the computer used for configuration purpose, and also on runtime computers belonging to the list of Servers of the Sql connection (see Advanced properties below).

How to create and configure a Sql Connection

  1. Open the Application Explorer and expand the configuration tree to select the Data Connections folder.
  2. From the task list, select Add a Sql connection. The Sql Connection creation dialog opens. ClosedShow picture

  3. Either accept the default or enter the Sql connection Name. The name is used as the Id of the connection throughout the application.
  4. Enter the optional Description. The description appears in the Application Explorer whenever the Data Connections folder is selected.
  5. Select the Data Source from either:
    1. Microsoft SQL Server - Creates a connection using the SQL Server ADO.NET provider.
    2. Oracle Database - Creates a connection using the Oracle Data Provider for .NET (ODP.NET).
    3. Built-in providers - Creates a connection using one of PcVue's built-in provider.
    4. Other providers - Creates a connection using any other third-party ADO.NET provider.
  6. Select the Data Provider. The drop down list is automatically populated with entries appropriate to the selected data source unless you have selected Other in which case all data providers available on the local computer are listed.
  7. Click Apply.

At this point, the Connection String, used when establishing the connection, is partially completed but you must then configure the properties in Data Source specific tab before the configuration is complete.

Configuring a SQL Server connection

  1. Select the Authentication Mode. You may need to ask your System Administrator for this information.
    • Windows authentication. The credentials used to connect to the DBMS will be those of the running SV DbConnect service. A User name and Password are not required.
    • SQL server authentication. You must enter a valid SQL Server User name and Password.
    • Active directory password authentication. For connecting to a Microsoft Azure database. You must also enter a User name and Password that are valid on your Azure Active Directory.
    • Active directory integrated authentication. The credentials used to connect to the DBMS will be those of the running SV DbConnect service. This authentication mode can only be used if your local Active Directory is extended to your Azure Active Directory. A User name and Password are not required.
  2. Select the SQL Server tab. This tab is only visible if you selected SQL Server as the Data Source. ClosedShow picture

  3. Select or enter the server name. A dialog displaying the list of discovered SQL server instances, both on the local computer and on the network, can be displayed using the Ellipsis button.
  4. Select the Authentication Mode. You may need to ask your System Administrator for this information.
    • Windows authentication. The credentials used to connect to the DBMS will be those of the running SV DbConnect service. A User name and Password are not required.
    • SQL server authentication. You must enter a valid SQL Server User name and Password.
    • Active directory password authentication. For connecting to a Microsoft Azure database. You must also enter a User name and Password that are valid on your Azure Active Directory.
    • Active directory integrated authentication. The credentials used to connect to the DBMS will be those of the running SV DbConnect service. This authentication mode can only be used if your local Active Directory is extended to your Azure Active Directory. A User name and Password are not required.
  5. Select or enter the database name. A dialog displaying a list of discovered databases, from the selected server instance, can be displayed using the Ellipsis button
  6. Click OK to confirm the configuration and close the dialog.

The Server name is the SQL Server instance name, it can take several forms depending on:

  • Where the SQL Server instance is located

  • SQL Server instance settings

  • System level settings

For example it can be:

  • (local) or .\ or . (dot) to connect to the default local SQL Server instance or .\MySQLServerInstanceName for a named SQL Server instance

  • The server IP address such as 192.168.x.y or 192.168.x.y\MySQLServerInstanceName for named instances

  • The server hostname such as MySQLServerHostname

  • The fully qualified instance name such as MySQLServerHostName\MySQLServerInstanceName

  • MyAzureDomain.database.windows.net for Microsoft Azure database and Microsoft Azure data warehouse instances

When using Windows authentication, a User name and Password are not required.

Pay attention to the fact that the credentials used to connect to the DBMS will be those of the running SV DbConnect service.

Configuring an Oracle connection

  1. Select the Oracle tab. This tab is only visible if you selected Oracle as the Data Source. ClosedShow picture

  2. Select either TNS (Transparent Network Substrate) or EZ Connect (Easy connect) for the connection type.
    1. If using TNS an Oracle configuration file, tnsnames.ora, provides the connection parameters. The .Net Service Name drop down list box is populated using the contents of this file and the appropriate entry must be selected.
    2. If using EZ Connect you must manually enter the Server name (or IP address), Port number and Database service name.
  3. Select the Authentication Mode from one of the following. You may need to ask your System Administrator for this information.
    • Windows authentication. The credentials used to connect to the DBMS will be those of the running SV DbConnect service. A User name and Password are not required.
    • SQL authentication. You must also enter a valid User name and Password.
  4. Click OK to confirm the configuration and close the dialog.

Configuring a built-in provider

If you select the Data Source as Built-in providers, you must configure the connection string in the Built-in tab. Use the ellipsis button to discover the properties supported by the selected provider.

Please refer to the documentation specific to the selected provider for more information.

Configuring other third-party providers

If you select the Data Source as Other providers, you can click the ellipsis button to discover the properties supported by the selected provider or manually enter the connection string in the Other tab.

By using a Data Source of type Other providers, you can take advantage of any third party ADO.NET provider. See the next paragraph for more information on how to manually set connection properties without the need to manually edit the connection string.

The behavior and level of support for such third-party ADO.NET providers may vary depending on the provider you decide to use. In particular, not all ADO.NET providers implement the necessary features for accessing the list of advanced properties. Refer to the product documentation of the third-party ADO.NET provider for more information.

Manually editing advanced properties of a connection string

Much of the configuration that takes place in the Data Connection dialog is used to generate a connection string that is then used by PcVue when connecting to the database. Advanced users can edit the connection string directly in the Sql connection dialog from the server specific tab. The Ellipsis button adjacent to the Connection String field displays a list of available properties as exposed by the selected ADO.NET provider.
As an example, here is the list of properties for SQL Server ClosedShow picture or Oracle ClosedShow picture

Some providers hide security related fields, including the user name and password. Then, they cannot be edited from the connection string.

Testing the connection

Once it has been configured, the connection can be tested using either the Test Connection button in the Sql Connection dialog or the Test Connection tool. PcVue will attempt to connect to the server using the configured Connection String. A dialog is displayed showing success or, should the connection attempt fail, error information.

The Test Connection tool allows you to test the connection end-to-end:

  • If the station from which you run the test is not in the list of Producers, the connection is established through the multi-station and therefore the connection will fail if none of the Producer stations is running.

  • If the connection string does not include the database, the test will consist of a connection to the DBMS instance only. It does not guarantee that all of the hosted databases will be accessible.

  • If the connection string does include the database, the test will consist of a connection to the database itself. It guarantees that the configured database is accessible, but it does not guarantee that all of the tables (and other database items) will be accessible.

The Advanced Properties tab

  • General
    • Timeouts
      • Command time-out - The wait time before terminating a command, sent by PcVue using DbConnect, and generating an error. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely). The default is 1 minute.
  • Servers - Server station list producing the Sql connection (optional). It must be a list of type Historical server. Used in multi-station applications. Those stations in the Server Station List will handle the interface to the DBMS and all other stations will delegate requests to those stations in the Server Station List. If the list contains an association, it must be of type Single active server.

The Traces tab

The Traces tab allows you to enable diagnostic trace messages. Each trace generates specific messages that are logged in the Trace Files and displayed in the Event Viewer dialog (F7). The following screen shot was taken with the default configuration. ClosedShow picture

  • General - Not used.
  • Configuration - Information associated with configuration. Useful when trying to diagnose configuration loading issues or if a problem is caused by the configuration or run-time of the SV DbConnect service.
  • Sql request - Shows all the requests sent to the SV DbConnect service. The trace includes the Id of the request, the actual Sql request text, if the request was successful or failed, and the processing time.