MSSQL2008: DTC Transaction - Internal abort

Question

Hi all,

I write a small own replication - a trigger which fires an DTC INSERT to another server (one reason for my own "replication": while trigger is running it calculates some data, another: it works from an express version to an express version).

When I do the initial insert from the same Host with the windows authentification it works fine. But there is a webserver on another host, which uses the sqlserver login (for testing sa). When this Host do the initial insert I get a Internal abort after the entlisting and creating phase in the DTCTransaction EventClass (Profiler).

The magic is: When I first fire it from the same Host with the windows authentification, I can fire it from the webserver and it works fine. But I just have to wait some minutes and it won't work.

Where is my error in reasoning...

Thanks! Greetz Teutales

Here is my initial server script:

 EXEC master.dbo.sp_addlinkedserver @server = @Servername, @srvproduct=N'SQL Server' 

 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @Servername, @locallogin = NULL , @useself = N'False', @rmtuser = @Serverlogin, @rmtpassword = @Serverpwd

Answer 1

Here is my own solution:

You can define the execution context for the trigger using WITH EXECUTE AS:

ALTER TRIGGER <triggername> ON <tablename> WITH EXECUTE AS OWNER AFTER ...

If you do this and execute a query which fires the trigger you get the error "Access to the remote server is denied because the current security context is not trusted. (Error 15274).

But there is a server option you can enable:

Alter database robot5 Set trustworthy ON

...and it works. Thanks for reading...

Answer 2

...doesn't work...

I try to explain again:

I want to source some statistics-tables out from one sql server to another sql server. I wrote same triggers to do this. Simultaneous I callculate in the trigger some additional data and write it to the second database, so I can't use the standard replication.

I created a linked server with this code:

EXEC master.dbo.sp_addlinkedserver @server = @Servername, @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @Servername, @locallogin = NULL , @useself = N'False', @rmtuser = @Serverlogin, @rmtpassword = @Serverpwd

My triggers look like this:

CREATE TRIGGER <schema>.<name> ON <schema>.<table> AFTER INSERT, UPDATE
-- some code
SET xact_abort ON 
BEGIN DISTRIBUTED TRANSACTION
  -- calculate some additional data before insert
  INSERT INTO <servername>.<database>.<schema>.<table> (<columns>) VALUES (<values>)
COMMIT TRANSACTION
RETURN

This works great if the initiator of the insert fireing the trigger uses "windows authentification".

My problem occurs when I use the "sql server authentification", it's necessary for our apache tomcat webserver:

OLE DB provider "SQLNCLI10" for linked server "<server>" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "<server>" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Could not open a connection to SQL Server [5].

When I look into the SQL Server Profiler the DTCTransaction EventClass returns

7 - Enlisting in a DTC transaction
6 - Creating a new DTC transaction
10 - Internal abort
16 - Transaction is aborting

When I first run the "windows authentification" I can successfully execute an insert with "sql server authentification" in the next minutes. When I wait a bit the same error occurs.

So my first solution was to alter the trigger to WITH EXECUTE AS OWNER

Alter database <database> Set trustworthy ON
ALTER TRIGGER <schema>.<name> ON <schema>.<table> WITH EXECUTE AS OWNER AFTER INSERT, UPDATE

This works if I run the insert from the local machine using "sql server authentification", but not from another server. But it schould also work from another server... Please help.