Adding security groups (not AAD, but AD groups) to Azure SQL

Question

Adding security groups (not AAD, but AD groups) to Azure SQL

WebTechie

SSChampion

Points: 11405
More actions
March 2, 2022 at 4:47 pm

#3991779

Hello,
I've added users to my Azure SQL Database by create a login and then creating the user and of course adding them to the role.
But I need to add an AD security group "BIDevelopers" to my Azure SQL database. I've been searching for examples. All of the examples are using Azure AD and not just AD security groups.
I know you don't just right click as in SSMS. In fact, I am using the Azure portal and Azure Studio. I know how to add a new user.
CREATE Login [NewUser] WITH password = 'N@wP@ssword!123'
CREATE USER [NewUser] FROM LOGIN [NewUser];
EXEC sp_addrolemember 'db_datareader', [NewUser];

How do I add a security group?

Thanks.

Things will work out. Get back up, change some parameters and recode.

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

First thing I notice is that your code for a new user is not using AD. You'd want a "FROM WINDOWS" not "WITH PASSWORD" for using AD.

Next, if I am interpreting what you mean by security group correctly, you just put the domain and group name in place of "NewUser" in your script.

The method for adding new logins is the same if it is a "real user" or a "security group", and the method I recommend for both on-prem and Azure is via scripting like you did. The TSQL is identical for creating a login on prem or in Azure.

Now a red flag I see in your script is that you are granting people read access to all data in the database. In my experience, this is RARELY required and can lead to security problems in the future. EVEN if the current state is that everyone should be able to read all data, is that going to be the case in the future? I would MUCH rather grant public SELECT access to all existing tables and I can revoke it later if it turns out that they shouldn't have read access on a specific table than add them to a group that gives them read access to everything and later need to revisit those users who should not have read access on some tables. I prefer the "least privilege" security model as it is EASY to grant people more permissions if required (via database roles... I strongly recommend you don't grant access to specific users). It can be a pain in the butt trying to remove permissions from some users from some objects without impacting their work.

Just my 2 cents though...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Michael L John One Orange Chip Points: 27060 More actions · Answer 2

You do not create a login, you create a user in the database.

CREATE USER [YourUser] FROM EXTERNAL PROVIDER;

And, even as an admin, there are only 3 folders with an Azure SQL Database. Databases, Security, and Integration Services Catalogs.

Remember, this is not a server.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

WebTechie SSChampion Points: 11405 More actions · Answer 3

Thanks for that both of you.

I had tried the FROM EXTERNAL PROVIDER. But I got an error message.

Msg 15006, Level 16, State 5, Line 1

'domain\BIDevelopers' is not a valid name because it contains invalid characters.

Msg 33134, Level 16, State 1, Line 1

Principal 'BIDevelopers' could not be resolved. Error message: ''

2. The Second thing is how does adding the login "BIDevelopers" allow the security to authenticate users in that security group? In SSMS, for on prem, I am adding "domain\BIdevelopers".

Thanks.

Things will work out. Get back up, change some parameters and recode.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

You need to use something like Azure AD sync to sync your on-prem AD to Azure AD.

Azure SQL Database can only accept AAD logins or SQL logins.

If the account you want isn’t in Azure Active Directory then you won’t be able to add it as a login to Azure SQL Database.

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;

GO

CREATE USER [AAD group] FROM LOGIN [AAD group];

GO