This entry is in the hope that I don't fall over this problem again. It's really very straight-forward but has cost me half an hour more than once.

 

I need to create a new SQL receive port today, to receive from a new database and run under a new BizTalk host instance. Step I took:

 

1. I created a new SQL Server account and granted permissions required to execute the stored procedure that the BizTalk receive location would execute

2. I created a new windows account for the new BizTalk host instance to run under, then created the new host & host instance 

3. I configured the SQL receive location to call the stored procedure

 

When I started everything up I began to receive messages in the even log telling me that user x was unable to login.

 

The problem was I that I had set the connection string on the SQL receive location to use the identity under which I'd configured the new host instance to run rather than the SQL account which I'd created.

 

The way all of the above is configured is dependent on whether you choose to go for windows authentication from the BizTalk host instance, or SQL authentication - as I had done in this instance. When using SQL authentication then you have to remember that there will be no relationship between the account of the host insance and the account used to connect to SQL server - this is simply taken from the connection string.