Database Auditing

With SQL Compliance Manager

SQL Compliance Manager

In this training video, I will be walking you through the audit configuration settings for compliance manager. We will cover instance level audit settings, such as privileged users, database audit settings, including before and after data sensitive columns and trusted users. The goal is to capture all the information required for your external auditors, internal auditors and or your own internal it needs while excluding all other events or activities. If you are required to adhere to specific regulatory guidelines, please consult with your auditors to ensure that the information you're capturing meets the regulatory guidelines, compliance manager captures event information at two levels, the instance level where events such as creating new logins, granting role permissions, or creating new databases occur. And at the database level where specific audit settings can be configured for each database.

Server Auditing

We'll start with the instance level settings first, right, click on the server or the instance, and then check properties. We'll go to audit activities. Now these are the audit activities for their entire SQL server. So any accounts that do any one of these things that we check here is going to get captured. I'm going to talk about each of these and why we may or may not enable something to start off with.

Logins

We have logins. Now you have to be careful with logins because it's not necessarily one-to-one relationship with an application with logging events, to your SQL server. For example, if you have an application that every time you do an activity, it creates a new session. Does that query whether it's an update, retrieve information and then closes that session, you can see hundreds of log-ins for an individual a day.

Failed Logins

So when we do a log-ins typically I like to do this specifically for my privileged users. So those are going to be my DBA days. The next thing we have here are my failed logins. Now it's always a good idea to capture those failed log-ins. It could be something as simple as I've missed typed in my password. It could be something where I've updated a service account, and now it's unable to successfully log into the SQL server or worst case scenario. Somebody is trying to hack into the system. So again, it's always a good idea to track those failed log-ins. I could enable security changes here and as well as database definition changes, but those are really things that would be done by my DBHs or potentially my developers.

So I'm going to enable that under privileged user auditing that we'll get to next administrative activities. Those are things like backing up your database, running a DBCC check DB, maybe rebuilding an index, things that are truly administration or administrative and function against my SQL server instance for auditing purposes. We typically don't need to capture that for internal. It needs, we may. And again, if we do that, we'll do that for our specific deviation. That way we don't capture just generally scheduled backups.

User defined events

And then we do have user defined events. Now a user defined event is not something that we create within compliance manager. It's actually, you can write T sequel code to generate. What's called a user defined SQL server event. It has a unique V event ID. And if you are using code to generate some of that, we can capture it.

Access filter

And then lastly, we do have this access check filter. Now, when a user does an activity, the first thing that happens is SQL server verifies whether or not they're allowed to do it. For example, let's say Chris is, has read only permissions on a database, and he's trying to do an update with this check filter here. We're simply not going to capture the activity because when he tried to do it, CQL server told him no access denied. However, in cases where you do actually want to capture, if somebody tried to do something and they weren't allowed to, if you uncheck this, we do have something called a permissions denied report, and you can capture that activity.

Privileged user

Talking with most auditors that I've worked with, and my customers have worked with typically, they only are looking for what a user actually did, not what they're weren't allowed to do. Next. We have privileged user auditing. The first step here is to select who your deviates are. Now privileged user is a user with elevated access. This would be people like DBAs possibly developers, and we want to track what they're doing at the instance level. Now, when you first click add, you'll notice the default is to look by server role. And I have customers that often come down here and say, see systems, administrators, and think that's exactly what they want to track. But the real answer is that we only want to track our human users.

And if you select to capture system administrators, we could also get service accounts and other things that are going on internally that we simply don't want to audit. So my recommendation is to do use the dropdown, go to my server logins and then select the appropriate deviates or developer groups or individuals in this case, I'm going to select my domain group called DPA's and I'll even go ahead and add myself explicitly as well. Now I can choose which particular events or activities that I'm going to capture because typically CBAs and developers do not use an application. There's a little bit more of a one-to-one login to each individual server they touch. And because they do have the elevated access I've chosen to capture their just be so I can track which servers they're looking at, failed log-ins.

As I talked about before, it's always a great idea to track failed logins and security changes. That's probably why we're auditing our DVS in the first place. We wanna capture whatever accounts they're creating, what additional privileges they're granting, or even revoking. And we're going to capture all those security type changes, administrative actions. Again, that's things like backing up the database from an internal. It need, if I want to go ahead and track that I can select it. Typically auditors just don't care. That happened database definitions. This is going to be anything to do with the change of the schema, whether it's creating a new index, creating a new database, updating a table, whatever it is I'm going to capture that information.

Regulatory requirements

Now, if you're doing something like Sox auditing, that's definitely something that you want to have enabled.

Some of the other auditor requirements don't necessarily need that. Again, you'll have to verify with your auditor to find out whether or not they want you to capture that information. I can also track database modifications. This is going to be insert, updates and deletes. Now, if I check that here, any database that either one of my DPA's or myself modify, or I should say updates, the data for is going to get captured. And that would include other databases other than what I've explicitly have selected here. So it's up to you on whether or not you want to track everything that your DBS are doing, or whether or not there's just some specific things. Now, in my case, because I do have my database specific auditing, I'm going to go ahead and uncheck that way.

If they were doing something in another database that really we don't need to audit, I'm not worried about capturing that information. The same thing would be true for selects. If I turn this on any select they run, I'm going to capture, but I'm going to be very specific on what type of select operations I capture. And I'll show you how we do that at the database level. Again, I could also capture those users to find events. Again, those events that you create using T SQL code, I've chosen not to do that. And because I am capturing database definitions, I can also choose if I want to enable capturing the SQL statements. Again, it's up to you on whether or not you need to capture that information.A lot of times when it does come to the DDL type events, we do go ahead and capture that from an auditing perspective. I've had customers tell me that yes, their auditors want it. No, their auditors don't want it. And it doesn't really depend on which regulatory guideline they're trying to adhere to. So again, you'll have to make a determination or verify with your auditor, whether or not you want to capture that kind of information. Now that we've completed the instance level audit settings, go ahead and click.

Database Auditing

Okay. And then let's start with the database specific audit settings. I'll choose this database where I click and select properties and then go to audit activities. And now let's cover the individual audit activities for this database.

I could capture the database definition changes, but I'm already doing that for my DBS or my privileged users. So I'm choosing to uncheck that. Security changes while I am collecting that for my DBS. Maybe I have a DB owner. That's not a DBA and they're not in the privileged user list. So I'm going to go ahead and leave that checked administrative activities. I don't care that it's been backed up or certainly my auditor doesn't. So I'm going to uncheck it database modifications. This is going to be things like my insert updates and deletes. It's probably why I'm auditing this database in the first place. I'm going to go ahead and check that now with select operations, there's a lot of selects that go on all the time. It was, and you'll know this. If you've ever run a profile or trace.

And because of that, I want to be very specific on the select information I capture. And we're going to find that under sensitive columns. So for now, I'll leave that unchecked. We also have that access check filter, and I could capture the SQL statements for DML and select activity if I want to. And then if I also want to capture the transaction status for the DML activity, that's things like begin transaction rollback or commit transaction, things like that. If I really want to capture those individual things, I can select that again. Auditors typically don't care about that. I'm going to leave it unchecked.

Data filtering

The next tab we're going to talk about is DML select filters. Now this is where we can be very specific in what we're going to audit on that database.

Whether we're going to audit all the tables or maybe select tables as an example, what I'll do is I'll show you where I've done that specifically for the MSTB database. Now, why would I audit the MSTB database? Well, I want to go ahead and capture any time anybody does an update to one of my SQL server agent jobs. So I'm going to Mo I'm going to audit that MSTB database because that's where everything is being recorded. That said there's also a lot of other stuff in there that I don't want to capture. So I'll go to audit activities. I just want my DML in this case. And then I'm going to select EML select filters. And I want to be very specific on the tables that I'm going to capture, and I'm going to search on the CIS job tables.

Track specific data

And now we'll go ahead and add each one of those. So this is an example where I can be very specific on the tables that I'm going to audit versus everything on that entire database. Go ahead and click. Okay. And that let's go back to the original database were working with. We just finished talking about DML select filters. Now let's talk about before and after data, when I enable capturing DML, I'm going to capture insert updates and deletes, who did it the time and date, of course, and then what database and table that was affected, but I'm not going to make any records of what the values were or what they got changed to in cases where data is very sensitive in nature. And I do need to capture that information or just very rarely changes.

And I want to make sure that I have information in case I have to correct something I can choose to select either by table or column information that I'm going to capture those values for. So I'll click on add select the table. And by default, it'll do all columns, but I want to just collect information for one specific column. So I'll click edit, say audited selected columns, and let's remove the ones I don't want. And for example, just leave salary. So now whenever the salary column is updated, not only will I have a record that Chris updated it, I will know what the original value was as well as what he changed it to. Now, let's talk about sensitive columns. This works very much in the same way that before and after does the differences. This is for select operations.

Sensitive data

I'm going to go ahead and select my employee table again, But this time, instead of anything, I just want to know whenever somebody access that social security number, that would be considered sensitive information. So in this example, if I were to do a select first name, comma, last name from employee, I'm simply not going to record that a select operation occurred. No sensitive information was accessed. However, if I do a select first name, comma, last name, comma SSN from employee. Now that sensitive information has been accessed, and I'm going to have a record that somebody pulled or retrieve that information specifically, Chris, at this certain day and time, the next setting is trusted users. Now, what is a trusted user? It's a user we trust and that we're simply not going to audit what they do on this database.

And the only kind of users that we trust would be user accounts that are actually not users are human users, but service accounts. I'll go ahead and click add. I'm going to check server logins. And in this case, I do have a domain group called Idera services, and I've got a number of service counts in there. Now, when any of my service counts does anything on this database, I'm simply not going to record that activity. This will really help eliminate a lot of that extra activity or noise as my customers call it in the reports because it's activity done by service accounts. And again, auditors are really only interested in what the human users are doing, not what non-human users are doing.

Privileged user activity auditing

And finally, we have our last tab, which is privileged user auditing.

Now in cases where maybe you have a user that's not a DBA or developer, so they're not a privileged user. However, maybe they're a DB owner, or they do things special on this database that other users don't. And for that reason, we need to audit them a little differently. So what I can do is click on, add, select the account. And then from here, again, I can define what are audited activities. I'm going to capture in addition to the regular database audit settings for this individual. So if you remember, what I had at the database level looks something similar to like this, where I was capturing security changes and database modifications. Maybe for this particular user, they don't use the application. They are using something that allows them to run selects or other things.

And for whatever reason, I've chosen that I need to capture that information for that user. So in this example, I'll capture their selects. Maybe I just want to capture their schema changes or the DDL. And now for this individual user, I'm capturing their information a little bit differently than everybody else again, in which way we can be very specific on how we audit the information and I'll go ahead and click, okay. The last thing you always want to do and get a habit of this. Now we will automatically force the chain or send the updated audit settings down to that agent. But since we've been in here and we've taken the time to make those configuration changes, go ahead and just right.

Click on the server and select to audit the update, the audit settings now, and now force the changes down at this point, we've completed all our audit settings.

Conclusion

And now we can go ahead and collect that information over the next few days. You want to review the information you're capturing, make sure it's everything you need in other stuff that you don't want to need. If you click on audit events, you can see here's the specific advanced information that I've been capturing so far. This looks good. And I'm just going to monitor it over the next couple of days. I hope this video was helpful for you. If you have any questions, please reach out to your account managers or send an email to [email protected] and we'll be happy to help you out. Thank you for your time today.