June 16, 2010 at 9:05 am
Hi all,
I write a small own replication - a trigger which fires an DTC INSERT to another server (while running it calculates some data, one reason for my own one - the other: 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 sa login. 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 Stefan
June 17, 2010 at 7:53 am
just want to specify my question:
I have two servers in different domains. I want to create on machine A a linkedserver reference to machine B. On both machines "sql server authentification" is enabled. I wish to connect with "sql server authenfification" to machine A and fire an event to the linked server B, also with "sql server authentification".
I have tried this about 10 hours. It works fine when I connect with "windows authentfication" to machine A, but not with "sql server authentification".
Help :pinch:
June 18, 2010 at 1:56 am
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 INSERT, UPDATE...
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... 😀
June 21, 2010 at 3:39 am
...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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply