Just working on another upgrade today and ran into one of those gotcha’s that can make you scratch your head for hours.  The upgrade job failed trying to do a datamover script:

PeopleTools 8.53.05 – Data Mover
Copyright (c) 2013 PeopleSoft, Inc.
All Rights Reserved
Message Set Number: 0
Message Number: 0
Message Reason: Invalid User ID and password for signon. (0,0)
PSDMTX Error: signon

Funny thing is I can log into Application Designer, and I have the application server and process schedulers all running with the same user.  I check to make sure that my connect id still has the correct access and make sure that no accounts are locked, and everything looks great.  I even have PeopleSoft Administrator as one of my roles and still no dice.

If you have ever ventured to look at the PeopleTools Tab in the Permission List Component, you will see there is an option for Data Mover Access.  Make sure you have a permission list with this option turned on.  I have a custom permission list just for the upgrade and I added it to that, and bingo I was back in business.

I got a note from a client the other day saying that they were randomly sending out a lot of messages when Integration Broker failed to send a message.

It turned out that they had the Error Notification Integration Broker process turned on, which notifies everybody with the role APP_MSG_ADMINSITRATOR that an error has occurred. However, since this is workflow based if you look at the role under the security area, there is a workflow tab, and by default this role uses a query based workflow routing.  The base routing is Query: _ROLE_APP_MSG_ADMINISTRATOR, and if you dig into this you will find that everybody that has access to a web services (entry on PSAUTHWS table) will get notified.

In order to route the messages strictly to the administrators assigned to the role APP_MSG_ADMINISTRATOR, turn off the use query to route workflow in the role configuration.  PeopleTools -> Security ->Permissions & Roles -> Roles – tab WORKFLOW.

I believe with release of PeopleTools 8.44, they introduced a new feature that requires a user id for internal web server communication to load web profile information from the database and also performance agent information.

By default PeopleSoft delivered the product with the default userid PTWEBSERVER.  Which uses a default delivered role of “PeopleTools Web Server”, which links to the delivered permission list “PTPT1500”.

When you build your PIA (PeopleSoft Internet Architecture) you will be asked to enter a userid and password for this internal web server communication.  If the information is incorrect you will often see the error: “CHECK APPSERVER LOGS. THE SITE BOOTED WITH INTERNAL DEFAULT SETTINGS, BECAUSE OF: bea.jolt.ApplicationException: TPESVCFAIL – application level service failure”.

This user information is stored in the configuration.properties file, which is located in the:

<webserv root>/applications/peoplesoft/PORTAL.war/WEB-INF/psftdocs/<piasite>/ folder (for weblogic)

You will notice that the userid/password are encrypted.  This encryption is done using the PSCipher utility that is delivered with the pia.  I haven’t verified this but in past releases you could enter the userid and password unencrypted and it worked fine, however, I always encrypt them now, so I have tested this against 8.5x.  It should also be noted that the userid and password are both case-sensitive.

To encrypt the password use the PSCipher script located at <webserv root>/bin. PeopleTools 8.51 moved utility files to the folder <webserv root>/piabin. I usually run the setENV first (however, it usually does it for you now) and then run PSCipher <newpasstoencrypt>.  It will return an Encrypted password in the form:   {V1.1}xxxxxxxxxxx= copy this password and place it into the configuration.properties file.

If you are still experiencing a problem, make sure that the user you are trying to connect with has the correct password entered into the user security screens, and that the account is unlocked.

With the release of the 8.51 Tools they also introduced a password for the domain.  You set this password in the configuration of the application server, and you have to enter it for things like integration broker and also in the PIA web configuration.  This password is encrypted the same way in the configuration.properties file.

Happy Configuring!

Peoplesoft has an interesting authentication process, and basically it goes something like this.

When you attempt to login in a 2-tier mode, for example in HR trying to log into application designer using the user “PS”, what will end up happening is it will lookup the connect id and password that is assigned in the Configuration Manager setup (pscfg). This user is a database user typically called “people”. This database user is granted select access by the grant.sql script when the system is built on the tables: psstatus, psoprdefn, psaccessprfl. In Oracle there is another table that this user can access and that is PS.PSDBOWNER (there is a public synonym so you can just look for PSDBOWNER).

The PSSTATUS table needs to have the OWNERID field set the main owner of the database. In Oracle it is by default “sysadm”, in SQL Server the default was typically “sa”, but that changed with the introduction of the access id profile, and so it would be the access id that you setup that should be in this field for SQL Server.

The PSOPRDEFN table needs to have the OPRID you are trying to log into with, and the OPERPSWD needs to be set correctly, this field is encrypted, but if you need to change it, go into SQL and update the password, and make sure you change the ENCRYPTED field to “0” zero. Then you can re-encrypt the password by running datamover in bootstrap mode and executing the command: ENCRYPT_PASSWORD *;. Also note the field SYMBOLICID, it needs to match the SYMBOLICID in the psaccessprfl table.

The PSACCESSPRFL has the fields SYMBOLICID, ACCESSID and ACCESSPSWD. The SYMBOLICID needs to match the symbolicid of the psoprdefn table entries. You can have multiple SYMBOLICID’s however, make sure they are all valid. PeopleSoft can throw errors if this table has invalid entries, or if the ACCESSID has limited access (but that is another story). The ACCESSID and ACCESSPSWD should be set to same user as the OWNERID in PSSTATUS. If you need to change these values do so in SQL, make sure you change the ENCRYPTED field to “0” zero, and then in datamover bootstrap mode execute the command: ENCRYPT_PASSWORD *;

Lastly, in Oracle, make sure that the PSDBOWNER table has one row that contains the database name, and the OWNERID as mentioned above. I always make these fields UPPERCASE as well.

If all of these entries are set correctly, you should have no trouble logging into PeopleSoft.

Note: The reason bootstrap works with a database user is that it skips over the PeopleSoft authentication and simply authenticates using the database security, however, bootstrap should be used with CAUTION.

It really amazes me how many times I find at client sites Application Servers that randomly won’t start. There are a combination of issues that can cause the problem, but typically I find the problem to be related to this group of tables.

PSSTATUS – this table has a field called OWNERID, which needs to make the schema owner in Oracle, and should match the ACCESSID in SQL Server.

PSACCESSPRFL – this table has the fields SYMBOLICID, VERSION, ACCESSID, ACCESSPSWD, ENCRYPTED. The accessid should match the OWNERID from PSSTATUS table, and the password would be the password at the database level for the schema/accessid user. If you change this to an unencrypted value you will want to change the accesspswd to an unencrypted value and set the encrypted field to 0, and then run datamover and do the encrypt_password *; statement to reencrypt the passwords. You will also notice the SYMBOLICID field, this is associated to the SYMBOLICID field on the PSOPRDEFN table. VERSION can be reset using the VERSION Application Engine. I recommend that there only be one entry in this table, sometimes having multiple entries can cause problems if the accessid/password are invalid for the environment, because PeopleSoft has a tendency to expect only one entry in this table.

PSOPRDEFN – this is your primary parent information table for security. Make sure that the password is set correctly, if you reset it to a clear text entry, you can reencrypt it using the encrypt_password *; command in datamover. Make sure you set the encrypted field to 0 (zero) before running datamover. You will also want to make sure that the SYMBOLICID matches that of the PSACCESSPRFL table. Lastly, make sure that the ACCTLOCK is set to 0 for the user trying to start the Application Server. You will also want to make sure it has a Permission List that allows it to start the application server.

PS.PSDBOWNER – In Oracle there is another table in the PS schema that contains the Database Name and Owner, make sure these are set correctly.

Make sure that the connect id user often known as “people” is setup in your database and it has been granted select access to the tables: PSACCESSPRFL, PSSTATUS, PSOPRDEFN.

Lastly, make sure that the USERID matches your user that you want to start the Application Server from PSOPRDEFN, and that the password is set correctly, and make sure that the CONNECT ID user / password is correct in the configuration files of the application server.

The typical 2-tier connect takes the userid and authenticates it against the PSOPRDEFN table using the connect id user, which in turn allows a connection to be made to the database using the symbolicid associated with the PSOPRDEFN table, which is tied to the accessid on the PSACCESSPRFL table. So once authenticated that system is actually running the session as “accessid” but controlled by the PeopleSoft security assigned to the user.