<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
SQL Server Audit Level
14 posts, Page 1 of 2
1
2
»»
SQL Server Audit Level
Rate Topic
Display Mode
Topic Options
Author
Message
Philip-918225
Philip-918225
Posted Thursday, April 16, 2009 9:29 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27,
Visits: 81
Hi All,
I have a SQL Server 2005 Server with AuditLevel 3 (0x00000003) as per the registry key AuditLevel. When I checked it through the Management Studio I'm seeing it recording only faied logins only which means that it should have been 2.
Audit Level Registry Value
None 0x00000000 (0)
Success 0x00000001 (1)
Failure 0x00000002 (2)
All 0x00000003 (3)
Then I tried to determine it through T-SQL,
I got the result as NULL.
I would like to cofirm that I am on AuditLevel 3. How can I confirm this?
Thanks,
Philip
Post #698590
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, April 16, 2009 9:52 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526,
Visits: 13,864
How are you checking this? The error log?
What is the setting in SSMS? All logins?
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #698607
SA-1
SA-1
Posted Thursday, April 16, 2009 9:58 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:04 AM
Points: 1,140,
Visits: 936
Do you have multiple instances on the box? If so, are you looking at the registry keys for the right instance?
Post #698613
Philip-918225
Philip-918225
Posted Thursday, April 16, 2009 10:02 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27,
Visits: 81
I have initially checked it via SSMS and it is having the Login Auditing option as 'Failed Logins Only'. Then I have checked the Reg key AuditLogin and saw it as 3.
Later I found the following SQL from this site,
----------------------------------------------------------
DECLARE @AuditLevel int
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
@value_name='AuditLevel',
@value=@AuditLevel OUTPUT
SELECT @AuditLevel
----------------------------------------------------------
which returned NULL.
I have then checked the SQL Server Logs and couldn't see any login audits there (this might be due to the fact that there wasn't any failed login attempts!)
Thanks,
Philip
Post #698616
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, April 16, 2009 10:05 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526,
Visits: 13,864
The key is dependent on which instance you are looking at. SSMS should read it from the registry, so I'm thinking that you're looking at the wrong key. Whatever is in SSMS is what should be set. Change it there.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #698619
Philip-918225
Philip-918225
Posted Thursday, April 16, 2009 10:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27,
Visits: 81
I have a number of DB's on the server and I thought the AuditLevel is for the entire DB Server as there isn't any Security Tab if you look at the properties of the individual instances.
under HKEY_LOCAL_MACHINE I have performed a serach for AuditLevel and it returned only 2 rows,
1. (Default) REG_SZ MSSQLSERVER
2. (AuditLevel) REG_DWORD 0x00000003 (3)
Thanks,
Philip
Post #698639
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, April 16, 2009 10:19 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526,
Visits: 13,864
There's always a security tab for 2005 instances. You select the server instance, right click, properties. The dialog that appears has choices on the left. Security is one of those.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #698645
Philip-918225
Philip-918225
Posted Thursday, April 16, 2009 10:32 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27,
Visits: 81
I think we are talking about the same thing. When I connect to the DB using Mangement Studio, I get ServerName (SQL Server 9.0.3077 - Domain\username) and I write click and the security tab says 'Failed Logins Only' this is what I referred in my earlier post. The individual databases under the Database tab which comes under the Server name hasn't got a security tab under properties.
My concern here is why it is having 3 in Registry and Failed Logins only (2) in SSMS. How can this be possible?
Thanks,
Philip.
Post #698667
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, April 16, 2009 10:39 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526,
Visits: 13,864
I can't explain the registry, and honestly you're not supposed to muck in there. The SSMS tool reads the registry, but it may be reading a different place than you're looking and that would be the confusion. The reading in SSMS is what you should go on. there's probably a corresponding server property to this that you can read from T-SQL as well.
The databases don't have a security tab since they are dealing with users, not logins.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #698678
Philip-918225
Philip-918225
Posted Thursday, April 16, 2009 11:14 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27,
Visits: 81
There was a confusion regarding SSMS (I didn't know that it is a reg reading tool) as I thought it is nothing but the Sql Server Management Studio (SSMS?).
As SSMS is a free tool I'm not allowed to run it on the server. That's why I have gone into regedit and searched for AuditLevel.
I guess only option I have now is to change the Audit option and see how it records details in the SQL Server Logs. Would you agree with it?
Thanks,
Philip.
Post #698713
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
