CODEWITHSUNDEEP
azure-sql-databaseazure-active-directory
dead mah
dead mah

Reputation: 179

How to add Azure AD Groups in Azure SQL Server

Can someone tell me how can I add Azure Active Directory groups into the azure sql server, I am using server manager tool to do this but cant find any way to figure this out, I can add simple Azure Active Directory user though..What about groups?

Upvotes: 13

Views: 40182

Answers (2)

Mike
Mike

Reputation: 1876

I will assume that you are wanting to provide access for end-users to connect, not Database Administrators. For my example below, let's say that the end-users are in a group called "AZ-Users", and that your Database Administrators (including you) are in a group called "AZ-DBAs".

For Azure SQL Databases, there are key things that must be in place to get this to work:

  1. There must be an "Active Directory admin" configured for your server. This can be any AAD user or an AAD group. You can check if this has been set or not by going to the Azure portal page for your server. Be careful that you are looking at the Overview page for the server, not the individual database, they are not the same thing. Detailed instructions here. In our example, we would configure this to be the AAD group called "AZ-DBAs".

  2. When you are ready to create the AAD login for "AZ-Users" on your Azure SQL Database, you must yourself be logged in using AAD... meaning a member of the "AZ-DBAs" group from my example above. You can use SSMS or any other similar tool for executing TSQL commands. Note that if you try a SQL auth connection instead, it won't work for step 4 below - you'll get this error:

Msg 33159, Level 16, State 1, Line 1
Principal 'AZ-Users' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

  1. Change the context to the database you want to provide access to for your end users.

  2. Execute this statement:

    CREATE USER [AZ-Users] FROM EXTERNAL PROVIDER

Note that this will create a "contained database user", as detailed here. That's it. This process works for AAD groups and AAD users.

  1. You will probably also want to grant some level of permissions as well, such as:

    EXEC sp_addrolemember 'db_datareader', 'AZ-Users'

Upvotes: 25

Dzejms
Dzejms

Reputation: 3258

Connect to the server via SSMS as your Azure AD admin. Create a new query with the db you want to affect. Run this:

ALTER ROLE db_datareader ADD MEMBER [AzureADGroupName];
GO

To modify permissions, do something like this:

ALTER ROLE db_datareader ADD MEMBER [AzureADGroupName];
GO

Upvotes: 0

Related Questions