![\"enable](\"http:\/\/kifarunix.com\/wp-content\/uploads\/2019\/03\/ssms.png\" "\\"\\"")
<\/a><\/p>\nCreate SQL Database Level Audit<\/h4>\n
To create SQL database level audit, navigate to On the window that opens up, set the Audit name, Audit destination (File), Audit File path, maximum Audit file size and leave the other options with default settings. To see a description of each options, check here<\/a>.<\/p>\n Once you are done configuring the Audit options, click Next, navigate to Right click on For demonstration purposes, we selected the:<\/p>\n You can read more about Audit Action Groups and Actions here<\/a>. Once done setting the audit actions and action groups, click Ok to save the audit.<\/p>\n Be careful when selecting the audit actions and groups. Otherwise, you may end end up with millions of audit events.<\/p>\n Now that we have the Audit itself and the database audit specification, they are disabled. To enable the audit, right click on the audit and select Next, enable the database audit specification by right clicking on the database audit and selecting Now that you have created and enabled the database level auditing for your database, run some tests to verify if the events are being captured. To begin with,based on our audit action groups selected above, create and drop a database user and check the audit logs.<\/p>\n Navigate to Security > Logins > Right click on Logins > New Login<\/strong>. On the new window, enter the username, set define the authentication method (Using SQL Server authentication in my case). You can opt to enforce password policy. Under Default database, select the database on which we enabled audit from the drop-down. To map the user to your database, click on User Mappings and check the database you want to map the user to. Click Ok to save the user.<\/p>\n Now, drop the database user created. Navigate to Databases > your-database (card-data in my case) >\u00a0 Security > Users > Right Click<\/strong> on user created > Delete<\/strong> > Ok<\/strong>.<\/p>\n You should now be able to see two events, create and drop user. Navigate to Security > Audits > Right Click <\/strong>on audit you created >View Audit Logs<\/strong>. Expand the log viewer to see more audit log details.<\/p>\n You can also query the audit log file saved under To query all the fields, use To enable server level audit, Navigate to Security > Server Audit Specifications > Right Click<\/strong> on Server Audit Specifications > New Server Audit Specifications<\/strong>. Set the name of the audit, choose the audit created earlier on the drop down. Choose the audit action types. In my case, I would like to see the authentication events to the database. Hence, have selected After that, enable the audit by right clicking the name of the server audit defined > Enable Server Audit Specification.<\/p>\n Perform the three events; successful, failed login and logout to the database. Ensure that you are using the SQL authentication method to login to the database.<\/p>\nSecurity<\/code> > Audits<\/code>. Right click on Audits and click New Audit<\/code>.<\/p>\n<\/a><\/p>\n<\/a><\/p>\nOk<\/code> to save it.<\/p>\nDatabases<\/strong><\/code> > Expand database you want to enable audit (card-data in my case)<\/code> > Security<\/code> > Database Audit Specifications<\/code><\/strong>.<\/p>\nDatabase Audit Specifications<\/code> and click New Database Audit Specifications<\/code> to create new database audit. On the window that opens up, set the name of the database audit, Select the Audit you created above from the drop down. Next, select the audit type actions.<\/p>\n<\/a><\/p>\n\n
DATABASE_PRINCIPAL_CHANGE_GROUP<\/code> which triggers an event when principals such as users are created, altered or dropped from a database.<\/li>\nSCHEMA_OBJECT_CHANGE_GROUP<\/code> which triggers an event when a CREATE, ALTER, or DROP operation is performed on a schema.<\/li>\n<\/ul>\nEnable Audit<\/code>.<\/p>\n<\/a><\/p>\nEnable Database Audit Specification<\/code>.<\/p>\n<\/a><\/p>\nTest Auditing<\/h3>\n
<\/a><\/p>\n<\/a><\/p>\nC:\\Audits\\CardData<\/code>. The query below selects specific fields of the log file.<\/p>\nselect event_time, action_id,server_principal_name,database_principal_name,database_name,object_name,statement,client_ip,application_name\r\nfrom sys.fn_get_audit_file ('C:\\Audits\\CardData\\*.sqlaudit',default,default) order by event_time DESC<\/code><\/pre>\n<\/a><\/p>\nselect * from sys.fn_get_audit_file ('C:\\Audits\\CardData\\*.sqlaudit',default,default)<\/code>.<\/p>\nServer-Level Audit<\/h3>\n
SUCCESSFUL_LOGIN_GROUP<\/code>, FAILED_LOGIN_GROUP<\/code> and LOGOUT_GROUP<\/code>.<\/p>\n<\/a><\/p>\n