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 68.
This is one of those special episodes.
It's only myself this week, Michael, and I have a guest, AJ Jagannathan, who's here
to talk to us about the release of Microsoft SQL Server 2022.
But before we get to AJ, I actually do have a couple of little news items that are related
to SQL Server.
The first is that we now have a STIG for Azure SQL Database.
Now, STIG stands for Security Technical Implementation Guides.
They're created by a group of folks called DISSA, and they create most of this documentation
for the Department of Defense.
But they're also available for commercial use as well.
They're peer reviewed.
They generally tend to be really good quality, but it's great to see something finally coming
out for Azure SQL Database.
The other thing I want to just want to touch on briefly is my colleague, Andreas Walter,
has written a blog post called Troubleshooting External Data and Access Policies in Azure
SQL and SQL Server.
This is actually a much needed post.
Reviewing access policies in SQL Server is not easy.
I'm going to be honest with you.
It's very worthwhile read.
Andreas also touches on integration with Purview.
We'll talk a little bit more about that as we continue through this podcast.
Those are the news items that I have.
Now let's switch our attention to our guest.
This week we have AJ Jaganathan who's here to talk to us, as I mentioned, about SQL Server
2022.
AJ, welcome to the podcast.
We'd like to just spend a quick moment and give our listeners a bit of background on
yourself.
Thanks, Michael.
It's my pleasure to be here speaking to you and sharing some knowledge with the audience.
Hey, folks.
My name is AJ Jaganathan.
I'm a Principal Group Program Manager in the Azure Data Team in Microsoft.
My team is responsible for building SQL Server and hybrid services and helping our customers
successfully deploy this platform for their applications.
We just released SQL Server 2022, literally, two or three weeks ago.
There was a SQL Pass Summit in Seattle.
I was actually a presenter there talking about funding security.
That was the day we actually announced the general availability of SQL Server 2022.
AJ, why don't you spend a few moments and give us an overview of the big changes and
the big messages that we have around SQL Server 2022?
Let me do a quick rundown of some of the major features of SQL Server 2022 and why we believe
customers would love to upgrade to this release.
It all starts with connecting to Azure.
We invested in several scenarios like SQL Minage Instance for disaster recovery, near
real-time analytics with Synapse Link, as well as insights lineage and policy management
with Microsoft Purview.
The second area is, as Michael said, security is always very critical.
SQL continues to be the least vulnerable database in the industry with the latest published
numbers from NIST, as well as investments like Ledger and Purview that you heard Michael
mentioned.
Finally, performance and availability is something we take pride on.
SQL ranked number one in the latest benchmark that HPE published for SQL Server 2022 for
the data warehouse non-clustered workloads.
You'll also see a ton of innovations in the built-in query intelligence which make your
apps run faster without any code changes.
Is this a short summary of some of the key innovations of SQL Server 2022?
When we look at a major area of innovation, as you mentioned, security, one of the big
ones is we're starting to see the first efforts around integrating with Purview for managing
roles but also just data governance in general.
Do you mind just spending just a few moments, just explain what that means for people, what
that means for customers?
Why should they even consider using Purview?
Over time, what we have seen is, I mean, the attack surface area has just increased.
Customers are finding it very hard to keep up with keeping their environments secure.
The environments are hybrid in nature.
You may have some deployments in your own data center, some in cloud environments, whether
it's in Azure or other clouds.
Customers are really looking for central experience, a single pane of glass if you will, to manage
such large estates.
This is where Purview comes along.
Microsoft Purview introduces this capability to centrally govern and manage on-premises
SQL Server in addition to other versions.
We go all the way back to SQL Server 2005 on the on-premises version as well as the cloud
deployments too.
It helps you with data cataloging, scanning, classification, setting up access policies,
etc.
It gives IT administrator or database administrator easy to use system to manage their environment.
The second part of Purview is role-based access control.
This helps with permissions management.
This has been a longstanding ask for providing more granular scope through policy-based management
where customers can set up policies for different users and roles for their SQL Server environments.
You can apply this at scale instead of having to run scripts individually at each server.
This works seamlessly across multiple instances where, let's say if you want to give temporary
access, for example, if you have hired a consultant on a short-term basis and you want to give
them temporary access to a production environment, you could easily do that through data read
policy and modify option is coming soon.
Or if you also have folks who want to do DevOps policies where you want to give them temporary
access to production environment for, let's say, performance monitoring or troubleshooting,
you could set up this policy through Purview without having to create specific logins and
permissions within SQL Server.
This works through the Azure Active Directory integration and this policy can push to multiple
instances of SQL Server from a single place.
It's also easy to revoke this policy once the time is elapsed.
It simplifies the entire security management from a single place.
That's the key innovation we are bringing to customers who are on SQL workloads.
AJ, we touched on Purview, but there are some big themes that we tried to address and we
did address in SQL Server 22.
What were those themes?
Yeah.
When we look at SQL Server 2022 capabilities, we have five major categories.
The first one, as I mentioned, is Cloud Connected.
This is where we want to bring customers the power of Azure to their SQL Server environment,
wherever they are running, whether it's in Azure or outside Azure.
As Cloud continues to innovate at a very fast pace, a lot of our customers, we talk to,
want to get the same innovations for their SQL Server environments.
With SQL Server 2022, you will see a lot of innovations that bring SQL Server closer to
Azure.
One of them is the managed DR using managed instance, where you can directly connect your
SQL Server instance to an Azure SQL managed instance.
Once the link is established, you will be able to use the managed instance as a disaster
recovery site for your SQL Server, or you can also offload your reporting workloads as
a read scale out thereby freeing up resources on SQL Server.
The third scenario where this helps us with migrations.
At some point, let's say you have established a link and you're using the managed instance
extensively and you decide to migrate to Azure, which all you need to do is initiate
a failover and it's now running primary on instance on your managed instance.
Multiple scenarios available through this feature.
The second big one is analytics.
With innovations and Synapse, customers are using this more and more.
How do we connect because operational data sitting in SQL Server with analytics running
in Synapse?
Synapse links helps with that.
Once you establish the sync, we will automatically synchronize the tables that you identify to
the landing zone in Synapse and then make it available in the Synapse workspace, which
then exposes you to do near real time analytics using Power BI directly from Synapse Studio.
Not only that, any changes that happen to the table on your on-premises SQL Server automatically
replicated to your Synapse environment, so you have the latest and greatest data available
quickly for you to do near real time analytics.
The second pillar is built-in query intelligence.
This is one of the biggest ask customers have is when they want to upgrade or modernize to
Azure and moving to a newer version, how do we make sure that their applications don't
have any performance issues?
End of day, customers care about performance as a key fundamental.
We have invested both in Query Store.
We have made it by default now because we have the confidence and learnings from Azure
SQL again where millions of databases have this turned on.
With the power of Query Store and the investments in Intelligent QP, we have addressed some
of the most common pain points either by just upgrading or whether you want to run on the
latest database compact level, you will be able to get the best features available.
Let me highlight a couple of them.
One is the parameter sensitive plan optimization.
Many customers today see this problem where you are running the same stored procedure
with a different set of parameters and each one fetches a different set of rows because
the data is queued.
The first user's plan is cached, so the second query will reuse the same plan which may not
be the optimal plan for that query.
For example, if the first one uses a seek and the second one needs a scan, you may have
undesirable impact.
This is called parameter sniffing problem.
With SQL 2022, we have addressed this where we can cast multiple plans for the same parameterized
queries.
So thereby, each query or each user gets the right version of the plan according to the
number of rows that it needs.
There are other similar features like the degree of parallelism feedback where customers have
a challenge on setting the right max DOP value, whether at the server level, database level,
or even at the query level.
So with this feedback mechanism, we are able to dynamically adjust the right DOP for your
query by reducing the DOP but yet trying to achieve the same performance using a learning
mechanism via belt leveraging the query store.
All these feedback features rely on query store where we persist all the knowledge we
gain as we run the queries.
So it also has an added benefit that if the server crashes or restarts or if your query
plan is evicted from cache, it can be retreat quickly from the query store so you don't
have to pay the cost of recompiling.
The third pillar I will talk about is scalability and availability.
As the data volume increases and the size of machines that customers run, SQL grows,
like very large memory systems, it's very important that we can scale in these environments.
So we have more than 25 plus features in the core engine which will help in this case.
One of the examples is parallel buffer pool scan where we can scan large memory footprints
where most of the memory is used by the buffer pool to scan the memory quickly and retrieve
the right data for the query.
On the availability side, to maintain the highest availability, we have invested in some
of the areas like contained availability groups which help you to include system databases
as part of your failover.
So thereby it's easiest administration and setup of your availability environment, HADR.
You don't have to separately set up master or MSDB then user, they can all combine them.
The other one is we partnered with Intel to introduce the quick assist technology for
backup compression, backup is a very CPU intensive task.
So by offloading this task to the secondary chip that Intel quick assist technology provides,
the SQL Server Core CPU is freed up for user workloads.
So these are some of the few examples of innovations in the availability and scalability pillar.
The next one is data virtualization.
This is actually a big one.
It started with SQL Server 2016 with PolyBase and we continue to expand this.
What we have realized is in today's world data is residing not just in a SQL Server database
but also in other non-relational formats like 4k files, delta files, CSV text files.
So how do we provide a way for customers to connect their SQL data with this data set
without having to do expensive ETL or moving the data into a database in a format that
SQL understands.
So data virtualization helps address that problem by expanding the PolyBase example.
We are also integrating with S3 API, which is an open source API.
This exposes to many more open source formats that are available in the industry.
And now customers can access data stored in Azure Data Lake or Blobstore or other S3 providers.
This technology can also be leveraged for backup restore with two URL with S3 API where
you can use this REST API for backing up your critical database assets.
And the last bucket of investments is the T-SQL.
T-SQL is the most popular language for SQL administrators.
We are expanding the T-SQL in the area of JSON for customers to operate with JSON type
data.
We have added several string data and windowing functions based on customer and community
feedback.
And then we are bringing a lot of these time series functions that were introduced in Azure
SQL Edge to SQL customers so they can operate on IoT or time series kind of data sets.
So this talked about a lot of features, but this in essence is some of the key pillars.
We have to continue to invest and grow in SQL server.
So the last area is security.
Security is also another core fundamental.
It's actually table stakes as customers have data that expands into multiple scenarios
and then the attack vectors increase.
Data is the most critical asset for any company.
So we continue to be an industry leader in the security space.
And I will let Michael highlight some of the investments here.
Just listening to you just then was really, really kind of interesting.
And I made this comment actually when we released SQL Server 2022 when I was at the keynote.
And it's funny because I'm obviously like the security nerd.
This is a big joke, right?
I'm the security nerd and a bunch of database engineers.
And so I made the comment saying, yeah, I kind of understand the words, but I'm not
sure about the sentences.
And yeah, there's still a lot that I have to learn about databases in general, but this
is great to see.
So yeah, security, there's a lot of big changes that we've made.
Yeah, so one of them we've already touched on and that's Microsoft Perview integration.
I want to quickly go through the list of items and I'll explain each in a bit more detail.
So the first one is Microsoft Defender for cloud integration, Perview we've talked about,
Ledger.
So we actually had Peter Van Hoeven here a few weeks ago talking about Ledger, very
cool technology.
As your Active Directory authentication, more importantly, client authentication.
Always encrypted with secure enclaves.
There's been some changes there as well.
Access control around permissions.
Access control server level roles.
Some changes to dynamic data masking.
Support for PFX.
There's actually a funny little story in there.
And then finally, actually one of my favorite changes is support for Microsoft TDS 8.0, which
is Tabular Data Stream 8.0.
And I'll talk about that because that has some really important TLS implications.
So let me start again at the very top and go through some of these things.
And AJ, if you have any comments to add to each of these, let me know.
All right, so the first one is Microsoft Defender for cloud integration.
So when everyone thinks of like Microsoft Defender for cloud, you think about, you know, just
Azure.
The actual fact, Defender for cloud doesn't just work for Azure.
It works for assets and, for example, AWS and GCP, but also works for on-prem items as
well.
So for example, SQL Server 2022.
So now we can actually use Microsoft Defender for cloud, you know, with your SQL Server databases
or SQL Server servers, I should say, on-prem or in VMs for that matter.
Microsoft Perview we already talked about.
So I'm not going to go through that in any more detail.
Another feature, Ledger for SQL Server.
This is, as I mentioned, we had Peter Van Hove on a few weeks ago, actually a few months
ago now, to go over Ledger.
And this is a way of providing cryptographic proof that data has not been tampered with.
Now, the most important part of that is that those hashes, which are actually called Merkle
trees, should be stored on something that is temper resistant.
So for example, you could use immutable storage and like as your storage account.
You could also use confidential ledger.
Now confidential ledger is not the same as Ledger for SQL Server.
Similar ideas, but a confidential ledger came out of the confidential computing team.
It's more of a general purpose ledger.
It's not just for databases.
We can store the Merkle trees in Ledger if needed.
Yeah.
Just to add on to your point, Michael, one of the value prop of bringing into SQL is the
simplicity of using this through SQL.
SQL is a very complex technology and it's a decentralized storage and interacting with
it.
It requires some programming skills.
Whereas with SQL ledger, you just enable it at the database level and then the engine
automatically takes care of it.
And then we have many views and functions to help customers to look at their ledger data
and then verify the integrity of the data and such.
So definitely looking forward to our customers using it for both auditing scenarios or use
cases where they have multi-party business processes where, for example, like a supply
chain management system where multiple organizations share the business process with each other,
but you want to make sure as the data flows, it's completely trusted and has not been tampered
with.
So it's a very good use case and we already have customers like Lenovo who have been using
this in Azure and now we're bringing the same experience to SQL Server.
Yeah.
Peter and I spoke at length, actually.
We were together at the past summit in Seattle and we spoke at length about Ledger and there's
some other aspects that aren't really being talked about with Ledger that much.
The first one is, okay, we throw around the word blockchain a lot, but I want people to
understand that it's actually not a blockchain technology.
It's actually not using signatures and blocks in the same way the blockchain does.
It's symmetric and the strength of its security comes from storing the hashes in immutable
storage.
The complete linchpin is storing it in immutable storage, but there is also a huge upside to
the way we have it implemented.
And as you mentioned, AJ, the fact that you can just create a table and go with Ledger
equals on and that's basically it.
That's the extent of configuration ultimately.
I mean, other than setting up a storage account, it's really that simple.
But the other aspect is that everything is symmetric.
So it's hashes as opposed to signatures and hashes are a lot more energy efficient than
signatures like substantially so.
So this is not just faster than blockchain.
It's also a lot more energy efficient than blockchain and that's a big deal, especially
if you're doing this thing, there's kind of stuff at scale.
Next one is Azure Active Directory authentication and this is for clients authentication.
You may think, well, hang on, don't we already have that?
Yeah, we do in Azure SQL Database and then Azure SQL MI, but not in SQL Server.
So you can actually have an on-prem system or a VM running SQL Server 2022 and you can
now authenticate your clients using Azure Active Directory.
You can also use things like managed identities if you're running in a VM.
You can do all those kinds of things.
So this is really cool.
This honestly from my perspective as a security guy, this is a huge, huge, huge, huge, huge
win.
I think this is a very important direction for our database products.
We really want to push people to using Azure Active Directory primarily because, one, the
client doesn't have to persist any kind of credential to connect to SQL Server.
That's number one.
Number two, you can start taking advantage of other defensive mechanisms that we have
in Azure Active Directory.
So for example, things like we can detect dodgy sign-ins enforcing credential policies,
those kinds of things.
So that's why you really need to be thinking about Azure Active Directory for client authentication
into SQL Server.
And Azure SQL Database, we still support on-prem, for example, UID and password.
But the problem is that password, you have to persist it somewhere and that's what the
attack is going to go after.
As with AAD authentication, there is no credential, right?
We don't persist it anywhere.
And if using like a managed identity in Azure, the credential is actually managed completely
by AAD.
So it's just better all around from a security posture perspective.
Next one is always encrypted with secure enclave, some small changes here.
We now support join, group, buy and order buy.
And we now support text columns using UTF-8 collations.
So historically, we didn't support UTF-8, turns out a lot of our customers are using
UTF-8 for collations in SQL Server.
So collations are just like the essentially what are you using to store your data so we
can do comparisons and orderings and so on because different alphabets have different
orderings.
And so collations help you with that.
Is that a fair definition, AJ, of collations or do you have a much better, more specific
example of what a collation really is?
Yeah, collation actually helps SQL Server engine understand the data in the table.
It helps influence query results.
If your data is following this collation, what sort order I need to use because if you
have a case-sensitive collation, as an example, you need to make sure when you're on the query
and you have an order buy, it follows the rules of a case-sensitive collation.
Or if it's a case-insensitive collation, then it doesn't matter.
So it's basically a way for operating on string data.
So I wasn't completely wrong then.
That's good.
So yeah, now we support UTF-8 collations and there's also been some performance improvements
as well and always encrypted.
This is always encrypted with secure enclaves.
The second two areas are somewhat related.
They're both to do with access control.
The first is we have some new granular permissions that help improve principle release privilege.
Look, I'm going to be honest with you.
There's a lot of privileges and roles in SQL Server that are not granular and you end
up granting someone a particular permission.
The next thing, they got access to stuff beyond what they really should have access to.
So we spent quite a bit of time trying to reduce the scope of some of those permissions.
Again, this has been a lot of work done by Andreas Walter and he's writing a lot of information
about some of these new permissions.
We also in the area of access control have new server-level roles.
These are, again, to help with principle release privilege.
So these are roles that provide very, very specific roles, funny enough, rather than
having a grab bag of roles that a user might have.
So again, it's all about granting people or entities specifically what they need access
to, but this is at a server level.
Next one is around dynamic data masking.
I'm going to start off by saying that dynamic data masking really isn't security, but if
you've got to pick a bucket, this will be the bucket it would go into.
It's all about just masking data, right?
So for example, social security numbers only show, like say for example, the last four
digits or something like that.
We now have much more granular unmasked permissions.
So you can set permissions like at the schema level all the way down to individual tables
and obviously columns as well.
But this is really great to see as well.
The second to last one is support for PFX.
Okay, so there's actually a really interesting story about behind this.
Ajay, I think you heard the story, right, about how I got involved in this, the PFX
stuff.
Do you hear about that?
Yep, the previous person left the company and you had to take over.
Yeah, so it came down to the wire, right?
I'm getting really, really close to shipping SQL Server 2022.
And one of the things you have to get done is support has to sign off on all the new
features because they have to be able to support it.
It makes absolute sense, right?
If they can't support it, then we can't ship it.
Well, it turns out the person who had worked on this originally had left the company and
so there was no support documentation for PFX support.
But I'll explain what PFX is in a minute.
And so my manager came to me and he says, do you know PFX?
I'm like, yeah, I know PFX.
He said, well, do you mind writing the support documentation for SQL Server 2022 so that
we can ship?
I'm like, yeah, I guess.
So I knew PFX from, so jokingly, I said to him, I said, it's funny, you should bring
that up because, yeah, I worked on PFX because I was in IIS, right, in our web server.
Of course I know PFX because we support the ability to import and export certificates
and private keys, but that was 20 years ago.
So congratulations on finally supporting in SQL Server 2022.
So what PFX is, it's just an industry standard format for, it's called PKCS 12 as well, is
just a way of exporting and importing certificates and private keys and potentially certificate
hierarchies and private keys in an encrypted blob.
That's basically all of this.
Don't get me wrong, you may think, oh man, does SQL Server not support exporting private
keys in an encrypted form?
Yeah, it always has, but it was a Windows format, not an industry standard format, it's just
something that's been around in Windows forever.
So we now support PFX.
So now that you can export and import using PFX files instead.
Great to see customers that need this are happy that it's finally there, but yeah, so we now
support PFX.
The last one, which is actually probably my favorite of all the features that we've added
to SQL Server 2022 is improved TDS support.
So this is TDS, Tabular DataStream, and we now support version eight.
Now, here's what's really interesting about TDS 8.0.
If you use TDS 8.0, you will have an encrypted channel.
We will use TLS.
There is no, you can't turn that off.
If you're going to opt in for using TDS 8.0, then the channel will use TLS.
That can't be turned off.
Now the other major change that we made here, which I think is incredibly important is that
historically TDS and TLS or whatever was used to protect the channel and provide server
authentication were intermingled.
Now we have broken them apart.
So TDS 8.0 and TLS is completely separate.
This is really important because it allows us to easily upgrade the TLS protocol versions
without having to jump through weird and funky hoops.
That's why like today, the older version of SQL Server can't use TLS 1.3.
They just can't.
Now we can.
So SQL Server 2022 does support TLS 1.3, but only when you're using TDS 8.0.
The other nice thing is that it allows, say, a firewall to do packet inspection because
you could actually terminate the TLS connection at the firewall and then do TDS packet inspection
at that point.
So one of the best sort of analogy is think of HTTPS.
It's very, very similar.
What HTTP is to TLS or those two used together, so HTTPS, you can think TDS is very, very
similar to HTTP in this case.
So it's TDS writing over TLS.
So this is great to see.
It allows us to use TLS 1.3 and it also allows us to upgrade to newer protocol versions with
ease relative to prior to TDS 8.
So those are the major features.
Again, I think my favorite is probably the TDS 8 because if you turn on TDS 8, then the
channel is protected.
There's no unprotected channel.
What would be next?
I don't know.
Peter will probably have me say ledger.
I actually really like ledger.
I like ledger a lot because it's just really, really simple.
Verification is simple.
It's easy to use.
It's part of the product.
You don't pay extra for it.
It solves a real world problem, which is great to see.
Do you have any particular favorites in there, AJ?
On the security side?
Well, let's start on the security side and let's just see if you can pick something
out from the other grab bag of stuff I don't understand.
Yeah.
I think on the security side, Purview is interesting because Purview is actually helping
customers manage not just the newer SQL Server version, but it also centrally helps you manage
your entire SQL estate, whether you're on Cloud or on-premises.
On the other side, I really like looking forward to the customers using our innovations
in the Cloud through these Cloud capabilities, both the MI link and Synapse link.
These are some of my favorites.
All right, well, let's bring this episode to a close.
AJ, thanks for joining us this week.
One thing we always ask our guests is if you had just one thought to leave our listeners
with, what would it be?
The thought is we are continuously innovating.
Database SQL Server is one of the most widely used databases in the planet and customers
are learning to adapt to the changing demands of the industry and we are keeping up with
that.
We continue to innovate and lead the industry in many aspects through these innovations.
SQL Server is very much alive and look forward to many more innovations in the upcoming years,
whether through our Cloud options or through on-premises products.
Do provide us the feedback if you have any thoughts or questions.
All right, well, again, AJ, thank you for joining us this week.
I always learn something on each podcast, but it's kind of crazy learning stuff about
the product that you're actually working on.
But anyway, again, I'm quite happy to take on the mantle of being the security nerd amongst
the database engineers.
I'm fine with that.
So, again, thanks for joining us this week.
And to all our listeners out there, we hope you enjoyed this episode.
This is probably going to be the last episode of the year.
So wishing everybody a safe break 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.
Music is from ccmixter.com and licensed under the Creative Commons license.