:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

Auditing in SQL Server 2012

Introduced with SQL Server 2012 is the SQL Server Audit feature. This long-overdue feature now adds a great native auditing functionality into the SQL Server DB Engine. An audit is the combination of several elements into a single package for a specific group of server actions or DB actions. The modules of SQL Server Audit combine to produce an output that is called an audit. The SQL Server Audit feature in SQL Server 2012 is intended to replace SQL Trace as the preferred auditing solution. SQL Server Audit is meant to provide full auditing capabilities, and only auditing capabilities, unlike SQL Trace, which also serves for performance debugging. The SQL Server Audit feature is built on top of Extended Events, a new high-performance eventing infrastructure introduced in SQL Server 2012. SQL Server Audit leverages the performance benefits of Extended Events, which offers both asynchronous and synchronous write capabilities (by default, SQL Server Audit uses the asynchronous eventing model).

SQL Server Audit is also tightly integrated with the Windows OSs and can push (write) its audits to the Windows App or Security Event Log. With SQL Server Auditing, you can set up auditing of just about any event or execution within SQL Server, and it can be as granular as you need (right down to a table and operation level). This ability is vital because not only can you track all these events, but you can use this auditing ability to fulfill app and DB audit compliance and look for patterns of misuse, or even specific "hot " objects that contain the most sensitive data in your DB. a branch under each DB called Security contains several of the common security-related nodes that you've seen before (Users, Roles, Schemas, and so on) . But now, there is a DB Audit Specifications branch from which you can set up and view the DB audit specifications you have defined. You can have as many specifications as you want, and again, they can be at varying levels of granularity.

Before you can set up a DB Audit Specification, however, you need to first set up a SQL Server Audit object. To do this, you need to use a couple of new entries in the Object Explorer under the Security folder at the SQL Server level: Audits and Server Audit Specifications.

Essentially, three main objects describe audits in SQL Server 2012:

• The Server Audit object—Used to describe the target for audit data, plus some toplevel config settings. This destination can be a file, the Windows App log, or the Windows Security log. The Server Audit object contains no info about what is being audited—just where the audit data is going. Multiple Server Audit objects can be defined with each object independent from one another (that is, they can each stipulate a different destination).

• The Server Audit Specification object—Used to describe what to audit at a server instance-wide level. A server audit specification need to be associated with a Server

• Audit object to define where the audit data is written. There is a one-to-one relationship between the Server Audit Specification object and the Server Audit object.

• The DB Audit Specification—Used to describe what to audit at a specific DB level. Where the audit data is written is determined by the Server Audit object it is associated with. Each DB audit specification can be associated withonly one server audit. A server audit can be associated with audit specifications for multiple DBs, but only one DB audit specification per DB.

To create a new Server Audit object, RightClick on the Audits item in Object Explorer and choose New Audit > When you set up a Server Audit object, you stipulate where the audit info will be written to. you can see that we are creating a server audit named NEW_SQL_Server_Audit and are defining it to use the App log at the Windows OS level as the audit destination. You can also choose to write to the Windows Security log or to a text file. Events written to the App or Security Event log can be accessed with Event Viewer or with dedicated Event log management software, such as MS Systems Center Operations Manager.

After you set up the Server Audit object, the then step is to go to the DB Audit Specifications folder, in the DB for which you can want to set up auditing. RightClick this folder and choose New DB Audit Specification to bring up the dialog. This is where you define your DB-level audits. In the Create DB Audit Specification dialog, you stipulate the name of the DB Audit object and the Server Audit object it will be running under. In this example, the DB Audit name is NEW_DB_Audit_Specif ication, and it will be running under the NEW_SQL_Server_Audit Audit object defined in  13.9. In this example, the DB audit is being set up to audit any CHOOSE statements (reads) run against the Employee table (which, of course, contains company-sensitive employee data) by any user (public). At this point you have produced a Server Audit object and DB audit specification associated with the server audit. However, neither of these audits is enabled. You can enable them by RightClicking on each and selecting Enable. As soon as the Server Audit object is enabled, it begins auditing and writing audit records to the specified destination (in this example, the Windows App log).

You can review the details by RightClicking on the Server Audit and selecting View Audit Logs or, if you are auditing to the Windows App or Security Event Log, by opening the Windows Event Viewer directly. One of the advantages of opening the Audit log from within SSMS is that it automatically filters the log to show only SQL Server Audit events. you can see that we've opened the Log File Viewer and selected to view the App log (where we directed our SQL Server Audit to go). A few CHOOSE statements were run against the Employee table and, sure enough, the audit info of the CHOOSE statements displays up in the App log. Within the Log File Viewer, you can filter your audit results or search them to look for patterns, specific violations, and so on. From the Log File Viewer, you also have the option of exporting the audit logs to a text file or to a comma-separated values (CSV) file. With a CSV file, you could import the audit logs into a DB for further analysis and correlation. It's up to your security and audit team to decide how these audits are to be used. In addition to DB-level auditing of actions at the DB level, you can also set up auditing of server-level events, such as management changes and logon and logoff operations. These are set up in the SSMS Object Explorer throughout the Server Audit Specifications item in the Security folder for the SQL Server.