Trigger on CDC table

Question

Trigger on CDC table

nic2885

SSC-Addicted

Points: 479
More actions
October 3, 2017 at 3:17 pm

#328941

Hello,
I created 2 tables one in SQL server and another in PostgreSQL both named as [tab1] columns (id, name). I enabled CDC on tab1 table in SQL server. I did test CDC and it works fine.
Further i created After insert trigger on CDC table "CDC.dboTab1_CT" to insert newly inserted data to PostgreSQL's tab1 table using linked server(linked server uses ODBC for Postgres). As soon as i created this trigger, even CDC stopped capturing data. If I drop trigger, CDC again resumes to capture data. When i checked job history for CDC capture it shows error - " Msg 15274, Level 16, State 1, Access to the remote server is denied because the current security context is not trusted. For more information, query the sys.dm_cdc_errors dynamic management view."
But, if i just insert using management studio query tool instead of trigger, linked server inserts row to postgresql without any issue.
I am confused on -
1) Why CDC stops capturing change, (Trigger is AFTER trigger)
2) Why insert through linked server works from query tools of management studio but not through trigger.
Note - For linked server, i am providing remote username password to connect to postgres, which definitely works as it is inserting rows from SSMS- query tools.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

The account SQL server is running under needs to be a login on the server mapped to a user with adequate permissions in PostgreSQL or you must create the linked server clicking the radio button on the security tab the reads: Be made using this security context and enter a login and password with adequate permissions.

nic2885 SSC-Addicted Points: 479 More actions · Answer 2

Joe,

Thank you for your response, but if you please see the screenshot attached, i am already using remote login with property "Be made using this security context". Further if it is login issue from linked server, it would have failed no matter from where i am executing. It works fine from SQL server management studio- Query tools just not from trigger.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

I found a reference here that notes a trigger on the CDC table causes issues: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/af412328-7855-4f3c-8059-7861ac2e6531/is-it-sensible-to-use-a-trigger-on-a-cdc-change-table?forum=transactsql
There's also a note in the CDC docs that says the table shouldn't be queried directly.

An interesting workaround here: https://social.technet.microsoft.com/Forums/sqlserver/en-US/8ae9cbb6-dce8-45b1-9d4f-1d3cef4648df/trigger-on-change-data-capture-tablesystem-tables-not-working-do-system-tablescdc-tables-need?forum=sqldatawarehousing

Insert to a table in the local db, then use that as a staging area to send data via the linked server.

nic2885 SSC-Addicted Points: 479 More actions · Answer 4

Thanks Steve.. This helps. I could store data by triggering it to another table in same database.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

Good luck. Let us know how it goes.

I think this is a bug or by design with the trigger. I suspect the security context gets messed up with the log reader, but who knows. Unlikely to be fixed.