On a few occasions as a team we have had to troubleshoot WPA/SPA problems with MSQQL/TDW implementations. I decided to put together a tip sheet for resources related to these problems.
Here are some IBM FAQ's
(search for 1228154)
When using SQL db with ITM 6, this is where the installation guide can mess you up some. With one of our classes, the SPA failed to connect when we clicked "test connection" from a reconfigure of SPA from MTEMS.
I found that the ITM installation had not created a database login for
the user that SPA was using. I created the login in SQL Enterprise Manager manually and all worked fine.
I've gone on to find that letting the ITM installation create this login in SQL Enterprise Manager works better than creating it manually since it seems to give it all of the permissions. However, the ITM install guide will steer you wrong with SQL server, telling you that you need to create things that you don't need to create because the ITM installation will do it for you.
I also had problems with the SPA not creating the summary tables in my lab and I had not given the OS account for the SPA user Administrator permissions in Windows.
You can try adding DatabaseName=
I've also seen the SPA wake up but not do any work because the database
tables in the warehouse db don't show as owned by the SPA user. It appears the SPA actually looks for tables by what user owns them before starting any work. I had a case where the tables created by the WPA were owned by 'dbo', and the SPA didn't look at them until they were owned by the SPA user.