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:

      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;
      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;
      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;
      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:

     SET CONFIG_DATA = convert_to_blob(
                                 '<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';

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.

Primavera P6 Session Hung on Oracle Database

Oracle Primavera P6 EPPM

Occasionally a user will close a P6 application and the user’s session does not just go away. The easiest thing I’ve found is to check to see if there are hung sessions in the usession table (in Oracle – other database types may use a different name). This is the type of error you might see:

Primavera P6 hung user session

Look for hung sessions by running the following query, plugging the actual username into ‘Username’:

SELECT * FROM usession WHERE user_id = (
SELECT user_id FROM users WHERE user_name = 'Username');

If any are present, run this query:

DELETE FROM usession WHERE user_id = (
SELECT user_id FROM users WHERE user_name = 'Username');

The user should again be able to log into P6 at this point.

Updating the PrmBootStrap.xml Primavera P6 DB Configuration File on Citrix Servers

Oracle Primavera P6 EPPM

I support an environment where users access the Primavera P6 thick client on Windows via Citrix.

If I connect to the Database Configuration app and add, modify, or delete the connection to a P6 database, the PrmBootStrap.xml file that is in my profile. For the version we are using (8.2), that location is C:\Users(username)\AppData\Local\Oracle\Primavera P6\P6 Professional. It appears that the file is also copied to the “%PROGRAMDATA%\Oracle\Primavera P6\P6 Professional” folder and the “All Users” profile (if it exists) as well, in “C:\Users\All Users\AppData\Local\Oracle\Primavera P6\P6 Professional”.
In the code below, I copy from the ProgramData folder instead of from my profile, but it would be easy to eliminate the outer for loop and uncomment two lines to make it copy from my profile.

Since I have multiple Citrix servers for P6, I normally would manually copy the file to the folders on the other Citrix servers so that the users will have the same list no matter where they log on. To simplify this, I wrote this Windows batch script to automate this copy process.

@echo off
setlocal EnableDelayedExpansion
rem //Citrix servers hosting Primavera P6 should be in the serverlist variable, separated by spaces
set serverlist=citrixserver1 citrixserver2 citrixserver3
rem //Windows username of person who has standard file
set usersname=foobar

set programdataunc=%PROGRAMDATA::=$%
set filepath=Oracle\Primavera P6\P6 Professional
set filename=PrmBootStrap.xml

rem set Sourceloc="C:\Users\%usersname%\AppData\Local\%filepath%\%filename%"

for %%s in (%serverlist%) do (	
	set Sourceloc="\\%%s\%programdataunc%\%filepath%\%filename%"
	if not exist !Sourceloc! (
		echo No file found: !Sourceloc!
	) else (
		for %%a in (!Sourceloc!) do set SourceFileDate=%%~ta

		set offset=0
		if "!SourceFileDate:~-2!" == "PM" set offset=12					
		set /a "SourceFileDateHour=!SourceFileDate:~11,2!+!offset!"
		set "SourceFileDateCompare=!SourceFileDate:~6,4!!SourceFileDate:~0,2!!SourceFileDate:~3,2!!SourceFileDateHour!!SourceFileDate:~14,2!"					

		for %%x in (%serverlist%) do (
			echo DestinationServer = %%x
			echo SourceServer = %%s
			echo .
			if not %%x==%%s (
				set firstloc="\\%%x\%programdataunc%\%filepath%\%filename%"
				set secondloc="\\%%x\C$\Users\All Users\%filepath%\%filename%"
				for %%y in (!firstloc! !secondloc!) do (			    
					if exist %%y (						
						echo =-=-=-=-=-=
						for %%a in (%%y) do set DestinationFileDate=%%~ta
						set offset=0
						if "!DestinationFileDate:~-2!" == "PM" set offset=12					
						set /a "DestinationFileDateHour=!DestinationFileDate:~11,2!+!offset!"
						set "DestinationFileDateCompare=!DestinationFileDate:~6,4!!DestinationFileDate:~0,2!!DestinationFileDate:~3,2!!DestinationFileDateHour!!DestinationFileDate:~14,2!"							
						echo DestinationFileDateCompare = !DestinationFileDateCompare!
						echo SourceFileDateCompare = !SourceFileDateCompare!
						echo .
						if "!SourceFileDateCompare!" gtr "!DestinationFileDateCompare!" (					    
							set NewDestinationFileName=!filename!_!DestinationFileDateCompare!
							echo !NewDestinationFileName!
							ren %%y !NewDestinationFileName!
							copy !Sourceloc! %%y						
						) else (							
							echo Destination file %%y is newer than or the same as !Sourceloc!.
						echo =-=-=-=-=-=
					) else (
						echo =x=x=x=x=x=
						echo INFO: %%y not found					
						echo =x=x=x=x=x=
			) else (
				echo Destination and Source servers are the same: no file copied.
rem pause

If you uncomment the pause command at the end, you can look at the command window to make sure it’s working properly.

Datacenter Migration, Authentication Changes, and Solaris – The Perfect Storm

Peter Griffin is calm while the world burns around him

The action of moving an Oracle Primavera P6 database from one server to another should be fairly simple. Changing the authentication type on that database from a virtual LDAP server to native Active Directory should also be simple. Doing both at the same time? Not so much. And even though I generally try to keep each major change in an application isolated from others so as not to cause unnecessary problems, the tight timelines I’ve had to deal with lately have made keeping these sorts of things separate well nigh impossible. The fact that most of our third-party applications are not under vendor support contracts also adds to the fun!

The good news is that I’ve gotten used to thinking about every possible thing that could go wrong with all these moving parts, and have an excellent record in averting disaster. Of course, there’s a first time for everything.

For a couple of months I had been aware that our VLDAP servers were going to be shut down, and all applications using them would have to authenticate their users directly against AD or some other method.

I had inherited responsibility for maintaining our P6 environment earlier this year, but had not had a chance to take any formal training or to dig too far into the documentation from Oracle, as I have many other applications to deal with besides this one. It’s been a power-through-it-with-my-hair-on-fire kind of year.

I was somewhat familiar with the P6 Web Administrator, and I learned that is where the VLDAP authentication settings were. Before the VLDAP server was shut down, I managed to change the settings to point at AD for (what I thought was) all of the P6 databases.

Users were able to get into the application both through a thick client via Citrix and also through the Web client – everything was working. All of the VLDAP servers were turned off but not yet decommissioned. Life was good!

A few days later, the P6 databases – which are Oracle Databases – were to be moved to a different database server which had a different IP address and the original databases were to be placed in a standby mode.

The same morning that the databases were to be moved, a user calls in a trouble ticket. He can’t log on to one of the P6 databases – not the primary one that’s used 95%+ of the time, but one that only had occasional use. The error he received specifically mentions the name of the old VLDAP server that I thought had been completely disconnected from the system.

About the time I get the call that this is happening, the databases get moved and no users are able to get into the system. Fortunately, this move was planned and everyone was expecting an outage.

I ask to have the named VLDAP server turned back on long enough to get this user back into the system and for me to reconfigure the authentication on his database (and any other databases I might have somehow missed).

This is where things get really interesting.

I had found the location of the file that configures the database config information for the thick client, which most people were using. For anyone who is interested, it is found in these two locations on a Windows server:

%PROGRAMDATA%\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml


C:\Users\All Users\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml.

The database connection info can be changed there.

<?xml version="1.0" encoding="UTF-8"?>
<BootStrap MajorVersion="8" MinorVersion="2" PatchVersion="0" DeplyomentVersion="4">
	<DataBaseAliases DefaultPMAlias="primavp">
			<AppType>Project Management</AppType>
				<Password>(encrypted password)</Password>
[... an Alias block for each P6 database ...]

I wouldn’t recommend changing the encrypted password in the Alias blocks, nor anything after the DatabaseAliases section.

Be sure to also increase the number in the DeploymentVersion field at the top of the file, otherwise, it won’t be sent to the working directory when P6 is started again.

The working directory for this file for each user is:

%LOCALAPPDATA%\Oracle\Primavera P6\P6 Professional\PrmBootStrap.xml.

This is how the file looks if Oracle Database is used for P6. Should you use MS SQL Server or some other system, YMMV.

Incidentally, I only had to make changes here because the name of the database actually changed – in the old location, it had a suffix that was removed. I found that as long as I could tnsping the database from the computer hosting the thick client, the users could connect. For an added layer of insurance, I added a tnsnames.ora file and added entries using the old database names, but pointed at the new database service names and servers, and finally changed the order to “(TNSNAMES, LDAP)” in the sqlnet.ora file to ensure that tnsnames was queried before the Oracle LDAP servers – just in case there was something I was missing here in changing the P6 XML file.

At this point, all users could get into all databases using the thick client, and the outage did not extend past the expected time.

I then proceeded to go to the P6 Web Administrator to look for the places I missed when changing the authentication settings. This is when a bit of panic set in.

P6 Web Administrator error message

A message like the one above shows up – and not only can I not get into the Web Administrator, I can’t get into the Web client either.

I assume (wrongly, by the way – don’t we know what assuming does by now?!) that I can change some XML or INI files and everything will be back to normal.

At this point, be aware that I had a full backup of the server and all databases, and I always backed up any files I changed so as not to corrupt the environment beyond repair. This is not business as usual – but as we had no working test environment, and no vendor support, I had little choice but (and perhaps most importantly – explicit permission!) to work in production.

Unlike the thick client, which is running on three Windows 2008 R2 servers, the Web client runs on Solaris 10 with Oracle WebLogic. Though I can log into the OS via SSH, I don’t have admin rights on the box.

I decide to run several searches, checking for all files with various strings – primarily the old database server name and the names of the databases.

The search I run uses this command:

find /u02/app/bea64 -type f -exec grep '(DB server name, etc.)' /dev/null {} +

I chose the “bea64” folder as the starting point of the search, as that’s where WebLogic is installed and all P6 files and folders seemed to be under that folder.

For further info about this use of the find command in UNIX: StackExchange.

I found a few instances here and there where the server names could be changed. I even found a single binary file that I tried editing with a hex editor. It didn’t matter what I did – I would stop and restart the Web services and the old server names would still show up in the web logs. I noticed that the logs always had a JDBC connection string – which told me that Oracle LDAP and tnsnames.ora were not being used by WebLogic to find the databases. This was made especially clear when I found that the Oracle client wasn’t fully set up correctly – meaning that I couldn’t tnsping the databases from the bash prompt without a few environment variables being set up by me.

I then found what would prove to be the tool that would save the day – the thick client (not web-based!) P6 Web Administrator! It was located at {p6_eppm_home}/p6/, where {p6_eppm_home} is the home folder for P6 selected at installation.

I tried running the app: ./…

First message:

Running Database Configuration Setup...

…getting excited now! And then…

Exception in thread "main" java.awt.HeadlessException:
No X11 DISPLAY variable was set, but this program performed an operation which requires it.
        at java.awt.GraphicsEnvironment.checkHeadless(
        at java.awt.Window.<init>(
        at java.awt.Frame.<init>(
        at java.awt.Frame.<init>(
        at javax.swing.SwingUtilities$SharedOwnerFrame.<init>(
        at javax.swing.SwingUtilities.getSharedOwnerFrame(
        at javax.swing.JWindow.<init>(
        at javax.swing.JWindow.<init>(
        at com.primavera.admintool.Initializing.<init>(Unknown Source)
        at com.primavera.admintool.AdminApp.main(Unknown Source)

So close!

I hadn’t worked much with UNIX in the last few years, but I remembered the X Windows GUI and decided to find out if I could run this app remotely using X Windows.

I was, up until this point, using PuTTY as my SSH client, and WinSCP to transfer files via SSH. But how to run X Windows?

After doing some searching, I found Cygwin, which I had used several years ago in attempting to set up Ruby on Rails in Windows.

Using this wonderful documentation, I was quickly able to get up and running.

I was able to open the P6 Web administrator remotely using X Windows!

At the local prompt in Cygwin:

$ export DISPLAY=:0.0


$ ssh -Y username@servername_or_IP_address

Enter your password at the prompt. You should then be logged onto the remote system via SSH. Now, at the remote prompt…

$ xterm &

Nota bene – from the documentation mentioned above: “By default, the OpenSSH server does not allow forwarded X connections. This must be configured on the remote host by adding X11Forwarding yes to the sshd_config configuration file. The OpenSSH server must be restarted or SIGHUP’ed to re-read the configuration file after it is changed.” Also: “The OpenSSH server requires the xauth command to be available to forward X connections. Consequently, it must be installed on the remote host.”

At this point, a new xterm window should open that has another command prompt from the remote system. Run the executable command – in this case, “./” from its folder, and the GUI-based app should open!

The thick-client P6 Web Administrator opened, I was able to change the JDBC connection strings for each database and also to reset the VLDAP authentication back to AD.

I saved my settings, restarted all services, crossed my fingers and stood on one leg.

BINGO! Everything worked now – even with the VLDAP server turned off!