In today’s world Auditing has become very important to control and detect the changes to the IT Systems, which may be planned or unplanned. SQL Server databases store important end user data and is often secured from unauthorized access, but there can be instances where there are unexpected changes to the system configuration to get access to the secure data or there can be cases where changes are made to the system and difficult to identify who made the changes. Proactive Auditing helps in identifying the changes made to the system and take corrective steps, thus can provide more control on the systems.
Auditing SQL Server Instance:
SQL Server 2005 was released around 15 years back and has limited options for built-in Audit solutions. With latest versions of SQL Server 2008 or higher there are better in-built Audit solutions. We can enable auditing in SQL Server using various methods including built-in options audit options, Customize existing features and build an audit solution which most of the third-party software’s does.
Below are some of the methods which we can use to Audit changes to SQL Server instances.
Triggers in SQL Server are fired when a certain event occurs and we can define a certain action to be taken like logging the change made into a table. DDL triggers are fired when a change is made using commands like ALTER, CREATE or DROP.
Advantages: DDL triggers allow us to specify an action to be performed when a particular event occurs. This is suitable for scenarios where we need to audit specific events, but not all DDL changes.
Dis-Advantages: DDL triggers cannot be used to track all changes to the SQL Server instance. Implementing DDL triggers require lot of customization and will be very huge task and can also result into issues when implemented without proper planning and testing.
SQL Server Server-Side Trace:
Using the SQL Server Traces, we can audit or track almost all activity that takes place on SQL Server instance. Trace data will be stored in a trace file and we can import the trace data into tables periodically and create Scripts to read the trace data and extract the required information.
Advantages: We can track all the activity that happens on the SQL Server instance. We can filter the captured events to trace only required data.
-Running trace on system can cause additional overhead on the system and the performance overhead depends on the number of events traced and the activity that happens on the server. Often server side trace overhead in not much, as most of the third party solutions internally rely on the traces.
-There is lot of customization required where we need to configure the trace to capture required data and then periodically import the data into tables and run queries against those tables manually or can run the queries through jobs which run on schedule to send the report via e-mail.
SQL Server Audits:
SQL Server Audit feature was introduced with SQL Server 2008. SQL Server audit lets one create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.
Advantages: This is a built-in feature, it is simple to enable and manage and no additional customization is required. Audit results can be saved to event logs or audit files, which can be referenced later. Audit can be enabled at different levels and for different issues.
Dis-Advantages: Database level auditing is limited to Enterprise, Developer, and Evaluation editions. There is no option to send an e-mail when a certain condition is met, so need to customize that piece.
SQL Server Extended Events:
Starting SQL Server 2008 R2 and higher, Extended Events can be used to track the changes that happen in SQL Server at granular level without much overhead. Extended events collect all the information similar to SQL Server Traces, and also additional details at more granular level.
Advantages: We can track all the activity on a SQL Server instance at more granular level. We can filter events based on our interest. Performance overhead is very minimal.
Dis-Advantages: This requires lot of customization for creating and managing.
Other In-built Options:
There are few other rarely used built-in options like C2 Audit, Common Criteria Compliance and Default Trace is available, but they target specific requirements, but has limited options and customization as per our requirement is not supported.
Third-Party Audit Tools:
Most of the organizations rely on third-party audit tools as they do not need much customization and can be configured to generate and send reports by e-mails. Advantage of the third party tools is that they are tested and used by many organizations already and documentation is available to use the tools. Disadvantages of third-party audit tools include additional cost and additional overhead of configuring and managing these tools.
Some of the popular third party audit products for SQL Server instance are Idera SQL Compliance Manager, ApexSQL Comply, Change Auditor, etc.
Idera SQL Compliance Manager is one of the most used products for SQL Server audits and compliance. Idera SQL Compliance Manager provides a comprehensive SQL Server auditing that can help us monitor, audit and alert SQL Server user activities and data changes. We can get detailed information about who did what activity and when it was performed, and how the changes were implemented, etc. We can use it to track changes, monitor and audit data access, schema changes and login failures. There are many built-in reports available to validate SQL Server audit trails. A DBA can configure alerts to get notified of suspicious activity. Idera SQL Compliance Manager can help you ensure compliance with regulatory and data security requirements.
More information about Idera SQL Compliance Manager can be found at https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager
ApexSQL Comply is another popular tool for SQL Server audits and compliance. This tool supports tracking data changes to SQL Database. It can capture and store data changes to a central database and tracks, who did what activity and when it was performed, and how changes were implemented. This tool supports many audit reports that are run from the central database. It can also inform a user about how and when an incident occurred, capturing relevant information such as which SQL or Windows host and application name was involved. Finally, this tool can also track table definition changes and unauthorized or malicious changes.
More information about ApexSQL Comply can be found at
Quest Change Auditor for SQL Server tracks, audits, reports and alerts you to any changes to our environment in real time. This tool is capable of tracking all user and administrator activity, such as who made what kind of changes, when the changes were implemented and from which work station changes were carried out. This tool monitors for critical change within the environment, and we can configure to get alerted in real time whenever there are critical changes to your environment. Since this tool maintains a centralized repository, it makes it easier to monitor multiple servers from a single location. It also supports role-based access, thereby allowing auditors to run searches and reports without the ability to make any configuration changes to the application.
More information about Change Auditor can be found at
There are many other third party products available for Auditing SQL Server and can be carefully evaluated and choose the best one based on the features provided, manageability and cost.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.