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@auditlevelSee 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy