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
May 7, 2026 at 6:09 pm
Just to add, if you don't want to look at it after it happens, review who the sysadmins are on the system currently, verify that they are all using Windows logins, then contact them all and request they stop doing it. If they continue, escalate to the managers and let the manager discuss with their team. If they are local logins, verify if they are still required and if not, disable them (like you do with the sa account). Force Windows authentication where possible.
It is good to figure out WHO is making the change (via XE), but also figure out who CAN make the change as that list may be really small and may be easy to figure out who the likely culprit is.
Like others have said, you cannot modify the mdf file directly - the file is "locked" once the SQL instance is up and running. The only exception would be if the 3rd party tool was stopping the instance, modifying the mdf, then starting the instance up again which is not likely, but also not impossible. One example I can think of is if you have something like HA set up BUT are not syncing the system databases between the HA. The HA that is set up COULD be just basic windows failover clustering without SQL being aware of the failover configuration. So Server A is the primary host but every now and then, the client reboots Server A. when Server A goes down, server B mounts the SQL data and log disks, but the system DB's are held on the C drive. So instances start up on Server B BUT master db is not the same master db as on Server A. Would be a really weird set up, but could explain the behavior. One way you could verify is spin up a new DB in the instance and it'll exist on disk, but when failover happens, if master isn't the same between the 2 (or more) servers, your "new" DB won't be there. Fun thing is a scenario like this wouldn't really get captured by an XE session as when the instance goes down, the XE session goes down too.
I mean, my last "guess" is a HUGE stretch and not likely the problem, but could be a possibility. More likely thing is someone is updating the password (manually or with some PAM tool or something like that) AND has been given sysadmin access on the instance. My first step to troubleshooting this would be to tackle it from both ways - start the XE session so you have the "who and when", and do the review of users with sysadmin permissions so you have a list of "who can make the change". If the list is small, you may even have a good guess who did it. If the list is large, might want to review and reduce the list.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 12, 2026 at 1:29 pm
Thanks guys. I will definitely look at all you options , see how we can implement this.
The main issue we have is that the guys are initially changing the password by accessing the master db from another server, they have unc path open to the server and the location of the master db and with some 3rd party tool they are manipulating user, activating , deactivating , changing passwords even changing usernames. We had sa disabled and renamed and they managed to en able and change passwords without initially logging in.
We have created 4 other sysadmin accounts for 3 sysadmins and one for system tasks, maintenance plans. All other system user cannot even login via ssms. This how far we have gone to restrict access. And still we have issues
Thanks sofar for all the advice
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply