Testing Distributed Transaction Coordinator on a Remote MSSQL Server using SQL Query Analyzer

When using transactions in SQL or BizTalk, the Distributed Transaction Coordinator (DTC) must be configured to allow network clients (typically referred to “Network DTC Access”)  This step is part of BizTalk installation on this blog (found in My Reference Posts), but I’ve broken it out here.

On the SQL Server that will allow remote transaction access:

  • Navigate to Administrative Tools, Component Services.
  • Click Component Services.
  • Expand Computers.
  • Select the local computer, right-click for Properties.
  • Select the MSDTC tab and click the Security Configuration button.
  • Check the “Network DTC Access” checkbox.
  • Check the “Network Clients” checkbox.
  • Click “OK” until the Component Services window is closed (DTC will stop and restart).

Once complete, perform this simple test to ensure you can execute transactions on a remote MSSQL client (requires SQL Client Tools):

  • Open SQL Enterprise Manager and connect to the server to test.  Local or SA crendentials are suitable for this test.
  • In the left pane, select the desired server.
  • Open SQL Query Analyzer.
  • Paste the following code in the query pane, modifying the [‘servername’] attribute with the server name you’re testing (square brackets are required).
  • Execute the query.

If DTC is properly configured, the query should execute and return a single field.

begin tran

create table #temp (columname varchar(20))
insert #temp select top 1 name from [xprtm-michaelco].master.dbo.sysobjects
select * from #temp
drop table #temp

commit tran

I’ve tested this in multiple environments where DTC operations have failed, but I have yet to see it fail in a previously-working environment under the following conditions:

  • A previously-working DTC environment, removing “Network Clients” or “Network DTC access”.
  • A previously-working DTC environment, stopping the DTC service on the SQL Box.

I’d appreciate any insight or observations from experts.

About Michael Coates
I am a pragmatic evangelist. The products, services and solutions I write about fulfill real-world expectations and use cases. I stay up-to-date on real products I use and review, and share my thoughts here. I apply the same lens when designing an architecture, product or when writing papers. I am always looking for ways that technology can create or enhance a business opportunity .. not just technology for technology's sake. My CV says: Seasoned technology executive, leveraging years of experience with enterprise and integration architectural patterns, executed with healthy doses of business acumen and pragmatism. That's me. My web site says: Technology innovations provide a myriad of opportunities for businesses. That said, having the "latest and greatest" for its own sake isn't always a recipe for success. Business successes gained through exploiting innovation relies on analysis of how the new features will enhance your business followed by effective implementation. Goals vary far and wide: streamlining operations, improving customer experience, extending brand, and many more. In all cases, you must identify and collect the metrics you can apply to measure your success. Analysis must be holistic and balanced: business and operational needs must be considered when capitalizing on a new technology asset or opportunity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: