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


Trouble Enabling SQL Audits


Trouble Enabling SQL Audits

Author
Message
jayoub
jayoub
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5743 Visits: 713

Trouble Enabling SQL Audits

I am running SQL Server 2014 SP2 CU8 on Windows Server2012

I am trying to enable an SQL Audit that writes to theWindows Security Log and shuts down the instances upon failure.

I am using the Virtual Service accounts NTAUTHORITY\MSSQL@instanceName to run the SQL Server Service

I have added the virtual service accounts to the LocalSecurity Policy Generate security audits and I have given the account fullpermission to the “Security” folder in the registry

HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security

The problem I am having is when I try to enable the auditthe service stops which is often normal, but when I start the service againaudit is disabled – It will not stay enabled. The only errors in the SQL Error Logs are

Source spid55

Message

Audit: Server Audit: 65537, Initialized and AssignedState: START_FAILED

-------------

Source spid55

Message

Audit: Server Audit: 65537, State changed from:START_FAILED to: TARGET_CREATION_FAILED

In some cases I have 3 instances on a box and two willenable just fine and one does not. Onone server with multiple instances none of them will enable.

Any ideas of where to look for the errors or what to dois appreciated.



Jeff
goher2000
goher2000
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3890 Visits: 1600
not sure where to look, but may be you can create a startup procedure to ensure audit is enabled when sql comes up, I am not sure if it will work you have to test it out



Sue_H
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69407 Visits: 14449
jayoub - Monday, March 5, 2018 8:37 AM

Trouble Enabling SQL Audits

I am running SQL Server 2014 SP2 CU8 on Windows Server2012

I am trying to enable an SQL Audit that writes to theWindows Security Log and shuts down the instances upon failure.

I am using the Virtual Service accounts NTAUTHORITY\MSSQL@instanceName to run the SQL Server Service

I have added the virtual service accounts to the LocalSecurity Policy Generate security audits and I have given the account fullpermission to the “Security” folder in the registry

HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security

The problem I am having is when I try to enable the auditthe service stops which is often normal, but when I start the service againaudit is disabled – It will not stay enabled. The only errors in the SQL Error Logs are

Source spid55

Message

Audit: Server Audit: 65537, Initialized and AssignedState: START_FAILED

-------------

Source spid55

Message

Audit: Server Audit: 65537, State changed from:START_FAILED to: TARGET_CREATION_FAILED

In some cases I have 3 instances on a box and two willenable just fine and one does not. Onone server with multiple instances none of them will enable.

Any ideas of where to look for the errors or what to dois appreciated.



There generally is a lot more logged than this - with the other entries providing a clue of what the issue is. I don't think I've ever seen it be just two lines like that.

Sue



jayoub
jayoub
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5743 Visits: 713
Thanks, I will keep trying.
If anybody can think of something please let me know

Jeff
jayoub
jayoub
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5743 Visits: 713
I think i figured out the problem
A mistake on my part. I actually added the permissions to the EventLog folder in the registry and it should be to the Security folder under it.
I added the permissions today and will try to enable the audit during the next outage, but I am sure it will work.

Jeff
goher2000
goher2000
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3890 Visits: 1600
well I hope it works for you, I personally like to keep the audit data in separate files, the reason being is (1) I can manage audit myself, what ever (purge) policy is defined for event/security/application log will not erase my data otherwise I will have to run after sysadmins to have it restored. (2) it is kind of filter in groups. so it makes it easy when I am looking for something. (3) easy to import to a database if required

I one script on all my instances to setup audit.


begin
set nocount on
declare @server_audit_groups table (saname nvarchar(100))
declare @loc varchar(500)
declare @sql varchar (500)
declare @okay int
declare @saname nvarchar(100)
insert into @server_audit_groups values ('APPLICATION_ROLE_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('AUDIT_CHANGE_GROUP')
insert into @server_audit_groups values ('BACKUP_RESTORE_GROUP')
insert into @server_audit_groups values ('BROKER_LOGIN_GROUP')
insert into @server_audit_groups values ('DATABASE_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_LOGOUT_GROUP')
insert into @server_audit_groups values ('DATABASE_MIRRORING_LOGIN_GROUP')
-- insert into @server_audit_groups values ('DATABASE_OBJECT_ACCESS_GROUP') -- will genrate too much info
-- insert into @server_audit_groups values ('DATABASE_OBJECT_CHANGE_GROUP') -- will genrate too much info
insert into @server_audit_groups values ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_OPERATION_GROUP')
insert into @server_audit_groups values ('DATABASE_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PRINCIPAL_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PRINCIPAL_IMPERSONATION_GROUP')
insert into @server_audit_groups values ('DATABASE_ROLE_MEMBER_CHANGE_GROUP')
insert into @server_audit_groups values ('DBCC_GROUP')
insert into @server_audit_groups values ('FAILED_DATABASE_AUTHENTICATION_GROUP')
insert into @server_audit_groups values ('FAILED_LOGIN_GROUP')
insert into @server_audit_groups values ('FULLTEXT_GROUP')
insert into @server_audit_groups values ('LOGIN_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('LOGOUT_GROUP')
--insert into @server_audit_groups values ('SCHEMA_OBJECT_ACCESS_GROUP') -- will genrate too much info
insert into @server_audit_groups values ('SCHEMA_OBJECT_CHANGE_GROUP')
insert into @server_audit_groups values ('SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OPERATION_GROUP')
insert into @server_audit_groups values ('SERVER_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_PRINCIPAL_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_PRINCIPAL_IMPERSONATION_GROUP')
insert into @server_audit_groups values ('SERVER_ROLE_MEMBER_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_STATE_CHANGE_GROUP')
insert into @server_audit_groups values ('SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP')
insert into @server_audit_groups values ('SUCCESSFUL_LOGIN_GROUP')
insert into @server_audit_groups values ('TRACE_CHANGE_GROUP')
insert into @server_audit_groups values ('USER_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('USER_DEFINED_AUDIT_GROUP')

SELECT @loc = replace(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(255)),'ERRORLOG','')
declare c1 cursor for select saname from @server_audit_groups
open c1
fetch c1 into @saname
while @@FETCH_STATUS = 0
--select @okay = count (*) FROM [master].[sys].[dm_server_audit_status]

--print @okay
--if @okay = 0
begin

set @sql='CREATE SERVER AUDIT [' + @saname +'] TO FILE (FILEPATH = '''
set @sql = @sql + @loc
set @sql = @sql + ''',MAXSIZE = 2048 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF )'
print @sql
--EXEC (@sql)

set @sql = 'alter server audit ['+@saname +'] with (state = ON)'
print @sql


set @sql = 'CREATE SERVER AUDIT SPECIFICATION ['+ @saname +'] FOR SERVER AUDIT ['+@saname +'] ADD ('+@saname +') WITH (STATE = ON)'
PRINT @sql
fetch next from c1 into @saname
end
set nocount off
close c1
deallocate c1
end




jayoub
jayoub
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5743 Visits: 713
Thank you for the reply

I actually wanted to audit to file, but the Security configuration state Audit to the Windows Security Event Log. We actually have an application that archives the security log of the servers, so i don't have to worry about it filling up or dealing with the files.

Jeff
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