Key features to create a SQL Server audit trail in databases
SQL Server offers a set of built-in auditing tools that can help make the process of tracking logins and other database activities easier for database administrators.
Regardless of the size of your organization, there's always a wide variety of items to keep track of in database systems like SQL Server -- user logins, data access, security protections and more. Collecting and maintaining information for database audits can be a daunting task, but SQL Server provides tools to help make it manageable, according to IT architect and author K. Brian Kelley.
In a recent webinar, Kelley discussed the auditing features that come with the database software and how database administrators (DBAs) can utilize them to create a SQL Server audit trail that documents the operational minutiae in systems for security, regulatory compliance and administration uses.
One of the many auditing tools that SQL Server offers is the default trace. That feature is in maintenance mode, and Microsoft doesn't recommend using it in new applications. But it can still provide some useful information, according to Kelley, a former SQL Server DBA who currently works as a data architect at AgFirst Farm Credit Bank in Columbia, S.C.
For example, the default trace mechanism can be used to audit user logins to check for application problems or potential security issues. "Out of the box, SQL Server makes it easy to catch failed logins," thanks largely to the default trace, Kelley said.
The default trace's main job is to capture all changes to SQL Server's underlying configuration and schemas on a temporary basis. Kelley noted that the feature's name is a bit of a misnomer, as it actually consists of four or five small trace files. SQL Server rolls over these files as needed when their storage capacity fills up. As a result, the default trace is essentially a rolling set of audit records, he said.
Extended Events offers a new audit track
Out of the box, SQL Server makes it easy to catch failed logins.
K. Brian Kelley Data architect, AgFirst Farm Credit Bank
For auditing in new applications, Microsoft recommends using its SQL Server Extended Events performance monitoring tool instead of the default trace feature. Extended Events was introduced in SQL Server 2008 to reduce the processing impact of database performance tuning and auditing processes. The technology became more useful after a set of GUIs was added to it in SQL Server 2012.
Similarly, Kelley stressed the importance of using Extended Events to create customized traces for a SQL Server audit trail instead of running SQL Server Profiler or server-side traces, two other older auditing mechanisms.
Users can still build client-level traces with SQL Server Profiler and server-side traces with SQL Trace, but Microsoft has deprecated those tools. Traces built with them tend to be fairly heavy on the performance side, Kelley said, and they were replaced by Extended Events in SQL Server 2012. "If you want to audit things that are new features in any of the versions after SQL Server 2008 R2, the only way you can audit for it, the only way you can detect it and use it, is with Extended Events," he noted.
Options for recording database logins
In the case of logins, SQL Server is automatically configured to record all failed ones, but users can customize it to fit their needs, Kelley said. The system can be reconfigured to track all successful logins or both failed and successful attempts. If you have an alternative tracking system, you can even disable SQL Server's mechanism.
Kelley warned, though, that caching successful logins on a busy system would be difficult because the event log would quickly start overwriting the data.
Changing SQL Server's settings for auditing logins is relatively easy, Kelley said. First, go into SQL Server Management Studio and right-click on Server. Choose Properties, then select Security. The Security tab allows you to choose between four login auditing options -- None, Failed logins only, Successful logins only or Both failed and successful logins. SQL Server needs to be restarted after any changes are made; otherwise, it won't collect the data the way you want it to, he said.
Alternative auditing tools
Additional auditing mechanisms available to DBAs include Policy-Based Management, which Kelley described as "basically group policy for SQL Server." The Policy-Based Management feature can be used to enforce policy settings in SQL Server's database engine, but he said it's best utilized as a "check system," as it can verify almost anything in a SQL Server instance.
Kelley considers Policy-Based Management to be under-utilized by most organizations. There are limits to what the tool can do, though. He cautioned that it's primarily a "detective control," as it only alerts DBAs to problems after they've already occurred. Additionally, Policy-Based Management isn't able to capture who did what in a SQL Server system; it's intended for ensuring that the configuration stays the way you want it to and alerting you when something is changed.
Data definition language triggers and logon triggers can also be used to help build a SQL Server audit trail in database systems. For example, they can be set to fire if someone attempts to log in to or alter a system. But Kelley said triggers should be used with caution, as they can add processing overhead and unexpectedly block processes. "It's often better to just audit for the event happening," and then fix the issue afterwards, he added in the webinar, which was posted on the MSSQLTips website.
Change data capture (CDC) wasn't originally intended for auditing, according to Kelley. Its original intent was to record data changes in SQL Server tables so users only had to process what had been changed to update a data warehouse. Nonetheless, Kelley said CDC can be used when auditing, as it also allows DBAs to read database log files. However, he added that doing so isn't a supported use of the technology and should only be done as a last resort.
One of the key things database auditors want to know is which users have access to a database and what kind of access those users have. Kelley said there are two main levels of security catalog views for principal users: sys.server_principals and sys.database_principals. The sys.server_principals one, in particular, corresponds to logins. He described the two catalog views as a DBA's "bread and butter" when trying to determine who actually has access to data sets and -- more important -- who is supposed to have access.
