Contents

Overview

Each application in the QEST Platform will require access to the QEST Platform database. Additionally, if Construction Hive publishing is used, access to the Hive Publishing databases may also be necessary. In each case the following steps apply:

1. Decide whether access will be provided via Windows Authentication or SQL Authentication

2. Configure the application to connect to the database using the desired approach

3. Ensure that the Active Directory group or SQL user has sufficient rights on the relevant database in SQL Server

QESTLab Applications

This section applies to the desktop applications in the QESTLab family as they all use the same method for connecting to the QEST Platform database. This includes QESTLab, the QEST Administrator Console, QEST Weigh Station, QEST Crush Station and QL1379. The connection is defined in the config.ini file that is installed alongside QESTLab in the installation path. The config.ini file has the following structure:

Where <<ODBC Connection String>> is any valid connection string defining a connection to the database. 

Required Access

QESTLab functionality requires that the application has rights to read and write data in the database as well as the ability to execute stored procedures. System administrator functionality will additionally necessitate rights to make dbo database schema changes. Therefore granting the application the database read (db_datareader) and write (db_datawriter) permissions alone, will not be sufficient and the simplest approach is to grant db_owner permissions to the relevant groups and users.

Options for Granting Access

There are a two options for granting the necessary access:

1. Using Windows Authentication or

2. Using SQL Authentication.

The simplest approach is to use Windows Authentication, configure a System DSN on the machine(s) that QESTLab runs on and ensure that the config.ini file is pointing to the DSN.

Note that rights will need to be granted to the appropriate user or group in SQL Server as well. 

Windows Authentication

If it is desired that Windows authentication be used for users running QESTLab, an Active Directory user group needs be created for this purpose. Database permissions can then be assigned to that group.If desired, system administrators and regular users can be split into different groups to separate out the rights to make schema changes. 

Once this is completed, users can be added to the relevant groups rather than needing to set user rights for each individual user.

If Windows authentication is used then access can be granted via a system DSN or an appropriate connection string, both approaches are described below but only one is required.

Using a System DSN

When QESTLab is first installed, it is assumed that a System DSN named QESTLab will be configured on the same machine that QESTLab is running on in order to provide access to the database. If a system DSN is used there is usually no need to modify QESTLab's config.ini file provided that the DSN is named QESTLab. The file will have the structure shown below, instructing QESTLab to use a system DSN named QESTLab. Refer to the following section about how to create a DSN.

SQL Authentication Connection String

Using a Connection String

An alternative way to define QESTLab's connection to the QEST Platform database using Windows authentication is to create an appropriate ODBC connection string and place this into the config.ini file. The structure of an appropriate connection string for Windows authentication, as part of the config.ini file, is shown below.

SQL Authentication Connection String

Where:

• {SQL Server Native Client 11.0} should be replaced with the appropriate driver name for the application server in question

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab

Creating a System DSN

A System DSN is a simple way of defining database connection parameters. A System DSN can be created through the ODBC Data Source which can be found in the Administrative Tools section of the Control Panel. The 32-bit version of the Data Sources (ODBC) shortcut should be used. In a 64-bit environment, the appropriate DSN setup tool is found in: [Windows installation directory]\SysWOW64\odbcad32.exe (ODBC) where [Windows installation directory] is usually C:\Windows.

The latest Microsoft ODBC Driver for SQL Server appropriate for the SQL Server and Windows versions being utilized should be used - please refer to Microsoft’s website for the ODBC Driver for SQL Server Version Compatibility Matrix.

Open

The DSN needs to set the "Default Database" to your QESTLab database (usually called "QESTLab"). For simplicity, it is recommended that this DSN is also named "QESTLab".

DSNs must be created for all application servers running QESTLab. All DSN names must be the same. By default, QESTLab assumes that the DSN will be named QESTLab. This allows the entries to be easily recognized when installing the application.

The following steps are to be used for setting up DSNs for Windows 7+ and Windows server 2008R2+

1. Open the DSN Setup Tool:

   1. On 32-bit systems, open Control Panel -> Administrative Tools -> Data Sources (ODBC)

   2. On 64-bit environments run the following executable directly C:\Windows\SysWOW64\odbcad32.exe (ODBC)

2. Select the System DSN tab then click the Add button.

   Open

   

    

3. The latest Microsoft ODBC Driver for SQL Server appropriate for the SQL Server and Windows versions being utilized should be used - please refer to Microsoft’s website for the ODBC Driver for SQL Server Version Compatibility Matrix, and download the appropriate version from Microsoft if it is not already available.

   Open

   

    

4. Type in a name for the DSN, Spectra QEST recommends using QESTLab for this name as this is the default name configured when QESTLab is installed. 

5. Select or enter the name of the SQL server or instance which has the QEST Platform database attached.

   Open

   

    

6. Leave the authentication selection as With Integrated Windows Authentication

   Open

   

    

7. Click Next

8. Select the tick box “Change the default database to:” and select the QEST Platform database in the list. Then click Next. (Note that you will need to have the QEST Platform database attached in SQL Server before this can be done, please refer to Restoring a QEST Platform Database Backup for more details).

   Open

   

    

9. Configure the following section based on how the SQL Server was installed and configured. The default options are generally fine. Then click Finish.

   Open

   

    

10. Select “Test Data Source” to test the connection. The output will differ based on SQL Instance version and edition but it is important that the following message appears: TESTS COMPLETED SUCCESSFULLY! Then click OK.

    Open

    

     

    Open

    

     

11. If an error occurs please check the user privileges on the SQL Server, the DSN configuration and the Domain. Click OK twice and the DSN will be created.

12. For verification, check the list of System Data Sources to confirm the DSN is created and visible.

Granting Database Rights to an Active Directory Group

Once the connection has been configured for QESTLab, access must still be granted to the database for the relevant Active Directory group in SQL Server itself. Refer to the appropriate section below for more details on this process.

SQL Authentication

If Windows authentication is not being used to connect the QESTLab application to the database, SQL authentication may be used. Otherwise, this section can be skipped.

This section describes how to configure QESTLab to use SQL authentication.

Defining the Connection

Two approaches to defining the connection using Windows authentication are described in this section, only one is necessary. 

Using a File DSN

A file DSN is a simple text file that defines a connection to a database in a structured manner. In order to use a File DSN, the following steps must be completed:

1. The file DSN file must be created and placed in a location where the Windows users running QESTLab have read rights

2. QESTLab's config.ini file must be modified to point to the appropriate file DSN

Creating a File DSN

To create a file DSN simply create a text file with the extension of .dsn in a location where users running QESTLab have read rights.

The contents of the file should look like this:

Example File DSN

Where:

• SQL Server Native Client 11.0 should be replaced with the appropriate driver name for the application server in question

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

• <<WORKSTATION ID>> should be replaced by the name of the computer on which the file DSN is located

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

Configuring QESTLab to use a File DSN

Once the file DSN has been created, QESTLab's config.ini file will need to be configured to point to it. The simplest way to do this is to modify the config.ini file using the following structure to point to a file DSN.

config.ini for File DSN

Where:

• <<FULL PATH TO FILE>>  should be replaced with the full path to the file DSN (e.g. C:\DSN\QESTLab.dsn)

Using an argument

An alternative method of configuring QESTLab to use a file DSN is to use the /FILEDSN argument when launching the application and providing the full path immediately following this argument. This can be done when launching the appropriate application via the command line or configured as part of the application shortcut. When editing a shortcut, the flag can be placed after the executable name in the Target field along with the full path to the DSN.

Open

Using a Connection String

It is also possible to create a connection string appropriate for SQL authentication and place this into the config.ini file that is installed alongside QESTLab. The structure of an appropriate connection string when using SQL authentication, as part of the config.ini file, is shown below.

SQL Authentication Connection String

Where:

• {SQL Server Native Client 11.0} should be replaced with the appropriate driver name for the application server in question

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

Granting Database Rights to SQL User

Once the connection has been defined on the QESTLab application servers, rights must still be granted to the database for the relevant SQL user SQL Server itself. Refer to the appropriate section below for more details on this process.

QESTField

QESTField Tests, Forms and Scheduler as well as the QEST Web App all communicate with the QESTNet service in order to perform work on the QEST Platform database. As a result, only the QESTNet service requires access to the database itself. QESTNet is configured to connect to the QEST Platform database through the QEST Web App. Details of configuring the database connection are provided in Initial Setup and Database Connections.

Manual Configuration

If manual configuration of the database connection is required, for example to correct incorrect details, the custom.app.config configuration file can be manually edited.

The custom.app.config file that resides in the /config subdirectory of the QESTNet installation directory, usually in C:\Program Files\Spectra QEST\QESTNET contains the configuration for the QESTNet service that can vary from instance to instance, including the database connection details.  Of interest here is the connection string with the name QESTLab_Data in the connectionStrings section of the file. Presented below are two common configurations for the QEST Platform database connection string, one using Windows Authentication for the service account running the QESTNet service, and the other using SQL authentication.

Only one of these approaches is required and the simplest is to use Windows Authentication for the service account that runs the QESTNet service.

Windows Authentication

In order to use Windows authentication, the QESTLab_Data connection string should be configured in this manner.

QESTLab_Data Connection String for Windows Authentication

Where:

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab

Grant Service Account db_owner Rights

At this point, the service account running the QESTNet service should be granted db_owner rights on the QEST Platform database using SQL server. Refer to the appropriate section below for more details on this process.

SQL Authentication

In order to use Windows authentication, the QESTLab_Data connection string should be configured in this manner.

QESTLab_Data Connection String for Windows Authentication

Where:

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

Grant SQL User db_owner Rights

At this point, the SQL User being used should be granted db_owner rights on the QEST Platform database using SQL server. Refer to the appropriate section below for more details on this process.

Construction Hive Publishing

Organisations that publish to Construction Hive will have a second database resident on their SQL Instances, usually named Hive_Publish. The products that publish to Construction Hive must be able to connect to this database (an in some instances the QEST Platform database).

Publishing Service

The Construction Hive publishing service itself requires access to the QEST Platform database as well as the Hive Publishing database. The definition of these connections is stored in the configuration files installed alongside the Construction Hive Publishing Service, in the /config subdirectory of the installation path. Both connections are defined as SqlClient connection strings, examples for each connection using Windows authentication and SQL authentication are given below.

Where:

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually QESTLab/Hive_Publish depending on the connection

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

Whichever approach is used, the appropriate domain (service) or SQL user will need to be granted db_owner rights on both databases. Refer to the appropriate section below for more details on this process.

QESTLab

In order to allow publishing from QESTLab, the application will need access to the Hive publishing database. This is configured by setting a valid ODBC connection string via the QEST Administrator Console along with setting a number of other options. To set these options appropriately:

1. Launch and log into the QEST Administrator Console

2. Navigate to Lab->Construction Hive->Options 

3. Set the following options in this way

Open

Example Connection Strings

The connection strings for the Hive Publishing database connection from QESTLab will take the same structure as those that define the connection to the QEST Platform database in the config.ini file. Some examples are shown below.

Using a System DSN

This connection string assumes that a system DSN named Hive exists on the same machine as QESTLab is running on and that the user running QESTLab has been granted db_owner rights on the Hive_Publish database using an Active directory group (Windows authentication).

Connection String

Using a File DSN

This connection string assumes that a file DSN exists in a location that is able to be read by users running QESTLab and has been defined to either use Windows Authentication or SQL authentication.

Connection String

Where:

• <<FULL PATH TO FILE>>  should be replaced with the full path to the file DSN (e.g. C:\DSN\QESTLab.dsn)

Building a Connection String

It's also possible to build a connection string directly. Two examples are presented here, one assuming Windows authentication is being used and the other assuming SQL authentication.

Windows Authentication

Connection String

Where:

• {SQL Server Native Client 11.0} should be replaced with the appropriate driver name for the application server in question

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually Hive_Publish

SQL Authentication

Connection String

Where:

• {SQL Server Native Client 11.0} should be replaced with the appropriate driver name for the application server in question

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually Hive_Publish

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

QESTNet

If QESTField Forms is used, then the QESTNet service will require access to the Hive Publishing database. This is configured in the Database Connections page of the QEST Web App by defining the  HivePublish_Data database connection.

Manual Configuration

If manual configuration of the database connection is required, for example to correct incorrect details, the custom.app.config configuration file can be manually edited.

The custom.app.config file that resides in the /config subdirectory of the QESTNet installation directory, usually in C:\Program Files\Spectra QEST\QESTNET contains the configuration for the QESTNet service that can vary from instance to instance, including the database connection details. Of interest here is the HivePublish_Data connection string.

Windows Authentication

In order to use Windows authentication, the HivePublish_Data connection string should be configured in this manner.

QESTLab_Data Connection String for Windows Authentication

Where:

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually Hive_Publish

Grant Service Account db_owner Rights

At this point, the service account running the QESTNet service should be granted db_owner rights on the Hive Publish database using SQL server. Refer to the appropriate section below for more details on this process.

SQL Authentication

In order to use Windows authentication, the HivePublish_Data connection string should be configured in this manner.

HivePublish_Data Connection String for Windows Authentication

Where:

• <<SQL INSTANCE NAME>> should be replaced with the name of the SQL Server or instance

• <<DATABASE NAME>> should be replaced with the name of the database, usually Hive_Publish

• <<SQL USERNAME>> should be replaced with the SQL user name

• <<SQL PASSWORD>> should be replaced with the password for the above SQL user

Grant SQL User db_owner Rights

At this point, the SQL User being used should be granted db_owner rights on the Hive Publish database using SQL server. Refer to the appropriate section below for more details on this process.

Granting db_owner Rights to a Database in SQL Server

This section describes how to grant db_owner rights to the QEST Platform database in SQL server. 

1. In Microsoft SQL Server Management Studio, open the desired SQL Server or instance in the Object Explorer

2. Expand the databases node, locate and expand the desired database 

3. Expand the Security node under the desired database

4. Expand the Users node, if the SQL user already exists under this node, skip the addition of a new user

5. If a new user must be added, follow the steps below

   1. Right click on the Users node and click New User

      Open

      

   2. The next step depends on whether Windows Authentication or SQL Authentication is being used. 

      1. If using Windows Authentication, select Windows User under User Type and enter the domain and name of the Active Directory group in the User Name field

         Open

         

      2. If using SQL Authentication, select SQL user with login under User Type and enter the SQL user name in the User Name field

         Open

         

   3. Click on the Membership page and select db_owner role

      Open

      

   4. Click OK and Close

6. If the desired user for already exists under the database security node, right click the user name and select Properties

   1. Click on the Membership page and select db_owner role

      Open

      

   2. Click OK and Close