After authentication and authorization, many organizations have a need for the third “A” of security: accounting. Typically, the IT world refers to that as auditing, and there are a number of ways that SQL Server can help you keep track of who’s doing what. I’ll start by introducing you to three auditing mechanisms that you probably won’t use, mainly so I can explain why you won’t use them; I’ll then intro-duce SQL Audit, which is the auditing technology you’ll probably rely on the most.
8.1 Triggers
Triggers are one way that SQL Server can help log actions made in a database. I need to start by pointing out that these absolutely require custom programming, and they’re far from foolproof: an administrator can easily disable triggers, bypass-ing them completely. They also come with a lot of design considerations and a few caveats. Because they’re a programming element, I’m not going to cover them in depth, since this isn’t a book about SQL Server programming, but I do want you to know what they are. If you want to learn a lot more about them, dive into the offi-cial documentation at http://msdn.microsoft.com/en-us/library/ms189799.aspx.
A trigger is a special kind of stored procedure, which is a script that lives inside a SQL Server database. Triggers can contain one or more T-SQL statements, and can in fact be quite complex (it’s also possible to create them in a .NET Framework lan-guage like C# or Visual Basic). Triggers run automatically in response to specific things happening inside the database.
DML triggers run in response to UPDATE, DELETE, or INSERT statements being run. They can either run in lieu of whatever change a query was trying to make, or they can run after the change is made. In theory, you could program a trigger to
76 CHAPTER 8 Accounting: what did you do?
log the change to a separate table, creating your own auditing system. Triggers don’t run in response to SELECT statements, so there’s no way to use a DML trigger to keep track of all access to data.
DDL triggers run in response to database schema modifications, such as removing columns from a table or creating a new table. Again, you could use these to log schema changes to another table to create a sort of audit log. DDL triggers don’t run in response to every possible configuration change in a database, so they’re not a catchall way of auditing all administrative activity.
8.2 SQL Server Profiler
In theory, SQL Server Profiler offers a way to track system activity, down to the query level. Profiler is a pretty heavy monitoring solution, meaning it creates its own perfor-mance impact and isn’t designed for continual use. It also doesn’t log its data in a way that facilitates using it as an audit log.
8.3 Event logs and C2 auditing
SQL Server has supported C2 auditing almost forever. C2 refers to a government stan-dard for auditing, requiring the server to log every possible access attempt, whether failed or successful. SQL Server logs that information to the Windows event logs.
Unfortunately, pretty much nobody uses C2 auditing, and I don’t recommend you even try it out, because it can generate an enormous level of log traffic—more than the Windows event log architecture was designed to handle. Turning on C2 auditing requires that you include logging overhead in your server capacity design. It’s that intensive. And the results are barely usable: the native event logs aren’t designed to be easily queried or used to generate auditing reports.
8.4 SQL Audit
SQL Audit, a built-in feature of newer versions of SQL Server (2008 and later), is specifically designed to provide a granular level of audit log-quality accounting.
Enterprise editions of SQL Server provide a full range of audit capabilities; Standard editions provide only a basic subset.
8.4.1 SQL Audit architecture
SQL Audit uses SQL Server’s native Extended Events engine (which you’ll learn about in chapter 17). Extended Events, unlike SQL Server Profiler, is a lightweight monitoring engine, meaning it’s designed to run continuously, in the background, without severely impacting SQL Server’s performance. SQL Audit receives events from the engine and uses them to create an audit trail of only the activity you specify, meaning you can decide how much detail you need to capture.
A server audit is defined at the SQL Server instance level, meaning they’re active for all databases, not just a single database. You start by creating an audit object, which contains the complete definition for what you’ll be auditing, where the audit log will
77 SQL Audit
go, and so on. You can have multiple active audits simultaneously, although I tend to try to keep everything consolidated into a single audit, or at least as few as possible, just to keep them easier to manage. You can also define database audits, which as the name implies are active only for a specific database.
Within an audit, you create an audit specification. That defines what will be audited.
Each audit object can contain one, and only one, audit specification. Specifications let you indicate what actions, or events, you want audited, such as failed logins, server role membership changes, and so on. Actions are organized into action groups, making it a bit easier to select an entire related set of actions. The actions available to a server audit are different than those in a database audit, because you can do different things at the server level (such as log in) and the database level (such as dropping a table).
Audits also include a target, which details where the audit log will go. These can be the Windows Security event log, the Application event log (which can be viewed by any authenticated user), or to a file. I generally use a file, since they’re easier to col-lect, consolidate, and manage than the event logs.
Note that the SQL Server service account must have permission to write to the tar-get; if it can’t, then the SQL Server instance may not start. You can modify the service startup properties to include the –f command-line parameter to start the instance and bypass auditing. That lets you get in and modify the target, disable the audit, or fur-ther troubleshoot the access problem. Additionally, if SQL Server tries to write an event to the audit target and fails to do so, you can configure the audit so that the instance will shut down immediately. The idea is to ensure all specified activity is audited; if it can’t be, then the instance isn’t allowed to run.
Database audits are referred to by an internal GUID. The audit doesn’t live in the database itself, but the database refers to the audit by that GUID. When you detach a database and move it to another instance, the audit doesn’t go with it, but the data-base will still be trying to refer to it by that GUID. That’s called an orphaned audit, and it’s similar to orphaned logins, which we discussed in chapter 6. You have to fix this by running the T-SQL ALTERDATABASE AUDIT SPECIFICATION command; look up the command for more details on correcting the problem.
NOTE Technically, an audit is an audit is an audit; it’s the audit specification that is either server- or database-specific. Specifications become detached from their audits when you move a database to another instance, and it’s that rela-tionship you’ll have to fix, reconnecting the database audit specification to the instance-level audit object it goes with (or creating a new one for it to refer to).
8.4.2 Creating an audit
I’m going to walk you through the process of creating an audit object, a server audit specification, and selecting an audit target.
TRY IT NOW Please follow along! I’ll be using an example that includes some of the more common server-level audit actions, so this is good practice for a production server.
78 CHAPTER 8 Accounting: what did you do?
Start in SQL Server Management Studio.
1 In Object Explorer, expand the Security folder.
2 Right-click Audits, and select New Audit….
3 Configure the General page as shown in figure 8.1. I’ve provided a name for the audit, as well as a file path. You don’t provide a file name; that’ll be generated automatically in the specified path. Notice that I haven’t configured this to shut down the instance if an audit fails. Also notice how I’ve configured the file han-dling: there will be no more than two files, of no more than 20 MB apiece. The audit will reserve that space immediately, ensuring it will have space to write to those files. Once the second file fills up, the first will be deleted and a new one created. So I’ll always have 40 MB in audit files.
4 The audit is disabled by default; you’ll see that in its icon once you click OK. We’ll enable it in a bit.
5 Right-click Server Audit Specifications and select New Server Audit Specifica-tion….
6 Configure the General page as shown in figure 8.2. I’ve attached this specification to the audit object I just created, and I’ve selected three login- and password-related action groups to include in the specification. Click OK. Notice that the specification is also disabled by default.
7 Enable both the specification and the audit by right-clicking them and selecting the Enable option; click Close on the status dialog box displayed by each.
Figure 8.1 Configure the General page for audit as shown.
79 SQL Audit
TIP If the audit fails to enable, then it’s likely the SQL Server instance’s ser-vice account doesn’t have read and write permissions to the file path you specified. I used C:\ in my example, and that is restricted on most server com-puters. You can fix the problem by creating a new path (such as C:\Audit) in Windows Explorer, and modifying its permissions to include SQL Server’s ser-vice account (or something like Everyone:Full Control, which isn’t awful for a lab environment). Then modify the audit to use that path instead.
Now let’s test the audit. I’ll launch a new copy of SQL Server Management Studio and connect to my instance. That should generate a login success audit, and your audit path should now contain a file with a .sqlaudit filename extension. The filename will include your audit’s name (Global%5Audit, in my example, with the “%5” being an encoded representation of a space) followed by a long unique identifier.
Audit files are in a binary format; you can’t simply open them in Notepad. Instead, in SQL Server Management Studio, right-click the audit and select View Audit Logs….
You’ll see something like figure 8.3, which shows all of the audited events. Notice that the bottom pane shows details about the selected event, and that you can filter, search, and export the log using toolbar buttons at the top. Exporting provides the ability to write to a text-based log format, or to a comma-separated values (CSV) file, which can be opened in Excel or imported into a database.
NOTE Once you’re done playing with auditing, you can disable the audit object by right-clicking it and choosing the Disable option.
8.4.3 Using SQL Audit for security auditing
Say “audit” and most folks’ minds justifiably jump to “security.” SQL Audit—particu-larly on Enterprise editions of SQL Server, where more granular auditing is available—
is well suited to maintaining security in the organization. Heck, I have customers whose legal and industry requirements demand that they audit all read access to cer-tain database tables (like those concer-taining confidential customer information). SQL Audit can do it.
That said, you don’t start auditing massive amounts of activity without first spend-ing serious time thinkspend-ing about it. Auditspend-ing all SELECT access to a busy database, for
Figure 8.2 Configure the specification as shown.
80 CHAPTER 8 Accounting: what did you do?
example, generates a lot of audit traffic. A lot, lot, lot. You’re going to have to set aside serious disk space to capture that information, and you’re going to have to have a plan to maintain those logs, archiving them for however long you’re required to do so.
That level of auditing is going to impact SQL Server performance to some degree (although you’d have to run tests in a lab environment to determine exactly how much), so you might well have to upsize your SQL Server computer to handle it: more processor, more RAM, and certainly more disk controllers and faster disks. Point being, SQL Audit can do it, but you’re not just going to flip the switch and be happy.
It’s going to require serious conversation and planning within your organization.
8.4.4 Using SQL Audit for troubleshooting
Folks tend to think of auditing as a security thing (“Make sure we know who accessed what, at all times!!!!”) but it’s equally useful as a troubleshooting tool. I’ll often create an audit and a server audit specification that audits failed login attempts. I’ll leave the audit disabled until a user calls in and says they’re having trouble logging in. I can
Figure 8.3 Use Management Studio to view the audit log.
81 Hands-on lab
quickly enable the audit, ask the user to try again, and then examine the audit log to see what’s going on. I can then disable the audit until I need it again.
Honestly, I find myself using SQL Audit a lot more for troubleshooting than for anything else. Yes, I have customers who need auditing for security purposes, but with such a wealth of information available, it’s a powerful troubleshooting tool. I can even use it to capture specific queries for tuning, if needed, and it’s a bit easier to turn on and off than it is to fire up SQL Server Profiler.
NOTE As you get started with SQL Server administration and maintenance, you probably won’t use SQL Server Profiler much. It’s a fairly advanced tool.
However, we’ll be using it to accomplish a few specific tasks, starting in chap-ter 11.
8.5 Hands-on lab
You don’t have a mandatory hands-on lab in this chapter, but I do want to offer an optional one. This requires that you have an Enterprise edition of SQL Server, though;
Standard and Express don’t permit granular database audit specifications.
See if you can set up an audit specification in a database, to audit use of the SELECT statement by members of the db_owner database role on that database. Don’t do this on a production server; perhaps download and install a trial of SQL Server Enterprise Edition to try this. In a production database this will likely create an enor-mous log very quickly. In fact, if you’re able to set this up, try running a few SELECT statements to see just how fast a large number of events is created.
82