How to Audit SQL Server For Free by Josephine Bush

This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.

In this article, you will learn how to setup SQL Server Audit with SQL Server Management Studio (SSMS) graphical interface.

What is Auditing?

Here in America when people hear auditing their first natural thought might be the internal revenue service auditing their taxes. Pretty much everyone goes UGH if they are contacted because they are being audited. It happened to me one time and the IRS person said hold on we think we owe you money! It’s the assumption that when you are audited on your taxes, you will wind up having to pay.

There is also the auditing when internal or external auditors come to your company to find out if you are following proper procedures. Those can be scary, too, so it’s no wonder auditing may have a bad reputation. Auditing in and of itself isn’t a bad a thing. In fact, I would argue, it’s a good thing. You want to make sure people are following proper procedures. In the case of the IRS or auditors at your company, there are laws that have to be followed with punishments if you don’t follow them.

In this article, what I’m talking about is auditing SQL Server to see what changes people are making at the server level or database level. Things that they maybe shouldn’t be doing and could cause problems down the road. The point of this auditing isn’t to punish people or get people in trouble, but more to shed light on possible issues or areas where you may need to tighten perms.

Why should you audit?

A lot of companies may not value auditing. Some companies are required to audit because of regulations. Even if your company isn’t required to audit, that shouldn’t stop you from auditing. The reason I set it up auditing was because I would have people come to me saying something is broken, and they can’t figure out why.

We also have many environments where too many people have access to change too many things. We are working on fixing that, but by having auditing, I was able to see who was changing what. What a relief! No more OMG why did this happen? and I can’t provide any insights. I could say to them, well I don’t see anything database side, or this user changed the schema on the 5th.

In the end, my company was acquired by a more heavily regulated company, and they needed the systems to be audited with proper reports reviewed weekly. My company was very pleased that I had already done the leg work making it easy for us to do the auditing. Originally, the management at my company didn’t think this was a priority, but I worked on it in between other projects, and basically built exactly what the new parent company needed without knowing they would need this. Auditing can help you regardless of who says you need to do it.

Problems Auditing can Solve

Auditing can help you solve a lot of problems. Like I said earlier, people come to you saying something is broken, why? You are in the dark without auditing. If you have the auditing setup you can see who changed something so maybe that change is what broke.

You can also see if someone used something in the case of a table or a user. We had a scenario where the sa password was handed out like candy. This was such a bad idea, and we had to get users off of sa. In order to do this, we had to know who was using sa. You can’t just ask around and say “hi are you using sa on this server?” You either get people who don’t respond or people who just don’t know or say they don’t have time to look. We audited sa, got a list of users, and those users and their managers were contacted to say we have to move off sa, we see you are doing these things with sa, so let’s get you a username/password setup so you can continue to do the work you need to do. As part of this, we also examined what people were doing to see if we could pair down on the level of perms they were granted in a production system. Always going for least perms. Most of these users didn’t even realize the power they had using sa, and all of them just used it in a way that was in alignment with their work.

We did catch one guy sharing out his domain user and password to allow someone else to perform his job duties while he was out of the office, which is a huge no-no. We’ve used auditing to see who might be using a shared account. A sql user may have been setup originally for one purpose, but now it’s been shared around, and we don’t want different users using the same login period. There was another case where the development team was trying to figure out what was writing to a table. They could see updated data, but had no idea where it came from, so it’s another way auditing can help you.

How to Audit SQL Server

To audit SQL Server, you can use built-in functionality called SQL Server audit. It’s good for capturing pretty much anything that happens on the database server. It’s very flexible and pretty easy to set up. I primarily use this for seeing any permissions or schema changes. You access it via SSMS and can also use scripts to set up and query the audit data. This article will only cover setting it up in SSMS graphical interface, but you can definitely script it all out, too.

SQL Server Audit Availability

The first version that SQL Server audit was available in was 2008, and it was only in Enterprise edition. As of 2012 version, Microsoft expanded it to be available at server level with all editions, and then database auditing was still only in Enterprise. By 2016 and onwards, you can do server and database auditing with any edition, so much nicer since a lot of people don’t only use Enterprise. Just note these limitations if you are on older versions and wonder why you might not be able to do everything outlined in this article.

Audit Requirements
To make SQL Server auditing work you need two or three things depending on what you want to audit.

• You’re required to create an audit specification, and this will determine where you store audit data.
• You will also need one server and/or one database audit for audit data to actually write to the audit specification. Each audit specification can have one each server and database audit. Those server and database audits are not dependent on each other.

SQL Server Audit Use Cases

The server audit is generally good for:
• Auditing server level changes and/or all the databases at the same time.
The database audit is good for:
• Auditing one database or a subset of activities in one database.
Let’s go through some examples so that you can see how that works.

SQL Server Audit Setup via SSMS

First, you need to create an audit specification. This is where your audit data will be stored. No audit data collects without this, and you can’t setup a server or database audit without first having an audit specification.
In SSMS, you can create an audit specification by right clicking Audits under the Security section.

You will then see a dialog box with lots of options. How to configure each is included below the image.

Suggestions on how to configure your audit specification

• Audit name: I tend to name it AuditSpecification or AuditSpecification_servername.
•  Queue delay: I always leave it at 1000. It’s just the wait time in milliseconds before it audits. I never see any lag.
• On Audit Log Failure: I choose continue. I think fail operation and shut down server are too drastic. I’ll just have a bunch of people screaming at me that there’s something wrong with the server. You may want to choose fail operation or shut down server if auditing is of the utmost importance like on a legal/financial reporting systems.
  • Continue – If the audit can’t capture the statement, it keeps auditing. You might miss a statement here and there, but I doubt that happens very often, if at all.
  • Fail operation – If it can’t audit, it’s going to cause the statement to fail.
  • Shut down server – If it can’t audit, it’s going to literally do what it says, it’s going to shut down the server.
• Audit destination:
  • File – I always write to the file choice because it’s easiest for me. I don’t have a lot of auditing restrictions. Yes, the auditors want to know what happened. They don’t think that we’re going in there secretly deleting audit files and not reporting what we said we would
  • Security log – this has more restrictions than writing to the application log
  • Application log – I could see application log writing if you have a Splunk application that reads all the logs and gathers them in a central repository
• Path: If you chose File for Audit Destination, then you need to choose a path. Make sure don’t put it on the C drive. Even though we’re going to limit the audit file sizes, you don’t want it accidentally filling up the C drive. I don’t usually put it on data drives or log drives either. We have an E drive for applications where I work, that’s a great place for it to go.
• I never let the audit just collect max files and max sizes. I’ve found 4 files of 50MB each is good for my needs when collecting permissions and schema changes. Your number and sizing of files depends on your needs. If you have maximum files, and they’re gigantic, then they’re next to impossible to query.
  • Maximum files – 4
  • Maximum file size – 50 MB
• Reserve disk space: Since my files are quite small, I never check that.

Once you’ve created it, it’s disabled by default. Right click it to enable it. No data collects if you don’t enable it.

If you chose the file destination for your audit, once you enable it, an audit file is placed on disk. This is where the data will live for your server and database audits that you associate with that audit specification. As the data collects, this file is going to grow to 50 MB because that’s what I specified in the configuration, and then it’ll create another file up to four (again because I specified four files). Once the fourth file is full, it will delete the oldest file and create another new file. That’s why it’s important for you to know how often you want to collect the data from the audit files so you don’t miss any data.

Audit Categories

Before we get into creating a server audit specification, let’s talk a bit about audit categories. There are three of them:

• Server level – gets everything at the server level, but you can also audit all the databases if you configure database actions at the server level. These capture permission changes and creating databases. Includes any action that doesn’t start with schema or database
• Database level – If you don’t want to audit all the databases the same way, then you can use the database level actions in each database to audit them separately. These include data manipulation languages (DML) and data definition language (DDL) changes. Namely things at the database level. Mainly, includes any action that starts with schema or database
• Audit level – audits the changes you make to the audit specifications. Turning an audit on or off is automatically captured and you don’t need to set that with the AUDIT_CHANGE_GROUP action.

NOTE: You don’t want the same database actions turned on at the server level and the database level because then you are getting duplicate audit records.

More information on server and database audit is provided in these links:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15#database-level-audit-action-groups

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15

Creating a Server Audit

The first of two optional audits is the server audit specification. You can set up this under the security section of the server, and right click it to create a new one.

A dialog box comes up and the options will be discussed below.

Suggestions on how to configure your server audit specification:

• Name: I tend to name it ServerAuditSpecification or ServerAuditSpecification_servername.
• Audit: You need to associate it to your audit specification. There is a dropdown with your audit specifications listed. You need this because this is where your audit data will live.
• Actions: I’m capturing perms and schema changes at the server level, and in all the databases on the server. That’s what these action types will do. If you just want to capture server changes, you’d remove all the ones starting with database and schema. You don’t have to fill out any of the other columns. they’re not applicable to these action types.

The server audit specification is also disabled by default, you can right click to enable. It doesn’t collect any data if it’s disabled.

Creating a Database Audit
The second of two optional audits is the database audit specification. These are in each database, you have to go to the database security section and right click.

A dialog box comes up and the options will be discussed below.

Suggestions on how to configure your database audit specification:

• Name: I always name it underscore database name because it helps identify what database it’s auditing like DatabaseAuditSpecification_Auditing.
• Audit: You need to associate it to your audit specification. There is a dropdown with your audit specifications listed. You need this because this is where your audit data will live.
• Actions: I’m capturing perms and schema changes at the database level. Don’t use this if you’re already getting database and schema changes at the server audit level or you wind up with duplicate audit records.

Where the database audit really shines is when you want to audit objects because you can audit things like insert, update, delete, select, and execute on objects in the database, like tables, views, and stored procedures. These action types do require you to fill in all the additional columns for the audit actions depending on the object class.

Let’s look at an example.

Suggestions on how to configure your database audit specification when auditing specific objects, schemas or databases:

• Name: I always name it underscore something descriptive to its purpose because it helps identify what it’s auditing.
• Audit: You need to associate it to your audit specification. There is a dropdown with your audit specifications listed. You need this because this is where your audit data will live.
• Actions:
  • Audit Action Type:
    • INSERT – gives you the ability to see who inserts to a table, schema, or database.
    • UPDATE – gives you the ability to see who updates a table, schema, or database.
    • EXECUTE – gives you the ability to see who executes on a stored procedure, function, schema, or database.
    • SELECT – gives you the ability to see who selects from a table, schema, or database.
    • DELETE – gives you the ability to see who deletes from a table, schema, or database
  • Object Class:
    • OBJECT: Choose this to see queries against a specific table, view, stored procedure, or function.
    • SCHEMA: Choose this to see queries against any object in a schema
    • DATABASE: Choose this to see queries against any object in a database
  • Object Schema: Required for OBJECT or SCHEMA object classes
  • Object Name: Required for OBJECT class
  • Principal Name: Required for OBJECT, SCHEMA, and DATABASE classes. Use public if you want to audit everyone. If you want to audit multiple users, you need one line for each user.

Once you create the database audit it’s also disabled by default, you can right click enable it. It won’t collect data when it’s disabled.

Querying Audit Data

You can query the audit via the GUI by right clicking the audit specification to view the audit logs. A dialog box pops up with the 1000 most recent events. Maybe there’s nothing listed because nothing auditable happened yet. Maybe you’ll see tons because there’s a bunch of stuff happening in the background that you didn’t realize was happening.

Disclaimer on Auditing

Be very careful how and what you audit. You can overload or freeze up a production server. It happened to me when I didn’t even think it was possible to crash a production server with an audit. I just thought somehow, I could stop the audit in between things it was auditing. Sometimes it’s going to be hard to stop your audit if it’s auditing too much or it’s going to be so hard to weed through all the data, it will be like trying to find a needle in a haystack with all the stuff you’re auditing. I just go with the less is more method of auditing.

This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.