Skip to main content
System StatusContact Support
VersionOne Community

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

The content in this article applies to On-premise VersionOne instances only. If you are an On-Demand (V1 Hosted) customer, see On-Demand (V1 Hosted) System Maintenance.

By default, the data mart loader setup creates two VersionOne-specific SQL Server login during setup and gives them login access with adequate permissions to the VersionOne runtime database and the VersionOne 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 VersionOne-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/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 SQLServer user that is created & 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 VersionOne run-time SQLServer 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 SQLServer rights, as it does not need to create a SQLServer login.

However, when switching an installation from a VersionOne-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 VersionOne.DatamartLoader.exe.config.  To use a different connection string with valid values for connecting to the VersionOne 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.

SQLServer User Permissions

As mentioned above, the data mart loader run-time needs access to your VersionOne 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:

VersionOne Database

The SQLServer user specified in the user.config for the VersionOne Database must be a user on the VersionOne database and a member of the following database roles:

  • public
  • db_datareader

Data mart Database

The SQLServer 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 SQLServer 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

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

  • Was this article helpful?