none
Can we use SQL adapter where Databse and BT box are in different zone separated with a firewall ? RRS feed

  • Question

  • We have two Secure zones SZ1 and SZ2. In SZ1, we have BT servers and MSGBOX. IN SZ2 We have our custom Audit log database.

    I tried to use a SQL adapter which is trying to fetch some record from Audit log databse (SZ2) and process it in BTS(SZ!).

     

    So, there is a call which is generating from SZ1 to SZ2 and all the ports are closed.

    I am getting following warnings and error.

    The adapter "SQL" raised an error message. Details "Login failed for user ''. The user is not associated with a trusted SQL Server connection.".

    The receive location "Recv Loc_SBIPending" with URL "SQL://VMSQLECOLLECT/ECOLECT_ITC_ITD/" is shutting down. Details:"The error threshold has been exceeded. The receive location is shutting down.".

    Suggest some solution.

    Thursday, August 26, 2010 7:20 AM

All replies

  • Hi,

    You need to open the TCP port (1433) SQL server uses to communicate. So if there is a firewall beteen SZ1 and SZ2 make sure to allow SQL traffic.

    If you use integrated security and SZ1 and SZ2 are in different domains you need to have a trust between the domains. Another option is to create the same account (with the same password) on both domains/servers. This is not the recommended way though.

     


    HTH,

    Randal van Splunteren - MVP, MCTS BizTalk Server
    http://biztalkmessages.vansplunteren.net

    Please mark as answered if this answers your question.

    Check out the PowerShell provider for BizTalk: http://psbiztalk.codeplex.com
    Thursday, August 26, 2010 8:05 AM
    Moderator
  • Hi Randal,

    The TCP port(143)  is open eventhough it's not working.

    These two zones are in two different domains.

     

    Thursday, August 26, 2010 10:15 AM
  • Hi,

    Actually the port number is 1433 but that is probably a typo.

    Are you using SQL Authentication or Windows Integrated authentication to logon to SQL Server?

     


    HTH,

    Randal van Splunteren - MVP, MCTS BizTalk Server
    http://biztalkmessages.vansplunteren.net

    Please mark as answered if this answers your question.

    Check out the PowerShell provider for BizTalk: http://psbiztalk.codeplex.com
    Thursday, August 26, 2010 10:18 AM
    Moderator
  • Sorry Mistyped it actually 1433 port is open and its default as per my understanding.

    We are using SQl authentication.

    When a service class is communicating from SZ1 to SZ2 with taking the connection string from BRE that is working. But in case of SQl adapter using the same connection string with sql authentication its throwing warning and then error.

    Thursday, August 26, 2010 10:35 AM
  • Chandan,

     

    - Are you sure that mixed mode is enabled on the sql servers?

    - Could you try to do a test connection from the sql schema generation wizard or open a sql management studio on the dev machine?|
    - Did you try to login into management studio on the sql server itself with the sql user credentials?

    - The user needs to have connect rights on the database, select, etc etc, rights on tables and stored procedures. Can you validate this?


    If this post answers your question or helped you in the right direction, please use the "Answer" button on this post.
    Sahin Ozdemir | Consultant, MCTS BizTalk Server, MCPD Solution Development Avanade Netherlands BV
    Thursday, September 2, 2010 8:35 AM
  • Randal,

     

    today we had a strange problem with the sql adapter. We're also using a sql account to connect to a db in the dmz. Via test connection, we weren't able to connect. We opened the port 1433 between both servers, but after some monitoring by an admin it seems that the adapter is using port 55x if i'm not mistaking.

    Does perhaps mean something to you? Tomorrow we'll be investigating it further.


    If this post answers your question or helped you in the right direction, please use the "Answer" button on this post.
    Sahin Ozdemir | Consultant, MCTS BizTalk Server, MCPD Solution Development Avanade Netherlands BV
    Thursday, September 2, 2010 4:11 PM
  • Hi

     

    i had a same problem when trying to connect to a database behind a firewall.This is related to DTC settings on your box.

     

    If your BTS box is in a domain different than your database and if they are seperated by firewall

    and lie with in the same organization then try using MUTUALLY AUTHENTICATED on the DTC settings of BTS box.

    Because if you have a load balance server in between then the network packets get confused routing back to requestor.

    Its technical though but try using mutually authentication and it should work.

     

    Thanks....

    SPN

     

     

    Friday, September 3, 2010 12:14 AM
  • Ok sounds interesing. I already had some issues with the ms dtc on the test environment, but during that time we had no issues with the connection test of the adapter. Back then it gave us an dtc error while running the orchestration and invoking the sql adapter to the dmz.

    I'll try to set the dtc settings of the sql servers and biztalk to mutual authentication and see what happens!

     

    Regards.


    If this post answers your question or helped you in the right direction, please use the "Answer" button on this post.
    Sahin Ozdemir | Consultant, MCTS BizTalk Server, MCPD Solution Development Avanade Netherlands BV
    Friday, September 3, 2010 6:33 AM
  • We're still running into the same issue for the second time now. Today We're trying to check wether the ports in the firewall are causing this issue.

    On the test environment we're planning to open port 135 and 5000-5020 to see what happens.

    The regular settings in the component services itself should be ok. The last time we had issues with configuring it correct, we got a support call with MS in order to get it right. I'm almost sure that the port settings are the key to solving my issue :)

    Regards,

    Sahin


    If this post answers your question or helped you in the right direction, please use the "Answer" button on this post.
    Sahin Ozdemir | Consultant, MCTS BizTalk Server, MCPD Solution Development Avanade Netherlands BV
    Monday, September 6, 2010 10:11 AM