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.