I have an issue where within my SQL Server 2016 Standard instance,
Login auditing is set to
Failed logins only but within the
ERRORLOG file, it is capturing
Both failed and succesful logins. Once I observed the behavior, I thought that the reason for this was that I had an
ON ALL SERVER FOR LOGON trigger enabled.
This trigger was created to prevent usage of a SQL Server credential when connecting with SQL Server Management Studio that originates from a
host_name that has not been delegated access. This was added to allow access from specific terminals outside of the domain (but within the same network) that the SQL Server is hosted on.
This is the trigger:
USE master; GO CREATE TRIGGER SSMSRemoteBlock ON ALL SERVER FOR LOGON AS BEGIN IF EXISTS (SELECT * FROM sys.dm_exec_sessions es WHERE es.login_name = 'ExternalDBA' AND es.host_name NOT IN ('ExternalHost1', 'ExternalHost2') AND es.program_name LIKE 'Microsoft SQL Server Management Studio%') ROLLBACK; END;
However, even after dropping the trigger, restarting MSSQLSERVER, and even the entire server,
Logon events are still being captured. I dug even deeper and I browsed to
Software\Microsoft\MSSQLServer\MSSQLServer within the registry, and the
REG_DWORD was not explicitly specified. So, I manually added it with a value of
Failed logins only. Restarted MSSQLSERVER again to no avail. Set the value to
None and restarted... still capturing all login events.
What else could contribute to this behavior?