Transcript

00:07

SQL Server Security and Compliance

In today’s video, we will be looking at IDERA’s solutions to help you with your security and compliance needs. The first tool, SQL Compliance Manager, will monitor audit and alert on user activity, including data schema and security changes, as well as Login activity. Its companion tool, SQL Secure, will report on effective user permissions, including those users that are a member of a domain group. It will also identify the vulnerabilities of your SQL Server security. Let’s take a quick comparison look of the two tools. SQL Compliance Manager will capture who did what, when, where and how. It’s a continuous auditing method and it’ll capture who did the changes. It will also alert on specific events as we capture them. SQL Secure will capture who can do what and identify SQL security vulnerabilities. It’s a scheduled snapshot approach and we can see changes over time. It will also alert unsecurity risks and violations.

01:20

SQL Compliance Manager

Let’s take a closer look at what SQL Compliance Manager can do. It’s all about auditing sensitive data, see who did what, when, where and how. I say audit sensitive data because we can be very granular on how and what we capture with that data against your SQL Server. We can also track changes such as data access, which is nothing more than security changes, schema changes if somebody were to happen to drop a table. We can also track things like failed logins.

Regulatory Compliance

Now, it’s one thing if I forget my password, but it’s another if somebody is trying to access something they shouldn’t be allowed to do. We can uncover those threats as well. We can certainly satisfy those audits. That’s what this tool is all about. We have all the data necessary to meet PCI, HIPAA, FERPA and Sox Compliance and really anything out there because of the data that we capture.

02:21

We have 25 built in reports. There’s really three that are most useful that I’ll show you later on in this video. It does utilize low overhead, or what we call a lightweight data collection agent that minimizes server impact. We also have a web based dashboard this simplifies access from any browser.

SQL Server Compliance

So how does SQL Compliance Manager work? Let’s talk about the architecture behind the scenes. Up front, we have the SQL Compliance Manager console. This is where we’ll register our servers to be audited, set up the audit settings as well as we can go through and explore the activity that we’ve captured. We can also run any of the reports from the console as well.

Compliance Manager Repository

Now, everything is stored on a SQL Server in what we call our Compliance Manager repository. It does require that SQL Server is installed locally with the Collection Server for Compliance Manager because we’ll be leveraging that database engine to process events as we capture them from our audited SQL instances.

03:33

Because of this, the version of SQL Server that we have installed locally must be at least as great as any of those that we’re auditing. For example, if I have SQL Server 2014 installed on the Collection Server, it’ll understand what an event looks like from SQL Server 2000 all the way up through 2014, but may not necessarily understand what an event looks like coming from SQL Server 2016. That’s why we do have that requirement.

Now, the way that this data flow works is that we have our audit settings locally. The Collection Server will then pass these audit settings down to that SQL Compliance Manager agent. That agent has two primary jobs. The first one is to take those audit settings and apply it to that SQL instance it’s responsible for. The second thing is, on a two to five minute interval, it’ll collect that data, compress it, and send it back to the Collection Servers to be processed.

04:37

Auditing SQL Server

Now, because of this method, the Collection Server itself actually doesn’t have to have any permissions whatsoever into the audited SQL instance. It’s just that local agent that needs to have permissions. The other thing is, if this Collection Server ever goes down or there’s a problem with the network, everything will still be audited. It will just be cached in a local file until the connection is reestablished and then the events are passed over to be processed. We can also take all the reports and deploy them to a Microsoft Reporting Services. This will allow you to grant other users permissions to run those reports without giving them any permissions whatsoever to the repository or the Collection Server.

SQL Server Permissions

A couple of questions that I always get asked when I do this presentation. The first one being, what is the permissions required for that SQL Compliance Manager Agent? We do require that it has Sysadmin privileges within SQL Server because we want to make sure that we’re capturing all the events that could possibly happen on that server.

05:49

The other question I always get is what is the storage requirement? Well, that’s really going to depend on how much event information we’re capturing. I have one customer that’s auditing 90 servers. However, the only activity they’re capturing is what their developers doing. That’s going to be things like DDL activity and maybe security changes. In that case, it’s very little data. I’ve got another customer that has auditing only one database, and of that database, they’re only auditing three tables. Yet with all their transactions, they actually capture 1 million transactions every about 90 minutes. Really, that’s what it comes down to. How much transactions are we capturing? A good rule of thumb with all the options enabled is about 1GB of storage space for every 1 million transactions.

SQL Server Performance

Finally, the last question that definitely comes up is what is going to be the overall performance impact when I audit one of my servers? Because of the audited method that we have, it’s actually very low. I think the official engineering answer is less than 3% overall utilization. Talking with my customers and what I’ve personally seen, it’s probably closer to less than 1%.

SQL Compliance Manager

Now let’s take a quick look at how Compliance Manager works in action. Here we have the SQL Compliance Manager console. I have a number of servers registered, including a failover cluster, a standard SQL Server, and even some named SQL instances. Compliance manager can also audit availability groups. Now, when it comes to auditing Availability Groups, we have two different methods. The first method is to just register the listener name. This, however, will limit us to only being able to audit those databases that are a member of that Availability group. Talking with my customers, their auditors prefer the node method. With the node method, we register each individual node of the Availability group, and then we can capture all the events that are going on with that SQL instance, not just those limited to the databases that are a member of that Availability Group.

08:14

Now, when it comes to auditing, Compliance Manager audits at two different levels. Reason being is different events or different activity happens at the different levels at the instance level. For example, if a DBA creates new database, obviously that’s going to happen at the instance level. This is also where things like some of the security changes, even the login activity is going to be captured at the instance level. In fact, even if I connect to a specific database, the authentication still happens at the instance or server level.

08:50

Database-Level Auditing

We have some very specific database level audit settings that we can also configure. I’m going to run through those real briefly so you can see how we can do our auditing and what we can capture. Let’s start by looking at the server level audit settings. Select your server, right click and select Properties. This first general tab talks about general things that have been going on with our auditing. There is one thing I want to point out, and that’s called the database integrity. This is not the integrity of the database that we’re auditing, but it’s actually about our event database, where we’re storing all the information as we capture it. If anyone were to go in there and delete data or modify the data when we run an integrity check, it’s going to fail. The reason why we have this check is that when you have auditors and you give them all these reports, one of the things they’re going to ask you is how do they know that the data you’ve given them is accurate and complete and it hasn’t been modified in any way?

09:59

That’s what database integrity is all about. Not only do we audit those external servers, we also audit ourselves. But let’s talk about the audited activities. There’s a number of things that we capture. Remember, this is at the server level.

10:14

Failed Logins, Security Changes, and User Defined Events

This is going to be for any user anywhere on the system. First, we can capture Logins failed logins, which is what I’m capturing here. I can also capture all security changes, database definition changes, administrative activities. These are things like running a DBC check, DB command, or maybe doing a backup. I can also capture user defined events. Now, this is not a user defined event that we create within Compliance Manager, but if you write TSQL code, you can generate what’s called a SQL Server user defined event. It has its own unique event ID, and if you want to capture that type of event, we can certainly do that by checking this. For most auditors, though, they’re good with just the standard event types. We also have an access check filter. When a user does an activity, the first thing that’s going to happen is SQL Server is going to verify whether or not they have permissions to do that activity.

11:15

Now, when it comes to auditors, typically they don’t care if somebody tried to do something and SQL Server said, no, you can’t do it because they didn’t have proper permissions. However, if you uncheck this, we will also capture any activity that doesn’t pass that access check, and we have a report in which you can look at that activity. So, for example, if a user had read only permissions and I had this unchecked and they tried to do an update, for example, SQL Server will say Permission denied. That activity will then also be captured in our permission denied report. You can verify whether or not that user should have had permissions to do it, and if so, correct their permissions or maybe find out why they’re trying to do something they shouldn’t have access to. For most auditors, they only care what activity a user actually did.

12:09

Privileged User Auditing

We’ll go ahead and leave that enabled. Now, we also have something called privileged user auditing. A privileged user is a user with elevated access, typically our DBAs or our developers, those with that sysadmin role. I’ve got myself added explicitly here, as well as I have a domain group called DBAs that’s got a number of accounts in there. If anybody from this group, this is the type of activity I’m going to be capturing on my SQL Server. Now, it doesn’t matter what database it is, again, this is going to be an auditing that occurs at the server level. I can capture Logins failed. Logins is always a good idea to track. I can also capture security changes. In fact, that’s probably why I’m going to be auditing my DBAs in the first place. I need to know what accounts they’re creating or what permissions they’re granting.

13:02

I can also capture administrative actions. Again, this is going to be something like a database backup. That could be important if you are doing Sox compliance, because we want to know if a DBA is doing something outside of a normal scheduled window. We have database definition changes like creating a table, dropping a database, and then we can also capture database modifications. Now, database modification is things like insert updates and deletes. If I check that, it doesn’t matter what database it is that they’re working on, I’m going to capture it whether in an explicitly audit database or not. I can also capture any select operations they do, including, as we talked about before, those user defined events. In my case, because I’m capturing the DDL, I’ve chosen to also capture the SQL statement associated with that. I’ll get the actual SQL statement that they ran to do that activity.

14:00

Audit Thresholds

We also have some audit thresholds. This is really just for my alert notifications that I have on my what we call the report card. If I go ahead and check any one of these and I click OK and I look at failed logins, if I exceed a threshold, it’s just going to highlight it.

14:23

SQL Statement Limit

The advanced tab is the biggest thing. Here is our SQL server limit, the statement limit so that when we capture the SQL statements, we’re going to by default truncate to just the first 512 characters. That’s usually enough information to understand what’s going on. However, if you do need to capture the entire SQL statement, just select to store the entire SQL statement. Or maybe increase this by up to 1024, something like that. Now let’s go ahead and look at my database specific audit settings.

14:59

Database Audit

Let’s take a look at my audit settings for the Financial Records database. Select the database right click and select Properties. Again, I have my general tab with some just general information about my auditing. But let’s look at my audited activities. These are the things I’m going to be capturing for anybody that accesses this database. In my example, I’m going to be capturing any of the security changes. Now, I know I’m already capturing this for my DBAs, but maybe I have a DB owner that is not a DBA and they’re not part of that server level privileged user audit settings. I’m going to capture any security changes. I’ve also chosen to capture all the database modifications, DML, insert updates, and deletes. That’s usually why I’m auditing a database in the first place.

15:49

Protecting Sensitive Data

Now, I could choose to also capture all select operations, but you got to be careful here. If you’ve ever run a profiler trace, there are tons and tons of selects that go on all the time. In my example, I really only care about what sensitive data has been accessed and we’ll define that under sensitive columns. I have my access check filters, so they must be able to do the activity and pass it in order to capture it.

SQL Statements

Finally, in my example here I am also capturing the SQL statements for DML and select activity. In some rare cases, I’ve had customers that I’ve also needed to capture transaction statuses. And we can do that as well. For the most part, we really don’t care about a begin transaction or a commit or a rollback. If a rollback occurs, nothing really happened and we don’t need to capture that activity. I also have something called a DML select filter. Now, we’ll use this in cases where we don’t need to audit everything in the database, but maybe just specific tables. One example would be the msdb database. Now, why would I audit a system database?

17:02

SOX Compliance

Well for SOX compliance. As an example, I need to track whenever any changes occur to the SQL Server Agent jobs. That’s where all this information is stored. I’ve chosen to audited and let’s look at what I’m doing. I’m just capturing the DML activity. More importantly, I’ve selected just the tables as they relate to the SQL Server Agent jobs. Now if any of this data gets updated, I’m going to have a record of it coming back over to my Financial records database.

I also have something called before and after data. Now, when we capture DML events, we’re typically just going to capture that. John did an update against this database and this table and it occurred this time and day. We may even capture things like the host that he was on as well as maybe the query. What we’re not going to capture is the actual data change, what it originally was and what we got to change to.

18:08

In cases where data is very sensitive nature or just rarely changes and we want to have a record of what that change was, we can add those tables and or columns to the before and after data. In this case, I’ve selected my employee table and more specifically the salary and Social Security number fields. So if either one of these gets updated, I’m going to have the original value as well as the new value. I’ll show you what that looks like in just a moment. Sensitive column works very much in the same way. The difference here is this is for those select operations.

18:43

So as I talked before, I didn’t want to capture all selects, but in this case, anytime the personal identifiable information or Social Security numbers is accessed, I’m going to make a record of it. So if I were to do a select first name comma, last name from employee, no sensitive information was accessed. I don’t make a record of that select statement.

19:06

Trusted Users

However, if I do a select first name comma, last name comma SSN from Employee now Sensitive information has been accessed and I’ll have a record of that select statement. We also have something called a trusted user. Unlike a privileged user, which is usually a human user with elevated access, this Is going to be a user we trust and that we’re not going to audit on this database. Typically the only accounts those would be my service accounts. I have an idea of services account domain group, number of services in there, and any activity that those services do, I will simply not capture for this audited database.

Privileged Users

Finally, we do have a privileged user audit setting. This is in the cases where maybe I have a special user, they don’t use the typical application and I need to audit them differently. If you recall at the database level I was capturing security changes and database modifications, but maybe because they aren’t using their typical application, I’m also going to capture all their select operations as well. Again, it’s just another way in which we can audit a special user differently than everybody else.

Regulatory Compliance

We can also apply a Regulatory Guideline for your audit setting. Instead of doing the manual steps that we just did, you can select your database. You can say apply regulatory guideline. This is really a wizard that it’s going to walk us through the settings that are predefined for whether it’s HIPAA or PCI or both. In this case, I’ll go ahead and select HIPAA, click Next and it’s telling you upfront what it’s going to set both for at the server level as well as the database level.

21:01

It will prompt for the privileged users. It will also confirm the privileged user audit activity that we’re going to capture, as well as the sensitive columns. Now, when it comes to sensitive columns, you will need to know where your sensitive data is located, in what databases and what tables. Later on I’ll show you how we can identify those through our column search. It does do a quick permissions check. Now this particular database has that Regulatory Guideline applied to it.

Import and Export Audit Settings

The other thing that we can do is we can also import or export audit settings. For example, if I wanted to save this or apply these settings to another server, I can go to File and first we would export it, for example, export the audit settings. Actually, in this case, what I’m going to do is I’m going to import some settings I had already saved.

22:03

We’ll do File and import the audit settings and then select the file. In this case I’m going to do it for that financial records database. Now we have export files for the databases themselves or even the instance. I’m going to select the instance, I’m going to select the database settings to import, in this case for financial records.

22:31

What server I’m going to apply it to, so I can even apply it to a different server and then which databases, and I could select one or all of them. In this case, I’m just going to select the financial records one. You have a choice of whether to add to the current audit settings or overwrite. I’m just going to go ahead and do an overwrite and now I’ve applied those settings. We have 25 built in reports, and really there’s only three that you’ll be using the most when it comes to your auditors. Let’s scroll down and the user activity is going to be your most user report.

23:10

It’s the most versatile. It really has all the information you’re probably going to need. I’ll go ahead and select my server. I’m going to go ahead and adjust the time ranges so I don’t have quite so much data. Now when I run the report, you’re going to see all that same information we saw before from the login user, the host that they were from the application they were using. In this case, I was doing it through SQL Server management studio.

23:39

The database affected what I was doing. In this case, it was an update. What I was updating, including the time and date, that can include the SQL statement. I could even do it by a Specific type of activity. Maybe I just want to look at the security type category and I’m going to have all that here. This is the most used report.

24:04

DML Activity

Other ones that are really common for your auditors are going to be your DML activity, the before and after. Again, it’s going to look very similar to what we saw before in the Event Explorer. It was an update, the time and day who did it, against what database, the table affected, as well as the column or columns that we have defined and what the original and new values are, as well as that primary key. The other one for the auditors is probably going to be the sensitive access the sensitive column activity. Again, very similar to what we saw in the Event Explorer. Select the time and day who did it, all that information, including the column or columns that we have defined. There are also some very useful reports for an internal purposes. For example, I talked about this before, the permission denied activity. This is going to show that activity where somebody tried to do something and they didn’t have the proper permissions.

25:10

We’ll go ahead and look at this server. Specifically, I’m looking at the failed DML activity. We’ll even include the SQL statement. Now when I go ahead and run this report, you can see that. For example, Bruce, who only has read. Only permissions, was on this host using SQL Server Management Studio against this database. He was trying to do an insert against this table. I even have the SQL statement here. Now I can go find out whether or not Bruce should have permissions, do an update, and if not, find out what Bruce was trying to accomplish. Other information that can be very helpful, one obviously is that we audit ourselves. Anytime there’s any changes to the system, we’re going to have that as well. That’s going to be under audit control. Changes. You can see here I’ve got information on where I’ve done changes to some of the audit settings.

26:03

SQL Server Activity Reports

So we track all that as well. Another report is the application activity statistics. When we look at that for a particular server, it’s going to summarize the activity for that server. It’ll sort it first by the application name. You can expand it out and then I can either look at it by the activity on Database, the event type, the login name, the target object. Let’s go ahead and look at the event type so you can see it has a summary of how many updates, how many deletes, things like that. Now, all the reports we have here can also be deployed to Reporting Services. Again, this is a way in which you can give other users access to the reports. They can subscribe to them, have them sent to them, emailed to them automatically, or the reports can go to a central location. They can do all this without giving them any permissions whatsoever to this Compliance Manager collection Service. Let’s go ahead and look at some of the options under administration. In cases where we can’t be quite as granular as we’d like with our upfront audit settings, we can apply what’s called an audit event filter. When we process the data as we collect it, the first thing we’re going to do is check to see if it matches an audit event filter.

27:29

If so, we’re simply not going to process that data. We’re going to skip that event and move on. An example would be, for example, if I’ve got a service account that I really don’t want to audit, but the DBAs know the password to it, so I can’t just add it to the trusted user list because there may be still some things I need to audit for it. So what can I do? Well, in this case I’ll create a new event filter. Maybe it’ll be for all events, or I could even specify only filter out DML type events. Think of this as nothing more than a big where clause.

28:08

SQL Server Event Auditing

Maybe it’s for a specific SQL Server. If so, I can select it, I don’t have to. You’ll notice it already says on any SQL Server. What is important in this case is first the login name. In this case I’ll give it my service account. The other thing to it in what I do want to filter out is when it runs my service job. I’ll add the application name and we’ll just call it Service Job. Now if you read through this, it’s telling you exactly what it’s going to do as we process the event. It’s going to filter out any event for any SQL Server, however, where the login name is specifically the Acme Service one, and the application name is Service Job. If a DBA knew the password and they logged in with Service One, but they’re using SQL Server Management Studio. We’re still going to capture that event information.

29:11

Data Filtering

However, if the service is doing its normal maintenance job or the service job in this case, then we’re going to simply filter it out. That’s one example and where we may generate a filter and I’ll just go ahead and call it Filter Service One. You can add description to it. That’s exactly what it’s going to do. I’ve also had where let’s say you’ve got an application that does its own internal auditing and you don’t want to duplicate those records. However, that application isn’t going to capture something done by DBA using SQL Server Management Studio. Or do you have one of those Power Excel users that creates a fantastic spreadsheet that, oh, by the way, also has a database connector to that database. Again, that application isn’t going to know about that activity. What we can do, and that’s what I’ve done here is I’ve created a filter for any DML events, and in this case I specified the SQL Server and the database name, but then I specified the application. Now any events captured from this application as we process it, we’re going to ignore it. If it’s happening through SQL Server Management Studio, or if it’s happening through Excel, for example, I’m going to process an event. Now what we have is a report that’s kind of an exception to.

30:32

Alerting

The audit report that maybe you have internal to that application. That’s where we may set up an event filter. The next thing that we’ll set up is an alert rule. Now alert rule is whenever an event matches specific criteria, we’re going to send out either an email, maybe log it to the Windows application event log, or even possibly send an SMP trap message. One of the most common ones that I always got asked when I was in support is I just want to get an email notification. Whenever there’s a security change, that’s probably the easiest one to set up. I click on plus event. We already have security changes selected. We don’t care about what server it is, we don’t care about the application name or anything else. All we want to do is send down the email notification. Anything in blue, we can adjust. Anything in red, it’s expecting us to set a value.

31:31

I’ll go ahead and click on specified addresses, give it my DBA’s team address, DBAs, and again I can adjust the message. I’ve got a number of fields here from the event itself that I can include anywhere in the message. You can DoubleClick on something. You can also go ahead and type in here and then that’s going to be the format of the email and just go ahead and give it a name. I’ve had customers ask me about wanting to be notified whenever a database or table has been dropped. So here’s an example of again, I’m looking in this case not just any DDL event, but a specific event. So I selected specific event. I selected DDL. More importantly, I selected the event type drop database. You can see there’s a number of different a whole lot of different DDL types. In this case, select Drop database.

32:43

Again, didn’t care about the SQL Server or anything else. But then I’m doing an email notification. Now whenever there’s a Drop database, I’m going to go ahead and send out an email alert.

One other example is let’s say we want to be more specific about a security change. What if it’s just only if somebody’s been granted the Sysadmin role? I’ll click plus event. Select specific event. This case is not a DDL, it’s Security. We’re looking for Add login to server role. Click Next. Now, I don’t care about what SQL Server it is, but I do care about the object because the object is going to reflect what server role was added. I’ll select object name and I’ll add Sysadmin. Email Notification, give it a name and finish. Now whenever anybody’s been granted since admin privileges, I’m going to get an email notification about it. I will also see that information under explore activity.

34:10

SQL Server Event Alerts

So if I go to Explore activity, select my server, you’ll notice there’s Event Alerts. When I click on that, you can see I’ve got a number that I’ve already captured. Here’s an example where there was an Add Login to Server role. When I double click on it has all that information about the event, the time and date that it occurred, what it was security. Specifically add login to server role. Sysadmin was granted to the user test two person. I’m the one who did it using Microsoft SQL Management studio.

34:49

Archiving SQL Server Audit Data

The last thing we will cover today for Compliance Manager is what do we do with all this event data? With most regulatory guidelines, you will be required to retain the audit data for several years. However, in most cases, once we have completed our audit review for a given fiscal year, it is no longer necessary to keep the audit data on our collection server. Since we are not allowed to delete the data, SQL Compliance Manager has a process in which we can archive the data. The settings for archiving can be found under Auditing, Archive and Retention and Archive Preferences. Archiving moves the collected audit data from the event database to a corresponding archive database in my settings here, the archive process will be running every day at 01:30 A.m.. When it runs, it will look for any event data older than 60 days and move that data into its matching archive database.

35:54

The archive events can be stored in monthly, quarterly, or yearly databases. Most customers choose monthly or quarterly. Although the data has been moved to an archive database, we can still run reports against this data. Once all the data has been moved from its archive databases, we can then detach the archived databases from Compliance Manager, back up the archive database in SQL, and delete or drop the archive databases. To detach a database, simply go to the server you want to detach it from, go to Archived Events, and then select the time period in which you want to detach. You can say that you’re going to remove this from the repository. More importantly, if you need to reattach database, simply restore that archive database. If you had already dropped it from your SQL instance, then come to File, attach archive database, and you’ll have a list of those databases that can be reattached.

37:10

SQL Compliance Manager

Once the data has been reattached, we can still run those same reports as we did before. I’ll just go to my audit reports. I’ll just pick one of the reports here, and you may have noticed this before, but as I do my drop down, not only does it have my live event databases, but it also has my archive databases. I can go ahead and run the reports against that archive database. Just be sure that you pick the time frame that would also include that data in the archive database, and then we have all the historical data as I was showing you before. That concludes our portion of Compliance Manager in the next video, we will cover SQL Secure.