Monday, 3 February 2014

Track all critical and granular changes on SQL server with real time alerts

Tracking SQL server changes for auditing is one of the top priorities for any SQL server administrator. Strict regulatory environment in which today’s companies operate necessitates them to give significant attention to security of sensitive and confidential business data. Payment Card Industry (PCI), Health Insurance Portability and Accountability Act (HIPAA), Sarbanes-Oxley Act (SOX) etc. are some of the compliance standards that can ambit your organizational operations making it absolutely necessary to create a safe and secure database environment that can stand scrutiny of these compliance standards. SQL servers, one of the favorite options of IT administrators for storing external customer data or internal business data, requires thorough auditing for all the accesses that are made to it, all changes that are made to server and database level objects and all the changes that are made to permissions.

Native auditing options available with the SQL Server

Till SQL Server 2005, auditing requirement was fulfilled using SQL Trace functionality that has a number of limitations when it comes to auditing. First of all it is a multipurpose functionality which requires a separate tool called SQL Profiler to implement auditing. This tool is not integrated with the main SQL server administration console – SQL Server Management Studio and also has a user interface that is not dedicated entirely for auditing.

With SQL Server 2008, the auditing capabilities of SQL server has been considerably improved by a new feature – SQL Server Audit, which overcomes a lot of auditing limitations faced by users who worked with previous SQL server versions.

Advantages of SQL Server Audit (2008) over SQL Trace

One of the most important aspects of it is the level of granularity at which you can perform the audit. You can target a particular action on a particular Server/Database object by a particular user – such is the level of granularity offered by SQL Server Audit.

Besides, it is, comparatively, very easy to administer, use and view audit data with this tool.

Also, the performance overhead on SQL server is considerably reduced while using this application.

Limitations of SQL Server 2008 Audit functionality

However, despite offering some clear auditing advantages over SQL Trace (SQL Profiler), SQL Server Audit functionality fails to bring cheer on the face of many SQL server administrators because of a number of limitations some of which are inherent to the tool while others originate from the scope and size of auditing required by those administrators:

  • Firstly, it doesn’t provide a central platform to audit all instances of SQL server in the organization; auditing, at most, can be performed at one SQL instance level.
  • Audit data can be stored on the disk or with Windows event logs, which means you need to import it to a new database or use Windows event viewer to see the audit data.
  • It doesn’t offer canned reports, so you will have to use SQL Server Reporting services to generate audit reports.
  • Then, there are, still, performance overheads (if not like SQL Trace) that can be quite annoying especially for very busy servers.

SQL Server auditing with dedicated auditing tools  

Native SQL Server auditing, as we just understood above, could be too complex and time-consuming. Therefore, many of the SQL Server administrators have stopped considering it as an option to rely on native auditing to meet strict regulatory requirements; instead they prefer commercial tools that have been designed for the express purpose of SQL server auditing and overcome all the above-mentioned limitations and offer much value beyond their worth.

They give you the option to perform auditing at organization level, monitoring all SQL servers across organization through a central platform. There are built-in reports to answer all possible audit questions as and when required. When it comes to meeting regulatory compliances, there are preconfigured reports that have been designed keeping in mind audit questions that you usually face.

Audit data storage for the unlimited period of time with the facility to archive old data that you won’t think is required for now; Automated report delivery with latest change data within and outside organization right in the Inbox of all the employees who need to see it; Real-time alerts to apprise administrators of critical changes - these are the usual features that you get with these tools. LepideAuditor for SQL Server is one such commercially available tool. It is also available in free trial version to give it a try before going for the licensed version.


Post a Comment