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 88.
This is one of those special episodes where we sort of interview people about a very specific
topic, so there's going to be no news.
It's also just myself here this week, Michael.
Again, part of the reason is because the guests we have this week, we have Shirat Unni and
Raoul Garcia, and they're both colleagues of mine in the Azure SQL Database team.
And this week, we're here to talk about best practices for securing SQL databases, SQL
server databases, both Azure SQL database, SQL MI, SQL managed instance, SQL in VMs as
well in virtual machines, so IaaS instances of SQL server.
Before we get stuck into the actual content, gentlemen, why don't you introduce yourself?
Shirat, why don't you go first?
Yeah, thanks Michael for having me here.
About myself, I am the principal engineering security manager for Azure Databases Red team.
I have about a decade or more of experience in all offensive security, so I can talk today
a lot about the different types of attack techniques and mostly cater to Azure databases
and SQL in specific and talk about what are different things that I've seen.
And I am Raoul Garcia.
I'm currently working on static analysis using CodeQL and cryptography at Microsoft.
But for more than a decade, I was doing security testing and security assurance for SQL server.
And since then, I have been helping Red teams and Blue teams attacking and defending SQLs.
All right, so let's move on to our topic, which is, as I mentioned, securing SQL databases
and actually more accurately looking at it from almost like an attacking perspective,
right?
You know, what things we see attackers doing and how we can help mitigate those and some
of the best practices that we can employ to mitigate those attacks.
Because, you know, attacks are going to happen, right?
The big difference is, are you compromised?
Attacks are going to happen.
So we need to make sure that the compromised part doesn't happen.
So let's talk about some of those topics.
So let's start with a subject that is near and dear to my heart.
And that is least privilege.
And in fact, one of the things about least privilege is that it's one of the cornerstones
of zero trust, right?
So in the case of Microsoft, we have three main pillars.
One of them is operating at least privilege.
And what that really means is just operating during the tasks that are required.
That includes users and processes at the minimum amount of privilege to get the job done and
absolutely no more.
So Shirat, why don't we start with you on some of the best practices around operating
as SQL databases at least privilege?
So to your point, Michael, least privilege is something that we hold very close to our
hearts and every discussion we have around security fixes or security bugs, we are most
of the times emphasizing how a least privilege could have avoided a lot of the disaster.
So with SQL databases in particular, what a lot of times happens is you see the front-end
web application or an app service that needs to talk to the database having a very high
privilege, right?
A lot of times what we overlook is the fact that while the application only needs to do
a very few tasks, like maybe reading data or updating specific tables, we are granting
high privileges like to an extent where we give sysadmin access to the database user
that is connecting by the application, right?
Now not only is the fact that the application compromise can now kind of make the problem
worse because the database user is high privileged, so you can do a lot of fancy things that we
talk about in the next part of our podcast.
The biggest challenge is the fact that we are now giving an attacker an opportunity
to do more with the database credentials, right?
To add to that, there are times where we see that the database service itself is running
with high privileges, right?
Which is bad, again, in the case where you're able to execute commands as the database service.
I mean, what comes to my mind as an attacker is we are always trying to see how can we
move from point A to point B and does that give us additional privileges?
If yes, we always take that direction because that's where we get the most back for our
bug, right?
And in case of both SQL Server database privileges and SQL Service privileges, what we have found
often is that there is more than needed privileges assigned and that's always bad.
I completely agree and I would like to add one more detail that is particular to SQL
Server.
When you have a scenario where the SQL user needs to execute elevated functions that is
beyond read or write data, for example, you need to execute certain administrative commands
from the application.
Instead of having to connect with a highly privileged user for that purpose on your application,
one tool that is at your disposal is store procedures and signatures that will allow
you for a very controlled escalation of privileges from a low privileged user to do exactly the
operation that you need and nothing else.
And that's unfortunately a tool that is often overseen when looking at how to mitigate and
lower the privileges on a system.
So an example there would be, let's say a user has read and write access to one table,
let's just make something up, but they need to do like one specific task as well, a little
bit of tasking just for a very small amount of time.
So you can actually write a store procedure, digitally sign it, and then grant the user
access to that store procedure.
And then so that user or role or something can actually execute that store procedure,
but that's all they can do, right?
They can't do anything else elevated and whatever that particular store procedure can do, it
can do and it can do it in a very, very constrained way.
So is that kind of the pattern that you're thinking of, Raul?
Yes, exactly.
And that will help you to control exactly what are the privileges of that user instead
of granting full DBO access to that user.
You just need to grant permission to run that procedure that you mentioned and the signature
itself will give the DBO like privileges for that operation.
Yeah.
And the key thing about the signature, right, is the one it verifies that that particular
store procedure is trusted because of signature stuff, but also that it's not been tampered
with, right?
So someone can't come in and modify the store procedure and then get it to do stuff above
and beyond.
So, and again, signature stuff, right?
That's exactly the idea.
Yeah.
I know very few people who actually know that, to be absolutely honest with you.
So yeah, if you're out there and you think you need to grant special privileges broadly
to users, seriously consider this signatures on store procedures that have very specific
requirements and then put an access policy around that store procedure, restricting the
store procedure access to Jim and Mary or whatever you need to do.
Fantastic.
The funny thing about least privilege is that if you run a sysadmin, then everything just
works, right?
Well, of course it works.
You're sysadmin.
What the heck do you expect?
But so does the attacker's code.
That's the problem.
If you get compromised, then the attacker's code runs at sysadmin too.
So if you take another color from zero trust, which is assume breach.
So if you assume breach and the environment is compromised, now what?
Well, the attacker's running as a sysadmin.
So that's absolutely a pathological example.
So yeah, least privileges is such an important design principle.
I mean, it's been around since the seventies.
I mean, the original Salser and Schroeder paper from the seventies.
I'll put a link to that if no one's ever read it.
If you're not ready, you should look at it.
And in there is least privilege.
So it's been around for almost 50 years.
The next one is, I don't see this a lot.
I mean, I have seen it with a couple of customers where they actually run the process.
Now I'm not talking about connecting to SQL server.
I mean, people actually run the SQL server process in a VM or even on prem as an administrative
account.
Now by default it runs as what's referred to in windows as a virtual service account.
And it's MS, it's like anti-service MS SQL server if I remember correctly.
And it's not an admin.
It's very least privilege accounts, a custom account.
But yeah, if you're running the process itself elevated, then that's also opening you up
for issues as well.
So Shirat, you want to have any comments there about issues you've seen or the dangers of
running the SQL process as an elevated account?
One of the things that you would do as a red teamer is once you have any sort of initial
access, you're looking to pivot into other systems or exfiltrate as much data or credentials
so that you can use it for your further pivoting.
So with SQL service running as system, which is godlike privileges on your windows operating
system, a lot of things can be done.
We will talk about this in a little bit usage of things like XP command shell or SQL agent
jobs.
But SQL server does have some powerful tools which can be used to run OS commands.
And one of the things that you can do as a system on windows is essentially read memory
or dump memory of the system so you get credentials of the network.
And once you have a sufficient privileged network credentials, you can then laterally
move within the network compromising more machines.
So it makes the attacker's life a lot more easier having system process compromise because
that gives an attacker a very solid initial access and also ways to establish persistence
on your network.
It all sounds very dangerous, but it all happens because we think that it's easier to just
run something as system rather than going back and doing the investigation of like,
okay, why exactly should I run this as system?
Is there a better way to do this?
Again, going back to principle of least privilege.
Yeah, I would like to make an emphasis on that principle of least privilege.
Twenty years ago, and I'm dating myself here, but 20 years ago, running SQL server as local
system was unfortunately very common.
And thankfully, thanks to the new defaults that Michael mentioned, that's no longer the
case, at least not as often as it was 20 years ago.
But for the few people who are still running as local system or other privilege account,
please consider the scenario that Sarah just mentioned and make sure that you minimize
privileges, find what you really need and make it work accordingly instead of just looking
for the easy solution.
Actually, I want to go one step further than that.
My advice would be just stick with the defaults, right?
Unless you have a really, really good reason to change the SQL server process identity,
just don't.
Just stick with the default.
Things just work.
If there's some funky requirement, then evaluate it, look at what changes you might need to
make and understand the risks.
But for the most part, the defaults are more than adequate for the vast majority of users
of the environment out there.
By the way, this only applies to SQL server on-prem, SQL server in a VM, in the case of
Azure SQL database, which will come onto at the very, very end, you don't control the
process identity.
It's all controlled by Azure and it is running at least privilege.
So to round out the least privilege section, I just want to talk about managed identities
really fast and then we can move on to some historically common attack vectors.
Don't underestimate the value of running a connection out of a virtual machine, for example,
into SQL server or say an Azure function app and using managed identities, allowing that
managed identity to have just least privilege into the environment, into the SQL server
environment.
You don't need to persist.
In fact, we may want to actually want to touch on this real quick before we move on to, I
suppose it really is like a common attack vector and that is connection strings.
If you have a connection string into SQL server, which you do, an application has to have a
connection string to connect.
There's a whole bunch of options in there and one of them is to use SQL authentication
with a username and password.
Well, the problem is where do you store that username and password, right?
Do you put it in the connection string?
Well, if it's in the connection string, where do you store the connection string?
You know, it was on disk.
Well, how do you protect this connection string from being compromised?
Because if an attacker now has that connection string, the attacker now has an identity and
a password.
So another way of doing that is to use a managed identity and that way the identity and the
credential is actually managed by Entra ID, which was Azure Active Directory.
So let's just take an example of a VM.
Let's say you've got a Node.js application running in a virtual machine and it connects
to SQL server.
You could actually run the VM as a managed identity and have it such that when the Node
application calls out to SQL server, it actually uses the managed identity of the virtual machine.
So there's no credential stored anywhere.
It's all managed by Entra ID.
That is the correct way of doing it.
That way there is no credential on the box.
So if you go back to zero trust again, assume breach, in the environment, if you whack the
environment, the attacker is now in the VM, there is no credential there.
There's none whatsoever, right?
It's all because it's managed by Entra ID, what was Azure Active Directory.
So that's always, in my opinion anyway, when I'm looking at threat models, both customer
and internally, when it comes to client authentication, I'm basically always looking for managed identities.
So Shirad, do you have any comments there about connection strings and usernames and
passwords?
Yeah, definitely.
I mean, I can talk a little bit about the username, password usage, and I'm sure we
can talk a lot more about cred stuffing and other attacks later on.
But one of the most common things is once you have access to an app service or a web
app, you are going through the code base, right?
Because that's one of the things to do to find additional credentials.
And to your point, if it's the SQL connection string with username and password, that gives
me access to one other server.
So I'm essentially pivoting from one server to web server to like a SQL server now, right?
That's a great amount of success.
And also the bad things about passwords, as you touched upon, is the fact that once it's
compromised, there is a possibility that the same pattern of the password or the same password
is used in other services.
Now, it's not probably very shocking to say this, but we do know that password reuse is
very common, right?
Especially for services that are backend, right?
Because people always assume that, hey, this is an easy enough password that I'm going
to set for all of my service A. So password reuse is something that can give me additional
access to other services within an enterprise or organization.
And all of those attacks that I just talked about can be mitigated by using something
like a managed identity.
Much harder for an attacker to get hold of them unless they are actually on that resource.
And even if they do, it's very highly unlikely that they can pivot from one resource to another
resource because managed identity ties down to just one resource and dies with that resource.
I swear that red teamers can never put a sentence together.
No conversation without using the word pivot.
It's like, tell me you're a red teamer without telling me you're a red teamer, right?
The moment they use the word, like a lateral traversal or pivot, I know you're on a red
team somewhere.
So yeah.
Okay, Raul, do you have any thoughts on that?
Oh, yes.
One thing to also consider is what I have seen in many cases is applications that use
SQL login and passwords never rotate the password.
So that password is there forever.
And if that application, well, if the SQL server is connected to the internet, it's
a huge opportunity for an attacker to try to find your password over time.
And since it never changes, you're just helping adversaries.
On the same page, one thing that we should always consider is the SA account on SQL server
on premises.
If you're using SQL authentication, by default, there's an SA account that is all powerful
in SQL server.
There's an option that you should really consider using that is to disable the SA account.
So even if tools out there are trying to crack the password for SA, they will not succeed
because the SA is disabled.
I shall go one step further.
You should probably even treat that as a honeypot, right?
If you see people, if you got alerting turned on and the logs show that people are trying
to touch SA, then you know full well there's probably either Sherat doing it or it's a
bad actor, right?
So I would use that as a honeypot at that point.
What do you think?
I do remember it will be on the audit records when you look at them.
Even if it's disabled, it should still show up.
Yeah, because it's an invalid login attempt, right?
Yep.
If not as a honeypot, it should at least give you an idea of how often you're attacked.
Yeah.
Because it's not a matter of if, it's a matter of how often.
All right.
Let's use a word that Sherat has used a dozen times so far.
Let's pivot the conversation to common attack techniques.
At least those that are historically common.
Let's start with everyone's favorite.
I'll let you go first this time, Raul.
What about XP underscore command shell?
A favorite topic of mine.
XP command shell is a really interesting standard procedure that allows you to execute arbitrary
commands on the operating system using the SQL server account.
Unfortunately, it's mostly used by attackers.
I have seen very few cases where customers and serious people use it for something productive
that cannot be done in other ways.
It's disabled by default, but you should still monitor any usage of it.
Going back to somebody compromises your SA account or says admin user, they can enable
it again and they can use it to run whatever they want, unfortunately.
Yeah.
I agree with Raul.
It's one of the favorite tools as a Red Teamer.
I'm saying this because when you actually get access to a database, you have limited
set of commands that you can run using T-SQL commands.
You're limited to what T-SQL is exposing.
A lot of it has to do with manipulating tables or doing mostly queries that help us manipulate
data.
That's pretty much it.
With something like XP command shell, it opens up the door to expand it to operating system
commands, which is quite interesting as an attacker because now you can do more things
using SQL Server, be it things like dumping a process memory or getting your own command
and control on that server by executing your own binary, downloading binaries onto the
box or even exfiltrating data from the box to your remote server.
All of these can be done because you can run the OS command.
We talked about it a little bit earlier, depending on what privileges you're running the SQL
Server or SQL Service as on your VM or on-prem, it gives me a different set of privileges.
If I have system access, then I can probably run all possible OS commands and compromise
the entire box or potentially get ideas to compromise the network itself.
Pretty nifty tool.
One other thing that I do want to add is by default, even though it's disabled, what I've
seen a ton of times is that the user that is running the app service has privileges
to go back and enable this XP command shell by setting the configuration, which is as
bad.
Again, going back to least privileges.
Disabled is great, but make sure that nobody has enough privileges to enable it back again.
Yeah, I'll tell you a quick story and then we'll move on to another topic.
Back in the day, it's a long, long time ago, I had a customer working with SQL Server and
the configuration was absolutely awful.
Basically, they were running a processor system and they had the XP underscore command shell
enabled and one day they couldn't boot the machine.
The reason is someone who was really, really evil actually deleted, this is a flashback,
but deleted boot.ini from the machine.
You can't just go deleting boot.ini, right?
Well, you can if the process deleting the file is running at system and XP underscore
command shell is available with just a simple Dell space slash boot.ini.
Yeah, so they couldn't boot the system.
It was easy enough to fix, but the point was made, right?
Don't run elevated and don't enable XP underscore command shell.
Neither of those have been the default for a long time, right?
We don't enable XP underscore command shell by default and we don't run elevated by default.
The process identity doesn't run elevated by default.
So yeah, it's magnificent to see.
All right, let's pivot again to something that's near and dear to Raoul's heart, which
is SQL Agent.
Yes, and the way to think about SQL Agent is scheduler for running automated tasks,
but a lot of times people forget that those tasks could be also operating system commands,
right?
Like running PowerShell scripts, again, under the context of a service.
I forgot if it's going to run under the SQL Server context or under the agent context,
but it's still one of those services running arbitrary commands on your machine.
And a lot of people forget about that part.
And it's again a tool that I have seen being abused many times and it's easy to hide because
all the tasks are on MSDB and it's automated.
It's not even something that I have to connect anymore in order for it to execute.
I just set it and forget about it.
So it's very important to use the SQL Agent role-based access control for managing the
agent roles.
They have different roles for managing and adding new tasks, to view the tasks or nothing
at all.
And in most cases, people should not have access to those tasks.
Yeah, I absolutely agree with Raul.
SQL Agent is pretty powerful in the sense that you can schedule jobs that includes running
both command exec as well as PowerShell, right?
It's pretty nifty for admins and it totally makes sense why the admins would use it.
But to Raul's point, we have to make sure that these jobs cannot be created, managed
or modified by any user, right?
I mean, it's fine if you really do have to use agent jobs to do automation, of course,
but the RBAC is very important, right?
Making sure that we know who is running these roles and also to some extent, if there is
a way to go back and audit if it's run according to the schedule that you want or if there
was any change done to the job.
And they're audited events, right?
Like if someone goes and modifies a SQL Agent task or adds one or deletes one or whatever
changes the identity under which one works, that's an audited event.
So I strongly recommend reviewing the tasks in the MSDB table themselves.
Don't rely exclusively on auditing events.
All right, let's change another topic, which is one of my favorite topics and that's SQL
Injection.
Unfortunately, we still see SQL Injection vulnerabilities.
I thought this is something that's long dead, but nope, absolutely not.
It's almost like 2005 called and want their vulnerabilities back, but we still see SQL
Injection vulnerabilities.
People are rushing their products to market and they're building code that queries back
in databases and they have SQL Injection vulnerabilities in them.
It's not a bug in the database.
I just want to point this out.
Database just sees a query, right?
And essentially what you're doing is you're building up a SQL statement that has some
untrusted data and you're probably using something like string concatenation to build up the
SQL statement.
And the problem there is that if the attacker controls that string, then they can make the
query basically do whatever they want within some confines.
So yeah, we still see SQL Injection and it doesn't matter what your language is.
You could be using the latest funky languages that everyone loves like Go and Rust, talking
to your favorite backend database and still have SQL Injection vulnerability.
So it all boils down to using what are often referred to as parameterized queries or placeholders,
depending on the library you're using, where the SQL statement is built up safely.
It's also really important to understand what the attacker controls.
So if the attacker controls part of the string that's used to build up the SQL statement
or part of the data that goes into the SQL statement, you need to make sure that you're
using good input sanitizing as well as parameterized queries.
Sherat, do you want to explain any more about that or add some more details?
Yeah.
I mean, that'll be really quick about this one.
You have summarized it pretty well and covered most of the points.
I mean, the thing with SQL Injection is regardless of what language you're writing your code
and ultimately you are going to write the SQL statements to query data.
And I've said this before many times that when you write that SQL statement or when
you do a code review, there is absolutely nothing wrong with the code because all it's
doing is it's select statement querying data.
It looks absolutely fine and works absolutely fine, except for the part that you are accepting
untrusted user input to be part of the code, right?
When you're not parametrizing your query, which is bad.
Unfortunately, we see SQL Injection even to this date.
And the real factor here is the statement actually works no matter how you look at it.
And it's very hard to tell if that is a security bug or not unless you really know about SQL
Injection.
So a lot of this is about education and awareness that how do you separate data from code and
what is the best way to do that?
Yeah.
Let me add to that and I'll hand it over to Rahul.
You bring up a really important point there.
If you're doing functional testing, the SQL statement works, right?
You're doing a nice, normal, everyday, happy, happy path query and everything just works.
The problem is in the face of untrusted input.
That's why it's so important.
One of the core developer skills, when I'm talking to developers, one of the core skills
and one of the core things they have to understand is what is the attacker control?
And if the attacker controls incoming data that you then use to build up a SQL statement,
that data is essentially toxic waste and you need to treat it as toxic waste and do all
the appropriate mitigations and defenses and cleansing of that data and then ultimately
parameterize queries to make sure that the statement is safe.
One of the definitions that I like to use for a secure system is a system that does
what it's supposed to do and nothing else.
The problem is in the face of untrusted data and string concatenation to build up SQL statements,
you can now start to have unexpected functionality like dropping tables or return more data in
the result set that was unintended.
It's that extra functionality that has now basically given you a security vulnerability.
I agree 100% a lot of it's education, use of good libraries, use of correct libraries,
but there is something else that we need to look at and this falls squarely into Raoul's
current domain and that is static and dynamic analysis.
So Raoul, one thing that you've been working on for the last quite some time is CodeQL.
Do you want to just give our audience a really quick overview of CodeQL and explain how it
can help them with detecting SQL injection vulnerabilities?
Absolutely.
CodeQL is a static analysis tool that the idea is it will instrument your code, either
if it's interpreted codes like JavaScript or if it's a compiled code, it will instrument
what you're compiling it and create a database that you can query and detect patterns in
your code.
And more specifically, the real power of CodeQL is data flow analysis.
So you can track the, we call it taint data from an external source, for example, a web
request, et cetera, that will ultimately flow into a SQL command.
And if it's coming from an untrusted data, if it's considered tainted, you better know
how you are sanitizing and protecting against SQL injection and we have written queries
for that.
They're publishing in GitHub, actually, Microsoft and GitHub.
We continue working on that and making improvements.
So I would like to invite people to try CodeQL.
If you like, it will help you to detect the flaws on the application because as we have
already mentioned, this is not a problem on the database itself, it's a problem on the
application.
And it's important to assume that there are bugs on your application.
It's a mistake.
It's easy to miss it.
Actually, my Pentastain team friends make fun of me because I'm a one trick pony.
Every time that I compromise a server, it's through SQL injection.
That's the first avenue that I use, but I keep using it because it works.
We always find something.
It's very easy to miss.
And I would like to emphasize that this is also the reason why we have been talking about
least privilege so much.
If you constrain what you can do when the account is compromised, when the application
has an error like this, you will have a better time than if you're using sysadmin.
Yeah, I really want to stress this point.
Actually, there's two points I want to stress.
The first one is the least privilege aspect.
Again, if the connection is elevated and you have SQL injection, the SQL injection vulnerability
can now probably start accessing and doing things above and beyond what a normal user
should do.
You might constrain the users to certain types of SQL, but it doesn't matter if the SQL injection
vulnerabilities.
All this thing, all that we've talked about so far, all sort of meshes together.
They all relate to each other or they become worse.
So, if you don't have least privilege or you have elevated process or you have XP underscore
command shell, catastrophic if you had SQL injection, elevated process, XP underscore
command shell, and connectors SA.
You're just asking for extreme damage of your data.
This is the least privilege aspect with SQL injection is critically important.
The other thing I really want to point out is CoQL is not just SQL injection.
One of the beauties of CoQL is that the queries are literally queries.
You're querying, as Raoul mentioned, essentially when you compile the code with CoQL, they
build up a database that can be queried.
It's really cool because you can literally write your own rules.
I'm not going to say that, hey, writing your own rules is dead easy, but it sure is a lot
easier than having to pay a customer or company $100,000 to write a new rule for your static
analysis tool.
If you have a public GitHub repo, you can use CoQL for free.
You can download CoQL and run it on your own code locally as well.
All right.
So, let's switch attentions now.
Start to sort of think about wrapping this up with a couple of topics.
Sharad, I know you have some thoughts about how attackers gather credentials.
One of the things that is very common for attackers is to do credential stuffing.
One of the common services, if you do a bit of research on the internet, is SQL servers.
A lot of times what attackers do is harvest credentials through various sources, either
through previous data breaches or through other channels.
Once they have these username password lists, they're just going after various services,
including SQL servers.
What we have found is oftentimes SQL servers, unfortunately, are exposed to the internet,
which I can say with some level of confidence that you do not ever need to expose your SQL
server to the internet because I don't think the customer or any of your services or customer
is directly talking to the SQL server to fetch raw data.
There may be some cases, but most times it's some front-end application that is querying
SQL server as a backend.
So, there is very less use cases for SQL server to be exposed to the internet.
We talked about this a little earlier as to how attackers go after the SA account.
SA account, high-privileged, gives you admin access to SQL database and server.
Attackers try to find reuse passwords or a combination of passwords that are breached
to compromise SA account that are exposed on the internet.
Now, the good thing with Azure SQL databases is when you create your own Azure SQL databases
by default, it's not exposed to the internet.
You get to choose how you want to expose your server.
There are multiple ways you can do this.
You can say, hey, I want to expose this to all Azure services, meaning any Azure VMs
or app services can connect to my SQL databases, or you can explicitly add SQL server firewall
rules, which lives in the master database.
At the same time, you can also granularly control SQL database firewall rules.
This is saying, hey, I want these databases to be accessible by only these apps or by
only these VMs.
This is a great level of protection that you can add to Azure SQL databases.
Do not expose it to the internet and have allow listed IP addresses from where your
SQL server is accessed.
I think this is really important, the Azure SQL database aspect.
This is the classic shared responsibility model where some of the security is managed
by Azure so that you don't have to.
For example, things like patching, anti-malware, monitoring, that's all taken care of by an
army of people.
That's their job.
That's all they do.
It's not like people on a part-time schedule.
This is precisely what they do.
But also the fact that things like least privilege, like the process runs at least privilege or
the backend processes run at least privilege, the fact that certain functionality is not
enabled by default.
For example, Xpandr score command shell is not there.
If I remember correctly, Raul, SQL agent is not there, right?
Correct.
SQL agent is not there.
Xp command shell is not there.
There's many other features that are mostly used by attackers and not by normal customers
that are not present in Azure SQL databases, which is fantastic.
It is, and yet people still manage to be able to build industrial strength solutions without
those components.
Of course, that includes SQL mail, everyone's favorite.
I don't know anyone who's used SQL mail, but that's another discussion for another day.
That copath isn't there, right?
That's just so incredibly important because it can't be attacked.
For example, SQL mail, if it's not there, it can't be attacked.
Same with the other ones.
The fact that you can also, by default, Azure SQL database does not connect to the internet,
has no listening ports, you must actually connect it to something at some point.
That could be, for example, the backend of an Azure function app, or perhaps, let's say
back to my Node.js example, you might have a Node.js application running on VM, for example,
and the SQL server, the Azure SQL database is listening on that VM.
You could put a, I'm sure I'm correct if I'm wrong here, but you can actually put a rule
in place that says traffic into that Azure SQL database must come from that, say, Azure
function app or that IP, the backend of the Azure function app or the backend of the virtual
machine.
Is that correct?
Yeah, that's correct.
You can specify the IP addresses of your VM where your Node.js app is running or your
function app and allow access to only that specific source.
What about VNAP?
Yeah, that's another great thing.
I mean, now that you brought it up, we can talk about it.
You can also have your SQL server in a virtual network, meaning you have your Node.js app.
I'll take that as an example, which is running on a VM, which is also on a VNAT.
What you can do is instead of connecting it over the public IP address, you can now have
these two VNATs or these two services connect over the VNAT using private IP address, all
through Azure Backboard.
That basically gives you some more flexibility in the sense that you don't really have to
maintain an allow list.
You could add other resources on that VNAT and they all will have access to the SQL databases
and vice versa.
That's an important point, right?
Because they're all listening.
For example, 10 dot blah, blah, blah, blah, blah address range is non-routable.
Everything that's on that VNAT can talk to everything else on the VNAT.
There doesn't need to be any specialized rules, any network.
You can add extra rules if you want around network restrictions, but you're getting a
degree of isolation just because it's on its own private VNAT.
Yeah, exactly.
All you have to now look at is the network architecture to understand the connectivity.
It's less about managing a list of rules that is somewhere hard to read.
Well, hard to maintain too, right?
Yeah.
It's just one more thing to maintain.
All right.
Let's bring up our last topic and that is Defender for SQL.
Huge fan of Defender for SQL.
I'm a huge fan of all the Defender products.
I think they're magnificent.
Defender for SQL allows you to protect in real time against certain kinds of issues.
For example, it can detect SQL injection.
It can also detect if there's some malicious behavior going on such as exfiltration that
doesn't look normal.
Raul, if I remember correctly, it also has an assessment capabilities.
Yes.
It's the pull that ability assessment feature on SQL Defender for Azure SQL databases.
Actually I wrote the original set of rules for that.
It has evolved since then, thankfully.
It's a feature that allows you to detect who are your principles, what permissions they
have, if there's any change on the database configuration, on the tables that you have,
permissions that you have.
So it's a very useful feature to keep track of any changes on your database.
Yeah.
Actually, just a point of clarification.
Defender for SQL will also work against SQL Server running in a VM on Azure, on GCP, and
AWS, which is actually kind of nice.
So it's not just looking at Azure instances.
It can look at AWS and GCP, IaaS components as well, which is really good.
All right.
Let's start to bring this thing to an end.
Gentlemen, one thing we always ask our guests is if you had just one thought to leave our
listeners with, what would it be?
Raul, why don't you go first?
The one thought that I have will be there's no silver bullet.
You have to consider all aspects, least privilege on the service account, the user that you're
using to connect, the permissions on the database, be aware of SQL injection, be aware of your
tasks, what information you have on your SQL agent tasks, et cetera.
So it's not just protect one area and you're done.
You have to look at it as a whole.
And reducing the text-service area, for example, using Azure SQL databases that by default
has a significantly reduced text-service area is a huge benefit.
Also monitoring everything good that Defender will bring you is something that you have
to consider.
Yeah, thanks for having me here, Michael.
It was a great discussion with Raul as well.
So one of the things that I would like to close with is to assume breach at every layer.
We talked about a lot of the different attacks and one thing that you can do, actually everybody
can do who is part of the team, is to come up with different things that you can think
of if a particular component.
It doesn't have to be just the front end or the application that is exposed to the internet.
It can be your middleware, it can be a network appliance that is in between, or it could
be the backend or it could be the SQL database.
We ask ourselves at every competent level or every identity or user that is involved
in that, this includes even the DevOps credentials.
We ask ourselves the question of, hey, what happens if there's a breach here at this point?
I like what Raul put it as well, there is no silver bullet, but at least what we can
understand is what will be the blast radius if any of those identities or components are
compromised.
So knowing the level of damage to some extent is always, always beneficial in the security
world.
So knowing how far an attacker can get can oftentimes be very, very bad.
So I just want to wrap this up.
This has been a great conversation.
To our listeners, just be aware that these two guys, they live and breathe securing both
our own instances of SQL Server, but also work on just making sure that customers deploy
things correctly and so on.
They really do come at it from a real world attack perspective.
If you think I'm evil, you should see these guys in action.
So with that, let's wrap this up.
Again, thank you so much, gentlemen, for taking the time out.
I know that you're both incredibly busy.
To all our listeners out there, we hope you found this episode useful.
I like to think this is a little bit different than some of the other episodes.
Stay safe and we'll 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 AzureSecPod.
Background music is from ccmixtor.com and licensed under the Creative Commons license.