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?
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
Update:
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.
