SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,865 questions
Using AAD login on SQL contained availability groups
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(144, 25%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EE%3C/text%3E%3C/svg%3E)
EvHulsel
0
Reputation points
I've been looking into SQL 2022's new feature of logging in on SQL servers with Entra ID accounts and got it working on the SQL server nodes in a cluster. I can't figure out if/how it can be done on instances using contained availability groups. As those have their own security settings I'm fairly sure this can't be done, but can't really find anything official on the matter.
When I try to add an account it says the "CREATE LOGIN FROM EXTERNAL PROVIDER command is not supported as Azure Active Directory is not configured fot this instance." Azure obviously doesn't know of its existence: it's not an SQL virtual machine and thus can't be configured for Microsoft Entra authentication.
Or am I missing something?
{count} votes
-
Erland Sommarskog 111.8K Reputation points MVP2024-09-24T21:42:08.7766667+00:00 Have you Arc-enabled the machine?
I played with this last year, and I believe that was I able to get Entra ID login to work also on my contained AG. But when I look in the contained master, I don't see any logins of type X; they are only in the instance-level master. So maybe my recollection is wrong.
The Entra ID admin is not actually added to master, it is picked up from the registry, so in theory they could do this for a contained AG as well.
-
EvHulsel 0 Reputation points
2024-09-25T08:09:17.74+00:00 Thanks for your reply, Erland.
I failed to mention the SQL servers are virtuals hosted on Azure and have the IaaS agent extention installed. After some further investigation it appears the extension only supports a single instance if there are multiple instances, in this case the default instance. I'm now confident Entra authentication on those instances isn't going to work anytime soon.
-
Erland Sommarskog 111.8K Reputation points MVP
2024-09-25T21:03:49.75+00:00 OK, I have not any testing on Azure VMs; My playing was with VMs at home. But it's quite a bummer if you can't have Entra ID on more than one instance.
I tried at little more, and I was able to log on to the instance with my Entra ID Admin to my contained AG. I tried to add another Entra ID, but this failed with some certificate error that I don't care to resolve right now. Nevertheless, I take this as that you can have Entra auth on a contained AG as such.
Sign in to comment
Sign in to answer