Greg,There is not a lot of "magic" that happens here. From what I understand you are getting the login error when the deployment data tries to create the package based on the credentials supplies in the deployment data record, correct? If the login error is occuring when the deployment data is trying to be created then it would have to be the credentials supplied within that record. The first thing I would try is setting up a test profile that creates the deployment data to ensure that it is not a profile issue. Another issue that I have seen in the past is the resolution of the SQL Server. For example, I have a machine in the office and one at home, both from which I create the packages from. At home, it cannot resolve the DNS name properly, but at the office it can. So I put in an IP address since the IP is the same at home as well as the office, this way there is always resolution. I also always supply the SQL login information rather than relying on Windows Auth for the same reason.
When SQL authentication issues arise it is generally a configuration or data issue. If the problem keeps up let me know and we can dive into the database itself and see what is in there.