I'm currently working with a customer using NW 10.6.4 and we are collecting a MS SQL 2010 audit events via Windows Collection (We have configured the audit of the SQL Server to write the audit log in the Windows Event Application Channel). Everything works fine but, we focused on two types of events: Adding a user to SQL and Add a user to a SQL Role. Here is an example of both events (modified to not show customer data):
%NICWIN-0-Application_33205_MSSQL$TESTINSTANCE: Application,rn=1262009 cid= eid=,Tue Feb 20 18:41:54 2018,33205,MSSQL$TESTINSTANCE,,,TESTHOST.lab.local,None,,Audit event: event_time:2018-02-20 18:41:54.5892291 sequence_number:1 action_id:CR succeeded:true permission_bitmask:0 is_column_permission:false session_id:183 server_principal_id:2 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:570 class_type:SL session_server_principal_name:LAB\ADMIN server_principal_name:LAB\ADMIN server_principal_sid:0105000000000005150000006d622f30dd31d744dc16ee12c5050100 database_principal_name:dbo target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:TESTHOST\TESTINSTANCE database_name:master schema_name: object_name:AddedUserName statement:CREATE LOGIN [AddedUserName] WITH PASSWORD=N'******' MUST_CHANGE, DEFAULT_DATABASE=[Delivery], CHECK_EXPIRATION=ON, CHECK_POLICY=ON additional_information: .
%NICWIN-0-Application_33205_MSSQL$TESTINSTANCE: Application,rn=1262046 cid= eid=,Tue Feb 20 18:45:36 2018,33205,MSSQL$TESTINSTANCE,,,TESTHOST.lab.local,None,,Audit event: event_time:2018-02-20 18:45:35.9404669 sequence_number:1 action_id:APRL succeeded:true permission_bitmask:0 is_column_permission:false session_id:182 server_principal_id:2 database_principal_id:1 target_server_principal_id:0 target_database_principal_id:0 object_id:0 class_type:SG session_server_principal_name:LAB\ADMIN server_principal_name:LAB\ADMIN server_principal_sid:0105000000000005150000006d622f30dd31d744dc16ee12c5050100 database_principal_name:dbo target_server_principal_name:DestinationUserName target_server_principal_sid:1fb7d86d5c11f344bfe4695b210f30cb target_database_principal_name: server_instance_name:TESTHOST\TESTINSTANCE database_name:master schema_name: object_name:securityadmin statement:EXEC master..sp_addsrvrolemember @loginame = N'DestinationUserName', @rolename = N'securityadmin' additional_information: .
When both events are parsed, the user that is the destination of the action (AddedUserName and DestinationUserName) are parser in fld46 (I have checked that using the Netwitnes Log Parser tool). In the first case, it doesn't realy matters, because that data is merged as Object metadata, but in the second event, the object metadata is used to put the assinged role, and I don't have any metadata that refers to the user added.
Does anyone have any suggestion to resolve this?
Regards,
Max
