March 5, 2018 at 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
|
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
March 5, 2018 at 10:17 am
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
March 5, 2018 at 12:10 pm
jayoub - Monday, March 5, 2018 8:37 AMTrouble 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
March 5, 2018 at 5:33 pm
Thanks, I will keep trying.
If anybody can think of something please let me know
Jeff
March 9, 2018 at 10:59 am
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
March 9, 2018 at 1:17 pm
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
March 11, 2018 at 2:38 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply