Skip to main content

Installing and Configuring Data Mart Loader to Use an Existing SQL Server User

This article explains how to install and configure Data Mart Loader with existing SQL server in Agility.

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

By default, the data mart loader setup creates two Digital.ai Agility-specific SQL Server login during setup and gives them login access with adequate permissions to the Digital.ai Agility runtime database and the Digital.ai Agility data mart database. These logins are use by the data mart for run-time execution. When the data mart is being used with Analytics, the setup program also creates a Digital.ai Agility-specific SQL Server login for Analytics and gives that user login access with adequate permissions to 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 the data mart loader to use an existing SQL Server user that is created and configured externally by an administrator.

Run Data Mart Loader Setup from the Command Line​

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, the data mart loader setup package must be executed from the command-line, with the UseExistingUser option appended as an argument, as follows:

Setup-VersionOne.DatamartLoader.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).

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.

However, 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.

Create user.config File​

When installing with the UseExistingUser option, setup puts placeholder values in the connection strings in Digital.ai Agility.DatamartLoader.exe.config. To use a different connection string with valid values for connecting to the Digital.ai Agility database, you must override it via a user.config file.

You should not change the VersionOne.DatamartLoader.exe.config itself as it gets overwritten with every install. The user.config is preserved across upgrades.

To configure the data mart loader to use the existing user, you must place a user.config file in same directory as VersionOne.DatamartLoader.exe.config. This is typically C:\Program Files\VersionOne\instance name}.

Your user.config file needs to have the following format:

 

<appSettings>

<add key="EnterpriseConnectionString" value="" />

<add key="DatamartConnectionString" value="" />

</appSettings>

Or, if you are using Analytics, the data mart loader user.config needs to have the following format

<appSettings>

<add key="EnterpriseConnectionString" value="" />

<add key="DatamartConnectionString" value="" />

<add key="AnalyticsConnectionString" value=" " />

</appSettings>

The data necessary for each of the value attributes can be found in VersionOne.DatamartLoader.exe.config. You can open VersionOne.DatamartLoader.exe.config with any text editor (Notepad.exe) and copy the existing XML elements into your user.config. In your user.config file you need to specify the SQLServer User ID and password to be used when the DataMart Loader runs.

As mentioned above, subsequent setup invocations (e.g. upgrades) will preserve the user.config file.

SQL Server User Permissions​

As mentioned above, the data mart loader run-time needs access to your Digital.ai Agility database and your data mart database. If you are using Analytics, the data mart loader run-time needs access to the Analytics reports database. The role for each database is different:

Digital.ai Agility Database​

The SQL Server user specified in the user.config for the Digital.ai Agility Database must be a user on the Digital.ai Agility database and a member of the following database roles:

  • public
  • db_datareader

Data mart Database​

The SQL Server user specified in the user.config for the Data mart Database must be a user on the data mart database and a member of the following database roles:

  • db_owner

Analytics Database​

If installing data mart for use with Analytics, the SQL Server user specified in the user.config for the Analytics Database must be a user on the Analytics database and a member of the following database roles:

  • public
  • db_datareader
  • db_datawriter

* Unfortunately, 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 hosting Digital.ai Agility and Analytics, and Data Mart scheduled task - to run under the identity of selected AD accounts. It would be best to use the existing arrangement with predefined SQL server accounts.

Update _Settings.lgx (If Using Analytics)​

When using the data mart loader with Analytics there’s one additional configuration file that needs modification. This file is called _Settings.lgx and it is located in the _Definitions subdirectory (C:\Program Files\VersionOne\instance name\Definitions\ _Settings .lgx). This file can be opened with any text editor (Notepad.exe).

In this file, the setup process created a Connection element with an ID of ahMeta

<Connection Type="… " ID="ahMetadata" ConnectionString="…" Label="ahMetadata" ahDBType="SQLSERVER" />

On this element, locate the ConnectionString attribute and replace the generated User ID and Password values. Make these changes in _Settings.lgx. Once finished Save and Exit your editor.

When using the UseExistingUser option, subsequent setup invocations (e.g. upgrades) will preserve the ConnectionString attribute in _Settings.lgx.

When you are finished, make sure to run the data mart loader. Errors are reported in the error log dataloader-log.txt.