Just a follow-up to anyone in future who has a similar question.
You can go into the properties of the database connection and save the
password.
In Excel 2007: Data Ribbon -- Connections Group -- Properties.
From that dialog box, click on Definitions and click Save Password.

In VBA:

Sub Initialize()
Dim CNN As WorkbookConnection
'...
'... (lots of code setting up the pivot tables)
'...
For Each CNN In ActiveWorkbook.Connections
CNN.ODBCConnection.SavePassword = True
Next CNN
End Sub