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 into the installation path. The config.ini file has the following structure:

[QESTLab Data]
ConnectionString=<<ODBC Connection string>>
TemplateLocation=

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 for information on how to create a DSN.

[QESTLab Data]
ConnectionString=DSN=QESTLab
TemplateLocation=

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.

[QESTLab Data]
ConnectionString=Driver={SQL Server Native Client 11.0};Server=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Trusted_Connection=yes;
TemplateLocation=

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 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". The SQL Native Client driver appropriate to the SQL Server and Windows versions being utilized should be used. Please refer to Microsoft’s website to download the latest SQL Native Client driver for your SQL server and Windows versions if the appropriate option is unavailable during DSN setup. Note that as of early 2013 the SQL Server Native Client (SNAC) along with its ODBC functionality are called Microsoft ODBC Driver 11 for SQL Server by Microsoft on their website.

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.
   
3. The SQL Native Client driver should be used. Please refer to Microsoft’s website to download the latest SQL Native Client driver for your SQL server and Windows versions if this option is unavailable during DSN setup. Note that as of early 2013 SQL Server Native Client (SNAC) along with its ODBC functionality are called Microsoft ODBC Driver 11 for SQL Server.
   
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.
   
6. Leave the authentication selection as With Integrated Windows Authentication
   
   
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).
   
9. Configure the following section based on how the SQL Server was installed and configured. The default options are generally fine. Then click Finish.
   
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.
    
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:

[ODBC]
DRIVER=SQL Server Native Client 11.0
UID=<<SQL USERNAME>>
PWD=<<SQL PASSWORD>>
DATABASE=<<DATABASE NAME>>
WSID=<<WORKSTATION ID>>
APP=Microsoft® Windows® Operating System
SERVER=<<SQL INSTANCE NAME>>

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.

[QESTLab Data]
ConnectionString=FILEDSN=<<FULL PATH TO FILE>>
TemplateLocation=

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.

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.

[QESTLab Data]
ConnectionString=Driver={SQL Server Native Client 11.0};Server=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Uid=<<SQL USERNAME>>;Pwd=<<SQL PASSWORD>>;
TemplateLocation=

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 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 use of a SqlClient connection string in the QESTNet configuration file.

The QESTNet.exe.config file that resides alongside the QESTNet service executable file, usually in C:\Program Files\Spectra QEST\QESTNET v#.#, contains all of the configuration for the QESTNet service, including the database connection details. An overview of the connection options can be found in the QESTNet Configuration Guide article. Of interest here is the connection string with the name QESTLab_Data in the connectionStrings section of the file, detailed here. 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

<add
    name="QESTLab_Data"
    providerName="System.Data.SqlClient"
    connectionString="
        data source=<<SQL INSTANCE NAME>>;
        initial catalog=<<DATABASE NAME>>;
        integrated security=True;
        multipleactiveresultsets=True;
        App=QESTNET" />

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.

<add
    name="QESTLab_Data"
    providerName="System.Data.SqlClient"
    connectionString="
        data source=<<SQL INSTANCE NAME>>;
        initial catalog=<<DATABASE NAME>>;
        User ID=<<SQL USERNAME>>;
		Password=<<SQL PASSWORD>>;
        multipleactiveresultsets=True;
        App=QESTNET" />

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 subfolder 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

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).

DSN=Hive

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.

FILEDSN=<<FULL PATH TO FILE>>

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

Driver={SQL Server Native Client 11.0};Server=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Trusted_Connection=yes;

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

Driver={SQL Server Native Client 11.0};Server=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Uid=<<SQL USERNAME>>;Pwd=<<SQL PASSWORD>>;

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. Of interest here is the connection string with the name HivePublish_Data in the connectionStrings section of the QESTNet.exe.config file that is resident in the QESTNet installation directory.

Windows Authentication

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

<add
    name="HivePublish_Data"
    providerName="System.Data.SqlClient"
    connectionString="
        data source=<<SQL INSTANCE NAME>>;
        initial catalog=<<DATABASE NAME>>;
        integrated security=True;
        multipleactiveresultsets=True;
        App=QESTNET" />

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.

<add
    name="HivePublish_Data"
    providerName="System.Data.SqlClient"
    connectionString="
        data source=<<SQL INSTANCE NAME>>;
        initial catalog=<<DATABASE NAME>>;
        User ID=<<SQL USERNAME>>;
		Password=<<SQL PASSWORD>>;
        multipleactiveresultsets=True;
        App=QESTNET" />

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
      
   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
         
      2. If using SQL Authentication, select SQL user with login under User Type and enter the SQL user name in the User Name field
         
   3. Click on the Membership page and select db_owner role
      
   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
      
   2. Click OK and Close

Products described on these pages, including but not limited to QESTLab®, QESTNet, QESTField, Construction Hive, and associated products are Trademarks (™) of Spectra QEST Australia Pty Ltd and/or related companies.

The content of this page is confidential. Do not share, duplicate or distribute without permission.

© 2019 Spectra QEST® Australia Pty Ltd and/or related companies.  Terms of Use and Privacy Statement