none
Using the WCF-SQL adapter through a firewall

    Question

  • I'm attempting to use the wcf-sql adapter to call a stored procedure through the firewall.  I need to narrow the range of ports used by the adapter and/or the dtc in order for security to sign off on it.  I tried using this KB250367 but when I do the Enterprise Single Sign on service won't start, due to not enough resources, preventing the host instances from starting.  Am I on the right track or is there a better way to accomplish this?

    Using BizTalk 2010 on Windows 2008r2 servers.

    Monday, August 05, 2013 4:21 PM

Answers

  • Hi,

    This will depend on how your environment is setup.  When you state “Through the firewall” are you referring to a firewall located inside of your BizTalk architecture (for example, between instances of your BizTalk server(s) and the SQL Server(s) which contain the BizTalk databases) or are you referring to a firewall between your WCF-SQL Adapter (on the BizTalk Server instance) and the SQL Server database where the stored procedure is deployed?

    In the case of the latter, the following list of port should be opened;

    Connection string

    SQL adapter target

    SQL Server

    1433

    TCP

    Retrieve and send messages from databases used by SQL adapter

    Connection string

    SQL adapter target

    DTC

    135

    TCP

    Transacted connection to SQL Server for SQL adapter

    Connection string

    SQL adapter target

    DTC

    5000-5020

    TCP

    Secondary RPC ports for SQL adapter

    noteNote

    You may need to open more secondary RPC ports depending on your server load.

    Depending on what you stored procedure does (only a select which does not alter the underlying table) you could set the bindings useAmbientTransaction property to false.  This would prevent a MSDTC transaction.

    Thanks,

    William

    • Marked as answer by formentia Monday, August 05, 2013 8:48 PM
    Monday, August 05, 2013 7:54 PM

All replies

  • There's also a set of articles describing the ports used by BizTalk Server, and SSO in particular.

    http://msdn.microsoft.com/en-us/library/aa577684%28v=BTS.10%29.aspx

    For the wcf sql adapter in particular, it's just the standard SQL Client ports, unless specified differently.

    Monday, August 05, 2013 5:17 PM
  • Hi,

    This will depend on how your environment is setup.  When you state “Through the firewall” are you referring to a firewall located inside of your BizTalk architecture (for example, between instances of your BizTalk server(s) and the SQL Server(s) which contain the BizTalk databases) or are you referring to a firewall between your WCF-SQL Adapter (on the BizTalk Server instance) and the SQL Server database where the stored procedure is deployed?

    In the case of the latter, the following list of port should be opened;

    Connection string

    SQL adapter target

    SQL Server

    1433

    TCP

    Retrieve and send messages from databases used by SQL adapter

    Connection string

    SQL adapter target

    DTC

    135

    TCP

    Transacted connection to SQL Server for SQL adapter

    Connection string

    SQL adapter target

    DTC

    5000-5020

    TCP

    Secondary RPC ports for SQL adapter

    noteNote

    You may need to open more secondary RPC ports depending on your server load.

    Depending on what you stored procedure does (only a select which does not alter the underlying table) you could set the bindings useAmbientTransaction property to false.  This would prevent a MSDTC transaction.

    Thanks,

    William

    • Marked as answer by formentia Monday, August 05, 2013 8:48 PM
    Monday, August 05, 2013 7:54 PM
  • The procedure is just a select statement so I set useAmbientTransaction to false which worked perfectly.

    Thanks

    Monday, August 05, 2013 8:48 PM