{"id":1143,"date":"2011-07-31T20:04:52","date_gmt":"2011-07-31T14:04:52","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1143"},"modified":"2016-01-07T11:31:06","modified_gmt":"2016-01-07T05:31:06","slug":"a-glance-at-sql-server-audit","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1143","title":{"rendered":"A glance at SQL Server Audit"},"content":{"rendered":"

Microsoft does few things right and one such thing was SQL Server Audit. Being audit compliant is not an option for most businesses anymore. \u00c2\u00a0Previous to SQL Server 2008, SQL Server audit involved setting up login audits, sql trace a.k.a sql profiler, DDL triggers and so on. Starting from SQL Server 2008, Microsoft decided to include a new feature called SQL Server Audit to be more in line with the compliance arena. \u00c2\u00a0While SQL Server 2008 Standard edition provides the SQL Audit Foundation (basic), the Enterprise edition provides a very robust and detailed audit capability.<\/p>\n

The Enterprise Edition lets you setup \u00c2\u00a0both at the instance and database level auditing. Even though Microsoft claims a significant performance improvement on SQL Server Audit vs SQL Trace, any sort of audit generates I\/O and lots of them. \u00c2\u00a0It takes storage to store all these audit logs as well. \u00c2\u00a0Use due diligence \u00c2\u00a0when choosing what all you want to monitor. I suggest you make a list of all the different actions you want to audit and then write down what all audit action groups<\/a> you want to use.<\/p>\n

Setting up audit in sql server is a 2 step process unlike the DB2 audit process I mentioned in an earlier post<\/a> . \u00c2\u00a0 Setting up sql server audit begins with \u00c2\u00a0opening SQL Server Management Studio and connecting to the instance you want to audit :<\/p>\n

1. Creating Server Audit Object<\/span>
\nThe\u00c2\u00a0Server Audit\u00c2\u00a0object defines the destination of the audit files and some high level configuration such as the delay queue, the maximum size of the audit files and so on. Once you define the server audit objects, you need to enable the audit. You can create multiple server audit objects to group similar type of audits for different purpose. To configure, right click Audit under the Security options and select ‘New Audit<\/span><\/em>‘. \u00c2\u00a0Ensure that the destination you choose have enough storage to hold the audit data until your audit reporting software can pick it up. Also setup the options to clean up the files.<\/p>\n

\"\"<\/a> .\u00c2\u00a0\"\"<\/a><\/p>\n

2. Creating Server or Database specifications
\n<\/span>As the name suggests, in this step you configure the actions you want to monitor. You can find Server Audit Specification under the Security options. Right click and choose ‘New Server Audit Specifications<\/span><\/em>‘. Here you can choose all the actions that you want to audit at the server (instance) level and link it to the server audit objects you created in the first step.
\nTo configure the database audit, open the database<\/span> you want to audit and find the Database Audit Specification under the Security option. Right click and choose ‘New Database Audit Specifications<\/span><\/em>‘. Choose all the actions you want to audit against the database objects and also choose the userids you want to audit.
\nUse this link <\/a>to select the appropriate action type for Server and Database .<\/p>\n

Once you have defined \u00c2\u00a0either or both the audit specifications, right click on each of them and enable them to start the audit.<\/p>\n

\"\"<\/a> \"\"<\/a><\/p>\n

Now that you have enabled the audits, you can\/should review the audit logs to ensure that the audit is collecting all the information you needed. \u00c2\u00a0To view the logs, you can either go to the event view on the windows server or right click the server audit object you created in step 1 and choose view audit logs<\/span><\/em>.<\/p>\n

\"\"<\/a><\/p>\n

Just like you enabled audits, you can disable audits as well. \u00c2\u00a0I cannot stress enough the importance of carefully selecting the actions you want to audit. Make sure you are auditing only what is required, otherwise you are going to pay the price for I\/O , CPU and storage.<\/p>\n

Some of the links that are useful are:
\n http:\/\/msdn.microsoft.com\/en-us\/library\/dd392015(v=sql.100).aspx<\/a>
\n http:\/\/msdn.microsoft.com\/en-us\/library\/cc280663(v=SQL.100).aspx<\/a>
\n http:\/\/msdn.microsoft.com\/en-us\/library\/cc280472(v=SQL.100).aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Microsoft does few things right and one such thing was SQL Server Audit. Being audit compliant is not an option for most businesses anymore. \u00c2\u00a0Previous to SQL Server 2008, SQL Server audit involved setting up login audits, sql trace a.k.a sql profiler, DDL triggers and so on. Starting from SQL Server 2008, Microsoft decided to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,358,658],"tags":[672,673,671,670,668,306,669,674],"class_list":["post-1143","post","type-post","status-publish","format-standard","hentry","category-database","category-how-to","category-sql-server-2","tag-audit-action-group","tag-database-audit","tag-database-audit-specifications","tag-server-audit-specifications","tag-sql-audit","tag-sql-server","tag-sql-server-audit","tag-sql-server-compliance"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1143"}],"version-history":[{"count":9,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1143\/revisions"}],"predecessor-version":[{"id":1499,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1143\/revisions\/1499"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}