Skip to main content
Version: 25.0

Installing and Configuring Analytics to use an Existing SQL Server User

This artilce explains how to install and configure Analytics for an existing SQL server in Agility.

The content in this article applies to On-premise Digital.ai Agility instances only.

By default, the Analytics setup creates two Digital.ai Agility-specific SQL Server logins during setup and gives them login access with adequate permissions to the Digital.ai Agility data mart database and the Analytics reports database. In order to create these logins, the individual running setup (or SQL login specified during setup) must have SQL Server permissions that allow them to create that login.

In some customer environments, IT or security policy dictates that account used during setup may not have the rights to create databases or SQL Server logins. In these cases, the preference is to have a DBA manage all aspects of the server, including the creation of the databases and the run-time login that the data mart loader is configured to use. This article outlines how to install and configure Analytics to use an existing SQL Server user that is created & configured externally by an administrator.

It is not possible to set up "existing user" environment with AD accounts via Setup UI. This is called "trusted connection" in SQL Server terms, and requires processes using it - in this case IIS app pools for Analytics - to run under the identity of selected AD accounts.

Analytics Setup

The Analtyics Installer for all currently supported releases prompts for this information. Therefore the commandline option is no longer required.

With the Fall 2010 release (v10.3), there is a new command-line argument that instructs setup not to create a Digital.ai Agility run-time SQL Server login. To use this option, execute the Analytics setup package from the command-line, appending the UseExistingUser option as an argument, as follows:

Setup-VersionOne.Analytics.exe /UseExistingUser:True

Usage: /UseExistingUser={1/true} or {0/false} (default)

The setting is preserved, so subsequent setup invocations (e.g. upgrades) will automatically honor it - there's no need to specify it again unless you want to revert back to standard behavior. To do so, you must set it to false (or 0).

When switching an installation from a Digital.ai Agility-created login to a pre-existing one, Setup will still attempt to drop old managed login. If setup does not have the required privileges to drop the old login, it will issue a warning but overall installation is considered a success. The Administrator should manually remove the old account in this situation.

Setup Permissions

When using the UseExistingUser option, the account used during setup needs fewer SQL Server rights, as it does not need to create a SQL Server login.

Create the SQL Server User(s)

As mentioned above, Analytics needs access to r Data Mart database and the Analytics metadata and reports database. You may choose to use one set of credentials for both databases, you may choose to have separate credentials.

Data Mart Database

The run-time SQL Server login created by your administrator for the Data Mart database, must have the following database roles:

  • public
  • db_datareader

Analytics Database

The run-time SQL Server login created by your administrator for the Analytics database, must have the following database roles:

  • db_owner

Post-Setup Configuration

These steps are no longer necessary. The Analtyics Installer for all currently supported releases updates the configuration file correctly.

When installing with the UseExistingUser option, setup puts placeholder values in the following configuration files:

  • C:\inetpub\wwwroot\instance}\Definitions\Settings.lgx
  • C:\inetpub\wwwroot\instance}\AdHoc\Definitions\Settings.lgx

You need to change both of these files to use the User ID and Password created by the database administrator.

In the first file (C:\inetpub\wwwroot\instance}\Definitions\Settings.lgx) you need to modify two Connection XML elements:

<Connection ID="V1Datamart" … />

<Connection ID="V1Analytics" … />

On each of these elements you need to modify the SqlServerUser value and the SqlServerPassword value.

In the second file (C:\inetpub\wwwroot\instance}\AdHoc\Definitions\Settings.lgx) you need to modify three Connection XML elements:

<Connection ID="ahMetadata" … />

<Connection ID="1" … />

<Connection ID="ahRepository" … />

On each of these elements you need to modify the ConnectionString attribute and replace the generated User ID and Password values.

When using the UseExistingUser option, subsequent setup invocations (e.g. upgrades) will preserve the modifications.

Data Mart and ETL

System Maintenance

On-Demand (V1 Hosted) Maintenance