Obviously the first thing to check is that you have set the correct password
for the user in the DTS Connection. Reset the password to make sure. The DTS
Connection dialog will validate the details when closing.
From time to time you may experience unexpected login failures even after
re-setting the username and password in a DTS connection.
The "Persist Security Info" OLE-DB property specifies whether to save the
password within the connection or not, and sometimes this is defaults to
false (0) without warning.
To check or fix this problem, open the DTS Connection dialog, and click
Advanced. Persist Security Info is usually the top property in the
data-grid, and should be set to true (1 or –1).