C H A P T E R 15
Auditing SQL Server
Auditing your SQL Server is an important aspect of protecting your data. As a database administrator, you need to know the users who are logged into your system or attempting to log into your system, and what they are doing once they have gained access.
Auditing what users are doing in your system is less important when you have total control over their access within SQL Server. In other words, if you get to determine all users' access and their permissions, then auditing your users is not as critical because you can restrict their permissions and ensure they perform only the actions that you trust them to perform. Unfortunately, most DBAs do not have total control; you have to give users the access the organization wants them to have, which makes tracking down who is doing what in your system important.
Historically, DBAs have had the ability to audit logon success or failures and enable C2 audit tracing, as well as use SQL Server Profiler or tracing to determine which users are logged in and what they are doing in your system. As helpful as those options are, with the exception of SQL Server Profiler and tracing, DBAs lacked the control to determine the captured events. Even with the default trace running, we often still lacked the exact information needed because no additional filters were added to the default trace. Fortunately, the Enterprise Edition of SQL Server 2008 added a feature named SQL Server Audit that lets you determine the events that are important to you and log them accordingly.
Choosing Your Audit Type
SQL Server Audit allows you to monitor your server from two different aspects: at the server level and the database level. When trying to determine what kind of audit you want to create, there are a several things to think about. Use the following list to help you decide which type of audit to create.
Choose a server audit if you want to monitor the following:
- Actions that impact the entire server
- Actions that monitor changes across all databases
- Actions that monitor changes to schemas to all databases
Choose database audit specifications if you want tomonitor
- Actions specific to a database, object, or schema
- Specific actions of a principal within a database
- Specific actions (
SELECT,DELETE,UPDATE, and other Data Manipulation Language [DML] statements) within a database
Once you figure out the type of auditing you want to capture the required information, follow the steps in the next couple of sections to create your audit and store that information.
Creating SQL Server Audits with T-SQL
Before you can define server-level or database-level actions to audit, you must create a SQL Server audit, which is shown in Listing 15-1.
Listing 15-1. Syntax for Creating a SQL Server Audit
CREATE SERVER AUDIT audit_name
TO { [ FILE (<file_options> [, ...n]) ] | APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ]
As you can see from the syntax, creating a SQL Server audit defines the setup information for an audit. The audit does not contain any information about the actions either at the database level or the server level within its definition. Actually, server-level and database-level audits must be added to a SQL Server audit to define how and where the information is captured and stored. Now, let's take a closer look at the syntax for creating a SQL Server audit.
After you name the audit, determine if you want it written to a file, application log, or security log. If you decide to write the data to a file, then you need to specify the file path and name, the maximum size of the file, the number of rollover files, and if you want to reserve the maximum file size on disk.
The configurable audit options consist of a QUEUE_DELAY, ON_FAILURE, and AUDIT_GUID. The QUEUE_DELAY option sets the time that can pass before an audit action processes. The representation of time is in milliseconds with the minimal and default value of 1000 milliseconds or 1 second. The ON_FAILURE option decides what to do if the target (location of the audit files) is unreachable. The two configurable options are CONTINUE and SHUTDOWN. The default value is CONTINUE. The AUDIT_GUID option allows you to specify the globally unique identifier (GUID) of an existing audit for purposes where the GUID needs to be the same from environment to environment.
Once you have determined the settings for your SQL Server audit, then creating an audit is fairly simple and straightforward, as shown in Listing 15-2.
Listing 15-2. SQL Script That Creates a SQL Server Audit
USE master;
GO
CREATE SERVER AUDIT exampleAudit
TO FILE
( FILEPATH = 'C:', MAXSIZE = 1 GB
)
WITH( ON_FAILURE = CONTINUE)
GO
Creating Server Audit Specifications
In order to audit server-level information, then you have to create a server audit specification. A server audit specification consists of server-level action groups. We will discuss server-level action groups in more detail in the next section. For now, just understand that the server-level action groups identify what you are auditing from a server level. The server audit specifications are tracked across the entire instance of SQL Server. There are not any boundaries within the SQL Server instance. Because of this lack of boundaries, you cannot filter down to specific databases within server audits. To create a server audit specification, use the syntax in Listing 15-3.
Listing 15-3. Syntax for Creating a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT audit_name
ADD (audit_action_group_name ), ...n,
WITH ( STATE= ON|OFF)
To create the server audit specification, you have to specify which SQL Server audit to associate the server audit specification to. Once you assign the server specification to the server audit, then you add the server-level audit_action_group name to the server audit specification. Once you have added all of the server-level audit_action_group names that you want to monitor, determine if you want to enable the audit during creation. If you don't, then you must enable it when you are ready to capture the actions in the audit.
Server-Level Action Groups
Server-level action groups are the predefined groups used to audit your server from a server perspective. Since server-level action groups are predefined, then you can't customize the actions that each group captures. The only level of customization you have for a server-level audit comes from deciding which server-level action groups you add to an audit.
There are a large number of server-level actions groups, so we won't be able to discuss them all here. However, we list some of the server-level action groups that we frequently like to use for our server audits.
Successful_Login_Group: Tracks successful principal logins into the instance of SQL Server.Failed_Login_Group: Identifies unsuccessful principal failures against the instance of SQL Server.Server_Role_Member_Change_Group: Captures the addition and removal of logins from fixed server roles.Database_Role_Member_Change_Group: Tracks the addition and removal of logins to database roles.Server_Object_Change_Group: Captures create, alter, or drop permissions on server objects.Server_Principal_Change_Group: Tracks the creation, deletion, or alteration of server principals.Database_Change_Group: Identifies the creation, alteration, or deletion of databases.Database_Object_Change_Group: Captures create, alter, or delete actions against objects within a database.Database_Principal_Change_Group: Tracks the creation, modification, or deletion of database principals.Server_Permission_Change_Group: Identifies when principals grant, revoke, or deny permissions to server objects.Database_Object_Permission_Change_Group: Captures grant, revoke, or deny permission changes to database objects.
As you can see, server-level audit action groups of SQL Server Audit allow you to monitor a number of actions that occur from a server level. Please review SQL Server Books Online and search for “SQL Server Audit Action Groups and Actions” for a complete list of the groups. Understanding the available options enables you to capture the relevant actions in your environment. If you do not know what is available to monitor, then chances are good you will miss something that could have saved you time when trying to identify the cause of a problem.
Listing 15-4 shows an example of creating a server audit specification with server-level audit action groups.
Listing 15-4. SQL Code That Creates a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION serverSpec
FOR SERVER AUDIT exampleAudit
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
GO
Testing Your Server Audit Specification
Now that you understand how to create a SQL Server audit and add server audit specifications, let's create an example to demonstrate how the server audit specification works with SQL Server audits. Listing 15-5 creates a SQL Server audit and adds a server audit specification; it also contains code that causes the audit to fire.
Listing 15-5. SQL Script That Creates a SQL Server Audit and Server Audit Specification
USE master;
GO
-- Create the server audit
CREATE SERVER AUDIT permissionChanges
TO FILE ( FILEPATH = 'C:',MAXSIZE = 1 GB)
WITH( ON_FAILURE = CONTINUE)
GO
-- Create the server audit specification
CREATE SERVER AUDIT SPECIFICATION serverPermissionChanges
FOR SERVER AUDIT permissionChanges
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)
GO
-- Turn the audit and server audit specification ON
ALTER SERVER AUDIT permissionChanges
WITH (STATE = ON)
GO
ALTER SERVER AUDIT SPECIFICATION serverPermissionChanges
WITH (STATE = ON)
GO
-- Creates actions that the audit will pick up
CREATE LOGIN auditTest
WITH PASSWORD = 'Test123!'
GO
EXEC sp_addsrvrolemember auditTest, sysadmin
GO
EXEC sp_dropsrvrolemember auditTest,sysadmin
GO
EXEC sp_addsrvrolemember auditTest, serveradmin
GO
EXEC sp_addsrvrolemember auditTest, processAdmin
To review the contents of the audit, use the fn_get_audit_file function. The following code allows you to see the results of Listing 15-5. The results of the preceding query are shown in Figure 15-1.
USE master;
GO
SELECT event_time,server_principal_name, object_name, statement,*
FROM fn_get_audit_file ('C:perm*',NULL, NULL)
Figure 15-1. Results of the audit from Listing 15-5
Creating Database Audit Specifications
If you've determined that you need to audit database-level information, then you need to create a database audit specification to capture that information. Database audit specifications consist of database-level audit action groups and/or database-level audit actions. We will discuss database-level audit action groups and database-level actions in the next couple of sections. For right now, just think of database-level actions and audit action groups as the actions that are defined in order to audit database-level activity.
The syntax for creating a database audit specification is shown in Listing 15-6.
Listing 15-6. Syntax to Create a Database Audit Specification
CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT audit_name
ADD ( <audit_action_specification> | audit_action_group_name ) , ...n
WITH ( STATE= ON|OFF)
audit_action_specification =
action [ ,...n ]ON [class ::]securable BY principal [ ,...n ]
Similar to creating a server audit specification, the database audit specification must be associated with a server audit. Once you specify the server audit, then add the database-level audit action group or an audit_action_specification. Adding databaselevel audit action groups is similar to adding server-level audit action groups. Adding the audit_action_specification requires a different syntax. We will discuss adding the databaselevel audit action groups and database-level audit actions further in the next couple of sections.
Database-Level Audit Action Groups
Database-level audit action groups consist of actions against a specific database. Database-level audit actions enable you to monitor actions on database, schemas, and schema objects within the database. The database-level audit action groups are predefined actions that don't allow customization.
Because there are so many database-level audit action groups, we will not cover them all here. However, following are the database-level audit action groups that we frequently use to audit our systems.
Database_Role_Member_Change_Group: Tracks the addition and removal of logins to database roles.Database_Object_Change_Group: Identifies whenCREATE,ALTER, orDROPstatements occur against database objects.Database_Principal_Change_Group: Tracks creation, alteration, and deletion of database principals.Database_Permission_Change_Group: Tracks when permissions change for a database user.Database_Object_Permission_Change_Group: Identifies the issuing of grant, deny, or revoke permissions to database objects.Schema_Object_Change_Group: Tracks theCREATE,ALTER, andDELETEstatements performed on schema objects.
We encourage you to go to SQL Server Books Online and search for “SQL Server Audit Action Groups and Actions” to get a complete list of all the database-level audit action groups. The more aware you are of the available database-level audit action groups, the better you can plan audits on your system.
The following code provides an example of adding database-level audit action groups to a database audit specification. This code sample also creates a SQL Server audit to associate the database-level audit action groups to.
USE master;
GO
CREATE SERVER AUDIT exampleAudit2
TO FILE ( FILEPATH = 'C:',MAXSIZE = 1 GB)
WITH( ON_FAILURE = CONTINUE)
GO
USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
GO
Database-Level Audit Actions
Database-level audit actions allow you to audit specific, customizable actions within the database. In other words, database-level audit actions against a database, schema, or schema object of your choosing will trigger an audit event. The following list contains the databaselevel audit actions with brief descriptions:
Select: Tracks execution ofSELECTstatements.Insert: Determines when the execution ofINSERTstatements occur.Delete: Identifies whenDELETEstatements execute.Update: TracksUPDATEstatements executed against the database.Execute: Determines whenEXECUTEstatements run against the database.Receive: Tracks whenRECEIVEstatements are issued.References: Identifies when the references of permissions are checked.
Referring to the syntax in Listing 15-6, you can see that adding the database-level audit action consists of an action on a securable by a principal. What does all that mean? An action is a database-level auditable action like the items listed in the preceding list—statements like SELECT, INSERT, UPDATE, DELETE. Securables, as discussed in Chapter 8, are the objects within a database that you want to audit. Examples of securables are tables, views, stored procedures, and other database objects. The principal is the SQL Server principal that you want to apply the audit to. In other words, when you define a database-level audit action, you are defining the type of statement you want audited against a table or view, or done by a user.
The following code provides an example of adding database-level audit actions to a database audit specification.
USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (INSERT,UPDATE,DELEETE ON Person.Person by dbo),
Testing Your Database Audit Specification
Since you understand the syntax of the CREATE database audit specification, let's construct one. Listing 15-7 creates a database audit specification along with some statements that will force the events to fire causing writes to your audit. For this example, we want to audit who is querying a specific production table. Since we cannot prevent non-application users from having access to the database, we can track if they are viewing tables unrelated to their jobs, such as the salary table for all the employees. Sometimes the best way to prove to management that not everyone needs access to production is to show them examples of abusing the privileges. Capturing users running bad queries or looking at information that they shouldn't helps with that argument. We also monitor the DATABASE_PRINCIPAL_CHANGE_GROUP just in case the user is smart and creates another user to query the table.
Listing 15-7. SQL Script to Create a Database Audit Specification
USE master;
GO
-- Create the server audit
CREATE SERVER AUDIT salaryViewing
TO FILE ( FILEPATH = 'C:'',MAXSIZE = 1 GB)
GO
-- Create the database audit specification in the database you want audited
USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION salaryQueries
FOR SERVER AUDIT salaryViewing
ADD (SELECT,UPDATE ON humanresources.EmployeePayHistory by dbo),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)
GO
USE master;
GO
ALTER SERVER AUDIT salaryViewing
WITH (STATE = ON)
GO
USE AdventureWorks2008
GO
ALTER DATABASE AUDIT SPECIFICATION salaryQueries
WITH (STATE = ON)
GO
SELECT TOP 10 *
FROM AdventureWorks2008.HumanResources.EmployeePayHistory
SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2008.HumanResources.Employee e
JOIN AdventureWorks2008.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityId
ORDER BY jobTitle, RateChangeDate DESC
SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2008.HumanResources.Employee e
JOIN AdventureWorks2008.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityId
WHERE rate > 50.
ORDER BY jobTitle, RateChangeDate DESC
USE Adventureworks2008
GO
CREATE USER sneakyUser FOR LOGIN auditTest
GO
To view the results of the database specification audit, use the following query. The results of the query are shown in Figure 15-2.
SELECT event_time,server_principal_name,database_principal_name,
object_name, statement,*
FROM fn_get_audit_file ('C:sal*',NULL, NULL)
Figure 15-2. Results of the audit from Listing 15-7
Creating SQL Server Audits Using the GUI
Since you know how to create SQL Server audits and add server and database audit specifications using T-SQL, we would like to spend a brief moment on the creation of SQL Server audits from SQL Server Management Studio. We are going to create a database audit specification, since you can include database-level audit action groups and a database-level audit action. Remember that database-level audit action groups are the predefined action groups created for you by SQL Server, while you get to define the database-level audit actions. As you know, server audit specifications only use server-level audit action groups, which are similar to the database-level audit action groups.
We are going to follow the same steps used in T-SQL: create an SQL Server audit, create a database specification, add database-level audit action groups, and then database-level audit actions to the database specification. To create a SQL Server audit, connect to an instance of SQL Server using SQL Server Management Studio. Once connected, navigate to the Security folder, expand the folder, then right-click on the Audits folder, and select New Audit. Figure 15-3 shows the options available in the GUI.
Figure 15-3. General page of the Create Audit dialog box
First, name your audit. Then set the queue delay—remember, the measurement is in milliseconds. Select the check box next to Shut Down Server on Audit Log Failure if you want to shut down the SQL Server instance when the audit is unable to log data to the target. After that, determine the audit destination. If you choose File, then you have decide the file path, maximum file size, maximum rollover, and if you want to reserve disk space. Once you populate all of the values, click OK to create the audit.
Creating a Database Audit Specification Using the GUI
After creating the audit, navigate to the database where you want to create the database audit specification, drill down to the Security folder, right-click on the database audit specification, and then select New Database Audit Specification. See Figure 15-4 to review the available options in the Create Database Audit Specification dialog box.
Figure 15-4. The Create Database Audit Specification dialog
First, name the database audit specification and select the audit that you want to associate the database specification to. After that, notice that you have four selectable options to create actions for the database specification: Audit Action type, Object Class, Object Name, and Principal Name.
In the first column, the Audit Action Type drop-down list contains the database-level audit action groups and the database-level audit actions. If you select a database-level audit action group, then you do not have any other selectable options to choose from. The action groups we previously discussed already have their actions defined. Choosing a database-level audit action gives you the flexibility to define exactly what you want audited.
The Object Class drop-down list contains three options: Schema, Database, and Object. In other words, do you want to audit the audit action for a database, a particular schema, or for a particular object? The object class you select will determine the types of object names that are available. Choose the object class that you want audited for the audit action type you selected.
The Object Names ellipses will contain all of the object names for the object class selected. For example, if you select Database, then the Object Name drop-down will list all of the available databases. Schemas and objects work similarly. Identify the object name that you want audited and select the name from the list.
After you select the object name, then select the principal name that you want to monitor. When you click on the ellipses, the list displays available principals. Choose the principal name that you want to audit from the list. Notice, you can even select database roles from the list. After you populate the options, click OK and create the database audit specification.
After creating the database audit specification, navigate to the recently created database audit specification, right-click on it, and select Enable to enable the database audit specification. You also need to enable the audit. So navigate to the audit that you associated the database audit specification with, right-click on the audit, and select Enable. Now both the database audit specification and the audit that it is associated with are enabled.
Reviewing Audit Files Using SQL Server Management Studio
We have demonstrated querying a file to review the audit information in a file, but we want to show you how to review the audit information from the GUI. Navigate to the Audits folder under the Server Security folder. Right-click on the audit that you want to review and select View Audit Logs. As shown in Figure 15-5, you can select multiple audits. That can prove helpful if you are trying to correlate when events occurred from multiple audits.
Figure 15-5. Results of a salaryViewing audit using SQL Server Management Studio
Audit-Related Startup and Shutdown Problems
Before setting up SQL Server audits, you should be aware that audit problems can lead to trouble in starting or stopping your SQL Server instance.
Failure to Start
When either the ON_Failure = On or Shutdown Server on Audit Log Failure option is checked, SQL Server will not start if it cannot initialize the target for an audit or if it cannot write to the target of an audit. When SQL Server starts, it will attempt to start any enabled audits. If failures occur, you will receive an unfriendly error message like the one in Figure 15-6.
Figure 15-6. Error message received when attempting to start SQL Server but unable to initialize the target file
An audit target may not initialize for a number of reasons. The drive might not allow you to allocate all the space required when Reserve_Disk_Space is set to on, the problem might be from changes in the file structure where the audit files are stored, and so forth. Whatever the problem, the result is that SQL Server does not start.
If you are like us, then you probably don't want your server not starting because of an audit not initializing. If that happens, then start SQL Server from the command line using the following:
sqlservr.exe -f -s < instance name>
The instance name is optional, and defaults to MSSQLServer. The -f option will start SQL Server in minimal configuration mode, which allows you to disable or fix the troublesome audit target.
Forced Shutdowns
If SQL Server shuts down because of a failure to write to the log, then it will write an event to the error log stating MSG_AUDIT_FORCED_SHUTDOWN. When this occurs, SQL Server still will not start until the problem that caused the shutdown is resolved. To bypass this issue, you can start SQL Server from the command line using the following:
sqlservr.exe -m -s<instance name>
As before, the instance name is optional. Using -m starts SQL Server in single user mode and allows you to disable the audit or change the shutdown option in order to continue.
You may be thinking that that this situation can easily be avoided by setting your audit to CONTINUE instead of SHUTDOWN. On the other hand, you may have a very good reason for shutting down SQL Server when you cannot log audit information. In either situation, rather intentionally or unintentionally, you may find yourself unable to start SQL Server after an audit shutdown, and you need to know how to get your server back up and running.
Useful SQL Server Audit Objects
Before you finish this chapter, we want to make sure you know how to quickly determine the active audits within your SQL Server instance. We also want you to know how to read data from an audit file without using the GUI. The following list describes the sys.dm_server_audit_status DMV and the fn_get_audit_file function.
sys.dm_server_audit_status: Lists information for all the SQL Server audits. The DMV contains the current status of the audits, the UTC time of the status change, and for file audits, the location and size of the files.fn_get_audit_file: Enables you to read the file of an audit. The function expects three parameters: the path and file name (which can include a wildcard), the initial file name to start reading from, and the audit file offset (which tells the function where to start reading from). You can specifydefaultornullfor the last two parameters. The output of the function is a list of audit records ordered by the time that the actions occurred.
Having the ability to review audit files and determine which audits are running proves useful in our environments. We hope these two commands are useful to you as well.
Summary
As we conclude this chapter, we hope you have learned some valuable information to aid you in auditing the activity of your server. SQL Server Audit is a new Enterprise Edition feature that definitely gives database administrators another tool to aid in monitoring servers. Take advantage of this new feature, use it to track activity on your server, and minimize the amount of time it takes to track down user activity on your server.