SQL Server AAD Security Group Admin

Question

SQL Server AAD Security Group Admin

Alex Ip 196

I'm having trouble understanding how to login with SMSS when I created an AAD security group as the AAD admin.

I get this error when I try to login with a user and password for a user in that security group, I also tried changing the database but I still get an error.

Accepted answer

0 additional answers

Your answer

Answer 1

Oury Ba-MSFT 21,016 Microsoft Employee Moderator

Hi @Alex Ip Thank you for posting your question. – it usually occurs when tenant is not reachable or SQL server does not have AAD Admin setup. I was able to reproduce the issue from my end.
Please check the below

Did you set allow Azure Services and resources to access this server
Add client IP

Please let me know if that does not help

Regards,
Oury

Alex Ip 196 Reputation points

2021-08-11T18:36:57.617+00:00

Yes I can confirm I added a firewall entry for my machine.

To add to the previous question, I was able to login with the SQL Admin /w password (not AAD), but I'm stumped as to how the AAD Admin works when the admin is a Security Group.
Oury Ba-MSFT 21,016 Reputation points Microsoft Employee Moderator

2021-08-11T21:37:12.49+00:00

Hi @Alex Ip Is the user part of the Azure active directory tenant ?
Could you also check if the security group you have added is listed in SSMS ( login to SSMS with SQL server admin and run this query from master db SELECT * FROM SYS.DATABASE_PRINCIPALS

Regards,
Oury
Alex Ip 196 Reputation points

2021-08-11T22:29:02.467+00:00

Yes, the user in the group is part of the AAD tenant.

I also checked the master db, it looks like it is there on row 9.
Oury Ba-MSFT 21,016 Reputation points Microsoft Employee Moderator

2021-08-12T06:35:53.75+00:00
Hi @Alex Ip Thanks for providing the information. Our product group have tested the same and it worked just fine . It may be an environment specific issue. Please check the below if that does not work then I will suggest to open a support ticket where detailed troubleshooting can be done to assist you further.

Check if you are using correct UPN( Please do not share it here)

Check if current version of SSMS is (18.9.2 is the latest)

Check if you are using the “Azure Active Directory – Universal with MFA” option in SSMS and username should be the complete UPN

Regards,
Oury
Oury Ba-MSFT 21,016 Reputation points Microsoft Employee Moderator

2021-08-12T23:28:28.86+00:00

Hi @Alex Ip Were you able to check the above ? Is the issue resolved?

Regards,
Oury

Share via

SQL Server AAD Security Group Admin

0 additional answers

Your answer