Azure active directory group authentication with SQL 2022?

Question

Post reply

Azure active directory group authentication with SQL 2022?

tommiwan

Mr or Mrs. 500

Points: 593
More actions
June 13, 2023 at 2:28 pm

#4210325

I think this may be a simple answer, but wanted to confirm.... I have set up azure active directory authentication with SQL 2022 which is running on an azure SQL VM. Can I now use Azure active directory groups to authenticate users to SQL? I tried adding an existing group and received an error, but maybe I just have my syntax wrong?

CREATE LOGIN [AADgroupname@domain.com] FROM EXTERNAL PROVIDER
Msg 33130, Level 16, State 1, Line 1
Principal 'AADgroupname@domain.com' could not be found or this principal type is not supported.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

did you also try to use just the group name (without the domain information) ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 2

Thanks for this idea.... I had not tried it without the domain. After some quick testing it appears to be working.

I need to put a bit more time in to testing it. I added a new user to the AAD group and they are not yet able to get in to SQL. I need to see if there is some sort of a delay or if it's something we're doing wrong.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

did you do the steps highlighted at https://cloudblogs.microsoft.com/sqlserver/2022/07/28/azure-active-directory-authentication-for-sql-server-2022/

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 4

I have this fully working now. Thanks to Johan for his suggestion. The other issue that was causing problems was that we have 2 subscriptions in azure. One is for PROD and one is for Non Prod. I thought that both of those subscriptions were connected to the same azure AD but they are not, so I was creating/modifying the group in the wrong subscription.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

Thank you for the feedback.

As always, checking and double checking, stepping back to get a broader view what's going on and then working back to the issue is key!