see if database is logging login success/failure

  • I'm trying to programatically (T-SQL) determine if MSSQL has success/failure logging enabled but I'm not sure where that is stored. I've looked at sp_configure but don't see anything there (even with advanced=1).

    I'm not doing C2 and I don't want/need to do C2. I've seen mention of "common criteria compliance" but I'm running Standard and not Enterprise.

    How can I check to see if login success/failure is enabled?

  • Programmatically, you can use WMI to check the Event Log (Application log) for the success or failed login



    A.J.
    DBA with an attitude

  • ppcx (5/18/2009)


    I'm trying to programatically (T-SQL) determine if MSSQL has success/failure logging enabled but I'm not sure where that is stored. I've looked at sp_configure but don't see anything there (even with advanced=1).

    I'm not doing C2 and I don't want/need to do C2. I've seen mention of "common criteria compliance" but I'm running Standard and not Enterprise.

    How can I check to see if login success/failure is enabled?

    This setting is in the registry. You can use the undocumented procedure xp_instance_regread to check the values.

    DECLARE @auditlevel int

    EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', @AuditLevel output

    SELECT@auditlevel

    See also here: http://www.sqlservercentral.com/articles/Security/sqlserverauditingpart1/1451/

    [font="Verdana"]Markus Bohse[/font]

  • MarkusB (5/19/2009)


    This setting is in the registry. You can use the undocumented procedure xp_instance_regread to check the values.

    DECLARE @auditlevel int

    EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', @AuditLevel output

    SELECT@auditlevel

    See also here: http://www.sqlservercentral.com/articles/Security/sqlserverauditingpart1/1451/

    That's just what I was looking [for]. Undocumented... Why does Microsoft do stuff like that? *sigh* I hope they don't take it away without replacing it.

    I appreciate the help. Thanks.

  • You could also use the xp_loginconfig extended stored procedure, although it comes with the dire warning in Books Online that it "will be removed in a future version of Microsoft SQL Server."

    exec xp_loginconfig

    name config_value

    ---------------------------- ----------------------------

    login mode Mixed

    default login guest

    default domain MYSERVER

    audit level failure

    set hostname false

    map _ domain separator

    map $ NULL

    map # -

    exec xp_loginconfig 'audit level'

    name config_value

    ---------------------------- ----------------------------

    audit level failure

    -- J.Kozloski, MCDBA, MCITP

  • kozloski (5/20/2009)


    You could also use the xp_loginconfig extended stored procedure, although it comes with the dire warning in Books Online that it "will be removed in a future version of Microsoft SQL Server."

    Looks ok for 2005 but has the warning in 2008 BOL. I wish they'd give an idea of what they recommend you use to replace stuff they're warning they're going to remove. Sometimes they do, like syslogins -> server_principals, but you don't get the same detail from server_principals.

    But xp_loginconfig looks useful, at least as long as it is around. Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply