May 6, 2026 at 6:43 am
Hi to all
We have situation at a client where someone is illegally changing passwords, reactivating sa user etc with a 3rd part tool. Does anyone know of any way to stop password updates.
Note we this guy is stopping sql agent services and auditing while he does whatever he does, so he causing some problems and resetting password on a daily basis for us. Any help or advice would appreciated
May 6, 2026 at 6:50 am
review all permissions on server and remove any that can be used to change those settings - including possibility of changing SQL Agent jobs (or the SP's they execute) to do that.
Ensure user also has no local admin access to that server as this can also be used to enter the instance through the backdoor.
if he is a DBA then he should be removed from company
May 6, 2026 at 7:33 am
This user seems to be using the 3rd party tool from another server. Somehow accessing the actual master.mdf file from another server. The problem is we have no idea who this person is, it could be one of there client domain admins. It is tricky as a third party provider to find a solution. We already have limited Rdp access to only 3 users
May 6, 2026 at 7:46 am
use a extended event trace to see who/when/how its being done - and if the other server account has sysadmin access to this one, remove it and grant only required minimum access to do whatever it needs (if any at all).
Master.mdf can't be accessed like that - all is done through normal sql. or else the whole server would be a mess
May 6, 2026 at 1:25 pm
xEvents is likely a good way to trace this. You can limit to just the alter user event for sa. Something like the following:
/*
================================================================================
Extended Events Session: Monitor_SA_Account
Purpose : Capture password changes and enable/disable actions for the [sa]
login at the server level.
Targets : ring_buffer (in-memory, immediate querying)
event_file (persistent, survives restarts)
Author : Generated for Steve Jones @ Redgate
Date : 2026-05-06
================================================================================
EVENTS CAPTURED
---------------
1. audit_login_change_password_event
Fires when ALTER LOGIN [sa] WITH PASSWORD = '...' is executed.
Predicate filters on target_login_name = 'sa'.
2. audit_server_principal_management_event
Fires on ALTER LOGIN [sa] ENABLE / DISABLE (and other principal DDL).
Predicate filters on target_server_principal_name = 'sa'.
NOTES
-----
* Adjust the event_file path (filename) to a valid directory on your server.
* STARTUP_STATE = ON means the session restarts automatically after a
SQL Server restart.
* The ring_buffer holds the last 4 MB of events in memory.
* The file target rolls over at 50 MB, keeping up to 10 files.
================================================================================
*/
-- ============================================================
-- STEP 1 – Drop the session if it already exists
-- ============================================================
IF EXISTS (
SELECT 1
FROM sys.server_event_sessions
WHERE name = N'Monitor_SA_Account'
)
DROP EVENT SESSION [Monitor_SA_Account] ON SERVER;
GO
-- ============================================================
-- STEP 2 – Create the Extended Events session
-- ============================================================
CREATE EVENT SESSION [Monitor_SA_Account] ON SERVER
-- ----------------------------------------------------------
-- Event 1: Password changes for [sa]
-- ----------------------------------------------------------
ADD EVENT sqlserver.audit_login_change_password_event (
ACTION (
sqlserver.client_app_name, -- application that issued the command
sqlserver.client_hostname, -- machine the connection came from
sqlserver.server_principal_name, -- login that *ran* the ALTER LOGIN
sqlserver.sql_text, -- the T-SQL statement
sqlserver.session_id, -- SPID
sqlserver.database_name -- current database context
)
WHERE (
-- target_login_name is the login *being changed*
[target_login_name] = N'sa'
)
),
-- ----------------------------------------------------------
-- Event 2: Enable / Disable actions on [sa]
-- ----------------------------------------------------------
ADD EVENT sqlserver.audit_server_principal_management_event (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.server_principal_name, -- who ran the command
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.database_name
)
WHERE (
-- target_server_principal_name is the login being managed
[target_server_principal_name] = N'sa'
-- ddl_phase 1 = COMMIT; filter out ROLLBACK noise (phase 0)
AND [ddl_phase] = 1
)
)
-- ----------------------------------------------------------
-- Target 1: Ring buffer – fast in-memory querying
-- ----------------------------------------------------------
ADD TARGET package0.ring_buffer (
SET max_memory = 4096 -- 4 MB
),
-- ----------------------------------------------------------
-- Target 2: Event file – persistent on-disk storage
-- !! Change the path to a directory that SQL Server can write to !!
-- ----------------------------------------------------------
ADD TARGET package0.event_file (
SET filename = N'C:\XEvents\Monitor_SA_Account.xel',
max_file_size = 50, -- MB per file
max_rollover_files = 10 -- keep 10 files before wrapping
)
WITH (
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, -- drop an event rather than block SQL Server
MAX_DISPATCH_LATENCY = 5 SECONDS, -- flush to targets within 5 s
MAX_EVENT_SIZE = 0 KB, -- unlimited individual event size
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON, -- correlate related events via activity ID
STARTUP_STATE = ON -- auto-start after server restart
);
GO
-- ============================================================
-- STEP 3 – Start the session
-- ============================================================
ALTER EVENT SESSION [Monitor_SA_Account] ON SERVER STATE = START;
GO
PRINT 'Extended Events session [Monitor_SA_Account] created and started successfully.';
GO
-- ============================================================
-- STEP 4 – Query the ring_buffer target for captured events
-- Run this at any time to see recent events.
-- ============================================================
;WITH XmlEvents AS (
SELECT
s.name AS session_name,
t.target_name,
CAST(t.target_data AS XML) AS ring_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON t.event_session_address = s.address
WHERE s.name = N'Monitor_SA_Account'
AND t.target_name = N'ring_buffer'
),
ParsedEvents AS (
SELECT
e.x.value('(@name)', 'NVARCHAR(100)') AS event_name,
e.x.value('(@timestamp)', 'DATETIME2') AS event_utc,
e.x.value('(data[@name="target_login_name"]/value)[1]',
'NVARCHAR(256)') AS target_login,
e.x.value('(data[@name="target_server_principal_name"]/value)[1]',
'NVARCHAR(256)') AS target_principal,
e.x.value('(data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)') AS [statement],
e.x.value('(action[@name="server_principal_name"]/value)[1]',
'NVARCHAR(256)') AS executed_by,
e.x.value('(action[@name="client_app_name"]/value)[1]',
'NVARCHAR(256)') AS app_name,
e.x.value('(action[@name="client_hostname"]/value)[1]',
'NVARCHAR(256)') AS client_host,
e.x.value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
e.x.value('(action[@name="session_id"]/value)[1]', 'INT') AS session_id
FROM XmlEvents
CROSS APPLY ring_data.nodes('//RingBufferTarget/event') e(x)
)
SELECT
event_utc,
event_name,
COALESCE(target_login, target_principal) AS sa_login,
executed_by,
client_host,
app_name,
session_id,
[statement],
sql_text
FROM ParsedEvents
ORDER BY event_utc DESC;
GO
-- ============================================================
-- STEP 5 – Query the event_file target
-- Replace the path with your actual .xel file location.
-- ============================================================
SELECT
event_data.value('(@timestamp)', 'DATETIME2') AS event_utc,
event_data.value('(@name)', 'NVARCHAR(100)') AS event_name,
event_data.value('(data[@name="target_login_name"]/value)[1]', 'NVARCHAR(256)') AS target_login,
event_data.value('(data[@name="target_server_principal_name"]/value)[1]','NVARCHAR(256)') AS target_principal,
event_data.value('(action[@name="server_principal_name"]/value)[1]', 'NVARCHAR(256)') AS executed_by,
event_data.value('(action[@name="client_hostname"]/value)[1]', 'NVARCHAR(256)') AS client_host,
event_data.value('(action[@name="client_app_name"]/value)[1]', 'NVARCHAR(256)') AS app_name,
event_data.value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
event_data.value('(action[@name="session_id"]/value)[1]', 'INT') AS session_id
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'C:\XEvents\Monitor_SA_Account*.xel', -- !! update path !!
NULL, NULL, NULL
)
) AS raw_events
ORDER BY event_utc DESC;
GO
-- ============================================================
-- STEP 6 – Optional: Stop and drop the session
-- Uncomment when you want to remove monitoring.
-- ============================================================
-- ALTER EVENT SESSION [Monitor_SA_Account] ON SERVER STATE = STOP;
-- DROP EVENT SESSION [Monitor_SA_Account] ON SERVER;
-- GO
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply