2

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?

  • I don't have replication enabled to test anything. If you put select SUSER_NAME() in your trigger, what values do you get from both an application insert and a replication insert? – Scott Hodgin Sep 28 '17 at 12:30
  • Well, the App insert result is the userid of the user connected to the DB and initiated the Insert to the table. The replication insert result is the account of the MSSQLAgent. You can use this method to distinguish between the two but it doesn't seem too elegant. APP_NAME() seems to be a better way. – FaultyOverflow Sep 28 '17 at 15:07
1

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 dbo.MergeLog table:

╔════╦══════════════════════════════════════════════════════╗
║ 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.

  • 1
    This is indeed a pretty good way to distinguish between the two. Just tried it out and it works fine. – FaultyOverflow Sep 28 '17 at 15:08
  • Since the name of the replication task contains the server name in it, this did the trick: IF CHARINDEX(@@SERVERNAME, APP_NAME(), 1) = 0 ... BEGIN ... END – FaultyOverflow Sep 28 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.