In this post I will show how to setup SQL logon auditing. You will have to complete a number of steps before you have a complete auditing. Beware that logging all logon events can fill up your database. This post includes the following steps:<\/p>\n
Configure SQL to audit logon events<\/strong><\/p>\n Login auditing can be configured to write to the error log on the following events.<\/p>\n To configure login auditing<\/p>\n Configure Ops Mgr to collect logon events<\/strong><\/p>\n You will need some suitable class to target your rule to. If you import the SQL MP you will get a number of\u00c2\u00a0SQL classes.<\/p>\n To collect also failed logon you need to create a rule to collect event ID 18456.<\/p>\n You can use the Effective Configuration Viewer from the Ops Mgr 2007 resource kit to verify if\u00c2\u00a0your new rule affect a SQL machine. You can also create a new event view, to show these events,\u00c2\u00a0after a couple of minutes you should see them.<\/p>\n Create a report to show the collected data<\/strong><\/p>\n There is no report model in Ops Mgr by default, so if you want to create a brand new report, you\u00c2\u00a0must start with create\u00c2\u00a0a report model. You can do that with SQL Server Business Intelligence Development Studio. Take a look at Jonathan Hamb step by step guide how to create a report model here<\/a>.<\/p>\n What you can do, with default reports, is a linked report. A linked report is like a shortcut to a program, it is a link that provide settings that inputs in a existing report. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.<\/p>\n To create a new report, for example a report showing all success logon events,<\/p>\n Now you can see a report with all Sucess Audit. You can now click the File menu and choose Publish. This report will now be stored as a linked report under Authored Reports. The next time you want to see success logons to SQL you can click this report direct in the console, and all the parameters will be there.<\/p>\n If you select MSSQLSERVER as source and 4 as event type, you will get both Failure and Success audit events, but\u00c2\u00a0you must first make sure you have rules to collect them both.<\/p>\n","protected":false},"excerpt":{"rendered":" In this post I will show how to setup SQL logon auditing. You will have to complete a number of steps before you have a complete auditing. Beware that logging all logon events can fill up your database. This post includes the following steps: Configure SQL to audit logon events Configure Ops Mgr to collect … Continue reading \n
\n
\n
\nEvent ID equals 18453
\nEvent Source equals MSSQLSERVER
\nClick Create<\/li>\n<\/ol>\n\n