Skip to main content
System StatusContact Support
VersionOne Community

Installing and Configuring Analytics to use an Existing SQL Server User

This feature is available in Ultimate edition only.

editions-u.png

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 Analytics setup creates two VersionOne-specific SQL Server logins during setup and gives them login access with adequate permissions to the VersionOne 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 SQLServer 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 Analytics to use an existing SQL Server user that is created & configured externally by an administrator.

Analytics Setup

The Analtyics Installer for all currently supported releases prompts for this information.  Therefore the commanline 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 VersionOne 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 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.

Setup Permissions

When using the UseExistingUser option, the account used during setup needs fewer SQLServer 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 your Data Mart database and the Analytics metadata and reports database.  You may choose to use one set of credentials for both databases, you 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.

  • Was this article helpful?