Recently while at a client we were running through the P6 installer to create a new P6 database on Oracle 11g. This usually goes smoothly, as long as all of the connection information is entered correctly. But this time we hit a snag.
We entered our connection information had the dba enter the secret admin password. Then we got an error:
SQLException: ORA-01017: invalid username/password; logon denied
What's going wrong? It seemed we were entering everything correctly, but it simply would not connect.
First, we verified that we could ping the database server. Yep, worked fine.
Next, we ran tnsping to see if we could see the database. Yep, that worked too.
Then we ran SqlPlus from the command line, using the same server/SID and same user and password. Again, everything worked fine.
We used the same connection information in the P6 setup dialog as what we verified with SqlPlus. This was quite a mystery. Now it's not uncommon for someone to fat-finger the password, or to mis-type the server or SID. But we had two people working on it and double checked, triple checked, nothing!
Then we asked around at the client if anyone had seen this kind of behavior before. Sure enough, someone asked if the system password was all uppercase or not. No, it certainly was not, because who would ever have a password with only uppercase characters? Well, it turns out that until 11g, oracle database passwords were ALWAYS stored as uppercase. I did not know that.
Usually you would not know this because the various components that connect to an Oracle database know to convert the password you enter into uppercase before sending it to Oracle, and silently do so. And this is exactly what was happening. The components used by dbSetup for the underlying database connection were taking the password we entered, translating it to uppercase, and that password was being rejected by the server.
Our immediate workaround was to have the dba change the password to all uppercase. A terrible solution, but we were pressed for time, and just needed that connection once to create the database.
In the light of the next day, I found a better solution. Oracle allows you to wrap your password in double quotes, which causes the password to be passed directly, with no translation to uppercase. This is the better solution by far.
So - why did this catch us unawares? Well, this is a new feature in 11g, and the case-sensitivity must be explicitly turned on. The XE client is still 10g, so it's not possible to have this issue during routine laptop installs of P6. And our clients who have recently upgraded to 11g have not enabled this feature yet.
A really good article explaining the new case-sensitive password feature in 11g can be found here:
http://www.oradba.ch/2011/02/case-sensitive-passwords-and-strong-user-authentication-2/
Well, another day, another lesson learned. And the overall lesson is that with software you just never know what's coming around the corner. Even though we install Primavera P6 routinely, when we get to a client it is rarely routine. Certain parts get smoother every time, but you never can tell what differences you'll find until you attempt the install on a specific system.
