When calling Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer.ConnectionString and assigned it a connection string like
“Data Source=sqlservernameorIP;initial catalog=BizTalkMgmtDb;user id=userid;password=pwd;” providerName=”System.Data.SqlClient”

I would get an error even I know the credential is correct and the SQL Server is setup with Mixed mode so it will take both SQL server authentication and Windows Authentication:
“Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

With the same user credential, I can connect to other database on the same server if I don’t use BizTalk API call. That indicates that the BizTalk API only accept Windows authenticated domain user account.

Then, how will I pass a domain user account through web?
1) Go to IIS where the web application is hosed.
2) Select the ApplicationPool for the app and click on Advanced Settings
3) Select Identity and change it to use Custom Account (from Built in Account). Click on Set and enter a domain account; you must enter the domain name such as TGIF\syang; this domain account must exist and must have proper access to the server where the BizTalk database resides.
4) If the domain account has not been given proper access to the BizTalkMgmtDb, now it is time to go to the database server, expand the Security folder then select the Logins. Add TGIF\syang to Logins as new user if it is not already there.
5) Then you must make sure the user is mapped to the database with proper role. This is done by clicking on User Mapping page after you bring up the user’s Properties. Select the BizTalkMgmtDb database by checking the Map box, then in the “Database role membership” window below, check BTS_Admin_Users.

Then, is it true that the connection string with a sql server user account no longer needed?
No, it is still needed. After I made the changes in IIS and was able to connect to the BizTalk Explorer, I intentionally passed in an invalid userid to the sql server connection string as in [“”Data Source=sqlservernameorIP;initial catalog=BizTalkMgmtDb;user id=userid;password=pwd;” providerName=”System.Data.SqlClient”], immediately, I got an error from BizTalk API call where it initializes the connection to BizTalkMgmtDb.

So, in summary, BizTalk Explorer API requires:
1) An authenticated and authorized Windows domain account to access its management database (where information like Receive Locations, Ports, etc reside)
2)If the application is a web application hosted in IIS, IIS Application Pool must be setup using Custom Account instead of the default Built-in Account
3)When passing in a SQL server connection string, this connection must be set to use Integrated Security or SQL server authentication with a user that is grant proper access. If using SQL Server authentication, steps 1 and 2) are still needed.