Search

GlobalSCAPE Knowledge Base

Specifying an Alternate Reporting Connection String

Karla Marsh
EFT Express (SMB) & Enterprise

THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server v6.3.16.1 and later within the EFT Server 6.3.x line
  • EFT Server v6.4.1.1 and later within the EFT Server 6.4.x line (This registry setting does not apply to EFT Server 6.4.0.)
  • EFT Server v6.5.0 - v7.3.x

DISCUSSION

When running a report or editing a report using the report designer within the EFT Server administration interface, the system will use the default database configuration defined for the Server. (On the Server's Logs tab, in the Audit Database Settings area.) Typically the database user credentials defined in these settings will have a high level of privileges within the database, including the privilege to insert and modify data.

Since the database credentials must be available to the reporting functionality within the EFT Server administration interface, it may present a security risk by exposing credentials for the higher privileged account credentials. In this situation, you would instead use an alternate, lower-privileged database user account for use with the reporting functionality. This lower privileged account would have its privileges limited to only those absolutely necessary for using the reporting functionality, such as being able to select data from tables.

A registry setting is available to allow an alternate set of user credentials for use with reporting within the EFT Server administration interface. This registry setting allows administrators to specify an alternate database connection string that will be used by the reporting functionality. Since the connection string includes the credentials to use when accessing the database, it allows the specification of the alternate database user.

The registry setting should be set on the computer running EFT Server. The setting will be immediately available; restarting the EFT Server service is not necessary.

When the registry setting is present, the value is used as the connection string for the reporting functionality. When not set, the existing method of creating the connection string based on the configured "Audit Database Settings" for EFT Server is used.

Specifying an Alternate Reporting Connection String

To enable this functionality the following registry entry must be created and set appropriately:

32 bit:

HKEY_LOCAL_MACHINE\SOFTWARE\GlobalSCAPE Inc.\EFT Server 4.0 "ReportsConnectionString"="<Connection String>"

64 bit:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\GlobalSCAPE Inc.\EFT Server 4.0 "ReportsConnectionString"="<Connection String>"

The value is of the String data type. <Connection String> should be set to the desired database connection string. An easy way to construct the connection string is to base it on the default connection string created using the "Audit Database Settings" configuration.

To obtain the default connection string:

  1. Log in to EFT Server via the administration interface.
  2. Click the Report tab.
  3. In the left pane, expand the GlobalSCAPE Reports node and click any one of the default reports.
  4. In the right pane, click Edit Report. The Report Designer appears.
  5. In the left pane of the Report Designer, click the report name.
  6. Click the Design icon, then click the Data Source icon.
  7. The report's Data Source dialog box appears.

  8. The ConnectionString box contains the default connection string. Copy and paste that string into a text editor, then edit it for your environment and paste the edited string into the registry setting's Value box.
  • An example of the default connection string for connecting to a SQL Server database using SQL Server Authentication is:

provider=sqloledb;server=sqlserver-host;database=EFTDB;UID=eft_audit;Pwd= y99Z5vKU;

  • To specify an alternate user you would edit the UID and Pwd settings in the string to specify the alternate username and password, for example:

provider=sqloledb;server=sqlserver-host;database=EFTDB;UID=eft_report;Pwd= 54RqYm5m;

Creating an Alternate Database Account

When accessing the database, EFT Server does not prefix object names with the schema/database name. Instead, EFT Server relies on the database to resolve the location of the objects. In SQL Server this is done by setting the user's default schema. In Oracle this requires using the "ALTER SESSION SET CURRENT_SCHEMA" statement to place the user in the correct schema. To achieve this in Oracle, a trigger must be created such that when the new user account logs in, they will automatically be switched to use the correct schema.

  • Creating an Alternate Database Account for Reporting in SQL Server:

In SQL Server you would typically create an alternate Login and Database Account in the existing EFT Server database and then assign the "db_datareader" role to the account.

The attached "SQLServerReportUser.sql" script is provided as one possible way to create a reduced-privilege database user account that may be used for reporting.

  • Creating an Alternate Database Account for Reporting in Oracle

In Oracle you would typically create a database user and grant the necessary privileges on the existing EFT Server database schema objects.

The attached "OracleReportUser.sql" script is provided as one possible way to create a reduced-privilege database user account that may be used for reporting. The script will create the new account, create the necessary trigger to switch the user into the correct schema, and set the required privileges for the user.

Details
Last Modified: 3 Years Ago
Last Modified By: KBAdminSE
Type: HOWTO
Article not rated yet.
Article has been viewed 53K times.
Options
Also In This Category
Tags