Changing the Authentication Mode in Oracle Primavera P6 EPPM Using SQL

Oracle Primavera P6 EPPM

I am currently in the process of setting up an Oracle Primavera P6 EPPM 18.8 environment. The operating system on the application and Web server is Windows Server 2012 R2, the middleware is Oracle WebLogic 12c, and Oracle Database 12c for the RDBMS.

This is the first time I’ve ever set up P6 from scratch, and I’m doing it without any assistance from Oracle, other than their documentation.

I’ve learned quite a lot in my adventure so far. One thing that I learned is that you REALLY don’t want to turn on LDAP Authentication everywhere until you’re sure it’s working.

(Relevant documentation: Configuring LDAP Settings in the Primavera P6 Administrator)

In the P6 Administration website, there are two places to change the type of authentication used: NATIVE, LDAP, and WebSSO. The Authentication tab:

P6 Administration screen - Authentication tab

And the authentication section on the database instance itself:

P6 Administration screen - Authentication section on DB instance

If both are set to “LDAP” (and presumably “WebSSO” as well) when things aren’t yet set up properly, you’ll get this lovely error: “Primavera P6 was configured to use a different authentication mode than the database selected”.

Google suggests that you read this Oracle doc to fix the problem. However, this problem is only fixed with this solution if only the DB instance setting is “LDAP”. If both are set that way, this solution does not fix the problem – though it should still be run so as to fix the setting on the DB instance page.

After poking around in the database, I discovered that the offending configuration setting was stored in a BLOB in the ADMIN_CONFIG table. Unfortunately, editing this BLOB is not easy without some assistance. Fortunately, I found an article on StackOverflow that had functions for converting a BLOB into a CLOB, at which point the REPLACE function could be used. Then convert the CLOB back to a BLOB, and UPDATE the database table.

First, create the two conversion functions:

CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
      l_clob         CLOB;
      l_dest_offset  NUMBER := 1;
      l_src_offset   NUMBER := 1;
      l_lang_context NUMBER := dbms_lob.default_lang_ctx;
      l_warning      NUMBER;
   BEGIN
      dbms_lob.createtemporary(l_clob, TRUE);
      dbms_lob.converttoclob(dest_lob     => l_clob,
                             src_blob     => l_blob,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => l_dest_offset,
                             src_offset   => l_src_offset,
                             blob_csid    => nls_charset_id('AL32UTF8'),
                             lang_context => l_lang_context,
                             warning      => l_warning);
      RETURN l_clob;
   END convert_to_clob;
   /



CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
      l_blob         BLOB;
      l_dest_offset  NUMBER := 1;
      l_src_offset   NUMBER := 1;
      l_lang_context NUMBER := dbms_lob.default_lang_ctx;
      l_warning      NUMBER;
   BEGIN
      dbms_lob.createtemporary(l_blob, TRUE);
      dbms_lob.converttoblob(dest_lob     => l_blob,
                             src_clob     => l_clob,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => l_dest_offset,
                             src_offset   => l_src_offset,
                             blob_csid    => nls_charset_id('AL32UTF8'),
                             lang_context => l_lang_context,
                             warning      => l_warning);
      RETURN l_blob;
   END convert_to_blob;
   /

Then run an UPDATE statement, something like this, changing the CONFIG_NAME appropriately:

UPDATE ADMIN_CONFIG
     SET CONFIG_DATA = convert_to_blob(
                          REPLACE(convert_to_clob(CONFIG_DATA),
                                 '<mode desc="SET_AUTHENTICATION_MODE" o1="NATIVE" o2="WebSSO" o3="LDAP" type="javax.swing.JComboBox">LDAP</mode>',
                                 '<mode desc="SET_AUTHENTICATION_MODE" o1="NATIVE" o2="WebSSO" o3="LDAP" type="javax.swing.JComboBox">NATIVE</mode>')
                          )
   WHERE CONFIG_NAME = 'myprimavera.bre.YourConfigName';
COMMIT;

After running this script and restarting the application servers, you should once again be back in NATIVE mode and able to get back into P6 to see what went wrong, and no longer crying over the website you thought you had bricked just a few minutes prior.

Don’t Put Spaces in Java Install Path!

Oracle Weblogic Server 12c logo

So you’ve decided you want to install Oracle Middleware Weblogic Server on Windows, but you haven’t installed the Java JDK yet. Are you thinking about installing it somewhere like “C:\Program Files\Java\jdk…”?

Do not pass GO, do not collect $200, go directly to Oracle installation jail! For whatever reason, Oracle – who also happens to own Java – has made their Weblogic Configuration Wizard completely unfriendly to any spaces being present in the Java installation path. If you don’t heed my advice, you’ll likely end up with the error message
“C:\Program is not recognized as an internal or external command…”
or something like it.

Either choose the default location for installing Java, or if you must put it on a different drive than C, only change the drive letter. This will prevent a great amount of frustration!

Copying Windows Registry Keys from One User to Another

Windows Registry - regedit.exe

After switching the logged on user on a Windows Service, I found out that the original user had some printers set up that were not present in the new user’s profile.

While I could have manually set up the necessary printers on the new user, it seemed that there must be a way to do this that would ensure that all of the printers were set up correctly on the new user.

Printer information for each user is stored in the HKEY_CURRENT_USER\Printers registry key.

However, the HKEY_CURRENT_USER hive only shows registry information for whatever user you’re logged on as. What if you wanted to copy from one user (other than you), to some other user?

The HKEY_CURRENT_USER key for each user is found in the registry under the HKEY_USERS hive, under each user’s SID. If you don’t have an easy way to find out what a user’s SID is, what then?

There is a solution! The information that is displayed in the HKEY_CURRENT_USER hive is stored in the NTUSER.DAT file in each user’s profile.

Each user’s hive can be loaded either from the Registry Editor or the command line. Both of these must be started as an Administrator to have the privileges to do this.

The example below shows how to do this from a command line. If the user whose hive to be copied is called OLDUSER, the following command (reg load) should attach the OLDUSER hive as HKEY_USERS\OLDUSER. This can be run from Command Prompt or PowerShell.

reg load C:\Users\OLDUSER\NTUSER.DAT

Do the same thing with the new user (NEWUSER):

reg load C:\Users\NEWUSER\NTUSER.DAT

Note: If either of these users is currently logged on or running a service, they must first be logged off or the service stopped.

In the Registry Editor, export (in this case) the Printers key from HKEY_USERS\OLDUSER into a .reg file.

Open the .reg file with an editor and replace all instances of OLDUSER with NEWUSER. Save the file. Execute the file by double-clicking on it. Confirm the dialog box to add the information to NEWUSER’s hive.

confirm registry change

To unload both users’ hives, execute the following commands:

reg unload OLDUSER
reg unload NEWUSER

(Remember to restart any Services that were stopped before loading!)

To verify that the information was copied correctly, you can log on as the new user and examine its HKEY_CURRENT_USER hive.