6 Using the Administrative Console to Configure the Reporting Client

Using the Administrative Console, you can configure the Reporting client to generate reports for every type of event that might occur in the course of regular business operation. Using Oracle Business Intelligence (BI) Publisher, you can output reports, with a selection of a variety of formats to suit your needs. This section describes the steps to install and configure the Reporting client and database, and to leverage BI Publisher to create reports for enterprise single sign-on events.

This section covers the following:

• Installing the Administrative Console and Reporting Client

• Installing the Reporting Extension

• Setting Up the Reporting Service as a Domain User

• Oracle Database Configuration Overview

• Setting Up the Oracle Database for Reporting

• Microsoft SQL Server Configuration Overview

• Using Oracle Business Intelligence Publisher for Deployment with Reporting

For complete instructions to install and configure Oracle Enterprise Single Sign-On Suite, refer to the Oracle Enterprise Single Sign-On Suite Installation Guide.

Also see the Reference section of this guide for Chapter 7, "Reporting Event Definition Table,".

6.1 Installing the Administrative Console and Reporting Client

If you have not already done so, download and install the Administrative Console and Logon Manager Agent. The Reporting Extension is installed as an option during the Agent installation and configured within the Administrative Console.

6.2 Installing the Reporting Extension

You install the Reporting extension on Client workstations during the Logon Manager Agent installation. After starting the InstallShield Wizard:

1. On the Setup Type panel, select a Custom Setup.

2. On the Custom Setup panel, expand Audit Logging Methods.

3. Select Reporting Server and set it to install.

4. Follow the on-screen instructions to complete the installation.

   
   Description of the illustration rep_logging_methods.png
   
   

Note:

Refer to the Oracle Enterprise Single Sign-On Suite Installation Guide for specific instructions.

6.2.1 Configuring Reporting Settings

To configure Logon Manager to begin capturing events, you must adjust some settings in the Administrative Console.

1. Open the Administrative Console by pointing to Start > Programs > Oracle > ESSO-LM Administrative Console.

2. Select a set of Global Agent Settings (or right-click Global Agent Settings, point to Import, click From Live HKLM, expand Live).

3. Expand Audit Logging and select Reporting Server. Configure the following settings:

   
   Description of the illustration reporting_database.png
   
   

   • Connection string: Enter the database connection string in OLE DB format. The machine must be within the domain. For example:

     Provider=SQLOLEDB.1;Integrated Security=SSPI;PersistSecurity Info=False;Initial Catalog=Database_Name;Data Source=database_server
     

   • Stored procedure: The name of the stored procedure in the database. When encoded events are sent to the database, the stored procedure is called to decode the XML file and store the events in the database. Set this to dbo.sp_WriteEvents.

   • Batch size: Defines the group size of events to be sent to the database Stored Procedure at one time. For example, if you have 1000 events in the Reporting Service cache and the Batch Size is 100, you will have 10 database Stored Procedure calls. (Default is 100.)

   • Cache limit: Maximum number of reporting events to be cached. Once this number is reached, the oldest events are discarded. For example, if the batch size is 100, and an end-user's system cannot connect to the reporting service, it will keep logging events. Once it gets to 1000, the oldest events will be discarded. (Default is 1000.)

   • Retry interval: Defines timeout in minutes between sequential operations of the Reporting Service Cache offloading events to the database. An interval is necessary to reduce database connection load. (Default is 30.) Retry Interval should be set to 0 when Reporting is used.

4. Export the settings to the Logon Manager Agent. For information on exporting Global Agent Settings, refer to the Logon Manager documentation.

6.3 Setting Up the Reporting Service as a Domain User

You can make the Reporting Server a domain user by assigning it the same privileges as the Domain Computers group. This eliminates having the Domain Computers group connect to the database and, when using an Oracle Database, also eliminates the need to specify a clear text username and password.

Note:

It is strongly recommended that you select Password never expires for user accounts that are used to log on as a service.

6.3.1 Overview of the Process to Set Up Reporting as a Domain User

This process consists of the following steps:

1. Creating the domain user account:

   • For Oracle, create the account that connects to the Oracle Database.

     or

   • For Microsoft SQL, create a domain user with the same rights to the SQL database as you would give the Domain Computers group.

2. Granting the Reporting Service domain user the "Log on as a service" privilege.

3. Running the Reporting Service as the domain user account you created. (For instance, run the sc config command on all workstations.)

For detailed instructions to set up your database for Windows integrated authentication, refer to the following sections:

• Setting Up Oracle Database to Use Reporting with Windows Integrated Authentication

• Setting Up Microsoft SQL Server to Use Reporting with Windows Integrated Authentication

Next Steps

After you configure Logon Manager to capture events and store them in the database, do one of the following:

• For Oracle Database, continue to Oracle Database Configuration Overview.

• For SQL databases, continue to Microsoft SQL Server Configuration Overview.

6.4 Oracle Database Configuration Overview

The following is a brief overview of the procedures that you must follow in order to successfully configure the Oracle Database to work with Reporting.

• Creating the Oracle Database User

• Creating the Database Table and Setting Up Stored Procedures

• Creating a Connection String

• Configuring Oracle Database on Client Machines

• Setting Up Oracle Database to Use Reporting with Windows Integrated Authentication

• Next Steps

6.4.1 Creating the Oracle Database User

You must create one user and grant the appropriate privileges to the account. This user will be the SSO Database table owner.

Launch the database in which you want to create the user and enter the following command in the SQL*Plus tool:

SQL> CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE user_tablespace TEMPORARY TABLESPACE temp_tablespace;

where user_tablespace is the default tablespace identified by the database administrator to store user objects, and temp_tablespace is identified to store temporary objects.

For example:

1. Start SQL*Plus (the Oracle SQL command line tool), and enter the following commands to log in:

   $ sqlplus
   

2. Press Enter.

3. Enter user-name:

   username/password@dbname
   

   where username is an existing administrative user in the database. For example, system/password will log the administrative user system with a password of password into the default database.

4. Create the user, grant these two default roles and their corresponding default privileges to the user that you created, and log out of the SQL command line tool:

   SQL> CREATE USER orauser IDENTIFIED BY oracle DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
   SQL> GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY, CREATE PROCEDURE TO orauser;
   SQL> EXIT
   

6.4.2 Creating the Database Table and Setting Up Stored Procedures

After you create the Oracle Database User, run the provided script, Oracle_Setup.sql, to:

• Upgrade an existing or create a new database table.

• Upgrade existing or set up new stored procedures.

The script might require some modification with respect to the location of the StoredProcedures.java file, which is initially set to D:\orcl_scripts. If you plan to use a different location, refer to the script's comment header for the exact line number where you can make this change.

When you have updated the StoredProcedures.java location (if necessary), you are ready to execute the following script using SQL*Plus (the Oracle SQL command line tool) to accomplish the remaining tasks:

$ sqlplus username/user_password < path_to_file\Oracle_Setup.sql

where username is orauser, user_password is oracle, and path_to_file is the path to the SQL script file.

For example:

$ sqlplus orauser/oracle < Oracle_Setup.sql

or:

C:\>sqlplus orauser/oracle < C:\oracle_setup\Oracle_Setup.sql

6.4.3 Creating a Connection String

In this section, you will create a connection string that will be used in the following section, Configuring the Oracle Database on Client Machines.

1. Open Windows Explorer and navigate to the folder in which you want to store the .udl file.

2. From the File menu, click New and then click Text Document. A new file named New Text Document.txt appears in the directory.

3. Rename this file, removing all spaces and changing the file extension to .udl, for Universal Data Link.

4. Double-click the Universal Data Link (.udl) file. The Data Link Properties dialog opens.

   
   Description of the illustration rep_oradb7.png
   
   

5. On the Provider tab, select Oracle Provider for OLE DB and then click Next.

   
   Description of the illustration rep_oradb8.png
   
   

6. On the Connection tab, enter the Data Source, User name, and Password. Check the Allow saving password check box and click Test Connection. For example: Data Source: ORCL, User name: orauser, Password: oracle.

   
   Description of the illustration rep_oradb9.png
   
   

7. A message will appear indicating that the test connection succeeded. Click OK.

8. Click OK to save the connection string to the Universal Data Link (.udl) file.

9. Open the .udl file in Notepad. In the next section of this guide you will be instructed to enter a connection string. Copy and paste this string and enter it into the Connection string field.

   
   Description of the illustration rep_oradb10.png
   
   
   Description of the illustration rep_oradb5.png
   
   

   The full connection string above reads as follows:

   
   Description of the illustration rep_connctn_string.png
   
   

6.4.4 Configuring Oracle Database on Client Machines

Note:

If you are installing Logon Manager on a 64-bit system and plan to configure the Reporting Service to store event data in an Oracle Database, you must install the 32-bit version of the Oracle Database client on the target end-user machine; otherwise, the Reporting Service will not be able to connect to the Oracle Database.

1. Install either Oracle Client with Oracle Provider for OLE DB or Oracle Data Access Components for Oracle Client.

   
   Description of the illustration rep_oradb1.png
   
   

   or

   
   Description of the illustration rep_oradb2.png
   
   

2. Next, configure the Oracle Client for connection to the Oracle Server. Edit or create the following file:

   <Oracle_Home\client_1\Network\Admin\tnsnames.ora:
   "# tnsnames.ora: Network Configuration File:
   C:\oracle\product\11.1.0\client_1\NETWORK\ADMIN\tnsnames.ora
   # Generated by Oracle configuration tools.
   
   ORCL =
   (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host_server_name)(PORT = 1521))
    )
    (CONNECT_DATA =(SERVICE_NAME = ORCL)))
   

   
   Description of the illustration rep_oradb3.png
   
   

3. Launch the Administrative Console by clicking Start > Programs > Oracle > Administrative Console.

4. In the tree, right-click Global Agent Settings, click Import, then click From live HKLM.

5. Under Global Agent Settings > Live, expand Audit Logging > Reporting Server.

6. Copy and paste the string you created in the previous section, Creating a Connection String. For example:

   "Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=user_name;Data Source=ORCL"
   

7. Set the Stored Procedure setting to:

   "SP_WRITEEVENTS"
   

   
   Description of the illustration rep_oradb5_crop.png
   
   

8. Right-click Live and click Write to Live HKLM.

9. Close the Administrative Console.

6.4.5 Setting Up Oracle Database to Use Reporting with Windows Integrated Authentication

To use Windows integrated authentication with Reporting, the ESSO Reporting Service must run as a domain user with permissions to write to the Reporting database (either Microsoft SQL Server or Oracle). To run the service as domain user on a workstation, the user must have "Log on as Service" permissions.

You can modify this setting (as detailed below) on your domain controller so that the setting is published to all client computers.

6.4.5.1 Creating an Active Directory domain user that will write events to the database

Create a user in Active Directory (henceforth referred to as the "Reporting Domain User"). You will grant this user permissions to write Reporting events to the database.

6.4.5.2 Modifying the Default domain policy to allow the Reporting Domain User to Log on as a service

Modify the Default domain policy on your domain controller so that all client computers connected to the domain have this setting defined.

Note:

Oracle recommends that you create a backup of the current Group Policy before editing the default domain policy. To create a backup, in the console tree, open Domains/Current Domain Name/Group Policy objects. Right-click Default Domain Policy, and select Back Up from the context menu.

The Group Policy is domain-wide and overwrites the local policy. If you need to configure any local accounts to log on as a service, refer to the documentation for the Group Policy Management Console for this procedure.

1. On your domain controller, click Start, click Run, enter gpmc.msc, and then click OK.

2. In the console tree, open Domains > Current Domain Name > Group Policy Objects. Right-click Default Domain Policy, and select Edit from the context menu.

   
   Description of the illustration rep_int_auth_with_sql1.png
   
   

3. In the Group Policy Management Editor's console tree, go to Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

4. In the details pane, double-click Log on as a service.

   
   Description of the illustration rep_int_auth_with_sql2.png
   
   

5. Verify that the Define this policy setting check box is selected, and click Add User or Group. Enter the new Reporting Domain User in the User and group names field.

6. Click OK when finished.

   
   Description of the illustration rep_int_auth_with_sql3.png
   
   

   To apply the Group Policy change immediately:

   • Restart the domain controller.

     or

   • Open a command prompt, and type: gpupdate /force. Then press Enter.

6.4.5.3 Verifying Publication of the Active Directory Permission on the Client Machine

Note:

Also see Microsoft's technical note about modifying permissions on an Active Directory domain or local computer to allow a domain user to log on as a service.

1. Ensure that the client is updated by opening a command prompt and entering the gpupdate /force command. For a discussion of this procedure, see the Microsoft Technical Library article at http://technet.microsoft.com/en-us/library/cc778890%28v=ws.10%29.aspx

2. Go to Administrator Tools > Local Security Policy.

3. In Local Security Policy go to Security Settings > Local Policies > User Rights Assignment (as shown in the following figure).

4. In the Detail Panel check for the updated "Log on as a service" policy. It should include the Reporting Domain User among the users who have this permission.

   
   Description of the illustration rep_int_auth_with_sql4.png
   
   

6.4.5.4 Configuring the ESSO Reporting Service on the Client Machine to run as this domain user

Perform these steps on all client computers where the ESSO Reporting Service is running.

To configure the ESSO Reporting Service to run under the Reporting Domain User account:

1. Open a command prompt and enter the following command:

   sc config "SSO Reporting Service" obj= "Domain\User" password= "password"
   

2. Press Enter.

   This command should return the following output:

   [SC] ChangeServiceConfig SUCCESS
   

3. Restart the ESSO Reporting Service:

   1. Open a command prompt and enter the following command:

      net stop "SSO Reporting Service" && net start "SSO Reporting Service"
      

   2. Press Enter.

      This command should return the following output:

      The ESSO Reporting Service service is stopping.
      The ESSO Reporting Service service was stopped successfully.
      The ESSO Reporting Service service is starting.
      The ESSO Reporting Service service was started successfully.
      

   Note:

   You can achieve the same results through the user interface by accessing the "Services" console on any client computer.

6.4.6 Setting Up the Server for Integrated Authentication

Perform the following tasks to set up the server for integrated authentication.

6.4.6.1 Verify the Windows Authentication Protocol

Ensure that the SQLNET.AUTHENTICATION_SERVICES entry in the sqlnet.ora file reads NTS. This setting must be modified on both the client and database server. You must edit or create the sqlnet.ora file. The file is located at:

ORACLE_BASE\ORACLE_HOME\network\admin\sqlnet.ora

Note:

Refer to the Oracle Database Platform Guide on Windows Authentication Protocols, User Authentication and more information.

6.4.6.2 Create the External Oracle User for the Domain User

You must create the new "Reporting Domain User" as "identified externally" on the Oracle Database and grant appropriate privileges to the account.

Note:

Refer to the Oracle Database Platform Guide for Manually Creating an External Operating System User, External user Authentication Task on the Oracle Database Server, External User Authentication Task on the Client Computer and more information.

Set OSAUTH_PREFIX_DOMAIN to true in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, to enable authentication at domain level, where ID is the Oracle home directory you want to edit. For more information, refer to the External User Authentication task on the Oracle Database Server in the Oracle Database Platform Guide.

1. Grant Administrative privileges to the New "Reporting Domain User" (for example, domainname\username) on the workstation where the Oracle Database is installed or will be installed.

2. Log on to this workstation as the New Report Domain User.

3. Launch SQLPLUS and log on as SYSDBA at the prompt.

4. Create the "Reporting Domain User" identified externally (refer to the SQL syntax below). Grant required privileges to the user that you created, and log out of the SQL command line tool.

   Create the User with the following syntax:

   SQL> CREATE USER username IDENTIFIED EXTERNALLY DEFAULT TABLESPACE user_tablespace TEMPORARY TABLESPACE temp_tablespace;
   

   Where user_tablespace is the default tablespace identified by the database administrator to store user objects, and temp_tablespace is the location to store temporary objects.

   The username would take the form OPS$DOMAINNAME/USERNAME, where:

   • OPS$ is the value of OS_AUTHENT_PREFIX set for your database

   • DOMAINNAME is the name of the domain

     and

   • USERNAME is the Reporting User with whose permissions the Reporting service would be running.

Example

The following examples were created using Oracle Database 11g.

SQL> CREATE USER "OPS$ESSOIDCQA\REPORTUSER1" IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY, CREATE PROCEDURE TO "OPS$ESSOIDCQA\REPORTUSER1";
Grant succeeded.
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\reportuser1>

In the above example OPS$ is the OS_AUTHENT_PREFIX prefix value and REPORTUSER1 is the username defined on the ESSOIDCQA domain.

Grant the User CONNECT, RESOURCE, CREATE ANY DIRECTORY and CREATE PROCEDURE permissions.

The Windows Domain user ESSOIDCQA\REPORTUSER1 (that is, the Reporting Domain User) will now be able to log on to Oracle Database if this domain user is logged on to the machine and if the Windows Authentication Protocol has been set.

Perform the following steps to verify these conditions:

1. Make sure you log on to the system with the new user (here, ESSOIDCQA\REPORTUSER1).

2. Make sure you have Windows Authentication Protocol set correctly. That is, the SQLNET.AUTHENTICATION_SERVICES entry in the sqlnet.ora file reads NTS.

3. Open a command prompt and enter sqlplus /@ORCL, where ORCL is the net_service_name defined in the tnsnames.ora file on the system.

   If configured properly, sqlplus will log the user on without prompting for a username and password. Once logged on, enter SHOW USER at the sqlplus prompt. This displays the current logged-on user.

Example

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\reportuser1>SQLPLUS/@ORCL
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 11 07:43:12 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW USER
USER is "OPS$ESSOIDCQA\REPORTUSER1"
SQL>

Note:

You can verify the value of OS_AUTHENT_PREFIX set on your database with the SHOW PARAMETER OS_AUTHENT_PREFIX command as shown in the following example. By default it is set to OPS$. You can modify this value to any string you wish. For more information, refer to the Oracle Database Platform Guide.

C:\Users\reportuser1>SQLPLUS /NOLOG
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 10 10:33:49 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
SQL> SHOW PARAMETER OS_AUTHENT_PREFIX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
os_authent_prefix string OPS$
SQL>

6.5 Setting Up the Oracle Database for Reporting

For an existing installation of the Reporting database set up on your Oracle Database system, follow the instructions in the section, Upgrading an Existing Oracle Database Setup. For a new database installation, follow the instructions in the section, Setting Up a New Oracle Database for the ESSO Reporting Service.

6.5.1 Upgrading an Existing Oracle Database Setup

Perform the following steps to run version 11.1.2 of the Reporting Service with integrated authentication under the Reporting Domain User account.

• Upgrade the Database Tables Schema.

• Grant appropriate permissions to the new Reporting Domain User account so that it can access the required Oracle objects.

• Create Public SYNONYM for SP_WRITEEVENTS.

6.5.1.1 Upgrading an Existing Oracle Database Setup

Run the provided script, Oracle_Setup.sql as the SSO Database table owner.

The initial location of the StoredProcedures.java file is D:\orcl_scripts. If you plan to use a different location, refer to the script's comment header for the exact line number where you can make this change.

After updating the StoredProcedures.java location (if necessary), execute the following script using SQL*Plus (the Oracle SQL command line tool) to accomplish the remaining tasks:

$ sqlplus username/user_password < path_to_file\Oracle_Setup.sql

where username is the existing SSO Database table owner, user_password is that user's password, and path_to_file is the path to the SQL script file.

Example

$ sqlplus orauser/oracle < Oracle_Setup.sql

or:

C:\>sqlplus orauser/oracle < C:\oracle_setup\Oracle_Setup.sql

6.5.1.2 Providing the Required Permissions to the New Reporting Domain User

Allow the new Reporting Domain User Execute permission on SP_WRITEEVENTS.

1. Log in to SQL*Plus with the existing orauser account and password. The orauser is the SSO Database table owner.

2. Grant the Execute permission on SP_WRITEEVENTS to the new Reporting Domain User, OPS$DOMAINNAME\USERNAME, with the following command:

   GRANT EXECUTE ON SP_WRITEEVENTS TO "username";
   

   The username will be in the format, OPS$DOMAINNAME\USERNAME, where OPS$ is the value of OS_AUTHENT_PREFIX for your database, DOMAINNAME is the name of the domain, and USERNAME is the Reporting User with whose permissions the Reporting service would be running.

Example

C:\Users\reportuser1>SQLPLUS orauser/oracle@ORCL
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 14 11:03:45 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW USER
USER is "ORAUSER"
SQL> GRANT EXECUTE ON SP_WRITEEVENTS TO "OPS$ESSOIDCQA\REPORTUSER1";
Grant succeeded.
SQL> QUIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

6.5.1.3 Creating a Public Synonym for SP_WRITEEVENTS

To create a public synonym, log on to SQL*Plus as SYSDBA and create a public synonym for SP_WRITEEVENTS using the following syntax:

CREATE OR REPLACE PUBLIC SYNONYM SP_WRITEEVENTS FOR USERNAME.SP_WRITEEVENTS;

Where USERNAME is the SSO Database table owner and the account whose permissions you used during your initial setup of Oracle Database for Reporting.

Example

C:\Users\reportuser1>SQLPLUS /NOLOG
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 14 11:15:35 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
SQL> CREATE OR REPLACE PUBLIC SYNONYM SP_WRITEEVENTS FOR ORAUSER.SP_WRITEEVENTS;
Synonym created.
SQL> QUIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Note:

For more information on Creating Synonyms, refer to the Oracle Database SQL Language Reference.

6.5.2 Setting Up a New Oracle Database for the ESSO Reporting Service

After you create the Reporting Domain User Identified Externally on the Oracle Database, run the provided script, Oracle_Setup.sql to:

• Create a new Database table.

• Set up required functions, and so forth.

Run Oracle_Setup.sql with integrated authentication of the new Reporting Domain User. After you run this script, this user becomes the ESSO Database table owner.

The initial location of the StoredProcedures.java file is D:\orcl_scripts. If you plan to use a different location, refer to the script's comment header for the exact line number where you can make this change.

After updating the StoredProcedures.java location (if necessary), execute the following script using SQL*Plus (the Oracle SQL command line tool) to accomplish the remaining tasks:

sqlplus / < path_to_file\Oracle_Setup.sql

where path_to_file is the path to the SQL script file.

Note:

Make sure you log on to the system as the Reporting Domain User. You will not need to enter a username or password to SQL*Plus when you provide the forward slash ("/") at the prompt. The current user is logged on automatically to the Oracle Database machine with the appropriate permissions.

Example to run the script

C:\Users\reportuser1>sqlplus / < C:\oracle_setup\Oracle\Oracle_Setup.sql

6.5.3 Creating the Connection String for Integrated Login

Use a .udl file to create a connection string for the integrated Login User as you would for any other Oracle Database user. For the Integrated Login User, do not specify any username or password. Enter the user name as "/" and test the connection.

Note:

Make sure you are logged on to the system as the Report Domain User who has been identified in Oracle Database as an external user, and that the sqlnet.ora file has AUTHENTICATION_SERVICES set to NTS.

Description of the illustration rep_oradb9.png

The connection string will read as follows:

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=/;Data Source=ORCL

6.5.4 Configuring the Oracle Database on Client Machines

Refer to the section on Configuring the Oracle Client and Server for this procedure.

Ensure that the SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file is set to NTS on both the client and the server.

Also see the Oracle Database Platform Guide for more information on External User Authentication Task on the Client Computer and more.

WARNING:

It is important to keep in mind that using a database for reporting will result in having a number of connections equal to the number of active users. This will have a substantial impact on memory requirements (for performance) and storage requirements (for data logged).

6.5.5 Next Steps

After you configure the Agent to report events and the database to store them, you must configure BI Publisher to locate them for publication. Continue to Configuring Oracle Business Intelligence Publisher.

6.6 Microsoft SQL Server Configuration Overview

The following is a brief overview of the procedures that you must follow in order to successfully configure the SQL database to work with Reporting:

• Creating the Database Table and Setting Up Stored Procedures

• Creating the Reporting Database User

• Setting Permissions to Log On to the Reporting Administrative Console

• Setting Up a Domain Computers as a SQL User

• Enabling TCP/IP Protocol

• Next Steps

6.6.1 Creating the Database Table and Setting Up Stored Procedures

The following procedure creates a database table and stored procedures for a SQL Server database. You must perform these steps for both upgrades and new installations.

1. Open SQL Server 2005 or 2008. Click All Programs > Microsoft SQL Server 2005 (or 2008) > SQL Server Management Studio.

2. Connect to the Database using Windows authentication, which should be the default.

3. On the left pane, navigate to Databases.

4. Right-click on Databases and select New Database. The New Database dialog opens.

   
   Description of the illustration rep_new_db.png
   
   

5. Enter a Database name, for example PLXReporting, and click OK. You should now see a PLXReporting database under Databases.

   Note:

   The database can have any name as long as the name is consistent in the queries and stored procedures.

6. Right-click on PLXReporting and select New Query.

7. Execute the following script on the SQL Server to instruct the database where to put the ESSO.Reporting.MSSQL.Decoding.dll:

   • For SQL Server 2005:

     DECLARE @AssemblyPath nvarchar(1024)
     SELECT @AssemblyPath = REPLACE(physical_name,
     'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf',
     'Microsoft SQL Server\MSSQL.1\CLR\')
     FROM master.sys.database_files WHERE name = 'master';
     SELECT @AssemblyPath
     

   • For SQL Server 2008 R2:

     DECLARE @AssemblyPath nvarchar(1024)
     SELECT @AssemblyPath = REPLACE(physical_name,
     'Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf',
     'Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\CLR\')
     FROM master.sys.database_files WHERE name = 'master';
     SELECT @AssemblyPath
     

     Note:

     The result of this query provides the correct path for the file ESSO.Reporting.MSSQL.Decoding.dll. After receiving this information, create the folder where this file will be placed:

     1. Browse to the path that resulted from the query above.

     2. Create a folder named CLR.

     3. Place ESSO.Reporting.MSSQL.Decoding.dll in this folder.

8. Open the MSSQL_Setup.sql file located in the Reporting package. Copy the contents of the file into the New Query panel.

   Note:

   The database name after the Use statement in the query must match the database name entered in Step 5 above.

9. Click Execute, which is located above the workspace pane. Upon completion, a success message appears in the bottom right pane.

   You have completed creation of the Database table dbo.tblEventsLog, under PLXReporting - Tables, and the stored procedures.

   
   Description of the illustration rep_table_creation.png
   
   

6.6.2 Creating the Reporting Database User

To create the Reporting database user:

1. In the SQL Server Management Studio left pane, expand the top node (server name), then navigate to Security > Logins.

2. Right-click on Logins and select New Login.

3. On the New Login dialog:

   1. Select SQL Server Authentication.

   2. Enter your login name and password.

   3. Unselect User must change password and next login.

   4. Select User Mapping in the left pane.

   5. Select the Reporting database (in this case, PLXReporting).

   6. Ensure all server roles except public are unchecked.

   7. Enable db_datareader, and db_datawriter.

4. Click OK.

6.6.3 Setting Up the Domain Computer

The following steps configure the Reporting Service to connect to the database.

1. In the SQL Server Management Studio left pane, expand the top node (server name), then navigate to Security > Logins.

2. Right-click on Logins and select New Login.

   
   Description of the illustration rep_security.png
   
   

3. On the New Login dialog, enter Domain\Domain Computers in the Login Name field, and then select Windows Authentication.

4. Select User Mapping in the left pane.

5. In the right-hand pane, select the reporting database, (in this case, PLXReporting) as shown in the following screen.

   
   Description of the illustration rep_sql8.png
   
   

6. Enable db_datareader, and db_datawriter.

7. Click OK.

6.6.4 Setting Permissions to Log On to the Reporting Administrative Console

To set permissions to log on to the Reporting Administrative Console:

1. In the SQL Server Management Studio left pane, right-click on the database name (in this case, PLXReporting) and select Properties, then select Permissions from the left pane.

2. Highlight the user that was created to access the Reporting database (in this case, dbuser).

3. Ensure Connect, Execute, and Select are enabled in the Grant column, as shown in the following screens.

   
   Description of the illustration rep_sql1.png
   
   
   Description of the illustration rep_sql2.png
   
   
   Description of the illustration rep_sql3.png
   
   

4. Click OK.

5. Right-click on the Reporting Database, and select Properties.

6. Select Permissions from the left pane.

7. Highlight Domain\Domain Computers.

8. Ensure Connect and Execute are enabled in the Grant column, as shown in the following screens.

   
   Description of the illustration rep_connect_permission.png
   
   
   Description of the illustration rep_execute_permission.png
   
   

9. Click OK.

10. From the top node, go to Security> Logins.

11. Under Logins, select the user created to access the Reporting Console (in this case, dbuser), right-click, and select Properties.

    
    Description of the illustration rep_sql4.png
    
    

12. Select Server Roles from the left pane, and ensure all selections except public are unchecked.

    
    Description of the illustration rep_sql5.png
    
    

13. From the top node go to Security > Logins.

14. Under Logins, select Domain\Domain Computers, right click, and select Properties.

    
    Description of the illustration rep_sql6.png
    
    

15. Select Server Roles from the left pane.

16. Ensure all selections except public are unchecked.

    
    Description of the illustration rep_sql7.png
    
    

6.6.5 Enabling TCP/IP Protocol on SQL 2008 Server R2

Note:

This step only applies to SQL 2008 Server R2.

1. In the SQL Server Configuration Manager, select SQL Server Network Configuration.

2. Select Protocols for MSSSQLSERVER.

3. On the right pane, under Protocol Name, ensure that TCP/IP is enabled.

   
   Description of the illustration server_config.png
   
   

WARNING:

It is important to keep in mind that using a database for reporting will result in having a number of connections equal to the number of active users. This will have a substantial impact on memory requirements (for performance) and storage requirements (for data logged).

6.6.6 Setting Up Microsoft SQL Server to Use Reporting with Windows Integrated Authentication

To use Windows integrated authentication with Reporting, the ESSO Reporting Service must run as a domain user with permissions to write to the Reporting database (either Microsoft SQL Server or Oracle). To run the service as a domain user on a workstation, the user must have "Log on as a service" permissions.

You can modify this setting (as detailed in Section 6.6.6.1) on your domain controller so that the setting is published to all client computers.

6.6.6.1 Creating an Active Directory domain user that will write events to the database

Create a user in Active Directory (henceforth referred to as the "Reporting Domain User"). You will grant this user permissions to write Reporting events to the database.

6.6.6.2 Modifying the Default domain policy to allow the Reporting Domain User to Log on as a service

Modify the Default domain policy on your domain controller so that all client computers connected to the domain have this setting defined.

Note:

Oracle recommends that you create a backup of the current Group Policy before editing the default domain policy. To create a backup, in the console tree, open Domains/Current Domain Name/Group Policy objects. Right-click Default Domain Policy, and select Back Up from the context menu.

The Group Policy is domain-wide and overwrites the local policy. If you need to configure any local accounts to log on as a service, refer to the documentation for the Group Policy Management Console for this procedure.

1. On your domain controller, click Start, click Run, type gpmc.msc, and then click OK.

2. In the console tree, open Domains > Current Domain Name > Group Policy Objects. Right-click Default Domain Policy, and select Edit from the context menu.

   
   Description of the illustration rep_int_auth_with_sql1.png
   
   

3. In the Group Policy Management Editor's console tree, go to Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

4. In the details pane, double-click Log on as a service.

   
   Description of the illustration rep_int_auth_with_sql2.png
   
   

5. Verify that the Define this policy setting check box is selected, and click Add User or Group. Enter the new Reporting Domain User in the User and group names field.

6. Click OK when finished.

   
   Description of the illustration rep_int_auth_with_sql3.png
   
   

To apply the Group Policy change immediately:

• Restart the domain controller.

  or

• Open a command prompt, and type: gpupdate /force. Then press Enter.

6.6.7 Verifying Publication of the Active Directory Permission on the Client Machine

Note:

Also see Microsoft's technical note about modifying permissions on an Active Directory domain or local computer to allow a domain user to log on as a service.

1. Ensure that the client is updated by opening a command prompt and entering the gpupdate/force command. For a discussion of this procedure, see the Microsoft Technical library.

2. Go to Administrator Tools > Local Security Policy.

3. In Local Security Policy go to Security Settings > Local Policies > User Rights Assignment (as shown in the following figure).

4. In the Detail Panel check for the updated "Log on as a service" policy. It should include the Reporting Domain User among the users who have this permission.

   
   Description of the illustration rep_int_auth_with_sql4.png
   
   

6.6.8 Configuring the ESSO Reporting Service on the Client Machine to run as this domain user

Note:

Perform these steps on all client computers where the ESSO Reporting Service is running.

To configure the ESSO Reporting Service to run under the Reporting Domain User account:

1. Open a command prompt and enter the following command:

   sc config "SSO Reporting Service" obj= "Domain\User" password= "password"
   

2. Press Enter.

   This command should return the following output:

   [SC] ChangeServiceConfig SUCCESS
   

3. Restart the ESSO Reporting Service:

   1. Open a command prompt and enter the following command.

      net stop "SSO Reporting Service" && net start "SSO Reporting Service"
      

   2. Press Enter.

      This command should return the following output:

      The ESSO Reporting Service service is stopping.
      The ESSO Reporting Service service was stopped successfully.
      The ESSO Reporting Service service is starting.
      The ESSO Reporting Service service was started successfully.
      

   Note:

   You can achieve the same results through the user interface by accessing the "Services" console on any client computer.

6.6.9 Setting Up Microsoft SQL Server for Integrated Authentication

Before performing the following steps for Microsoft SQL Server Integrated Authentication, you must complete the procedures described in Creating the Database Table and Setting Up Stored Procedures in the SQL Database Configuration section of this guide. You must perform those steps for both upgrades and new installations.

6.6.9.1 Configuring a Login and Role for the New Reporting Domain User in the Microsoft SQL Database

1. In the SQL Server Management Studio left pane, expand the top node (server name), then navigate to Security > Logins.

2. Right-click on Logins and select New Login….

   
   Description of the illustration rep_int_auth_with_sql5.png
   
   

3. In the New Login dialog:

   1. Select Windows authentication. Enter the Reporting Domain User as the Login name.

      
      Description of the illustration rep_int_auth_with_sql6.png
      
      

   2. Select User Mapping in the left pane.

   3. Map the Reporting Console user to the PLXReporting database.

   4. Enable the db_datareader and db_datawriter role memberships for the Reporting Console User.

      
      Description of the illustration rep_int_auth_with_sql7.png
      
      

   5. Select Server Roles in the left pane.

   6. Ensure all roles except public are unchecked.

      
      Description of the illustration rep_int_auth_with_sql8.png
      
      

4. Click OK.

6.6.10 Setting Permissions for the Reporting Domain User

To set permissions for the Reporting domain user:

1. In the SQL Server Management Studio left pane, right-click on the top node (server name), and select Properties.

2. In the Properties dialog, select Security from the left pane.

3. Select the SQL Server and Windows Authentication mode radio button.

4. Select Permissions from the left pane.

5. Select the user (Reporting Domain User, created in the previous section) in the Logins or roles section.

6. In the bottom pane, Explicit Permissions for <name>, ensure Grant is enabled for the Connect SQL permission.

   
   Description of the illustration rep_int_auth_with_sql9.png
   
   

WARNING:

It is important to keep in mind that using a database for reporting will result in having a number of connections equal to the number of active users. This will have a substantial impact on memory requirements (for performance) and storage requirements (for data logged).

6.6.11 Next Steps

After you configure the Agent to report events and the database to store them, you must configure BI Publisher to locate them for publication. Continue to Configuring Oracle Business Intelligence Publisher.

6.7 Using Oracle Business Intelligence Publisher for Deployment with Reporting

The Reporting service uses Oracle Business Intelligence (BI) Publisher to generate reports for collected data. The following procedure explains how to configure BI Publisher to receive data from the Reporting Service. Refer to BI Publisher documentation for complete information about using this tool.

It is assumed that you have already installed BI Publisher 11g.

6.7.1 Configuring Oracle Business Intelligence Publisher

1. Open BI Publisher in your browser (the typical URL is

   http://host:7001/xmlpserver).
   

2. Submit credentials for an administrator account.

3. In the BI Publisher window, select the Administration menu.

   
   Description of the illustration bi_pub_choose_admin.png
   
   

4. Under Data Source, select JDBC Connection.

   
   Description of the illustration bi_pub_choose_jdbc_crop.png
   
   

5. Click Add Data Source.

   
   Description of the illustration bi_pub_add_data_source_crop.png
   
   

6. For the data source name, enter: ESSOReportingDB. Provide the Reporting database connection information as in the following examples:

   • Example of Oracle 11g connection information:

     
     Description of the illustration bi_pub_ora11g_stngs_crp.png
     
     

   • Example of Microsoft SQL Server 2005 database connection information:

     
     Description of the illustration bi_pub_sql_stngs_crop.png
     
     

7. Click Test Connection to verify that the connection is operational. You will see a Confirmation message when the test succeeds.

   
   Description of the illustration bi_pub_test_conct_crop.png
   
   

8. In the Security section, set the roles that will have access to the ESSOReportingDB data source.

   
   Description of the illustration bi_pub_set_roles_crop.png
   
   

9. Click Apply to save your settings.

10. On the Administration page under System Maintenance, select Server Configuration.

    
    Description of the illustration bi_pub_server_config_crop.png
    
    

11. In the Catalog section choose catalog type Oracle BI Publisher - File System and set a folder on your hard drive. This folder will be used for storing your reports. Click Apply.

    
    Description of the illustration bi_pub_choose_cat_type_crop.png
    
    

Continue to the next section to add Oracle Enterprise Single Sign-On Suite reports to Business Intelligence Publisher.

6.7.2 Deploying Reporting

To deploy Reporting

1. Open the BI Publisher Catalog folder on your hard drive.

2. Create a Reports folder in this location. BI Publisher searches for the Reports folder in the BIPublisherCatalog directory, so it is important that you create this folder in the correct place.

   
   Description of the illustration bi_pub_deploy_folder_crop.png
   
   

3. Copy Oracle Enterprise Single Sign-On Suite reports to the Reports subfolder.

   
   Description of the illustration bi_pub_reports_folder_crop.png
   
   

4. Restart Business Intelligence Publisher.

5. Log on to Business Intelligence Publisher and navigate to Catalog > Shared Folders. Oracle Enterprise Single Sign-On Suite Reports are ready to use.

6. Under the Report Folder for each component (for example, Logon Manager), for each Report Type "Data Model," click Edit.

   
   Description of the illustration bi_pub_datamodel1_crop.png
   
   

7. In the Properties dialog, select the database connection you created previously as the value for the Default Data Source.

   
   Description of the illustration bi_pub_datamodel2.png
   
   

   Then select from the List of Values to the left of the Properties dialog. Change the Data Source in this dialog as you did in the step above, assigning the previously-created database connection.

8. Save your changes after completing this process.

   Reports are ready to use.