Preparing MS SQL Database (when not using PostgreSQL)

Prerequisites

Microsoft SQL Server is not installed by the Locator  installer. The following tasks must be completed before installing Locator :

• Microsoft SQL Server must be installed
• A database with the proper settings for Locator must be created
• An application service account with the proper access must be created

Microsoft SQL Server Installation

If you intend to use Locator with Microsoft SQL server, SQL Server 2019 is recommended as the most up to date option and the one we are currently testing against.
In theory, any version of Microsoft SQL server presently in Mainstream or Extended support ( https://learn.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16 ) should be compatible, as should any future versions that retain feature compatibility with SQL Server 2012. All editions are supported, including Express.

SQL Server can be installed on the same server as Locator  or on a different server. If SQL Server is on a different server, it is recommended that the connectivity between the Locator server and the SQL server have high throughput and low latency, else application performance will suffer.

Locator Database Creation

The Locator installer creates the objects (tables, views, stored procedures) in the Locator database, but it does not create the database itself. This must be done before running the installer.

In order for the application to work correctly, the database must be created with the correct settings. ayfie provides a script that creates the database with the required settings. It is strongly recommended that this script be used to create the database.

The script to create the database is in the LocatorCreateDatabase.zip file. This file contains an SQL script and a batch file to run the script. The script requires the SQLCMD tool to execute it.

The batch file takes two arguments:

1. The server name (including instance if used) on which to create the database. This is required.
2. The name of the database to create. This is optional and defaults to “Locator ” if not specified. 

The batch file assumes that the SQLCMD tool is in the path. If it is not, you can set the SQL_TOOLS environment variable to specify the location of SQLCMD (trailing backslash is required).

The SQL script can be modified before running it to specify additional options for the CREATE DATABASE command (e.g., to specify file locations). However, it is strongly recommended that none of the SET options be modified as this can cause the application to behave incorrectly. If a different collation is desired, a case-sensitive (CS) collation must be used.

The SQL script also creates a role named db_executor. This role grants execute access to all stored procedures in the database. This role is required for the application service user.

Application Service Account Creation

Locator uses integrated security to connect to SQL Server. This requires the use of a service account with the necessary privileges. The Locator application components require read/write access to all tables and views in the database, and execute access to all stored procedures in the database.

The recommended process for creating and configuring the service account is as follows:

1. Create a domain service account for the Locator  application.
2. Create a login for this account on the SQL Server instance that hosts the Locator database.
3. Grant the login access to the Locator database with the following roles: public, db_datareader, db_datawriter, db_executor.

The credentials for this service account will be entered in the Locator installer when configuring the SQL Server connection.

Installer Requirements

The Locator installer uses integrated security to connect to the database under the context of the user running the installer. This user needs to have a login to the SQL server hosting the Locator database and needs access to create and modify objects in the database as well as read and write data.

The necessary access is most easily granted by giving the user access to the Locator database and assigning the db_owner role. The minimum level of access necessary to run the installer can be granted by assigning the db_ddladmin, db_datareader, and db_datawriter roles.

Locator Custom Install with Microsoft SQL Server

Please complete the Prerequisites and review the Installer Requirements sections before starting the install. 

On the Install Options Page, choose "Custom Install" to continue to the Database Information page.

On the Database Information page, click the drop-down and select "Microsoft SQL Server 2012".

The Database Network Address refers to the SQL Server instance hosting the Locator  database. If the port or instance need to be specified, they must be included here.

The Name of Database field specifies the name of the database that has already been created. If it was created with a name different from the default of “Locator ”, enter that name here.

The Application Username, Application Password, and Application Domain refer to the service account that was described in the Prerequisites section.

Once all of the database information has been entered, click the "Verify Connection" button. This verifies that the installing user and the service account both have access to the database. If the connection is verified, the Next button is enabled. See the Troubleshooting section below for issues with "Verify Connection".

Troubleshooting

Invalid Application Service Account Credentials

This happens when the Application Username, Password, and Domain have values that do not match the Application Service User, or when they are equivalent to the installing user.  It is important to use a service account here as any change in the password will mean Locator will not appropriately function.

Install User Cannot Access the Database

This happens when the install user cannot access the database. The user who installs the Locator  product must have the access described in the Installer Requirements section in order to properly complete the install. Note:  this error can happen as a result of a network issue as well, if the database is remote from the Locator server.

Application Service Account Has No Access to the Database

This happens when the specified application service account does not have access to the database at all. Refer to the Prerequisites section.

Application Service Account has Inadequate Permissions to the Database

This error happens when the Application user can access the database, but does not have the necessary permissions. Refer to the Prerequisites section.

Custom Install Options

As of version 2.6, Locator  supports separating its components in the install.  This means a user can choose to not install some components.  Please note that this will require in-depth knowledge of the product, and will require that the components be available to use in a separate server.  Any case in which core components are deselected will need to be done with full knowledge that it may case the product to not work as intended.  Some components are required, and are not selectable.

This feature was added primarily to handle installing the connectors necessary for the environment in which Locator is installed.  Under the Standard Connectors tree is a list of all the fully customized connectors that Locator supports.  By default and with Typical Install, Exchange, File Server, and SharePoint Connectors are all installed with the product.  The user can now in the Custom Install Options page of the installer, add any of the other connectors to the install, which were traditionally provided via a separate install.

Under the Database/IDS Connectors are the connectors that run on a standard database with a lower set of customization needed for the connector to run.  Any of these can be included in the install as well.