Ok, so you want to update the service account in Project Portal 2007, meaning the identity of the application pool that serves that site. No problem, you're a big-shot SharePoint expert, Project Portal is just part of SharePoint, you can handle this. You go ahead and do everything Microsoft tells you to do:
http://support.microsoft.com/kb/934838
http://support.microsoft.com/kb/832770
Wonderful. Then your buddy asks you why you went through all that, when all you had to do was execute it through Central Administration:
Central Administration --> Operations --> Service Accounts --> Web Application Pool --> Web service = Windows SharePoint Services Web Application --> Application pool = <name of app pool, confirmed in IIS>. Select the account, and hit OK.
Oh, yeah, you tell your buddy you forgot about that. Sleep deprivation.
So anyway, you did the right thing and didn't really bother with the Microsoft articles, just updated the credentials through Central Administration, did the iisreset /noforce like you're supposed, to and everything is gravy.
Then, 10 minutes later, someone complains that Project Web Access doesn't work. And that someone is right, you get the lovely SharePoint non-descriptive error that you know and love.
While the articles I linked to are relevant, and they work, they don't work to fix this problem. That's why I gave them to you but I'm not really talking about them. Doesn't hurt if you do that stuff, but it doesn't fix PWA.
The problem is that Project Server is a little funny about some of the services running as the same identity as the application pool.
Update the following Windows Services to use the same account as the one used for the Project Portal application pool:
Windows SharePoint Services Administration
Windows SharePoint Services Search
Ok, maybe you don't really need to do both of those services, but that's what got it to work for me.
In addition, one more thing to do in Central Administration:
Central Administration --> Application Management --> Create or configure this farm's shared services --> Hover over the SSP name and select the drop down box, then select Edit Properties. Update the SSP Service Credentials and whatever else you want to change, then hit OK.
NOW your site is configured to use the updated credentials, and PWA actually works.
Good luck! (And why haven't you upgraded to 2010?!?)
-Tom
P.S. This article isn't comprehensive, my goal is just to provide you with information you might not find elsewhere. So you still need to give the service account the appropriate rights, on the database as well as on the Project Portal server. If you don't feel like looking them up, take a look how the current (old) account is configured in SQL, as well as in (admin) group membership on the web server, and local security policy --> local policies --> user rights assignment.