11th SQL Server Security Audit

This article is the 11th of the SQL Server Security series, please refer to the original text for more information.

SQL Server Auditing
SQL Server auditing means that you can monitor events in a database or server instance. The audit log contains a list of events that you choose to capture, generating activity records for database and server objects, principals, and operations on the server. You can capture almost any data that happens, including successful and unsuccessful logins, read, update, deleted data, management tasks, and more. Auditing can drill down into databases and servers.
It seems strange that there is a way to retrace the events that have occurred. But audit logs are often the first and best resources you use to detect attacks, especially when an attack only probes the database, but has not yet successfully accessed or compromised data. When such an attack occurs, the supervisor puts pressure on you, and the audit log may help you figure out what happened, what data was accessed, and the source of the attack. If there is no log, before you can determine what happened, you must catch the attacker before replying to them.
SQL Server provides support auditing functionality long ago, but until SQL Server 2008 auditing is truly a core feature, there are built-in objects, T-SQL support, and a user interface in SSMs. Microsoft learned a lot from the initial version and made some good improvements in SQL Server 2012. The biggest change is that Microsoft makes it possible for all versions of SQL Server to use auditing, not just the Enterprise edition. This means that you can now create a cross-database audit specification in any version. But the message is mixed: Individual database audits are only available for enterprise, development, and evaluation editions. So, if you are testing or evaluating the end-use SQL Server version of your production environment, make sure that you have selected the features you need.
SQL Server Audit Object
Whether you use the T-SQL or the SSMs user interface to manage auditing, you manipulate the following three objects:
Server Audit:The server audit object is the top-level container for auditing, and you will always use that object for auditing. Typically, you will create a server audit rollup of one or more audit specifications for a specific purpose (such as compliance or a specific set of server/database objects). In this object you can configure the name of the audit, the audit log save path, the audit file maximum limit, and the action when the audit log fails. You can also define a filter to control the write of the event log.
Server Audit Specification:In this object you can define service-level events to capture write audit logs. This specification needs to be associated with the server audit you created earlier. This is where you define which events on which objects you want to record.
Database Audit Specification:This object is similar to the server audit specification, which you use to capture events on separate databases. It is also associated to a server audit.
Typically you will use a server audit to combine one or two other objects depending on the type of event you want to capture. If all events occur in a database, use the database Audit specification object. Otherwise, if the event spans two or more databases, or server-level events, the server audit specification object is used.
When you create a server audit, the audit log has three storage locations: file, security log, Application log. You should be careful. Select the audit destination (audit log storage location) because it can easily contain sensitive information such as social Security numbers, credit card numbers, payroll, corporate financial data, and so on. Therefore, you should use a location that is not accessible to unauthorized users. This means that the application log may not be a good choice because by default the user does not need to increase permissions to view it. But the security log can be a good choice because access requires admin privileges. Files can also be a good choice, because you can use the system's built-in security features to protect files in folders or folders.
Create a server audit
You can create an audit using the SSMs graphical interface or T-SQL statements. It's very easy to create under SSMS and we're going to do that. Open SSMs to connect to the local DB instance. Auditing, security, Object Explorer, right-click Auditing Select New Audit to open the Create Audit dialog box.
You can use the dialog box to set various properties of the server audit:
The audit name is automatically initialized to the audit-20150908-143152 format according to the date and time, and of course you can modify it yourself for whatever you like. I set the sample audit to better identify the audit.
Next, you can set the queue delay (in milliseconds), which is set to 1 seconds by default, representing the maximum time to wait for log writes. This value is a tradeoff between performance and security: a shorter time limit makes it more likely to log critical events before a disaster fails, but may affect server performance. I let the server audit keep the default setting, which means that, in the worst case, I might lose an entry for one second. As an example of auditing this is acceptable!
Next, set the action when the audit log fails when SQL Server cannot write an audit log condition, such as no free disk space. The default is to continue, just to generate an error in the Windows event log, and the audit continues to execute. The Shut down server option was available when auditing was crucial and neither the operation nor server execution should co Ntinue in the face of a audit failure. This is a tough choice (shutting down the server) and auditing is critical when it is necessary. The operation failure option is somewhere between the previous two extremes, allowing the failed transaction to be rolled back. I'll still leave this as the default.
What you need to choose is the audit target. There are three options: file, security log, Application log. If you select the next two Windows event logs, the remaining options in the dialog box are disabled, and the other options apply only to the audit target file.
If you choose the File option, you will have to select a path. In this example, I chose D:\SQL2012, a folder that can be cleaned up at any time to make room. You can set any path you like, a different partition or a network disk.
Other options for file audit goals let you set the file size and number of files. You can set the maximum number of rollover files to a maximum or unlimited. For the maximum number of rollover files option, SQL Server overwrites the oldest file with the new file once the maximum number of files in the folder is reached. For the maximum number of files option, once the maximum is reached, the audit log is written to fail. If you select the associated unrestricted option, the number of rollover files will not be limited. You can also set the maximum file size or size unlimited. The last option in the dialog box--Reserve disk space, tell SQL Server to pre-allocate disk space equal to the maximum file size you specify, and if so, apply only if the maximum file size unrestricted option is not selected. Therefore, you have enough options to control the amount of space on the specified drive for your audit file.
Figure 11.1 shows the audit I created named Sample audit

Figure 11.1 Creating a server audit in SSMs
You can also create an audit using T-SQL statements. Code 11.1 Creates a sample Audit with the same settings using Create SERVER Audit. I generate the related script by creating the Script button in the Audit dialog box.

 UseMaster;GOCREATESERVER AUDIT[Sample Audit] to FILE(FILEPATH=N'D:\SQL2012', MAXSIZE= 0MB, Max_rollover_files= 2147483647, Reserve_disk_space= OFF) with(Queue_delay=  +, On_failure= CONTINUE);GO

Code 11.1 T-SQL code Create sample Audit
Code 11.2 shows the creation of a server audit that writes logs to the application log instead of to the file. Because there are no additional options, the code is more concise than the previous.

CREATE SERVER AUDIT sqlserveraudit      to Application_log      with = ,  =CONTINUE); GO

Code 11.2 Creating an audit written to the application log
Now in the Object Explorer, security---audit node, you will see two objects, 11.2 shown. Notice that a red down arrow icon is superimposed on the magnifying glass icon. This indicates that the audit is not enabled, which is the default state when it was created. You can enable auditing by right-clicking the Audit object, selecting Enable auditing from the pop-up menu, or by using the ALTER SERVER audit statement in code 11.3. Once you have enabled auditing, the Red Arrows will disappear, although you may need to refresh the Object Explorer again. If you are following the SSMs interface, go ahead, enable the sample audit audit, and we'll use it next.

Figure 11.2 Server auditing under Object Explorer

 alter  SERVER AUDIT sqlserveraudit with  (State =  on  ); go  

Code 11.3 T-SQL enable Sqlserveraudit auditing
Create a server audit specification
Once you have created a server audit, you can define the audit events you want to write. Server auditing is essentially a container for audit specifications. Remember that there are two types of audit specifications: Server and database audit specifications.
I will create a server audit specification to log information that logs SQL Server login failures. This is a database-scoped server-level operation, so I need to create a specification at the server level. Using the server audit specification, ssms-> Security, right-click the Server audit specification, select New Server Audit specification, open the New Server Audit Specification dialog box.
You can accept the auto-generated canonical name, but I'm named Testsqlserverauditspec. Then select Server audit from the Audit drop-down list, which contains all the server audits that already exist on the instance. In this case, I chose to use sample Audit, which means that the audit log entries are saved to the file.
You can then use the Actions section of the dialog box to define the events or groups of events that you want to record. The list of available action types is very much, as shown in 11.3. This example selects the Failed_login_group type.

Figure 11.3 Select the Audit action type
The remaining columns of the Failed_login_group action type are disabled because there are no other options available for that type. But other types allow you to associate operations with various server objects. The dialog box should look like figure 11.4, and when you click the OK button, it will create the specification.

Figure 11.4 Defines a new server audit specification
The Server Audit specification node under Object Explorer will appear with the newly created server audit specification, which is disabled by default. You can right-click the specification and select Enable Server audit specification.
You can also use the T-SQL statements in code 11.4 to create a new server audit specification. The code uses a WITH clause option to receive the status parameter--enable or disable the specification. If you omit this clause, the state is set to off by default.

CREATE SERVER AUDIT specification Testsqlserverauditspec      for [Sample Audit] ADD (failed_login_group)      with =  on ); GO

Code 11.4 T-SQL Create server audit specification
To test the audit, connect to the instance of SQL Server, and then try to log in to the database with an incorrect password. You can open a different ssms or use the Connect button under Object Explorer to do so.
Then right-click on sample Audit and select View Audit log to open the Log File Viewer dialog box. It may take some time to load the list of log entries from the disk file, but eventually you will see the login failure information shown in 11.5. Each row contains a considerable amount of information about the event, making it cumbersome to scroll to the right to see all the data. But when you select a row, the data appears at the bottom of the window for easy reading. Unfortunately, the columns you see in the diagram do not have a good format.

Figure 11.5 Viewing the server audit log
Note the server audit automatically captures modify audit events When I enable server auditing. The second line of information is captured in the figure.
Tip: You need to enable server auditing and server audit specifications in advance to capture and write events. Usually, you will want to create them when it is enabled.
Create a Database audit specification
Creating a Database Audit specification is very similar to creating a server audit specification. The main difference is the ability to capture the event scope of the log. The database audit specification captures events that occur within a single database, such as accessing data through T-SQL statements, modifying permissions on structures or database objects, or executing stored procedures. Another important difference is that you create the database audit specification under the database, not the security node.
If you want to create an audit record, any user or program executes a SELECT statement to get data from the AdventureWorks2012.Person.Person table. Expand Object Explorer, Database->adventureworks2012-> security, right-click the database Audit Specification node, select New Database Audit specification, open the Create Database Audit Specification dialog box, and it will automatically generate a name that you can modify.
Figure 11.6 shows the Select Audit action type on the Person.person table, associated to the previously created audit sample Audit. In this example you want to select the Objects object class (another option is database and schema), the person schema, The person table name, and the principal name. Unfortunately, you cannot type the object and the principal name directly. You must click the ellipsis button next to it to open the Select Objects dialog box.

Figure 11.6 Creating a Database Audit specification
You can set the principal name to any database user or role, including the public database role to overwrite all users accessing the database. Similar to the server audit specification, you can add any action you like to the specification.
Once you have created the specification, remember to enable it.
You can also use the T-SQL statement in Code 11.5 to create this specification, with the WITH clause option enabled specification.

 UseAdventureWorks2012;GOCREATE DATABASEAUDIT specification[Person Data Access]     forSERVER AUDIT[Sample Audit]    ADD(SELECT  onOBJECT::P Erson.person by  Public)     with(state=  on);GO

Code 11.5 T-SQL code to create a database audit specification
You can then test the audit specification by executing a SELECT statement on the table. Once you have done this, refresh the log file Viewer, 11.7. As you can see, the log contains a very complete event message, including the SQL statement that caused it.

Figure 11.7 Audit log after querying Person.person
Note that the other columns of information in the picture do not have any content, because the resulting event is directly a SELECT statement. However, when the SELECT statement is executed under a stored procedure or other code module, the additional information contains the XML code block for the T-SQL stack information. This can be used to differentiate whether a query is executed in a query or code module.
We create a stored procedure called Tempperson, which contains the SELECT statement for the statement column in the picture. After you have executed the stored procedure, you will get the result shown in Figure 11.8, as well because someone executed the SELECT statement on the Person.person table. But this time note the other information column.

Figure 11.8 Executing a stored procedure that encapsulates a SELECT statement
The additional Information column contains the XML code, including some code information that causes the audit log, and most importantly the schema and name of the stored procedure.
Writing Custom Audit information
You are not limited to capturing only the default information for each event. You can also create user-defined audit events that allow you to write any logs you wish to the audit log. Code 11.6 Demonstrates how to implement this. The code first disables the specification only if the audit specification (either server or database) is disabled before it can be modified. Then add the User_defined_audit_group action type to the TESTSQLSERVERAUDITSPEC specification and enable it immediately. The last line of code uses the Sp_audit_write system stored procedure to write some interesting things to the audit log.

 UseMaster;GOALTERSERVER AUDIT Specification Testsqlserverauditspec with(state= OFF);GOALTERSERVER AUDIT Specification TestsqlserverauditspecADD(User_defined_audit_group) with(state=  on);GO--Write Some custom audit informationEXECSp_audit_write9999,1N'Something in SQL Server succeeded!'

Code 11.6 Writing Custom audit information
Of course, you can also modify the server audit specification through the SSMs graphical interface.
If you execute code 11.6 and then review the audit log, you will see that the entry appears at the top of figure 11.9. You can still get a lot of background information for auditing events, including the event's T-SQL statements, but you'll find that the user-defined information item at the bottom is what we define.

Figure 11.9 Audit log contains user-defined information
Tip: If the User_defined_audit_group Server audit specification is disabled, the database ignores sp_audit_write.
Filtering Audit Events
SQL Server also includes the ability to filter events written to the audit log, which uses the same filtering mechanism as extended events. Filtering gives you fine-grained control over what is written to the audit log by SQL Server. However, it is important that the server still generates all the log entries for the events that you define in the specification, and then uses filtering to determine whether to write the events to the log. Therefore, even if the entry is not written to the log, generating an event entry requires overhead. This means that auditing events that create specific objects are usually better than filtering.
You want to create an audit that records all events of a specific type, in addition to the information for a specific login name. The first part of code 11.7 creates the carol login and maps to the ADVENTUREWORKS2012 database. Then use the SUSER_ID method to retrieve the principal ID of the new user (I am 288). Then, the second section creates a server audit with a WHERE clause that filters out the principal ID of 288 and enables server auditing. Now Carol have free reign on the database, safe from server audit events!

--Part 1:create the login and database user UseAdventureWorks2012;GOCREATELOGIN Carol withPASSWORD= 'GEP2ZYDT+5CQW';CREATE USERCarol forLOGIN Carol;SELECT suser_id('Carol');--Part 2:create the server audit--Change principal ID from 288 based to suser_id from previous statement UseMaster;GOCREATESERVER AUDIT Filteraudit toApplication_log with(Queue_delay=  +, On_failure= CONTINUE)WHEREserver_principal_id<> 288;ALTERSERVER AUDIT Filteraudit with(state=  on);GO

Code 11.7 Create a login and then create an audit that excludes this user
You can also use the Audit Properties dialog box to filter server audit events and right-click the server audit selection properties. Select the filter page, as shown in 11.10. Note that the text box contains only the WHERE clause predicate that is used to create the server audit statement, enclosed in parentheses. There is no need to include the WHERE keyword.

Figure 11.10 Adding filters with audit properties

--* * * * up * * *-- **************** UseAdventureWorks2012;GODROP USERCarol;--Clean Up Database Audit specificationALTER DATABASEAUDIT specification[Person Data Access]  with(state= OFF);DROP DATABASEAUDIT specification[Person Data Access];DROP PROCEDUREDbo.tempperson; UseMaster;GO--clean up the server audit specificationALTERSERVER AUDIT Specification Testsqlserverauditspec with(state= OFF);DROPSERVER AUDIT specification Testsqlserverauditspec;--Clean up server auditsALTERSERVER AUDIT[Sample Audit]  with(state= OFF);DROPSERVER AUDIT[Sample Audit];ALTERSERVER AUDIT Sqlserveraudit with(state= OFF);DROPSERVER AUDIT Sqlserveraudit;ALTERSERVER AUDIT Filteraudit with(state= OFF);DROPSERVER AUDIT Filteraudit;--Delete LoginDROPLOGIN Carol;

View Code

Summarize
The SQL Server auditing feature lets you log entries for various events at the server instance and database scope. You can use the SSMs graphical interface or T-SQL statements to create auditing objects that combine a number of options, such as filtering entries, writing custom information, and viewing T-SQL stack information. Auditing is an important security feature of SQL Server that can help you find suspicious activity in your database so that you can eliminate your data security vulnerabilities.

11th SQL Server Security Audit