Keeping sensitive data secure, easily audited, and in compliance with the latest privacy and data-collection laws has become an increasingly challenging task for DBAs and IT managers. Corporate-governance laws and initiatives—such as Sarbanes-Oxley (SOX), Gramm-LeachBliley (GLBA), and the Payment Card Industry (PCI) Compliance initiative—have made the difficult task of enterprise data management even more so. Many corporate-governance laws have severe penalties for non-compliance, including fines and possible jail time for company executives. And many tasks related to complying with such requirements are falling squarely on the data side of the enterprise, which translates into more work for data managers. We'll take a look at the demands auditing and compliance are placing on DBAs and explore how SQL Server 2005 and available software and hardware solutions can help meet some of those challenges. (Also see the sidebar, "Auditing and Compliance Features in SQL Server 2008," for a quick look at the auditing and compliance support that SQL Server 2008 will provide.)
Identify Data to Be Audited
Compliance demands are putting pressure on companies of all sizes. As JC Cannon, a privacy strategist in Microsoft's Corporate Privacy Group, explains, "Some enterprises are deciding to take the safe route… and decide to save everything forever, just to ensure that they have all the information needed for the audit process." Cannon says that those same pressures are pushing Microsoft to develop features in future versions of their products that help beleaguered DBAs and IT managers more easily and effectively meet their auditing and compliance needs.
If you've just been handed the task of ensuring that your company's data environment is adhering to the latest auditing and compliance law, where do you begin? According to Bryan Bain, SoftTree Technologies' director of sales and marketing, your first step should be to look at the big picture. Bain says that one of the biggest mistakes an IT manager can make is to focus on architectural solutions to compliance issues before determining what information a company needs to satisfy requisite audit processes.
"Enterprises should be most concerned about identifying what exactly needs to be audited," explains Bain. "We've seen cases of companies believing that they needed to put an auditing infrastructure in place for every database in the entire company. One customer was looking at auditing more than 800 databases, which would have been a Herculean task that would have taken months to deploy. They eventually learned that fewer than 60 of those databases contained the data they needed to satisfy their SOX compliance needs."
Bain says that IT should work closely with auditors early in the process to determine exactly what data those auditors are looking for and who needs to see what types of information. Microsoft's Cannon concurs: "Some companies don't do a proper assessment of what they have and where all their data is. They need to evaluate the sensitivity of the data and look at data permissions. For example, some people may have access to certain parts of a table but not the entire table. Find out where the sensitive data is and ensure that the right data is protected when it needs to be." Proper information gathering and solid planning done early in the process can save months of work.
Basic Auditing Using SQL Server
Once you've determined your overall auditing and compliance needs, the next step is to evaluate your current infrastructure and determine whether it can provide the auditing and compliance solutions you require. For smaller companies and those exempt from some compliance laws and regulations, that solution may largely rest on using the inherent capabilities of SQL Server 2005.
According to Al Comeau, Microsoft SQL Server security lead, SQL Server 2005 already has some basic features that can assist with meeting auditing and compliance demands. "If someone doesn't need to dig too deeply into custom auditing, they can turn on C2 audit mode in SQL Server Management Studio," says Comeau. "\[Doing so\] provides for a default audit that can always be running... \[and\] provides an audit trail that records all attempts (both successful and unsuccessful) to access objects, statements, and other aspects of the audited database." (For more security tips from Comeau and other members of Microsoft's SQL Server team, see the Web-exclusive sidebar, "SQL Server 2005 Security Tips.")
SQL Server 2005 SP2 also introduced another useful auditing feature: the login trigger. According to Niraj Nagrani, a Microsoft senior product manager, you can customize login triggers to perform a wide range of functions. "You can control who logs into the database and also when they log in. SP2 triggers also allow you to implement time-of-day, time-of-week, and other restrictions." Narani explains that using login triggers can help enforce other auditing and compliance-related controls, such as restricting access to certain usernames and creating records of connection activity.
Even with SQL Server 2005's built-in features that support auditing and compliance, some DBAs might find that they have little knowledge or experience with implementing auditing functionality, or perhaps the specific auditing and compliance demands on their enterprise can't be met by using SQL Server alone. In either case, DBAs can turn to an emerging market of third-party software and hardware solutions that extend SQL Server 2005's auditing and compliance capabilities.
Third-Party Auditing and Compliance Solutions
Several auditing and compliance products are geared toward enterprises that use only SQL Server 2005 (as opposed to multiple database platforms). Two of the most widely used products in this category are Idera's SQL compliance manager and ApexSQL Tools' ApexSQL Audit. Idera President and CEO Rick Pleczko touts the speed of the company's software solution, noting that enterprises are often wary of vendor software that has the potential to degrade the performance of their live servers. "Our design goal was to keep our overhead on the server to less than 5 percent," says Pleczko. "We typically don't see any of our customers exceed 2 percent... even when fully loaded." Pleczko sees Idera's focus on only SQL Server 2005 as a competitive benefit and notes that the company has licensed technology from Microsoft to make sure Idera's product offerings minimize the impact on database performance.
ApexSQL President Brian Lockwood stresses that while some customers may go the route of using a collection of database tools to address their needs, others are looking for an integrated solution for auditing and compliance needs. "\[Standalone\] log readers aren't really the best auditing tools," says Lockwood. "Most people want a more sophisticated (and integrated) auditing solution."
Lockwood explains that ApexSQL Audit has robust support for auditing using Data Definition Language (DDL) triggers and provides templates to streamline and simplify the creation of those triggers. It also integrates with a standalone ApexSQL Audit Viewer. Like Idera, ApexSQL has focused on developing applications solely for SQL Server and supports SQL Server 2005, 2000, and 7.0.
But what if your company supports more than one enterprise database application? Auditing solutions exist for heterogeneous environments as well, often with support for leading platforms such as Oracle, Sybase, and IBM DB2. Offerings in this category include DB Audit 3.2 from SoftTree Technologies, Lumigent AuditDB 6.0, and Quest Software's InTrust for Databases series of audit-related applications. All these products work on multiple platforms and use unified management consoles that provide a common interface across those platforms. This integration can help IT address compliance concerns across a disparate enterprise where multiple database architectures are in use.
Lumigent Technologies' Senior Director of Product Marketing Mike Puglia stresses that not only is supporting multiple platforms desirable for some enterprises, but integrating that support with multiple auditing approaches—all in one vendor solution—is often desirable. "Our AuditFlex architecture is platform agnostic," explains Puglia. "It also combines transaction-log reading, native auditing, and network capture into one auditing solution. We support all three methods and let the customer choose which of those methods is most important to them."
Yet another database auditing and compliance solution comes in the form of network hardware appliances that also provide database protection and monitoring functions. Products such as Guardium's SQL Guard Security Suite (with AuditGuard software module), Tizor Mantra, and Imperva's SecureSphere Database Monitoring Gateway all provide standalone devices that promise to simplify certain auditing, monitoring, and security tasks. On the security side, some enterprises have also turned to appliances that more heavily emphasize perimeter defense, such as Xceedium Gatekeeper.
Since many of these appliances are installed separately from the database server, their use doesn't affect performance of the database itself. Hardware vendors also tout their plug-and-play convenience.
Hardware or Software?
Although hardware solutions promise to provide less of an impact on database performance, software vendors say that argument is flawed, pointing out that improper use of full native SQL Server 2005 transaction logging and auditing has created a misconception that all software-based auditing slows system performance significantly.
Lumigent's Puglia argues that correctly installed software-based auditing solutions can be configured so that they only minimally affect server performance. Hardware-appliance vendors can also have a software footprint, Puglia says, pointing out that Guardium's software tap (S-TAP) and Tizor's Enterprise-Tap (E-TAP) are both software applications that need to be installed on host machines to track database access by local, privileged users who don't go on the network. Without those software tools running on a host machine, the appliances couldn't track access by local users.
The proliferation of separate network appliances that provide security, auditing, load balancing, and other features can lead to crowded rack space. Hardware vendors argue that some of the proliferation is a necessary evil, since the other option—installing the software equivalent of those features on a host machine—would bring system performance to a crawl.
"Many of the functions that hardware appliances offer can't easily be consolidated into one unit," says Imperva CTO Amichai Shulman. "Perimeter security, enduser security, database auditing, and network operations… these are all areas that different people in different departments may need to access, often with different security levels and permission levels." Shulman argues that functionality might eventually be consolidated on some devices, but a host of compliance, security, infrastructure, and privacy demands preclude the development of a single appliance that can solve every problem by simply plugging it into the network.
No Silver Bullets
Regardless of the set of solutions you choose, many vendors caution users to avoid looking for a single, "super product" that promises to solve every auditing, compliance, and security need in every instance. A poorly implemented software solution can seriously degrade system performance, whereas reliance solely on hardware solutions can lead to rack-space congestion and other problems.
Vendors do agree that IT needs to approach auditing and compliance with the specific auditing needs of their enterprise first, then spend some time talking to vendors, attending Web seminars, and trying out products. Perhaps most importantly, all agree that even the best products can't replace the solid research, detailed planning, and sound business processes that need to be in place long before the first purchase order for an auditing solution is issued.
"It all starts with good business processes," says Bill Bartow, Tizor's vice president of marketing. "A mistake some companies make is to simply automate currently existing processes, which might not be the optimal solution for the auditing and compliance needs of that organization—then you're just automating bad processes. Start with your business process and policies, and make sure you've gone through them from a solid data-governance perspective."
Database Auditing and Compliance Products Mentioned in This Article
ApexSQL Audit, ApexSQL
DB Audit 3.2, SoftTree Technologies
InTrust for Databases, Quest Software
Lumigent AuditDB 6.0, Lumigent
SecureSphere Database Monitoring Gateway Imperva
SQL compliance manager, Idera
Xceedium GateKeeper, Xceedium