We had an issue, the DTA database was empty, nothing was being tracked, and we were seeing a weird error message that pointed at something else in the event log:

 

Either another TDDS is processing the same data or there is an orphaned session in SQL server holding TDDS lock. Either another TDDS is processing the same data or there is an orphaned session in SQL Server holding TDDS lock. SQL Server: <virtualmachinename>\<virtualinstancename>. Database: BizTalkDTADb

 

We found some answers from Mike H’s Fantastic Blog: http://geekswithblogs.net/mhamilton however it missed some points, so we expanded upon that:

 

Active Directory Server

 

  1. Add our service (BtsService) account to BizTalk Application Users group in domain (may not currently be, but it should be)

 

SQL Server

 

  1. Add BizTalk Server Administrators to BTS_ADMIN_USERS role in BAMArchive database
  2. Add user accounts for BizTalk Application Users and BizTalk Isolated Host Users in BAMArchive database
  3. Add BizTalk Application Users and BizTalk Isolated Host Users to BTS_HOST_USERS role in BAMArchive database
  4. Add BizTalk Server Administrators, BizTalk Application Users and BizTalk Isolated Host Users to BAM_EVENT_WRITER role in BAMPrimaryImport  database.
  5. Add BizTalk Application Users and BizTalk Isolated Host Users to BAM_QueryWS role in BAMPrimaryImport database.
  6. Add BizTalk Server Administrators, BizTalk Application Users and BizTalk Isolated Host Users to BAM_EVENT_WRITER and HM_EVENT_WRITER roles in BizTalkDTADb database.
  7. Add BizTalk Application Users and BizTalk Isolated Host Users to BAM_CONFIG_READER role in BizTalkMgmtDb database.
  8. Add BizTalk Application Users and BizTalk Isolated Host Users to BAM_EVENT_READER role in BizTalkMsgBoxDb database.

 

Expanded on from: http://geekswithblogs.net/mhamilton/articles/37523.aspx

 

  1. Add our service (BtsService) account to BizTalk Application Users group in domain.
  2. Add user accounts for BizTalk Application Users and BizTalk Isolated Host Users in BAMArchive database
  3. Execute SQL Server Enterprise Manager (EM).
  4. Expand the SQL Server instance we are working with, and expand Databases.
  5. Expand the BAMArchive database, and select Roles.
  6. Add BizTalk Server Administrators to BTS_ADMIN_USERS role in BAMArchive database. Double-click (or right-click Properties) the BTS_ADMIN_USERS role. If the domain group BizTalk Server Administrators group is not a member of this role, click the Add button to add it. (NOTE: If the group does not appear as available when you click add, you will need to cancel this, select Logins for this database, and add the <domainname>\BizTalk Server Administrators group as a new login to this database before you can add it as a role.)
  7. Click okay to save.
  8. Add BizTalk Application Users and BizTalk Isolated Host Users to BTS_HOST_USERS role in BAMArchive database. Select Users on the left and ensure that the BizTalk Isolated Host Users group is a login for this database. If it is not, right-click on the right pane, select New User and add this login to this database.
  9. Select Roles on the left, and double click the BTS_HOST_USERS role. IF the <domainname>\BizTalk Isolated Host Users group is not a member of this role, click Add and add this login to this role. Click OK when done.
  10. Expand the database BAMPrimaryImport on the left.
  11. Select Users and here we need to add our BizTalk Service Account. This is not done automatically during the ConfigFramework.
  12. Right-click on the right pane, select New User and add our service account login to this database.
  13. Next, click Roles and double click the BAM_EVENT_WRITER role on the right. Add our service (BtsService) account, and the BizTalk Server Administrators logins to this role.
  14. Click OK to save.
  15. Double click the BAMQueryWS role and add our service (BtsService) account and the BizTalk Isolated Host Users accounts to this role.
  16. Click OK to save.
  17. Next, expand the BizTalkDTADb database object, and select Roles on the left.
  18. On the right double-click the BAM_EVENT_WRITER role, and add the BizTalk Server Administrators login and the service (BtsService) login to this role. Click OK to save.
  19. Double-click the HM_EVENT_WRITER role, and add the BizTalk Server Administrators and the service account to this role as well. Click OK to save.
  20. Next, expand the BizTalkMgmtDb database object, and select Roles on the left.
  21. Double-click the BAM_CONFIG_READER role, and add the service account to this role. Click OK to save.
  22. Next, expand the BizTalkMsgBoxDb database object, and select Users.
  23. Right-click on the right pane, select New User and add the service account as a login to this database. Click OK to save. Now select Roles on the left.
  24. Double-click the BAM_EVENT_READER role, and add the service account to this role. Click OK to save.

Special Credits to Greg B, for working through the solution.