SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Audit Level


SQL Server Audit Level

Author
Message
Philip-918225
Philip-918225
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146977 Visits: 19434
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
My Blog: www.voiceofthedba.com
SA-1
SA-1
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2723 Visits: 944
Do you have multiple instances on the box? If so, are you looking at the registry keys for the right instance?
Philip-918225
Philip-918225
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146977 Visits: 19434
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
My Blog: www.voiceofthedba.com
Philip-918225
Philip-918225
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146977 Visits: 19434
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
My Blog: www.voiceofthedba.com
Philip-918225
Philip-918225
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146977 Visits: 19434
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
My Blog: www.voiceofthedba.com
Philip-918225
Philip-918225
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search