Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Audit Level Expand / Collapse
Author
Message
Posted Thursday, April 16, 2009 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 16, 2009 9:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
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
Posted Thursday, April 16, 2009 9:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, Visits: 944
Do you have multiple instances on the box? If so, are you looking at the registry keys for the right instance?
Post #698613
Posted Thursday, April 16, 2009 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 16, 2009 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
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
Posted Thursday, April 16, 2009 10:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 16, 2009 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
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
Posted Thursday, April 16, 2009 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 16, 2009 10:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
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
Posted Thursday, April 16, 2009 11:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse