March 9, 2010 at 8:17 pm
Hi, I'm posting this for a colleague who is struggling with a trigger that won't fire off an insert statement.
The DML trigger is fired from a specific database in server A.
The trigger selects data from a table in a linked server, Server B, in order to get some of its information, so we know that the servers are successfully linked and Server A is able to select data from Server B.
However, later in the trigger, Server A sends an Insert statement to Server B to insert data into one of its tables, and this insert fails.
My best guess is that whatever permissions the trigger is executing with from the one server are not allowing inserts into the table on the other server.
I am assuming triggers are fired off under the table owner or some pre-defined principal role, but I can't find any documentation on what this might be. Can anyone point me in a direction on where to look this info up, or give me any advice on how to troubleshoot the permissions actioning an insert from a trigger on one server into a table sitting on another server?
We are using Windows Authentication, and I wouldn't doubt that this is also playing some role in the problem. Even if we add an "execute as" to the trigger, we still have no joy.
March 10, 2010 at 7:29 am
try this approach on serverA mgmt studio
INSERT INTO [serverB].dbname.dbo.serverB_table(object_text,created_date)
SELECT cast(object_text as nvarchar(MAX) ),created_date FROM dbname.dbo.serverA_table
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 10, 2010 at 7:35 am
can we see the trigger itself? it might not be written correctly, or not using the INSERTED table correctly and causing an error.
Lowell
March 10, 2010 at 4:06 pm
No, it is financially sensitive info, cannot show the trigger itself, but we have run the insert statements individually, confirmed all data being inserted exists and is valid, and have tested the trigger between two test servers, and it works fine.
It's definitely a security issue, I think something on the server running the trigger is actually the problem.
I'm just looking for some info on how to tell what permissions the trigger would be firing on... would it be the table owner's credentials being used to activate the trigger? Anyone know where I can find this info?
We are running under Windows NT authority, and I have had trouble launching some things called from client apps in this regard... even if you use EXECUTE AS to explicitely set the calling user, it still reverts to using the windows credentials.
So I'm really just looking to find out what role would be launching the trigger by default. Or how to find out what the default permissions would be on a trigger.
I need to make sure whatever permissions are launching the trigger are also valid on the server we're trying to insert into.
March 10, 2010 at 4:09 pm
Bhuvnesh, I'm not sure what your code is trying to achieve, but it doesn't seem to have anything to do with the info I am trying to get.
March 10, 2010 at 4:28 pm
sharon.bender (3/9/2010)
Hi, I'm posting this for a colleague who is struggling with a trigger that won't fire off an insert statement....
However, later in the trigger, Server A sends an Insert statement to Server B to insert data into one of its tables, and this insert fails.
My best guess is that whatever permissions the trigger is executing with from the one server are not allowing inserts into the table on the other server.
...
I am sorry, I do not know for sure the answer to your question about how to find out the security context of the action, but my advice would be:
1. Check the setup of the linked server to see what settings are on the security page that might apply
2. Run a sql trace on the target server to see what is happening and in what security context.
3. wrap the trigger code in a try...catch and store the error data somewhere - or even write it to the log - so that you can stop guessing what the problem is.
4. Find some other way of doing what you are doing so that you are not making linked server requests in a trigger - it is not really a good idea to do this.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
March 10, 2010 at 10:43 pm
sharon.bender (3/10/2010)
Bhuvnesh, I'm not sure what your code is trying to achieve, but it doesn't seem to have anything to do with the info I am trying to get.
then post your trigger code
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 12, 2010 at 6:48 am
You might check your permissions in Server B. Keep in mind that the the insert on that server will be acting in the context of the login set in for the linked server in Server A.
March 12, 2010 at 7:03 am
Saw mister.magoo's post after I had already written a reply, so this may be some duplication.
You should check that:
1) The Distributed Transaction Coordinator is running on both servers.
2) OLE DB Providers are configured for distributed updates http://msdn.microsoft.com/en-us/library/ms190918(SQL.90).aspx
3) linked servers are configured for distributed updates using sp_serveroption for "data access " and "remote proc transaction promotion ".
4) SET XACT_ABORT may need to be set to ON.
5) check the collations.
Make sure that all testing is done within a transaction as trigger statements always run within a transaction.
BEGIN TRANS
INSERT INTO [serverB].dbname.dbo.serverB_table
(object_text,created_date)
SELECT cast(object_text as nvarchar(MAX) )
,created_date
FROM dbname.dbo.serverA_table
SQL = Scarcely Qualifies as a Language
March 14, 2010 at 5:02 pm
Thanks a million Carl! That all sounds like great advice, and I've passed it on to the programmer having the problem to check things out.
Love the line SQL = Scarcely Qualifies as a Language too! How true!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply