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
114. This week it's myself, Michael. Here by
myself, the other guys are all busy. I also have
two guests this week to talk about SQL Server
2025 security updates. Peter Vanhover, who is
a regular on this podcast. In fact, I think this
is number three. and Pratim Dasgupta, again,
both from the Azure Data team and the SQL Server
team. But before we get to our guests, I want
to take a little journey around the news. I have
four little items to talk about. The first one
is, in case you're not aware, Microsoft Build,
just finished in Seattle, and both myself and
Sarah were both presented. I presented on reflections
on 25 years of writing secure code. That's the
book. First of all, it's absolutely terrifying.
It's been 25 years since the first edition of
Writing Secure Code came out. Lots of stories.
It was a lot of fun giving the presentation.
I also talk about the story where I convinced
Bill Gates to write the foreword for the second
edition. Just lots of stories and also how things
have progressed over the years, especially from
a tooling and sort of knowledge perspective and
how threats have evolved. The other one that
Sarah did was with Pamela Fox, deploying an end
-to -end secure AI application. It's a fun watch,
if nothing else. You'll learn a lot from this.
So again, I'll provide links to both of those
presentations. There are plenty of other security
presentations as well, but I just want to call
out those too many because, you know, we're both
on the podcast. The other one which really caught
my eye is a blog post from Ravinda Gupta. It's
called Demystifying Azure Private Link Benefits,
Pitfalls, and Best Practices. Honestly, This
is probably the best five minute read I've ever
read on Azure Private Link. It gives a really
concrete set of examples of the benefits, kind
of how it works and why you should use it. One
thing that we find in the red team is one of
the major problems we see. is when a service
actually has a very flat network. In other words,
they don't isolate critical parts of their infrastructure
from other parts of the infrastructure. And that
makes it really easy for the red team to traverse
across the network. So PrivateLink really helps.
And many other security isolation techniques
really help make life really hard for the red
team and for attackers for that matter. So if
you're not familiar with PrivateLink or it's
still a bit of a mystery to you, then do go ahead
and check out this blog post. The last news item.
There's a blog post been written as a follow
-up from Sarah's blog post, which is understanding
and mitigating security risks in MCP implementations.
There's a follow -up to that. Actually, it's
not really a follow -up. It's another blog post,
sort of concerns about MCP from a security standpoint.
and what we're doing at Microsoft to help mitigate
some of those issues. So again, if you're using
MCP, which is the model context protocol used
to build AI applications, we sort of link things
together, then do go take a good look at this.
All right, that's the news out of the way. So
let's turn our attention to the guests. As I
mentioned, this week we have two guests, Prateem
and Peter. So gentlemen, why don't you go and
just take a moment and introduce yourself to
our listeners. Sure. Hello, everybody. I'm Pratim
Dasgupta, and I'm the product manager working
on identity and SQL authentication. Hi, everybody.
Yes, I'm Peter. I'm also a PM product manager
in the data platform security team, and I'm managing
everything related to data encryption. So transparent
data encryption, always encrypted, and also ledger,
which is everything related to data integrity
and not data encryption. All right. So actually,
before we get stuck into the actual content.
So first of all, Peter, since I think last time
we spoke on the podcast, you got a promotion,
right? Oh, yes, yes. I did. I did. Yeah. I'm
a senior PM now. You're a senior PM. Fantastic.
That's good. Good stuff, man. You know, you do
good stuff, man. So, yeah, congratulations. Thanks.
Let's get stuck into this. So we've got three
broad categories, entry authentication, cryptographic
updates, and then security cache improvements.
So why don't we start with Prateem, and if you
want to go through the main entry authentication
improvements in SQL Server 2025. Oh, yeah, sure.
So, Michael, as you'd already be knowing, we
have a pretty big push that we do not want to
use passwords. I mean, Hantra is there from a
long, long time. uh but not for the our customers
that much i if if you have tried in sql server
2022 which is an on -prem though you can enable
entra and you can remove sql logins which use
passwords but you still rely on credentials like
certificates to set the entra so in a way the
passwords are gone but you are still relying
on some kind of credential right that can be
stolen and hacked and whatnot right but with
2025 on arc enabled machines you would not even
need a credential to start up your Entra authentication
on SQL. There's a managed identity that's provisioned
by the Arc agent itself. So what I mean is as
soon as your Windows VM in an on -prem system
is Arc enabled, you get a managed identity provision.
So that VM is actually registered with Entra
servers. And now in SQL 2025, we have taught
SQL Server to use that identity and speak to
on the service to authenticate any users and
logins. On top of that, you can also use that
identity for out -mount connections. For example,
you want to take a backup from SQL database to
Azure storage maybe, you don't have to use any
SAS keys or anything for the storage. You can
use that identity, give that identity a block
contributor access, the usual thing that you
do for the storage access. and there you go using
a backup restore usual commands and a create
credential manage identity is equal credential
free you can take backups and you can restore
backups so this is one of the example that that
we are pushing forward you can also use ekm for
that so yeah i mean this is an effort that we
keep on we plan to keep on doing it uh push forward
for every feature not to use any kind of credential
So I just want to ask you a question about the
managed identity and just about the lack of credentials.
So I'm sort of in a bit of a unique position
here because, first of all, I used to work in
the Azure Data team, so very familiar with SQL
Server and so on. But now I work in the Red team,
and the first thing that we go hunting for is
credentials, right? I mean, if we can find credentials,
then that lets us traverse across the network.
And a lot of the credential work or removing
credentials that are being done across the company
are there because of the secure future initiative
work, which is removing credentials from the
environment. So is this an example of removing
credentials from the environment to sort of help
with the SFI requirements? Yes. So that is one
of the prime motto, that any service or every
service that Microsoft provisions and it's customer
facing or not, we do not want folks like you,
me, or any user to manage their own credential.
And it's not limited to passwords. It's anything,
any artifact that can be used to authenticate.
We've had issues where we've found, you know,
SaaS tokens that have led to essentially exploitation
of storage accounts. So yeah, anything where
a credential is persisted is a number one target
of attackers. So it's magnificent to see SQL
Server, you know, going above and beyond to make
sure that these things are removed. Yes. So it's
not completely new. So if you were a VM customer,
like, completely Azure customer, you're already
using Azure VMs, you could have done it in SQL
2022. So 2022 and Azure VMs already knew how
to use managed identities, but then we are just
restricting every customer to come to Azure.
With this effort, even the customers who are
not in Azure shop, just an on -prem shop, can
actually take use of this. So it's an effort
to secure everybody, I'd say. All right. Well,
that's the internal authentication stuff out
of the way. That was short and sweet. But again,
I really want to stress, I think these changes
are really important. I mean, as a world, we
need to get away from these sort of credentials
that can be persisted somewhere because, again,
the attackers go after them. All right, so the
next thing is cryptographic updates, and I think
that's where you come in, Peter. Yep, that's
right. Most of the crypto updates, the users
of SQL Server 2025, they won't even probably
notice it because... We're doing some stuff under
the covers. Well, if you would look in system
tables, you will notice that we're using different
types of algorithms and better algorithms. But
yeah, mainly the users, they will not notice
it that we have made SQL Server even stronger
on security level. The first one, what we've
done, Michael, and you're probably familiar with
it, is the PBKDF for password hashes. We've enabled
this. by default in SQL Server 2025. So what
we did in previous versions of SQL, we were using
a SHA -512 hash with a 32 -bit random and a unique
salt. So that was a pretty good method to make
it impossible for hackers to fetch passwords
that are stored in SQL Server. Now what we've
done is we're using that new algorithm, so the
PVKDF. And we're still using that SHA -512 hash,
but we're hashing the password. Instead of one
time, we're hashing it multiple times, like 100
,000 iterations, which significantly is slowing
down brute force attacks, of course, by using
this. How will customers... know that they're
using this new functionality. Well, if you look
at the passwords that are stored in the SQL login
system table, you will notice that the first
byte is the version of the algorithm that is
used. And in SQL Server 2025, that is going to
be 03. If you still see 02, then it means that
you're using the old algorithm here. A little
bit of context to this, as you kind of mentioned.
I was actually the PM for this particular feature
back in the day when we first implemented it.
So it was actually a requirement that came in
from a very large bank in the US who needed it
for compliance reasons. So we worked closely
with the customer and we produced a version,
as you mentioned, it's a PBKTF, stands for password
-based key derivation function. And we iterate
over the hash or the originating password. And
as you say, a unique salt. I'm so glad you said
unique salt. A lot of people say random salt.
It doesn't have to be random. It has to be unique.
So using a unique salt and a random password,
and obviously a random password, hopefully a
random password, and then iterate over that 100
,000 times, which, by the way, at Microsoft is
the... lower limit under the microsoft security
development life cycle for pbkdfs and then we
store that result and historically it was it
was there in sql server 2025 but we hid it behind
a trace flag so it was off by default and uh
yeah it's nice to see this on by default and
as you mentioned you know this is version three
hence the first byte is zero three and but we
only change make sure is this correct i assume
this is the case we don't auto -migrate from
version 2 to version 3? Because we can't, because
we don't have the password, right? Yeah, that's
a good question, Michael. What we do, sorry to
interrupt you, is when you migrate a login or
you script a login from SQL 2022 and you run
that script on SQL 2025, we will still use the
old algorithm. Until you do an alter login and
you change the password, then we will start using
the new algorithm. If you don't change anything,
we will still use the old algorithm. So it's
transparent if you migrate logins. And that's
just so incredibly important because we don't
have the password, so we can't generate the new
password verifier, right? So we don't store the
password. We store the verifier of the password.
Correct. That's good. Okay, that's great news.
All right, what else we got? The second one that
we have is the OAP support for certificates and
asymmetric keys. Azure SQL and SQL Server, the
RSA algorithm is used for asymmetric encryption.
But the RSA algorithm cannot be used in its pure
form. So what we're doing to achieve security,
these messages, they require padding, right?
So currently, we're using or the data is encrypted
with RSA using the PKCS version 1 .5, but that
has a specific weakness, right? So what we've
did is we upgraded it to a newer version with
a new padding type called Optimal Asymmetric
Encryption Padding or the OAEP, right? So that
introduces some hashing between the RSA encryption
and the padding check, right? which makes it
significantly much more difficult for an attacker
to understand what they can see. Yeah, there's
a really nasty vulnerability called a padding
oracle attack. And OAEP essentially mitigates
that, which is good. Always a good thing. Yeah.
Again, something you cannot see unless you look
in a system table called key encryptions, where
you will see that the certificate or the asymmetric
key Sorry, the key that is encrypted by the certificate
or the asymmetric key is using that OAP. So on
that topic of OAP, I mean, do we add that because
it's a fun thing to do or do we actually add
it because customers ask for it, compliance reasons,
modern threats, all of the above? Yes, all of
the above, Michael. Okay, fair enough. We just
wanted to make it better and stronger as always.
So we are the most secure database platform.
So yeah, we wanted to make it even stronger.
And that's the reason also why we implemented
this. So the next topic is TDS 8, TLS 1 .3 support,
which is one of my favorite topics. So do you
care to go into some depth about that? SQL Server
2022 already supported TDS 8 .0. So you could
use an extra parameter in your connection string.
This encryption equals strict. which means that
at that point, you were using TDS 8 .0. On the
other hand, you also had to upgrade your drivers.
But anyway, we already supported it. The problem
was, Michael, that in SQL 2022, all our internal
features like SQL agents, like BCP, always -on
availability groups, all these features, they
were not using TDS 8 .0. So we wanted to address
this problem. And now in SQL Server 2025, all
our internal features will support TDS 8 .0.
We're not there yet. So we're still in public
preview. And some of the features like SQL command
and BCP, they already support it. But the others
will come later, probably by GA. But for sure,
they will all use TDS 8 .0. And so for clarification,
so TDS is tabular data stream, right? Which is
the native SQL server communications protocol
on the network. That's right. Yeah. Okay. And
then TLS is just, well, hopefully we all know
what TLS is at this point. And just a little
bit of history. So back in the day, so prior
to TDS 8, there were some very strong interdependencies
between TDS prior to 8 and TLS that made it really,
really difficult to upgrade TLS protocol versions
to more modern versions. So now we've done all
that hard work to break the two apart and break
the dependencies. So now they can be essentially
upgraded independently, which is really good
news, which is the way it should be, right? It
really should just be the same way HTTPS works,
right? We can upgrade HTTP if we wanted to underneath
the covers. We can use... 1 .0, 1 .1, 2 .0, whatever,
and then just change the TLS protocol version
at will. And luckily, we're in a position like
that now with TDS, which is great to see. Good
news. All right, last one is security cache improvements.
What the heck is that? Yeah, that's a known problem
that existed for years that we finally resolved.
And the problem is, well, Every time that a user
or a login makes a connection to SQL Server,
what we're doing is we're storing all these permissions
of the users and the logins in the security cache.
One of the benefits of storing that in the cache
is, of course, it's speeding up the query execution.
Now, the problem is that if... there are certain
actions that will invalidate that security cache.
For example, if you create a new login on the
server or if you change the permissions of a
specific login, if you drop a login. Either way,
so all these actions will invalidate the cache
and what I mean by that is it will invalidate
the cache of the whole database or the whole
server, depending if it's a login or a database
permission that you've changed. Now, this means
that every single connection that is open at
that point to your instance will need to rebuild
the security cache from scratch, right? And this
can significantly impact the performance on the
server, right? If you just have a couple of connections,
no worries, you won't see any difference. But
if you have like thousands of connections that
needs to rebuild all their cache entries, yeah,
definitely you will notice some performance impact.
So what we've done, Michael, we addressed that
problem and currently in SQL Server... 2025,
we're only invalidating the cache for a specific
login. Let's say the three of us, we have a login
and I change a permission in my login. It means
that my cache will be invalidated, but not yours
and not the cache of Protein, which, yeah, of
course, reduces that performance impact. Currently,
it applies to create and drop login scenarios
and permissive changes on individual logins.
We're currently in public preview. By GA, we
should also cover alter login as well. For group
logins, so it only works currently for individual
logins. For group logins, you will continue to
experience the server level invalidations. We're
working on that and probably, yeah, we don't
have any timeline yet, but probably it will be
released in one of the next CU updates that we
will do on SQL Server 2025. But yeah, huge improvement
here and a lot of our customers will be happy
with this improvement. So a couple of things.
So first of all, CU being cumulative update.
Yes. And so is it, are you saying it's always
been this way until like? SQL Server 2025? My
goodness. So that must have caused like huge,
my guess is this was a real nagging customer
pain point, support pain point. Yeah, yeah, definitely.
Wow. Support team, they had lots of incidents
because of that. Yeah. And my guess is it probably
wasn't a very easy fix. That's why it probably
took a while to get done. Yeah, that's right.
Yeah. And still is. Yeah. So command by command,
because there are lots of commands that, are
triggering and securing the cache invalidation.
So we need to cover them both. So we currently,
like I said, we have create and drop and alter
login, but there are plenty more like create
application role or create certificates also
invalidates the cache. So yeah, we still have
some work to do, Michael. Wow. That's good to
see though. I mean... I mean, anything that improves
performance and improves security at the same
time is always a good thing. Yes. All righty.
So one thing we started asking our guests, probably
started just a couple of months ago now, is,
so what does a typical day look like? When you
come to work, what does a typical day look like?
Every day before I sleep, I try to see what I
have in the next day's meeting. And for whatever
reason, every day starts different. As soon as
I open my emails, there are a bunch of emails
that I'll have to answer and then go to meetings.
And long story short, every day is a little bit
different. But the vanilla part is you have a
bunch of meetings, meet with people, answer customer
queries, and then take some time to write specs,
update customer documentations, speak with engineering
to see what issues are going on, if we are on
the time for deliveries, and if you get some
time. then read about new features that we should
be doing and what our competitors are doing and
whatnot and read to them and hear podcasts, see
videos so that I learn. It's a mixed bag, I'd
say. My day starts with a coffee. I cannot start
without coffee. So first things first, coffee.
I'm in a different time zone than Protein and
my other colleagues. So I'm based in Europe,
which is a huge advantage for me because when
I start my... working day everybody else is asleep
so I can do I can yeah read all the emails I
can prepare the the next meetings for the day
I can I never get disturbed right I just can
do whatever I want I don't get any well not that
many emails during the day so what I do I typically
work Around my noon time, then sometimes I just
go for a run or go out to walk with my dog. And
then I start again at 3, 4 in the afternoon with
all the calls that I have with people in Redmond,
with engineering, with other PMs currently working
on customer managed key and fabric, which takes
a lot of my time. working on SQL Server 2025,
preparing presentations and stuff like that.
So, yeah, that's how my life looks like. I like
the team that you said, you know, you're very
conscious of making sure you stay on top of things.
I think that's really important, you know, carve
out some time to sort of learn, you know, new
stuff. All right, so let's bring this episode
to an end. So we like to ask, I guess, something
we've done since the very beginning. is if you
had just one final thought to leave our listeners
with, and what would it be? Yeah, I can get started
with it. So because SQL 2025, the Entra features
are ARC dependent. So I would say if one of our
customers is trying it, they should make sure
that the ARC extensions and the SQL ARC extensions
are the latest ones so that they don't run into
some random issues. All right. And I would say
we've made SQL 2025 even more secure. So we did
a couple of crypto updates. We've done updates
on the security cache. So yeah, we always say
secure by default. And that is still true. And
last but not least, go download SQL Server and
just try it out yourself. Very cool. All right,
so let's now officially bring this episode to
an end. Gentlemen, thank you so much for joining
me this week. I know you guys are both very,
very busy, so I appreciate you taking the time.
And to all our listeners out there, we hope you
found this episode useful. 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 ccmixter .com and licensed under the
Creative Commons License.