ADFS and Azure SQL Managed Instance

[sysnickm]

The ADFS docs aren't very clear on this, there are some docs for ADFS 2016 that say Azure SQL isn't supported, but I think those docs are only talking about the Azure SQL that use the DTU models and not the newer options like Managed Instance.

Is there any guidance around using a SQL Managed Instance for the ADFS configuration database? Has anybody tried this just to test it out?

[#2]

@sysnickm 

We're actually coming up to this soon and I'm interested to see the article you found where it's stated as being unsupported as I didn't find any articles that even mentioned the Azure SQL platforms, let alone rule them supported or unsupported.

I expect this is the usual Microsoft scenario of it being unsupported only because they haven't bothered testing it against Azure SQL MI.

But as to whether it's possible, yes, it is.

In addition to setting up the SQL MI, you will also need to establish native Kerberos support as described below in the overview. The two articles after the overview are related to the actual steps required to configure the trust-based model (the "modern" model is of zero use to services like AD FS that run under a non-interactive process.)

• What is Windows Authentication for Azure Active Directory principals on Azure SQL Managed Instance? ...
• How to set up Windows Authentication for Azure Active Directory with the incoming trust-based flow -...
• Configure Azure SQL Managed Instance for Windows Authentication for Azure Active Directory - Azure S...

We have just done this for two instances of Microsoft Identity Manager and it works just fine.

One critical difference with SQL MI from the other Azure SQL platforms is that MI uses the DNS servers specified on your vNet resource - if you've defined any there. So, it's critical that your internal DNS health is good. This is particularly true if - like us - you deployed SQL MI securely by disabling the public access endpoint and deploying an additional private endpoint (you do get an initial one behind the scenes but it's not manageable from what I've seen.)

Additionally, you're going to need to ensure that the AD FS service across all cluster members is an Active Directory domain user account, and that that account is being synchronised via AAD Connect out into Azure - it needs to exist in both AD and AAD.

We now have both Identity Manager instances happily talking to SQL MI, with AD FS and AAD Connect (which does come with an unsupported status, however, the benefit outweighs the risk) soon to follow.

One thing to be careful of with SQL MI is storage performance. This isn't a significant factor for AD FS's SQL workload but it's worth mentioning all the same.

Storage performance with SQL MI is dictated by the storage size chosen when establishing the MI (though this can be changed later.)

• Resource limits - Azure SQL Managed Instance | Microsoft Learn

So, while AD FS requires almost no space, SQL MI is the kind of thing that only makes financial sense (it's very expensive, as all IaaS services are) once you consolidate many databases onto it, in which case you could run into throughput issues quickly on the entry tier as more databases are added.

Cheers,

Lain

[#3]

@LainRobertson 

Thank you for the detailed response!

Here is the link to doc that states that it isn't supported: https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/overview/ad-fs-requirements

This line is buried in there: "Unfortunately, SQL Azure is not supported for the AD FS configuration database."

Right now our team is looking at options, and the option they are leaning towards is setting up a pair of VMs and running SQL Server in an always on configuration. I've not done the numbers, but I think moving to MI might be better in the long run.

[#4]

@sysnickm 

Ah, okay. It's under the hardware requirements, where I had checked that article but only under the "configuration database requirements", where - ironically - it doesn't get a mention:

• AD FS 2016 Requirements | Microsoft Learn

AD FS is and always has been the most basic of SQL clients, so this won't stop us. Given it works on SQL 2008 and above, the only real barrier to Azure SQL MI is the authentication side, which we've successfully navigated using the SQL MI Windows authentication trust-flow model.

The irony with Azure SQL MI is that its specific focus is being able to lift-and-shift more on-prem SQL workloads, yet when it comes to many Windows Server roles and wider Microsoft platforms, there's a massive hole in their testing and support for their own direction.

You'll find yourself doing a good amount of this kind of cost/benefit analysis of your own, as if you wait for the product teams to get it together, do the testing and issue a support statement, you'll still be running on-prem services for years to come (which doesn't bother me as I'm a hybrid guy, but it's out of step with Microsoft's constant sales pitches to move everything to the cloud.)

Edited to add:

Running two IaaS Azure guests which in turn run traditional SQL in an AlwaysOn configuration does have some technical merit, not the least of which are that it's easier to set up and is fully-supported.

That said, the total cost of both guests plus relevant SQL Server licencing can quickly end up being the more expensive path depending on how many databases you can consolidate on SQL MI.

Cheers,

Lain