May 18, 2009 at 8:51 am
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?
May 18, 2009 at 11:29 am
Programmatically, you can use WMI to check the Event Log (Application log) for the success or failed login
A.J.
DBA with an attitude
May 19, 2009 at 8:22 am
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]
May 19, 2009 at 2:21 pm
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.
May 20, 2009 at 9:11 am
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
May 20, 2009 at 10:52 am
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