Azure SQL Database - Auditing & Threat Detection

Has your database been attacked by someone?

Most of our applications are data-driven applications and attackers can use various techniques for getting into the database. One of the famous and most common techniques is SQL Injection which is a Code-Injection that modifies the statement written in the application and get it executed as attacker wants.

Here is a link for reading more on SQL-Injection: http://www.w3schools.com/sql/sql_injection.asp

Whether the database is a On-Premises database or Cloud database, if the code has been written poorly, then it is exposed for attackers. And the biggest issue is, in most cases, we do not know whether the database is attacked or not. What if someone can detects possible attacks and notifies you immediately? Yes Microsoft Azure SQL Server can do it.

If you have worked with Azure SQL Servers, you have already noticed a Setting called Auditing & Threat Detection in the SQL Server Blade. It allows you to set Auditing and in addition to that, we can enable a feature called Threat Detection which is still in Preview mode (means it will not always work as you expected until the final version is released.).

Read the post written on Auditing for more info on it: How to check success and failure logins attempts in Azure SQL Database

Threat Detection detects anomalous database activities that are considered as security threats and notifies us with details. This means, it can detects possible SQL-Injections as well. However, as per my experienced, it does not detect immediately at the initial tries but later, notification is sent immediately. As per some experts, it needs some times to learn the patterns for notifying but it is too early to make a such statement as this is still in Preview mode.

Let's see how we can use Threat Detection with an example. I created a Azure SQL database with following schema and inserted a set of records. Assume that this Sales database contains Sales made by Sales Representatives.

-- Create User table
CREATE TABLE dbo.Users
(
 UserId int identity(1,1) PRIMARY KEY
 , UserName varchar(10) NOT NULL
 , Password varbinary(1000) NOT NULL
);
GO

INSERT INTO dbo.Users
 (UserName, Password)
VALUES
 ('Dinesh', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Dinesh123'))
 , ('Jane', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Jane123'))
 , ('Jack', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Jack123'));

-- Create Sales table
CREATE TABLE dbo.Sales
(
 SalesId int identity(1,1) PRIMARY KEY
 , UserId int NOT NULL
 , Constraint FK_Sales_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (UserId)
 , SalesDate date NOT NULL
 , SalesAmount decimal(16,2)
);
GO

INSERT INTO dbo.Sales
 (UserId, SalesDate, SalesAmount)
VALUES
 (1, '2016-01-01', 10000.00)
 , (1, '2016-01-02', 12000.00)
 , (1, '2016-01-03', 14000.00)
 , (2, '2016-01-01', 9000.00)
 , (2, '2016-01-02', 110000.00)
 , (3, '2016-01-01', 17000.00)
 , (3, '2016-01-02', 126000.00)
 , (3, '2016-01-03', 19000.00)

Next step is creating a simple web application. I created an ASP.Net Web Application that contents with the Azure SQL Database created. I added a Web Form that contains two input boxes for accepting User Id and Password, a button for Loading User's Sales and a GridView for showing retrieved data.

This is Button-Click method written.

string sql = "SELECT u.UserName, s.SalesDate, s.SalesAmount FROM dbo.Sales s INNER JOIN dbo.Users u ON s.UserId = u.UserId WHERE ";
sql += " u.UserName = '" + TextBoxUserId.Text + "' AND DECRYPTBYPASSPHRASE('DK?DF%23:D', u.Password) =  '" + TextBoxPassword.Text + "'";

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);

connection.Open();
SqlDataReader reader = command.ExecuteReader();

DataTable table = new DataTable();
table.Columns.Add("UserName", System.Type.GetType("System.String"));
table.Columns.Add("SalesDate", System.Type.GetType("System.DateTime"));
table.Columns.Add("SalesAmount", System.Type.GetType("System.Decimal"));

DataRow row;
while (reader.Read())
{
    row = table.NewRow();
    row["UserName"] = reader["UserName"].ToString();
    row["SalesDate"] = Convert.ToDateTime( reader["SalesDate"]);
    row["SalesAmount"] = Convert.ToDecimal( reader["SalesAmount"]);
    table.Rows.Add(row);
}
GridView1.DataSource = table;
GridView1.DataBind();

connection.Close();

As you see, SqlCommand is not a parameterized command, hence attacker can use a simple SQL-Injection to modify the statement and get different set of data.

All done. If I check for User:Dinesh, I will be seeing Sales related to him.

However, if you use a SQL-Injection like below, I can get all Sales Records regardless of the user. 

We are not going to talk about the SQL-Injection and the poor code written in the Web Application. Let's see how we can get a notification from Azure when something like this is tried out by someone.

As you know, all we have to do is, enable Threat Detection. This how it has to be set up in Azure SQL Server Blade via Auditing & Threat Detection Setting.

Everything required is done. As you see, Threat Detection is ON and all Threat Detection types have been selected. I have added my email address for receiving alerts. Note that, you need to enable Auditing for enabling Threat Detection and it should be configured to all Audited Events.

Now if you try again with a SQL-Injection, you will immediately get a mail with details;

I can click on the View Related SQL Statements and see more details. If I click, it opens the Audit Records Blade that shows all records activities.

As you see, it is very easy to set up and you can be alerted on all suspicious activities. Try and see.