I've talked about problems deploying BizTalk Server 2004 solutions that use the SQL adapter in the past. Here's another trick that might help you resolve problems with it:
When you use BTSDeploy.exe with a binding file that creates ports that use the SQL adapter, the adapter will try to verify that the settings specified in the port configuration make sense. As part of that, it will try to connect to the specified server and database using the connection string configured in the port.
Normally, people configure the ports to use SQL Server integrated authentication, and ensure that the user used to run their BizTalk Host Instances have access to the database. However, as it turns out, when you configure the port using BTSDeploy, the SQL adapter will indeed connect with integrated security, but under the context of the user running BTSDeploy, not under the context of the host instance user. Thus, if the user running BTSDeploy does not have access to the DB (and normally shouldn't have), then deployment will fail.
Lesson: Grant access to the DB to the user doing the solution deployment, and then remove it as soon as the ports have been succesfully created on BizTalk.
