Symptoms

When you attempt to run one of the reports listed below from the adTempus Console, you receive the following error message:

Cannot connect to database

The problem occurs with the following reports:

  • Job Accounting Report
  • Job History Report

The problem may occur in the following scenarios:

  • The user running the report is not a member of the Administrators group; or
  • adTempus is using the default SQL Server Express database, and the Console from which you are attempting to run the report is a remote Console (that is, it is running on a different computer than the adTempus service).

Cause

The adTempus Console does not ordinarily communicate directly with the database (it communicates only with the adTempus service), so users using the Console do not need permission to access the adTempus database directly. However, these two reports do make a direct connection to the adTempus database, bypassing the Console and the service. As a result, the user running the reports must have read permission for the adTempus database.

In addition, by default, SQL Server Express is configured to allow only local connections. Therefore the connection will fail if the report is being run from a remote computer.

Resolution

To resolve this issue you must reconfigure SQL Server to allow remote connections and/or to allow access to the user attempting to run the report.

Allow Remote Access to SQL Server Express

Follow the instructions in article K00000387 to allow remote access to SQL Server Express.

The reports should now run correctly from the remote Console.

Grant Read Permission to Affected Users

The user who is running the Console must have read permissions for the adTempus database. This permission requirement is separate from any permissions that have already been configured within adTempus itself, and must be configured within SQL Server.

Follow these instructions to make the changes.

  1. Determine whom you will grant access to, based on your security policies and on who needs access to the reports. You may choose to grant access to only a single user, or  to a group of users. In the example below, we assume that a Windows domain security group called "adTempus users" has already been created, and we grant access to members of that group.
  2. Log in as an Administrator to the computer where the adTempus service is installed.
  3. Run the "adtdbutil" program found in the adTempus program directory (e.g., "c:\program files\arcana development\adTempus\adtdbutil.exe"). This utility will connect to the adTempus database and display an empty query-entry box.
  4. Enter the following text into the query box: 
CREATE LOGIN [MYDOMAIN\adTempus users] FROM WINDOWS; 
CREATE USER [MYDOMAIN\adTempus users] FOR LOGIN [MYDOMAIN\adTempus users]; 
EXEC sp_addrolemember N'db_datareader', N'MYDOMAIN\adTempus users';

Note: Replace "MYDOMAIN\adTempus users" with the user name or group (including domain name, if appropriate) that you want to grant access to.

  1. Press the F5 key to execute the command.
  2. The Messages panel at the bottom of the window should display the message "Command completed successfully".

The designated user(s) will now be able to run the reports.

Note: If you are using your own SQL Server instance to host the adTempus database, you or your database administrator can use SQL Server Management Studio to make the necessary changes, and any changes should be made in accordance with your organization's security policies.