Welcome to the Azure Security Podcast, where we discuss topics relating to security, privacy,
reliability and compliance on the Microsoft Cloud Platform.
Hey everybody, welcome to episode 81.
This week is one of those special episodes.
This week I am with a colleague of mine, Sravani, who's here to talk to us about auditing in
SQL, the SQL Server and various other SQL related products.
So there will be no news, we'll wait until our next episode and we'll go through the
latest and greatest Azure Security news.
So Sravani, thank you so much for joining us on the podcast this week.
Hi Michael, thanks for having me here.
I have been in the podcast in the past when we were talking about the Windows authentication
feature for Azure AD principles for SQL managed instance.
It's nice to be here and I have been working with Microsoft for 11 years now and it's total
15 years of experience in SQL Server and SQL Database.
I worked on almost all flavors of SQL, starting SQL Server 2005 till SQL 2022 and then the
SQL on Cloud, the Azure Database and the SQL managed instance.
So I primarily work as a program manager for SQL security features and own SQL auditing
for all flavors of SQL.
Along with that, I also work on some of the authentication related features for SQL managed
instance.
Apart from security, high availability and performance are some of my areas of interest
and have done lots of work as an escalation engineer in those pillars.
So yeah, that's about me.
Fantastic.
So let's start off with a really basic question.
When we're talking about logging and auditing here, which products are we talking about
and what kind of events are we talking about?
Like, are we talking about SQL control plane, SQL data plane, Azure control plane, Azure
data plane?
What sort of logging are we sort of worried about here?
Okay.
So today we are talking about SQL Azure Database.
So Azure SQL Database.
So the auditing, as I said, auditing works for all flavors of SQL, the on-prem SQL server
starting SQL server 2008 till SQL server 2022 and the cloud products, the Azure SQL Database
and SQL managed instance.
So today we will majorly focus for Azure SQL Database, which is slightly different compared
to SQL managed instance and on-prem SQL server, how it works.
And for Azure SQL, we have data plane auditing and for control plane, which is from the portal,
any operations that you perform from the portal, which comes under the control plane and those
operations are logged in the activity logs, which are available in the Azure monitor.
So today from SQL, we do not audit any control plane operations at the portal level, but
we offer the data plane auditing for the Azure SQL Database.
So for SQL SQL server and other flavors of SQL, the auditing itself is built differently
to meet security compliance, regulations and requirements.
So how it works is basically we support wide range of action groups.
So there are a lot of action groups that we support.
For example, there is a batch completed group, which is responsible to capture all actions
related to our database.
It could be any batch that is executed against the database.
So this is majorly focuses for any DMLs, DDLs, which are executed inside the database.
So this is one of the action group that we support.
Along with that, we support, as I said, we support wide range of action groups.
For example, schema related changes, there are certain action groups, but if there are
any changes related to users, groups, permissions, there are action groups to capture that information.
Similarly, we have certain action groups where if you want to know if there are any failed
logins, successful logins, so we have a dedicated action groups for that.
So first of all, why do we support wide range of action groups?
To provide flexibility to customers to configure auditing based on their security requirements.
Our customers has like different set of wide range of servers, the cloud services, right?
So some might be related to medical, some might be related to finance.
So there are different organizations and each organizations will have their own security
and compliance requirements to meet.
So in order to provide the flexibility and collect only the data that they need to collect,
we provide the support to have a lot of action groups.
And along with the action groups, we also support actions like if you want to capture
like a select, update, insert, delete against any specific table or a database or by a specific
user.
So this is like a kind of filters that they can configure while configuring the auditing.
So this is what we do for all flavors of SQL.
However, when it comes to Azure SQL database being a PaaS service, the first thing is we
don't enable auditing by default.
Auditing customer has to enable the auditing from the server blade in the Azure portal.
So when it comes to Azure SQL, we support two types of auditing.
One is a server level auditing.
The other one is a database level auditing.
The name itself tells you like the server level is basically when you configure it,
all the databases has the auditing enabled.
And if you do not want to configure a server level audit, like if you have like specific
databases for which you need to enable auditing, then you can just go ahead and enable the
database level audit.
These are the two configurations that we support for Azure SQL.
And what happens is when you configure auditing, you need to write the logs to a target, right?
So I mean, traditionally for on-prem servers, what you do is you configure auditing and
then write the audit logs to your one of your log folders on your Windows machine.
So what happens with Azure SQL is we support three types of targets.
So one, you can write the audit logs to the storage account.
And two, you can write the audit logs to log analytics workspace.
And three, you can also write the audit logs to event hubs.
So why we support these three different targets is again to provide flexibility to customers
so that they can choose what targets they want to use for their auditing.
What I see is most of the customers use multiple targets for certain databases.
They just store it in the storage account and use their own mechanisms to review these
logs.
And most of the customers use event hubs and log analytics because that gives you kind
of Azure security insights to understand what is happening on the server.
So these are the different targets that we support.
So like I said, for Azure SQL database, once the database, once the server is created in
Azure, customers go and enable auditing from the portal.
When they enable, they choose either server audit or database audit.
And then they choose this one of these targets to store these audit logs.
So this is how it works.
And when it comes to Azure SQL, we have a default auditing.
So when you enable it from the portal, we always enable the default audit, which is
having three action groups, which are default, that is database successful logins, action
group, and failed login action group, and the batch completed group.
So these are the three action groups that we configure by default.
So in case if customer has a requirement where they need to capture a different set of action
groups, that is when they have to do a custom audits for their Azure SQL.
So for performing custom audits, we do not have a UI today.
You have to use PowerShell ARM templates from the portal.
You can use one of these client tools to configure custom auditing.
And when they configure custom auditing, they can configure specific action groups, specific
actions.
If they want to do filters like a specific user or a schema or a database, there is a
filter called predicate expression, which they can use while using PowerShell, and they
can have a custom audit configured.
So this is like an overall picture how it works for Azure SQL.
And this is all about the data plane.
So anything that comes from the client to the Azure SQL database in the data plane is
audited using this auditing.
And for the control plane, like I said, the activity log, which is provided from the ARM
from the Azure would be your control plane audit log for now.
So you said a couple of things at the very beginning.
I just want to make sure everyone understands.
So DDL is data definition language.
So that's like create table, create schema, that sort of stuff, right?
And then DML is data manipulation language.
So select star, insert, update, that kind of stuff.
And then the other thing you mentioned was that you're sort of bucketing SQL queries
into action groups.
So what is an action group?
Action group is basically a set of actions that are executed inside your database.
So for auditing, like I said, we bucketized these actions into these groups so that if
customer wants to configure auditing, they can choose what should be the action group
that I want to configure.
Like I said, for example, I mentioned about batch completed group, which is a server level
as well as a database level action group.
And this is basically when you configure this specific action group, what happens is an
event is raised whenever any batch is executed inside your database.
So any operation that completes execution raises this event.
And then this event is captured and returned to the audit logs.
So for auditing in the backend, we kind of use extended events architecture where all
these events are captured based on the actions and action groups that you configure for the
database.
We have various action groups like batch completed group, batch started group.
And then we have something related to databases.
Like if there is anything changed, you can configure database change group.
Like I said, database logout group.
If a specific user is logged out from a database, you can configure this database logout group
to audit that specific event.
Okay, thanks.
So that makes a lot more sense now.
You mentioned at the beginning that for Azure SQL database, auditing is not enabled by default.
What's interesting about that is there's been in the news just recently, nation states involved
with compromised accounts.
One thing that sort of came out of that is a lot of customers didn't have certain kinds
of auditing enabled.
So now we're seeing a lot more impetus on people using auditing.
So is it fair to say that because it's disabled by default, that we really need to have more
people enabling auditing in Azure SQL database?
Is that a fair comment?
Absolutely, Michael.
That's a fair comment.
Why we don't enable by default is even though the auditing is a built-in feature, the targets
are the cost for customer.
And depending on their workloads, we don't know how much data they are going to generate.
Like if you have a heavy OLTP system and I do not want to take a decision on behalf of
customer because the targets are going to be pretty costly.
We have customers who have the audit log is like four times of their source database size
and they are just not aware of that because it's the default audit and it just audits
everything in the database.
So that is why we don't make it default.
We want customers to choose what targets they want to use.
They want to do based on their cost, based on their compliance requirements and things
like that.
So absolutely, like once the database is deployed in Azure, customers should review the auditing
configuration and see if they want to enable it to meet any security requirements.
Yeah, I just want to make sure I get this 100% correct.
So you can actually have a relatively fine grained audit policy rather than just auditing
absolutely everything.
Could you put a policy in place as an example?
Let's say you decided that you only want to, I don't know, I'm making this up as I go along.
Let's say you've got 20 offices and one particular office is potentially in a hostile environment.
Again, I'm making this up.
You could audit just stuff coming from that DNS range or that IP address or at least something
to identify that environment or those users.
Can you go to that level of granularity?
Yeah, so there is a way to filter those activities and the specific users, specific groups.
So as I said, today, one of the challenges that we have for Azure SQL database is we
do not have a UI where you can go ahead and configure a custom audit.
So the default audit basically collects the batch completed group, which collects pretty
much everything that happens in the database.
So if you want to have this specific, more precise auditing, we have to use the custom
auditing, which is either you configure it using ARM templates or using partial commands.
So I did publish a blog, a blog, a blog, which has more commands with each scenarios and
examples like if you want to filter a schema, filter a user, filter a database or filter
a certain statements.
So I have given like certain examples like how to configure this predicate expression
and filtering.
But you know what the good news is, we have this in pipeline to have a custom UI so that
customers do not have to do these commands and then configure auditing.
So that work is in pipeline.
I do not have the timelines, but soon we will have a custom configuration and available
for auditing.
But till then, customers have to do it manually using partial commands or ARM templates.
Until I read your blog post, I didn't even realize that there was a way of producing
sort of smaller log files by essentially filtering them.
I didn't even know that existed.
And also it's funny, a lot of people think that the extensive functionality in any Azure
service is what's available in the portal and that is simply not true.
Often the most common things are in the portal, but some things are not in the portal, but
they can still be accessed through like you say through PowerShell or through the CLI
or through an ARM template or a bicep template or something like that.
That's good to know.
Yeah, again, I didn't know that until I actually read your post on that.
Anything else people should know about logging?
I mean, what about things like our other database products like SQL MI for example, SQL Managed
Instance?
So one of the difference that I would like to call out between, so SQL Server, SQL MI,
today you can configure auditing using T-SQL statements, right?
So which is like customers are pretty much comfortable with.
So for Azure SQL database today, you cannot configure auditing using T-SQL.
So that is the one of the challenge that we are also trying to fill up and see how can
we enable support for T-SQL so that this kind of filtering becomes more easy for SQL database,
right?
Azure SQL database.
So this is one challenge I would say that is something that we are trying to fill up.
So today for SQL Server and Managed Instance, you can configure all auditing using the T-SQL
commands while for SQL database, Azure SQL database, we cannot do that using T-SQL.
So customers have to use these PowerShell and ARM templates.
So this is one of the difference.
And the other one is, so when we spoke about action groups, right?
There are server level action groups and database level action groups that we support for SQL
Server and even for SQL Managed Instance.
But for Azure SQL, today we support only database level action groups.
So initially this is a SQL database and we enable support for all database related action
groups.
But there are certain server level action groups which we do not support for Azure SQL
today.
So again, this work is also in pipeline and we are working on fixing these feature parity
gaps between Azure SQL and SQL Server to ensure both all flavors of SQL have the same kind
of support for the action groups.
So but yeah, not many customers asked for this, but there are customers who need this
support for the server level action groups.
For example, if there is a login failure at the server level, they would like to know
like which database, which login failed.
So this is something again, we are currently working and soon it should be available as
well for customers.
And just one more thing that I would like to call out is by default for Azure database
today, we capture only the first 4K characters of the SQL statement.
So we do not roll over the records to the next record.
So in SQL Server, what we do is if the SQL statement has more than 4K characters, we
roll over to the next audit log record.
So for Azure SQL, why we do this is to consider the performance and to ensure there is not
too much of logging or overhead.
By default, we do not write this.
But if any customers has a workload that will have queries that are more than 4K characters
in limit, we still enable this feature from the backend.
This can be enabled from the backend.
However, we are again working on a feature where we want to make this default and at
the same time have the better performance in place.
So these are some of the differences when compared to SQL Server and Azure SQL.
And all these differences are already documented in our public documentation.
But I just wanted to highlight them.
Just sort of switching tacks a little bit now.
So querying these logs, first and foremost, what sort of permissions are there around
it to restrict access to the log files?
And also, how do you query these logs?
What functionality exists to actually go in and find out what happened?
That's a great question, Michael.
So first of all, we write these logs in a different format.
That is.xel format, which is basically an extended event log format that is native to
the SQL Server.
So today, how customers view these logs?
For Azure SQL database, there are three ways.
One, in Azure portal, when you configure auditing for your SQL database, when you go to auditing
blade, there is a view audit logs option.
And that view audit logs will give you within a timestamp, what is the audit log look like.
And that is like preview of the audit logs.
We don't expose all columns of the audit log, but some of the columns which we feel
they are important.
So it tells you what event happened, when it happened, by whom it happened, and what
is the change that is done.
So these are the columns that we publish in this preview of the audit logs.
And this is available in the Azure portal once the auditing is configured.
Though we say the logging is asynchronous, I will say that pretty much you will see the
logs are available as soon as you made some change in the database.
So that is the one way to view it.
The second one is there is something called Azure security insights.
So we provide a dashboard view of Azure security insights and tells you what exactly happened,
like if there were any fail logins, successful attempts, number of users accessing the database.
So this comes when you have auditing configured to log analytics or Event Hub as a target.
So when customers use these are the targets, they can also use this SQL security insights
that comes from the Azure portal and which tells you like how many action groups are
executed, how many databases are there, and how the databases are distributed by IP addresses
and what kind of SQL principles access the database.
So this is like a security insights that we provide.
And this is a nice great overview to tell you overall picture in terms of what is happening
on the server for a given time period.
And the third way, which is basically the native way that we have been using for several
years is using a T SQL function, which is fn get audit data.
And this function supports reading multiple audit files and gives you a view like what
exactly what are whatever the data is captured in a given time period.
So you can either read one log file or you can read multiple log files.
It basically takes inputs like what is the log file and how many files you want to read.
And then you just have to run this query in your management studio connecting to any database.
All you need is access to your audit logs wherever they are stored.
They may be locally on your system or maybe they are sitting in a storage account.
All you need is access to those log files.
And once you have the access and if you have a required permissions to run this function
inside SQL, you can just read the audit logs.
And this is just a T SQL and customers can just do filtrations, whatever filters they
want to apply for a specific database user time period, anything.
So that is how they can view.
Apart from all this in SSMS also there is an option when you have a server audit configured
and when you right click there is a view audit log option that we provide.
So there either you can view the audit logs of the existing server or even you can open
an audit log which is sitting in a storage account or some other target.
You can just go to the path, open the audit log and it gives you a view in the SSMS as
well.
So these are the different ways customers can access these logs and view the audit logs.
Yeah, I knew about the function that was available because I ran it a couple of times and the
funny thing is it produces so much information.
I was just absolutely blown away just how much data is there in those logs including
stuff I have no idea even what the columns mean to be honest with you but here it is
what it is.
So before we sort of wrap this episode up is there anything else, any other things that
people should know about before we move to the end of the podcast?
So one important thing is for Azure SQL Database auditing is not a default feature.
You have to enable it and then choose what targets that they want to configure and they
should also aware of the default audit action groups which basically captures everything.
So if that is not what customers want they can go ahead and have a custom audit configuration
so that they can have a more crisp and more detailed logging based on their security and
governance requirements and we have everything updated in our public documentation and if
there is any feedback, if there is any piece of information that is missing we will keep
adding it.
They can just go to GitHub and report any information that is missing and we will be
happy to assist there.
But yeah, please review the auditing configuration for all your Azure SQL Database and enable
wherever it is required.
Alright so one thing you know as you have already been on the podcast, one question
we always ask our guests is if you had just one final thought to leave our listeners with
what would it be?
I would say please turn on your auditing for Azure SQL Database so that you don't miss
any important information.
I think that is a perfect ending and I really want to sort of add my two cents there as
well that there is a lot of nation state activity going on right now and there always is and
the lack of audit logs is proving problematic.
So yeah, if you are running Azure SQL Database or any product for that matter make sure that
you are auditing especially the data plane and obviously the control plane as well.
So with that let's bring our episode to an end.
So Vani, thank you so much for joining us this week and to all our listeners out there,
stay safe and we will see you next time.
Thanks for listening to the Azure Security Podcast.
You can find show notes and other resources at our website azsecuritypodcast.net.
If you have any questions, please find us on Twitter at Azure Setpod.
Background music is from ccmixtr.com and licensed under the Creative Commons license.