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 54.
This week is myself, Michael.
I'm actually here by myself with our guest.
We're trying a little bit of an experiment here.
So for those of you who don't know,
a few weeks ago, I actually moved into a new team.
I moved out of Microsoft Services,
I was working mainly with our customers on
securing their Azure workloads,
and I moved into the Azure Database Security team.
Fantastic move for me.
I've always had a love for engineering at the back end,
and this is just an opportunity I just could not turn up.
By the way, there's no coincidence there when Thomas Weiss was on.
He and I had a few discussions in the green room,
so to speak before we were recording,
and one thing led to another,
and next thing I'm working with Thomas.
So anyway, this is a little bit of an experiment as I mentioned.
Well, I think we're toying with the idea of having many episodes,
where we focus on one little feature,
or well, not necessarily a little feature,
but one specific feature of a specific product.
This week, we're going to look at
SQL Managed Instance and authentication.
So with that, let me introduce my guest.
This week, we have Sravani Saluru,
again, who's here to talk to us about SQL MI and authentication.
Sravani, thank you for joining us this week.
Would you mind taking a moment and just give our listeners
a little bit of a background?
Sure. Thanks, Michael. Hi, everyone.
I'm Sravani. I have been working with Microsoft for the past 10 years,
and initially worked as a support engineer supporting SQL Server on-prem customers,
as well as the cloud customers,
and recently moved to the database platform security team,
which is part of the SQL Server product group.
I'm currently working as a program manager and owning few features in security portfolio.
One of the features that I own is SQL Auditing for Azure Database, MI, and SQL Server.
Also today, there is one more feature which we are going to talk about that is currently in
public preview and I'm the owner of the feature.
So that's Windows authentication for SQL MI.
Also, I have quite experience in SQL Server in terms of performance,
high availability, and security.
I did some work in high availability area as well.
So let's just get stuck right into it.
So SQL managed instance.
There's a whole bunch of different members of the SQL family.
Can you explain briefly where SQL MI fits and what its role is compared to all the other versions of SQL?
So I think SQL itself is very well known in the database world and it's spread across.
You name a platform and SQL exists there.
So the most important of us are SQL Server,
which works on both Windows and Linux.
And this is the native SQL Server, the on-premise version,
which exists for several years now.
And today, SQL 2022 is going to be released soon.
So that is our on-premise story and we have a lot of enterprise customers who are using
SQL 2022 as their top database servers today.
Now coming to the cloud, as we all are aware, in cloud we offer infrastructure as a service
and platform as a service.
So SQL exists in both places.
So in infrastructure as a service, you can have your SQL servers,
the native SQL servers running on IAS, basically either the Windows VMs or even the Linux VMs.
So that is the infrastructure as a service.
And coming to the past, most of you are aware we use SQL, the Azure Database,
which is the platform as a service offering for SQL Database,
and which comes in SQL Database as well as the Elastic Pools,
where you can have a group of databases as part of your SQL Azure Database.
So that is one of the offerings in the past.
The other one, which is basically the one which we are going to talk today,
is the SQL Managed instance.
As the name itself indicates, it's basically a SQL Server which is managed in the cloud.
So most of our customers wanted something which is very,
very close to their on-prem servers because when it comes to the cloud Azure Database,
that is there are certain features which are server-scoped features like, for example,
CLR or Service Procure or machine learning.
The SQL agent, per se, many of our customers run a lot of jobs.
And for Azure Database, we do not have a SQL agent today.
So they still want to manage their SQL Server in cloud, but at the same time,
they want to be as close as their on-prem servers.
So that is where your SQL Managed instance comes into the picture.
So it is very close, compatible with your SQL Server.
It gets you all server-level-scoped features that you want.
At the same time, the maintenance is completely done by Microsoft,
where the high availability point in Tamry store, everything is offered just the way it is needed
and absolutely zero work from the customer side.
And the best part of the SQL Managed instance is you just have an online migration.
You do not have to do anything.
We use log shipping in the back end and you can just do a switchover whenever you are ready to migrate to cloud.
So that is the main beauty of the Managed instance.
You know, it is funny, every once in a while, someone says something where the penny just drops,
where you realize why something is the way it is.
And you said that there are some features that are server-scoped.
And that made so much sense to me as soon as you said that.
Because I was always wondering, why does something not exist in Azure SQL DB?
And the reason is because some of those features are server-scoped.
Server-scoped. That is good.
Right. So for example, the CLR, as you mentioned, right?
The Common Language Runtime Support where you can write back-end code and say C-sharp.
Yeah, that made absolute sense when you said that.
Actually, the other funny thing is the first version of SQL Server I actually used back in the day,
and I am really showing my age here, is SQL Server 4.2 running on LAN man.
That is even before 2007.0, right?
Hey, so here is a question for you then.
Here is a question for you.
Do you remember or do you know what the sample database was?
What the sample database was back then?
No, don't ask me.
It was the authors.
Remember, it was the authors database.
I know. I heard those stories.
Yeah, yeah. Absolutely.
I heard those stories.
Okay, now you are making me feel really, really old.
My grandpa told me this story about the authors database.
Now, I am only kidding.
No, that is fantastic.
That is actually really good.
I mean, and I think you really put things in perspective there.
So I think one of the really nice things about SQL MI,
or perhaps two of the really nice things about SQL MI,
one, it has a really high degree of isolation, right?
I mean, when you have a managed instance, that's your box.
I mean, that is your instance.
That is not shared with any other compute.
Is that correct?
Yes, that's correct.
Yeah. So for some customers who absolutely categorically
and emphatically require a massive degree of isolation,
then this is certainly an option.
And then the other one is,
whether it being a database product that's very, very close to on-prem
without being on-prem, in other words, we do the management.
Remember, because it all shared responsibility, right?
And so what we've got here is a SQL Server instance
that is managed by us.
Like we do it, we take care of patching and all the sort of good stuff.
But you obviously control, you the customer controls,
the databases and user accounts and so on.
But because it's so close to SQL Server,
it's one of the easiest to use for lift and shift scenarios.
Is that correct?
Yes, that's good.
Okay. All right.
So we're mainly here to talk about authentication options.
So before we talk about SQL MI specifically
with the new Windows authentication stuff that's in preview,
why don't we just take a quick whirlwind tour
about the around the basic authentication options
that we have in the SQL products?
Sure. So the native SQL Server supports
Windows authentication and the mixed authentication, right?
So the Windows authentication is basically for the domain users.
And the mixed authentication is you can have either domain users
or a SQL Server lock-ins,
which you can create inside SQL Server
and use them to authenticate the users.
So that is how the SQL Server has been supporting for several years.
And then with SQL 2022,
we also started supporting Azure Active Directory users as well.
So that is with the SQL Server story.
And when it comes to Azure SQL Database,
we offer Azure Active Directory authentication,
where you can have authentication using Password or using MFA,
the multi-factor authentication,
or you can also use Azure Active Directory integrated authentication.
So one of the challenges that we have seen
when customers wants to migrate to Azure SQL Database
is they might have a legacy applications,
which may not be able to use this Federation providers
that they need for this Azure Active Directory integrated authentication to work.
So that could be one of the blocker for many applications to migrate.
If you have a legacy driver,
which may not support this Azure Active Directory authentication.
So that is when we have this Windows authentication for Azure AD users
for SQL Managed Instance.
This feature is in public preview.
Currently, and in few months, it will be GA.
So with this, we are going to unblock all the migrations
which are blocked because they could not migrate
because of the legacy drivers or the legacy applications
and the way the applications are designed.
They cannot use, they can only use Windows authentication,
and that support is not available today.
So that is where this Windows authentication for SQL MI comes into the picture.
And this is going to unblock many of our customers.
This is important, right?
I mean, if we say that we've got these lift and shift scenarios,
authentication is obviously critical.
And you don't want things to start breaking
because you move this thing into the cloud
and all of a sudden your authentication just breaks.
Yeah.
So this is obviously a critically important feature that is coming in SQL MI.
So could you explain just sort of briefly kind of how it works
and what are the machinery is required to make it work successfully?
Absolutely.
So I will just talk about this feature,
like how it design and how it's going to work.
So first of all, by enabling this Windows authentication
for Azure Active Directory principles,
customers can migrate to Azure Managed Instance
without implementing any changes in their application, right?
So they just have to do this initial setup
for the authentication flow to work.
And once that is done from the application side,
they don't have to make any changes.
So how it works basically is we use this Kerberos authentication workflow
in the backend.
The main prerequisite here is customers have to ensure
that their Windows principles,
the Active Directory principles are synchronized
with the Azure Active Directory,
which is in today's world,
we have seen customers are already doing that
because they are, along with modernizing their applications,
they are also modernizing their identity.
And most of our customers already have
like a hybrid Active Directory domains running in their environments.
So this is a prerequisite where they have to ensure
the principles are synchronized between their Active Directory
as well as the Azure Active Directory.
Once that is done, we have to establish this Kerberos authentication flow
with the Azure Active Directory.
For this to work, we support all versions of Windows,
but like there are two ways to establish this Kerberos authentication.
So if you are using, depending on your client mission,
for example, you are using Windows 2012 or other versions
where you do not have a latest OS version
where you can use this modern interactive workflow,
for those servers, we can just go ahead
and use an incoming trust-based authentication.
I think this is something that, at least the SQL server customers
hard for several years, right?
When you have to connect across domains,
you have to establish this incoming trust,
and basically you are going to use Kerberos authentication protocol
in the backend to make this connection
since both the domains are trusted.
So this is not something that is new,
but just that it's now implemented for the Azure Active Directory.
So the customer's domain and the Azure AD are now in trust with each other.
So you can make this Kerberos authentication to work,
and through which the Windows authentication happens.
So this is for the clients which are using Windows 2012.
So all they have to do is establish this incoming trust
between Azure Active Directory and their domain services.
So with the incoming trust-based workflow,
the prerequisite is just your client must be running
on a Windows Server 2012 or higher version.
And second thing is it has to be a domain-joined machine.
It should have access to the Active Directory
because when the Kerberos token has to be issued,
it has to connect to the local directory to check for the experience,
the service principal name,
and then get the Kerberos token and send it to the Azure Active Directory.
So to only two conditions,
the client must be running on Windows 2012 or above.
The second one is it should have access to their local Active Directory domain.
Once this two is done, all you have to do is establish this incoming trust
using the Kerberos KDC server,
that is the Kerberos distribution center.
So for that, we already provide a PowerShell commandlet.
So customers just have to install the PowerShell commandlet.
And once the module is installed,
just to use their domain name past the credentials,
and then create the trust object for the Azure Active Directory domains
in their local Active Directory.
So that is the first workflow which works.
You through incoming trust based.
And there is another workflow for which your machine need not to even join a domain.
In this scenario, if it is a Azure AD joined machine,
so if you have a VM which is a Azure AD joined machine,
then you do not have to even establish any incoming trust.
It works through modern interactive flow.
So for this modern interactive flow to work,
your Windows version must be either see 2022 server
or it's Windows 10, 20 H1 and about.
So if you have a client which is running on this Windows version,
and if it's Azure joined, the Azure AD joined or a hybrid joined machine,
you do not have to do anything.
It's already knows about the modern interactive workflow
since it's already joined to Azure AD.
All you have to do is enable this Kerberos token during the login.
So there is a group policy that you have to configure on the client machine
in your local policies.
So you just have to go to the sec poll and update this local policy
which tells you that use Kerberos token during the login process.
So you just enable this setting and you just have to connect to the managed instance
using the Windows authentication.
So in this scenario, since this is a modern interactive workflow,
it works only for the applications which has the interactive connection method.
So if I need to give examples like maybe a SQL server management studio
or a SQL server profiler or an extended event where you can explicitly specify
and interact while connecting to the service.
And it will also work with applications like which uses impersonation.
However, this may not work for certain applications which will run as a service
because since it's configured as a service, there is no interactive method of login during the login.
So if you have applications which are running as service,
in that scenario, you still have to establish incoming trust
and then use the incoming trust based authentication model.
So with these two in place, either modern interactive flow or with the incoming trust,
we pretty much cover all kinds of clients that are running either joined active directory,
a domain join server or an Azure AD joined machine or just a hybrid machine
or any of these clients, right?
And you can connect to the managed instance.
So the second aspect is to allow managed instance to understand
the Windows authentication.
We have to enable this SPNs, which is quite familiar to all SQL server customers
because the service principle names are pretty known, right?
If the SPN is not correct, you know, you have seen scenarios where the authentication just fails.
So for managed instance to understand this Windows authentication,
we have to enable this service principle which is available in Azure portal itself.
So you do not have to do anything.
Let's say you want to configure Windows authentication for your managed instance.
All you have to do is go to your Azure portal, go to the managed instance,
and in managed instance in the principles, you can just enable the service principle for the managed instance.
Once the service principle is enabled, you also have to provide an admin consent for managed instance
so that it can allow applications to communicate with the managed instance.
So to provide the admin consent, you just have to go to the Azure Active Directory in portal
and then search for your managed instance and provide admin consent.
Again, this can be also done in the portal itself.
It just takes couple of minutes to do both the steps that I described
and they are documented in our public documentation with these screenshots.
So it's easy for you to access.
Again, just to summarize, there are two aspects.
One, the configuration that needs to be done at the SQL MI
where you need to enable the service principle by setting the service principles to on
and provide the admin consent for SQL MI in your Azure Active Directory.
So these are the two steps from the MI side.
Once that is done, on the client side for the Kerberos to work,
based on the client version, if it is Windows 2012 or above, you will do an incoming trust.
For the incoming trust to work, all you need to do is create the Kerberos tokens.
We have a partial module which you can use, provide the domain credentials to connect your domain
and provide your Azure Active Directory domain and it just creates the required Kerberos for you.
And the other way is just to use the modern interactive flow. If you have machines which are running on
Windows 10, 20 H1 or Windows Server 20, 22 and above,
you just have to set the local policy to enable Kerberos token during the lock-in.
Once that is done, if the machines are joined to Azure AD or if they are hybrid joined,
it just establishes the connection.
So once you do both the settings, you can test the connection either using an SSMS
or any client tool that connects to the SQL Server.
So I have one point, one question and a comment.
So the first one is when we are talking Windows authentication, we are just talking Kerberos here, right?
Yes.
Okay, because just for those who are not aware, technically there is no real such thing as Windows authentication.
It is kind of two things. It is either Kerberos or the classic local accounts, the SAM account,
which is like domain slash name. So we are talking Kerberos.
Now, one of the most important things that people should be aware of is with Kerberos,
Kerberos provides mutual authentication, right?
So it is authenticating both ends of the communication.
And the key thing there is I think all the service principal name.
And the service principal name is things like the machine name,
like the IP address or the DNS name, the identity under which it is executing,
and also the protocol import number.
And that is really important so that way Kerberos can actually authenticate the endpoint.
So I just want to make sure everyone is aware when we are throwing the service principal name thing out there,
what we are talking about.
It is just this unique identifier that identifies the endpoint.
And you said something really interesting there, which is that the service principal name must be registered in as your active directory.
And the moment you said that, again, that is when the penny drops, right?
That is the key part.
If you don't have the SPN registered in AAD, there is no way AAD is going to know,
or Kerberos is going to know what the endpoint is.
It is not going to be as authenticating the endpoint.
Yes, that is critically important.
So another question is, does this require things like, let's say ExpressRoute.
Is that required to make this work?
For this feature to work, you do not need any ExpressRoute.
It just works through any connection.
Right.
That is good to know.
I mean, a lot of things, some services that we have that provide this sort of functionality may require things like ExpressRoute.
But that is great to see that we do not need that.
So what are some of the major business benefits of all of this?
I mean, one thing that I see is just unblocking some migrations to Azure, right?
That is one thing I see as a major issue, or a major benefit.
Is that a fair comment?
And what other scenarios do you see as being valid here?
Yeah, so that is a major, I mean, that is a top scenario that we are targeting today to unblock our customers to migrate to Azure.
All the legacy applications which could be running on IIS with AppPools, right?
With identity and run as a service.
So we want to unblock those customers who don't have access to Azure,
for Federation services, or they might be using some legacy drivers, things like that.
So this feature we are targeting to migrate as many as customers as possible to MI.
And the second part is ultimately, like I said, since we have SQL MI pretty much close to your on-prem servers,
you can use all the client tools that you use to use in SQL Server, like SQL Profiler, extended events.
All those client tools are going to just work fine with this Windows authentication.
So that is another benefit that we are seeing today.
Now, this is really cool stuff, I think.
Again, for those who want to move workloads quickly to Azure with minimal downtime, minimal effort, minimal re-engineering,
SQL MI can be a valid option.
I think for Greenfield, I think a lot of people will look at, say, Azure SQL DB,
for migration, and this makes absolute sense.
By the way, something people should be aware of if they are not already aware of this.
When you spin up a version of SQL MI, it can take hours, just so you know.
I mean, we are literally allocating your own hardware for you.
So don't be surprised if you can't just spin one up in 30 seconds and then come back.
It is not going to be there, right?
We are allocating system resources at the back end, so it is a little bit different.
Again, the reason for that is it is literally your own isolated environment,
and that is what we are spinning up at the back end.
All right, I think that is anything else you want to mention?
If not, do you have a final thought you would like to leave our listeners with?
About the final thoughts, I mean, I have been into, I mean, I worked as a SQL administrator for several years in my experience.
Of course, I am not competing with Michael for sure.
I just realized how old Michael is, I mean, he was working from SQL 4.0, so he surely knows better than me.
But I myself worked as an administrator, and I know like at least 70% of the work goes into this administration part, right?
But today, with SQL in-pass, where we have this platform as a service, you have everything that you need.
You have Azure Database, you have Elastic Ports, you have Hyperscale,
and you have this managed instance.
So, if you are still not migrated to Azure, I will just leave it to you to think what is that one point that is blocking you not to migrate to cloud?
Because today, the cloud services in Azure provides you the topmost security,
provides you the topmost availability point in time recovery, what not?
Everything that you need for a database server.
So, if someone comes to me and tells me that, you know, what from tomorrow you don't have to worry about your administrative stuff,
just go and work on your applications, that's going to be like the great thing to me, right?
So, just think about it.
And if you have any blockers, you think that this is the blocker today for us to not to move to the pass,
either to database, Azure Database, or to managed instance, just let us know your thoughts, right?
Just put things in perspective. You know, you've got SQL Server on-prem, you've got SQL Server in VM,
but you've got to manage absolutely everything.
Then you've got SQL MI, where the back end, like the anti-malware, the updates and so on, that's all handled by us.
And then you've got SQL, Azure SQL DB, which is the straight up pass solution, where we manage even more of the back end.
But yeah, you're right. You really want people to move to platform as a service as much as possible,
because more of it is handled by Microsoft, right?
It's handled by the Azure infrastructure and by the people at the back end, which is always beneficial.
Okay, well with that, let's bring this episode to an end.
Give us your feedback as well, if you like this idea of just sort of running relatively short sessions with no news,
just focusing on a specific product or a specific part of a product from a security perspective.
And let us know what topics you'd like to learn about.
So with that, let's bring this episode to an end.
So Varni again, thank you so much for joining us this week.
And to our listeners out there, thank you for listening.
Take care and we'll see you next time.