1

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 AuditLevel REG_DWORD was not explicitly specified. So, I manually added it with a value of 2 for Failed logins only. Restarted MSSQLSERVER again to no avail. Set the value to 0 for None and restarted... still capturing all login events.

What else could contribute to this behavior?

4
  • 1
    Sounds like you have C2 or common criteria turned on. – Sean Gallardy - Retired User Apr 26 '18 at 22:09
  • @SeanGallardy-Microsoft C2 is disabled, and common criteria isn't available in Standard Edition. So unfortunately I'm still at a dead end. – PicoDeGallo Apr 27 '18 at 2:21
  • Can you please run this and post your output? SELECT S.name AS 'Audit Name', S.is_state_enabled, S.type_desc AS 'Write Location', SA.name, SA.is_state_enabled, SAD.audit_action_name, SAD.audited_result FROM sys.server_audit_specification_details AS SAD JOIN sys.server_audit_specifications AS SA ON SAD.server_specification_id = SA.server_specification_id JOIN sys.server_audits AS S ON SA.audit_guid = S.audit_guid – SqlWorldWide Apr 28 '18 at 11:54
  • @SqlWorldWide Apologies for the late reply. I ran the above and received no results from the query. – PicoDeGallo May 15 '18 at 16:19

Your Answer

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

Browse other questions tagged or ask your own question.