April 16, 2009 at 9:29 am
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
April 16, 2009 at 9:52 am
How are you checking this? The error log?
What is the setting in SSMS? All logins?
April 16, 2009 at 9:58 am
Do you have multiple instances on the box? If so, are you looking at the registry keys for the right instance?
April 16, 2009 at 10:02 am
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
April 16, 2009 at 10:05 am
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.
April 16, 2009 at 10:15 am
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
April 16, 2009 at 10:19 am
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.
April 16, 2009 at 10:32 am
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.
April 16, 2009 at 10:39 am
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.
April 16, 2009 at 11:14 am
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.
April 16, 2009 at 11:18 am
SSMS is not a free tool. It's the tool built by MS and designed to connect to and manage SQL Server. It's installed as part of the client tools, and may or may not be set up on your server.
However, when you connect to an instance with SSMS, it reads data from the system tables, the file system, and the registry on that server to determine how SQL Server is being run or is configured.
You should be using SSMS to administer your servers, not the registry.
April 16, 2009 at 11:39 am
Thanks for all those who tried to help me out.
I guess we are going away from the topic. I have SQL Server Management Studio on all of the servers and I'm using it to administer the DB's. I guess you are referring Mangement Studio as SSMS? Is that right?
I will keep you posted once I see any difference with reg entries / sql server logs once I change it to 'Successful and Failed Logins'. Still I am not able to understand my current settings.
Thanks,
Philip.
April 16, 2009 at 12:23 pm
SSMS is SQL Server Management Studio. That's correct.
I think the confusion is that SSMS should be the source for verifying how things are set. Not necessarily the registry as you might not get the correct key matched to the correct setting.
March 22, 2012 at 7:49 pm
Sorry to dig up an old thread, but this thread was returned by Google when I was looking at getting the same information, and I have something to add to assist anyone else stumbling across this tread looking to read the AuditLevel via script.
Use xp_instance_regread instead of xp_regread when looking for info in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer branch of the registry, it will sub in the correct instance syntax.
DECLARE @AuditLevel int
EXEC master..xp_instance_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
@value_name='AuditLevel',
@value=@AuditLevel OUTPUT
SELECT @AuditLevel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply