Trigger Security with Linked Server

Question

Post reply

Trigger Security with Linked Server

sharon.bender

SSCrazy

Points: 2853
More actions
March 9, 2010 at 8:17 pm

#217513

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.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

Lowell SSC Guru Points: 323518 More actions · Answer 2

can we see the trigger itself? it might not be written correctly, or not using the INSERTED table correctly and causing an error.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sharon.bender SSCrazy Points: 2853 More actions · Answer 3

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.

sharon.bender SSCrazy Points: 2853 More actions · Answer 4

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.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

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);

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

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;-)

William Vach SSCertifiable Points: 5540 More actions · Answer 7

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.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 8

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

sharon.bender SSCrazy Points: 2853 More actions · Answer 9

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!