Using SQLServer 2014+. The database i'm interested in, is part of a Merge Replication schema. Is there a way to detect in an insert trigger, if it was fired by an Application insert statement or a Replication-Agent insert trigger?
One way would be to inspect the
APP_NAME() property inside the trigger.
I setup a simple merge publication and subscription, then created a "log" table with a trigger on the subscriber table to log the details.
IF OBJECT_ID(N'dbo.MergeLog', N'U') IS NOT NULL DROP TABLE dbo.MergeLog; CREATE TABLE dbo.MergeLog ( id int NOT NULL CONSTRAINT PK_MergeLog PRIMARY KEY CLUSTERED , AppName sysname NOT NULL ); GO IF OBJECT_ID(N'TestMergeTrigger', N'TR') IS NOT NULL DROP TRIGGER TestMergeTrigger GO CREATE TRIGGER TestMergeTrigger ON dbo.MergeReplSource AFTER INSERT, UPDATE AS BEGIN INSERT INTO dbo.MergeLog (AppName, id) SELECT APP_NAME(), i.id FROM inserted i; END GO
After the merge ran, I saw the following in the
╔════╦══════════════════════════════════════════════════════╗ ║ id ║ AppName ║ ╠════╬══════════════════════════════════════════════════════╣ ║ 1 ║ SourceServer-Test-MergeReplTestPublicat-DestServer-1 ║ ╚════╩══════════════════════════════════════════════════════╝
The "AppName" column will reflect the names of the servers involved along with the merge replication publication name. Inspecting the
APP_NAME() should allow you to definitively identify inserts against the subscriber from merge replication vs inserts from other apps.
One should be aware that the app name is simply a connection-string parameter set by the application itself, and as such can be "faked". Having said that, in order to provide the correct fake value, an attacker would need to know the name of the publication name, along with both the source and destination server names. The scenario is not very likely, but something to be aware of.