Granting Access to QEST Platform Databases
Applies to All
This article discusses options for granting the necessary access to the QEST Platform databases for the various products that make up the QEST Platform.
Contents
- 1 Overview
- 2 QESTLab Applications
- 2.1.1 Protected Location
- 2.1.2 Note
- 2.2 Required Access
- 2.3 Options for Granting Access
- 2.3.1 Options
- 2.4 Windows Authentication
- 2.4.1.1 Using a System DSN
- 2.4.1.1.1 SQL Authentication Connection String
- 2.4.1.2 Using a Connection String
- 2.4.1.2.1 SQL Authentication Connection String
- 2.4.1.1 Using a System DSN
- 2.4.2 Creating a System DSN
- 2.4.3 Granting Database Rights to an Active Directory Group
- 2.5 SQL Authentication
- 2.5.1 Password Structure
- 2.5.2 Defining the Connection
- 2.5.2.1 Using a File DSN
- 2.5.2.1.1 Creating a File DSN
- 2.5.2.1.2 Example File DSN
- 2.5.2.1.3 Configuring QESTLab to use a File DSN
- 2.5.2.1.4 config.ini for File DSN
- 2.5.2.1.4.1 Using an argument
- 2.5.2.2 Using a Connection String
- 2.5.2.2.1 SQL Authentication Connection String
- 2.5.2.1 Using a File DSN
- 2.5.3 Granting Database Rights to SQL User
- 3 QESTField
- 4 Construction Hive Publishing
- 4.1 Publishing Service
- 4.2 QESTLab
- 4.2.1 Example Connection Strings
- 4.2.1.1 Using a System DSN
- 4.2.1.1.1 Connection String
- 4.2.1.2 Using a File DSN
- 4.2.1.2.1 Connection String
- 4.2.1.3 Building a Connection String
- 4.2.1.3.1 Windows Authentication
- 4.2.1.3.2 Connection String
- 4.2.1.3.3 SQL Authentication
- 4.2.1.3.4 Connection String
- 4.2.1.1 Using a System DSN
- 4.2.1 Example Connection Strings
- 4.3 QESTNet
- 4.3.1 Manual Configuration
- 4.3.2 Protected Location
- 5 Granting db_owner Rights to a Database in SQL Server
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:
Decide whether access will be provided via Windows Authentication or SQL Authentication
Configure the application to connect to the database using the desired approach
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:
[QESTLab Data]
ConnectionString=<<ODBC Connection string>>
TemplateLocation=
Where <<ODBC Connection String>> is any valid connection string defining a connection to the database.
Protected Location
The default installation path is a protected location on Windows systems so only system administrators will be able to modify files in this location. In order to modify the QESTLab config.ini file it will be necessary to either
Open it in a text editor that is running as an administrative user, or
Copy it to an unprotected location, modify it there and then copy it back, which will require elevated rights
Note
If there exists more than one QESTLab application server in a given implementation of QEST Platform, the config.ini file for each machine that QESTLab runs on should be identical.
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:
Using Windows Authentication or
Using SQL Authentication.
Options
Only one approach is required and the decision will depend on internal business and IS/IT processes.
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
[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.
SQL Authentication Connection String
[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 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.
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+
Open the DSN Setup Tool:
On 32-bit systems, open Control Panel -> Administrative Tools -> Data Sources (ODBC)
On 64-bit environments run the following executable directly C:\Windows\SysWOW64\odbcad32.exe (ODBC)
Select the System DSN tab then click the Add button.
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.
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.
Select or enter the name of the SQL server or instance which has the QEST Platform database attached.
Leave the authentication selection as With Integrated Windows Authentication
Click Next
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).
Configure the following section based on how the SQL Server was installed and configured. The default options are generally fine. Then click Finish.
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.
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.
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.
Password Structure
If SQL Authentication is to be used with QESTLab the password for the SQL login must not contain the semicolon character at all (i.e. ";"). These are special characters in the format of connection strings and will not be parsed correctly. Other special characters are fine to use.
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:
The file DSN file must be created and placed in a location where the Windows users running QESTLab have read rights
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
[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.
config.ini for 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.
SQL Authentication Connection String
[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 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.
Protected Location
The default installation path is a protected location on Windows systems so only system administrators will be able to modify files in this location. In order to modify the custom.app.config file it will be necessary to either
Open it in a text editor that is running as an administrative user, or
Copy it to an unprotected location, modify it there and then copy it back, which will require elevated rights
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
<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.
QESTLab_Data Connection String for Windows Authentication
<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 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.
To Database | Defined in | Setting Name | Example Value for Windows Authentication | Example Value for SQL Authentication |
---|---|---|---|---|
QEST Platform | /config/interop.config | SourceConnectionString | Data Source=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Integrated Security=true; | Data Source=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Uid=<<SQL USERNAME>>;Pwd=<<SQL PASSWORD>>; |
Hive Publish | /config/interop.config | KeyMappingConnectionString | Data Source=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Integrated Security=true; | Data Source=<<SQL INSTANCE NAME>>;Database=<<DATABASE NAME>>;Uid=<<SQL USERNAME>>;Pwd=<<SQL PASSWORD>>; |
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:
Launch and log into the QEST Administrator Console
Navigate to Lab->Construction Hive->Options
Set the following options in this way
Option | Value |
---|---|
Publishing Database connection string | See below |
Suppress project parents | False |
Use key mapping database | True |
Use publishing database | True |
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
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.
Connection String
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
Connection String
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
Connection String
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. 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.
Protected Location
The default installation path is a protected location on Windows systems so only system administrators will be able to modify files in this location. In order to modify the custom.app.config file it will be necessary to either
Open it in a text editor that is running as an administrative user, or
Copy it to an unprotected location, modify it there and then copy it back, which will require elevated rights
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
<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.
HivePublish_Data Connection String for Windows Authentication
<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.
In Microsoft SQL Server Management Studio, open the desired SQL Server or instance in the Object Explorer
Expand the databases node, locate and expand the desired database
Expand the Security node under the desired database
Expand the Users node, if the SQL user already exists under this node, skip the addition of a new user
If a new user must be added, follow the steps below
Right click on the Users node and click New User
The next step depends on whether Windows Authentication or SQL Authentication is being used.
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
If using SQL Authentication, select SQL user with login under User Type and enter the SQL user name in the User Name field
Click on the Membership page and select db_owner role
Click OK and Close
If the desired user for already exists under the database security node, right click the user name and select Properties
Click on the Membership page and select db_owner role
Click OK and Close
Related content
Integrity | Curiosity | Empathy | Unity
The content of this page is confidential and for internal Spectra QEST use only. Do not share, duplicate or distribute without permission.