Configuring BizTalk Server 2004 for SQL Server Connection
There might be a possibility to get some problems in the BizTalk configuration wizard to get connected to SQL Server.
This problem occurs due to windows authentication process and domain account.
The following are some suggestions for configuring BizTalk Server for SQL connection.
BizTalk Server 2004 supports local accounts only on a single-box configuration. You have to use Domain Accounts for multiple box configuration. This implies that the SQL Server and BizTalk server should be on the same machine in order to use local accounts.
More information on configuring BizTalk Server is provided here
One important point to note is that, BizTalk Server supports local group and user accounts only in
single computer configurations. BizTalk Server supports domain group and user accounts in both single and multiple computer configurations.
Refer this guide provides more information on how to configure BizTalk Server 2004 on multiple machines
BizTalk Server single box configuration is pretty straight forward.
SQL Server is needed for BizTalk to work, this is due to the reason that BizTalk keeps all its configurations in SQL Server. But it is not necessary for the application data to be in SQL Server, third party BizTalk adapters are available to communicate with other data sources.
But you have to have SQL Server installed as BTS relies heavily on SQL Server for its infrastructure needs.