SQL Server Audit, Prevention and Detection Of Attacks - It's Much Easier Than You Think!

Preface: A comprehensive paper on SQL Server Audit, Prevention and Detection of Attacks - it’smucheasierthan You Think! Is available [here] and the corresponding [Slide share presentation] (https://www.slideshare.net/JoeSantarcangelo1/ssasqlserveraudit) (yes it's a shameless plug for our great team). This post will dive into some of the specifics behind this attack scenario in order to help you better understand what actually happened with this recent breach at LinkedIn? In short, there were a lot of services enabled on this server and a lack of audit policies in place. This allowed the attackers to move about freely without any monitoring or alerts generated by SQL Server Audit. It was only when they decided to wipe out the data that the company even knew what was going wrong.

Audit Policy - How can you monitor if someone is deleting data?

When it came to figuring out how the attackers were moving about so freely within LinkedIn's servers, their first step was to look at SQL Server Audit. As one may already know, Audit helps us track who did what, when they did it and where they did it. However, there are some things that might need explaining here regarding how insecure deletions actually occur with SQL Server.

Let's take a look at a sample table that we will be working with:

CREATE TABLE Users ( Id INT IDENTITY ( 1 , 1 ) NOT NULL , FirstName NVARCHAR ( 50 ), LastName NVARCHAR ( 50 ), Username NVARCHAR ( 50 ) NOT NULL CONSTRAINT aspnet_Users UNIQUE CLUSTERED ); INSERT INTO dbo . Users VALUES (N‘admin’, N ‘Admin’, N ‘admin@admin.com’); GO --INSERT INTO dbo.Users VALUES ('joe','Joe','joe@email.com');

DROP TABLE Users; ALTER TABLE dbo. Users ADD CONRAINT PK_Users PRIMARY KEY CLUSTERED (IdASC);

This table is simple enough; we've got an identity column for our primary key and three columns for first name, last name and a unique username for each user. Now let's assume that we have a perfectly good reason to delete data from this table. We'll use the following T-SQL command:

DELETE FROM dbo. Users WHERE Username = N ‘admin’;

Assuming you aren't familiar with T-SQL, what just happened? For those who are not aware, when you issue a DELETE statement against the Users table without specifying any conditions - all rows within that table will be removed. In other words, no matter how many rows may exist within the Users table, any rows with a Username of 'admin' will be removed in one fell swoop. You might think that sounds pretty bad, but what's worse is that all of this just happened without SQL Server actually telling us it happened. If you are familiar with auditing within SQL Server, then you likely have seen some sort of audit kicked off when data was deleted. This is because when running against tables which have an Audit policy defined against them; deletions are logged regardless of whether or not conditions are used to narrow down the scope of the delete operation. As mentioned earlier however, there were no Audit policies configured on either db_owner or guest , so nothing was logged regardless if the users had something like DENY DELETE ON *.* or merely no permissions at all.

SQL Server Audit - How It Works

Before we get into configuring SQL Server Audit, let's first explore how it works and why it does what it does. When a database server creates an Audit object against a database, this object will define a series of events that can be audited as well as a directory where the logs created by those events should be written to. Therefore when one looks at their audit directory on each of their servers, they'll see something similar to the following:

In order for us to have access to any of these files within our audit directory, they must first be opened using Notepad as Windows already has some pretty tough security mechanisms built around its files. This can be annoying as we can't even view these files within SQL Server Management Studio unless we first right-click and select Open With:

Audit Log Directory - Note Notepad As you can see, there's no way to view the contents of any of the Audit logs without first opening them in Notepad. This ought to make us wonder why Windows has such a tough security policy around this directory, but unfortunately it doesn't really like answering questions like that anymore.

Conclusion:

First and foremost, we need to realize that SQL Server Audit is a security control and as such, it does what it needs to do in order to protect itself. There's no reason for us to bypass the fact that we can't view these Audit logs within SSMS as they aren't meant for us anyway. What the audit directory is used for is simple enough: Security! The main question that continues to come up surrounding SQL Server Audit however is "With all of these logs being written into my system, isn't this going to be difficult/impossible/time-intensive/etc.?

If you want to see SQL Server topics visit Saeed Developer for more details.

Also, read these related articles

Is SQL A Programming Language? | What is SQL | How to Learn SQL

A Guide to SQL Data Analysis for Beginners

Mysql import SQL file | Mysql import database

What is Database Administrator | How much does a database administrator make | Salary for DB administrator