July 11, 2012 at 3:57 pm
Hi All: I have a sql2005 std server running in a domain (it's actually the DC and part of an SBS2008 domain).
I configured a linked server to a remote SQL 2008 server in a separate domain. I verified that I could run a select and insert query from the 2005 server to the linked server.
I then configured a trigger on a table so that when a row was inserted it would insert the row on the linked server (using the same sql statements I used to verify connectivity)...I didn't receive any errors when creating the trigger.
When I record is inserted into the local table, however, I'm receiving the following error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server [my linked server] was unable to begin a distributed transaction.
I googled around a bit and found these two threads, which recommended relaxing the MSDTC authentication:
http://www.sqlservercentral.com/Forums/Topic415654-149-1.aspx
I followed the instructions there for relaxing the security on both servers, but I'm still getting the error. Not sure what else to try at this point?
July 12, 2012 at 10:05 am
What authentication are you using for the linked server security?
Could you script it out and paste that?
July 12, 2012 at 11:23 am
Hi and thanks for helping out.
I'm not mapping any local-to-remote logins.
I have "For a login not defined above..." set to the last option, with a SQL login/pwd that's valid for the dB on the remote server.
In the Server Options, I have "DataAccess=TRUE" and RPC/RPC OUT=FALSE"
After looking more at MSDTC security issues, however, I've decided to not use a trigger and instead have saved the trigger as a SProc and am running it using a SQL Server Agent job. It seems like getting MSDTC running between the two remote machines is problematic from a security context, and not worth setting up for this one purpose.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply