Triggering actions on SQL Login Pw Change

Question

I am attempting to implement the event notification code from https://www.mssqltips.com/sqlservertip/2708/tracking-login-password-changes-in-sql-server/

I am not seeing any results in the PasswordChangeLog table when I change the password. there should be an entry in this table for every time I change the password.

There are no errors that I can see. I will work on this again tomorrow on a different server and see if I can get more from the error log (this server is polluted)

Here is the code I am running, it's mostly a cut & paste from Aaron's post. I have reviewed several times...am I missing something? My one significant change is that I added a trigger on the PasswordChangeLog to run a stored procedure whenever an entry is detected.

--TriggerEventOnPasswordChange
USE msdb

GO
IF OBJECT_ID('dbo.PasswordChangeLog', 'U') IS NOT NULL 
  DROP TABLE dbo.PasswordChangeLog; 

CREATE TABLE dbo.PasswordChangeLog
(
    LoginName  SYSNAME,
    EventTime  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO
CREATE TRIGGER RunSyncer
ON  dbo.PasswordChangeLog
AFTER INSERT AS
  BEGIN
    SET NOCOUNT ON
    EXEC sp_Syncer
  END

  GO

ALTER TABLE dbo.PasswordChangeLog ENABLE TRIGGER RunSyncer

GO

CREATE QUEUE PasswordChangeQueue;
GO


CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO


CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @message_body XML;


    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.PasswordChangeQueue
       ), TIMEOUT 1000;


       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChangeLog(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
    END
END
GO
ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Answer 1

Event Notifications would typically involve a MonitorED server, and a MonitorING server (collection server).

If what you posted is for the server to be monitored, you're missing a few things. The basic outine would be to:

1. Create an ENDPOINT
2. Grant CONNECT to the endpoint to specific group or PUBLIC
3. Create a ROUTE, referencing the name of the collection
   server, and the GUID of the database that will receive the messages (where your Activation proc resides).

Something like this:

CREATE ROUTE [My_CollectionRoute] 
WITH 
 SERVICE_NAME = 'My_Service'
,BROKER_INSTANCE = <SOME GUID>
,ADDRESS = 'TCP://COLLECTION-SERVER:4022'

Once you have the collection server configured properly, you would create the Event Notification session on the monitorED server.

On the collection server, you need to:

1. Create an ENDPOINT
2. Grant CONNECT to the endpoint to specific group or PUBLIC
3. Create a ROUTE, referencing the name of the monitored server, and the GUID of the MSDB database
4. Optionally create tables to persist the events you collect
5. Create the Activation proc
6. Create the Queue, which references the Activation proc
7. Create a Service based on the Queue
8. Grant send on the service to user or PUBLIC

Hope this helps --