Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope.

I understand and accept that this will require escalation to DTC.

My concern is that our sql instance houses database for distributed applications that, in some cases, require interaction through APIs only. If I enable MSDTC I believe it will apply across databases... this means that when we consume APIs or others consume our API on the same SQL instance we are potentially controller transactions between two theoretically separable applications. Perhaps if the sql actions fail in the methods employed by the API it may be none of my business, and they shouldn't know or care whether I'm invoking their method inside if a DB transaction.

So, in my specific case, if I could use MSDTC solely within my one database it would be appropriate. I am ok with an exception if I do an action across databases requiring escalation.

  • Your question is unclear. Are you asking if you can enable DTC for only a single database? Or do you have some question about the effects of DTC? – Max Vernon Nov 7 '13 at 17:48
  • @MaxVernon I would like to enable DTC for only a single database. – Matthew Nov 7 '13 at 17:55
  • DTC only applies when attempting a distributed transaction. Therefore, if a particular database never issues a distributed transaction, it will not be affected by DTC. If it does, it will be, by design. – Max Vernon Nov 7 '13 at 18:49
  • @MaxVernon this is the crux of my question... When I invoke API calls I don't want to enact any control over transactions they may or may not be invoking in their own databases (on the same instance) ... In that situation I want an error (which is what I currently have)... However, I want escalation when code Within my own db requires it. – Matthew Nov 7 '13 at 19:08
  • MSDTC is either installed or it isn't. From what I can tell, there is no configuration option per database that controls it. – Max Vernon Nov 7 '13 at 19:19

Microsoft Distributed Transaction Coordinator is a service that runs on the machine that cannot be configured per database. The service is either installed, or it isn't.

Since a distributed transaction must use a linked server, you could theoretically use permissions on any linked servers you have configured to allow only your process access. You could use a DDL Trigger to prevent creation of any further linked servers (or at least to audit their creation).

Ad-Hoc Distributed Queries can be turned off at the server level using EXEC sp_configure 'Ad Hoc Distributed Queries' - this setting may help prevent the majority of distibuted query attempts, especially if the users don't have the ability to create stored procs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.