Lesson Learned #23: How to add AAD Security Groups to Azure SQL Database?

Published Mar 13 2019 06:38 PM 22.2K Views

First published on MSDN on Mar 10, 2017
In these days, we are receiving multiples cases where our customer needs to add Azure SQL Database groups from Azure Active Directory.

This is an example to how to archive it.

  • Using the Portal in Azure:

    • I created a user called SQLMember.
    • I created a group called SQLGroup, adding the member SQLMember.

 

 

  • Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database

 

 

  • Finally, I disconnected from SQL Server Management Studio and I connect again using the user SQLMember@mydomain.com to the database and I was able to connect without issue, applying the read-only permissions for the database

 

4 Comments
%3CLINGO-SUB%20id%3D%22lingo-sub-1190995%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190995%22%20slang%3D%22en-US%22%3E%3CP%3EDid%20you%20create%20the%20user%20in%20Azure%20Active%20Directory%20or%20SQL%3F%3CBR%20%2F%3ESame%20for%20the%20Group%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608876%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413373%22%20target%3D%22_blank%22%3E%40astanton1978%3C%2FA%3E%26nbsp%3Byes%2C%20because%20he%20created%20the%20user%20using%20the%20FROM%20EXTERNAL%20PROVIDER%2C%20meaning%20its%20not%20a%20SQL%20login%2C%20and%20is%20signing%20in%20using%20%22%3CSPAN%3ESQLMember%40mydomain.com%22.%20This%20is%20a%20domain%20user.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%20the%20title%20indicates%20that%20the%20group%20is%20an%20AAD%20objects.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHe%20is%20being%20quite%20clear%20about%20it.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639178%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639178%22%20slang%3D%22en-US%22%3E%3CP%3Eif%20you%20add%20a%20user%20to%20the%20active%20directory%20group%20after%20you've%20created%20an%20azure%20db%20user%20for%20that%20group%20does%20it%20still%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1845328%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1845328%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20we%20add%20AAD%20Application%20in%20security%20group%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-368866%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Mar%2010%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3EIn%20these%20days%2C%20we%20are%20receiving%20multiples%20cases%20where%20our%20customer%20needs%20to%20add%20Azure%20SQL%20Database%20groups%20from%20Azure%20Active%20Directory.%20%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20an%20example%20to%20how%20to%20archive%20it.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EUsing%20the%20Portal%20in%20Azure%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CLI%3EI%20created%20a%20user%20called%20SQLMember.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3EI%20created%20a%20group%20called%20SQLGroup%2C%20adding%20the%20member%20SQLMember.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EUsing%20SQL%20SERVER%20Management%20Studio%20connected%20to%20the%20User%20Database%20using%20the%20Azure%20Active%20Directory%20Admin%20User%20and%20clicking%20New%20Query.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CUL%3E%0A%3CLI%3EI%20create%20as%20user%20group%20of%20this%20database%20-%20CREATE%20USER%20%5BSQLGroup%5D%20FROM%20EXTERNAL%20PROVIDER%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERunning%20SELECT%20*%20FROM%20SYS.DATABASE_PRINCIPALS%20we%20could%20see%20that%20the%20group%20has%20been%20created%20in%20Azure%20SQL%20Database%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20executed%20the%20TSQL%3A%20EXEC%20sp_addrolemember%20'db_datareader'%2C%20'SQLGroup'%20to%20give%20the%20permissions%20to%20this%20group%20just%20of%20read-only.%20-%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms187750.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms187750.aspx%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EFinally%2C%20I%20disconnected%20from%20SQL%20Server%20Management%20Studio%20and%20I%20connect%20again%20using%20the%20user%20SQLMember%40mydomain.com%20to%20the%20database%20and%20I%20was%20able%20to%20connect%20without%20issue%2C%20applying%20the%20read-only%20permissions%20for%20the%20database%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368866%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Mar%2010%2C%202017%20In%20these%20days%2C%20we%20are%20receiving%20multiples%20cases%20where%20our%20customer%20needs%20to%20add%20Azure%20SQL%20Database%20groups%20from%20Azure%20Active%20Directory.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eaad%20security%20groups%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20DB%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edb_datareader%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eselect%20from%20sys%20database_principals%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esp_addrolemember%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Nov 03 2020 07:34 AM
Updated by: