If one was to make a list of all single-word phrases that will make any database administrator bang their head on their desk surely that list would include the word “audit”. No doubt that the idea of an audit, as well as a visit from auditor, has caused more than one painful experience for most database administrators. An auditor once insisted that I should remove the ‘sa’ account on a SQL 2000 instance because “using that account is bad, and you shouldn’t be doing that”. Well, I wasn’t using it, as I have always used windows authentication where possible, which isn’t always the case. He was just looking at a list of logins for the instance and immediately focused on that account; he told me not to use it, and then asked me how soon I would be able to get rid of it.
“Never,” I tried to explain, but he went off to the deep recesses of the building to write the report he’d already set his mind on writing. I never saw him again, and I can only assume that he was promoted at some point to oversee the Federal Reserve banks judging by the financial state we are in these days.
Auditing a SQL Server instance is not anything new. Previous and current versions of SQL have had C2 auditing functionality, which can incur more overhead than necessary and is not always flexible. For example, if the c2 logs fill up the disk to which they are being saved, the SQL instance will shut itself down. The data being collected by c2 gives details about both failed and successful attempts to access or modify objects and data, but the results are not returned in a way to make it easy to determine the nature of the events, simply the events themselves.
SQL 2008 Enterprise version allows for you to quickly and easily configure SQL Server Auditing to automate your auditing needs. The simple explanation for how this works is as follows:
- Create a SQL Server Audit object
- Create an Audit Specification (can be at the server or database level)
- Turn it on
It really is that easy to get it up and running. The hard part will come later on when someone stops by to ask for the details surrounding specific events, but even that is not difficult to provide due to the inclusion of an audit log reader.
Creating a SQL Server Audit
A SQL Server Audit is a security object that collects and logs either actions or groups of actions. It is similar to a parking enforcement officer who walks around town, checking on parked cars. If a car is found to be in violation of a particular code, the officer will write a ticket (i.e., output to a log). In our case, the violations could be found at either the instance level or the database level. SQL Server Audits are always in a disabled state when created, which is the same as saying that you need to remind your parking enforcement officer when it is time for their first day of work.
You can create a SQL Server Audit with T-SQL or through SSMS, look for it under the Security folder and you will find a folder named ‘Audits’. It is worth noting that you create a server audit, not a database audit. This is because the audit object is associated with the entire instance and yes, you can have multiple audits defined at the instance level. You will next specify what exactly you want to audit, which will be either an instance specification, database specification, or both if required.
If you right-click on the ‘Audits’ folder under the Security node inside of SQL Server Management Studio you will see a screen similar to Figure 1. Give your audit a name, and then decide if you want to store the information in a file, the Security event log, or the Application event log. If you select a file location you will need to supply a path for the binary file that will be produced. Click ‘OK’ and you are done. Of course, you could do all of this using T-SQL if you so desire, and could generate a script from this screen as well.
Creating a Server Audit Specification
Now that you have created a Server Audit, you will need to create either a Database Specification or a Server Specification. In SSMS, the Server Audit Specifications folder is located right below the Audits folder; you really cannot miss it unless you go looking somewhere else in which case go back to the start of this sentence and try it again. You can create only one Server Audit Specification per SQL Server Audit, but the specification can encompass multiple audit action groups, which just means that you define all of the instance level actions you want to be audited.
I will say there is a plethora of action groups for you to choose from, because no one knows how many actions groups make up a plethora and I like being intentionally vague. You can check the BOL for more details, or just be lazy and click on this link. I will even provide you with a screenshot (Figure 2). You’re welcome.
When you create the specification you define the state to be either ON or OFF, unlike the SQL Server Audit, which will only be OFF upon creation. The Server Audit Specification must be enabled before you can audit the action groups which only makes logical sense and I feel silly for even bothering to remind you. Here are some of the more important server audit specifications, and what they are used for:
FAILED_LOGIN_GROUP – This specification is used to identify when a principal tries to connect to the SQL instance and fails. Typically this would be a minimum audit requirement, to track failed login attempts against the SQL instance.
SERVER_ROLE_MEMBER_CHANGE_GROUP – This specification will track events that either remove or add logins to a fixed server role. Often times audits will focus on high level accounts, such as members of the System Administrators fixed server role. Use of this specification would allow for you to easily determine if and when logins are modified with respect to a fixed server role.
AUDIT_ CHANGE_GROUP – Possibly one of the most important specifications, this is the one that tracks changes to existing audits as well as the creation of a new audit. This is very important as often an auditor will want some proof that the current audit process has not been tampered with in any way.
Creating a Database Audit Specification
A Database Audit Specification can be created for each database on the instance for each SQL Server Audit. This allows for a great deal of customization as you are able to have different types of audits on different databases on the same instance. So, one database may require you to track all changes to objects in any schema, but the other database may only require you to log any time a user has been added or removed. A Database Audit Specification utilizes Extended Events to track and log events, meaning you can add audit multiple action groups or audit events to a specification, giving you the flexibility to perform various audit tasks.
You can create a Database Audit Specification with T-SQL or through SSMS, you can find it in SSMS by going to your database and expanding the Security folder (Figure 3). This is often going to be the level of detail you will need to drill into, as you will probably be asked to track CRUD events or to provide details about something that happened weeks ago.
Here are some of the more important database audit specifications, and what they are used for:
- DATABASE_OBJECT_CHANGE_GROUP – This specification is used to capture events related to the creation, the dropping, or altering of any database object, including schemas. Quite possibly one of the most often requests we see from auditors.
- DATABASE_ROLE_MEMBER_CHANGE_GROUP – This specification comes in handy when you need to track users that are added or removed from a database role.
- DATABASE_PRINCIPAL_CHANGE_GROUP – This specification is used whenever principals (i.e., database users) are created, dropped, or altered within a database. It is also used when trying to track events related to the creation or removal of a database role.
Enabling the SQL Server Audit
If you are one of those that just love to use T-SQL, then go ahead and enable the SQL Server Audit using T-SQL. The syntax for enabling the audit is as follows:
ALTER SERVER AUDIT GothamCityAccess
STATE = ON;
The above code would be quite useful (and necessary) if you needed to enable an audit on a hundred instances across your enterprise. If you are lazy like me, or just have a few instances to manage, just go into SSMS, find the Audit, right-click, and enable it. Done. Of course, you had better make certain your Specification(s) are already enabled; otherwise you will not be collecting anything.
The ability to be able to script out most of the actions you configure through SSMS with regards to SQL 2008 Audit is quite valuable for large enterprises. It will allow for you as an administrator to ensure you have consistent audits configured across all instances with a few clicks of the mouse. For example, you could configure an audit to track all failed logins, script that out, and deploy to every instance with the assurance that it is being applied in a consistent manner everywhere; that all instances will begin tracking the same events in the same exact way.
Once the audit is enabled you can view events in a log file viewer, providing of course that the events to be audited have happened. In other words, if you look to enable auditing on failed logins, you need to make certain a failed login has actually happened before you decide to send me a nasty email and say “this doesn’t work”. Right-click on your Server Audit object and select the ‘View Server Logs’ option (Figure 4).
SQL 2008 Security Audit is very easy to implement. There is no wizard, but the overall architecture is not so complex that a wizard would even be necessary. The GUI itself will help lead you in the right direction. For example, you cannot create specifications unless a SQL Server Audit exists, which should be a big red flag for you to help guide you to create the SQL Server Audit first.
Once you create your server audit, you would then create either a server audit specification or a database audit specification (or both). You would enable the specifications, then the server audit, and you will begin to collect information on the events as they happen. The use of the log viewer to review events is quite valuable, as you can filter and drill down into the specific events you need to review. As more and more pressure comes towards database administrators to provide the means to track events and activities, SQL 2008 Audit provides a variety of ways to get the job done.
I love the direction that Microsoft is taking with regards to auditing. This new functionality is much more robust than anything they have had previously. It’s like the difference in eating at a restaurant that serves lobster bisque and foie gras with one that has a menu with words like “bucket”, “basket”, or “gutbuster”.