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.
Best Answer
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.