Home
%3CLINGO-SUB%20id%3D%22lingo-sub-970155%22%20slang%3D%22en-US%22%3EGeneral%20availability%20for%20Azure%20AD%20server%20principals%20(logins)%20for%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-970155%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20happy%20to%20announce%20a%20general%20availability%20(GA)%20for%20Azure%20AD%20server%20principals%20(Azure%20AD%20logins)%20for%20SQL%20managed%20instance%20(MI).%20This%20feature%20allows%20Azure%20AD%20users%20to%20create%20logins%20in%20the%20master%20database%20for%20MI%2C%20grant%20MI%20server%20level%20permissions%20for%20these%20logins%20and%20create%20Azure%20AD%20users%20with%20%26nbsp%3B%20%26nbsp%3B%20logins%20for%20individual%20MI%20databases.%3C%2FP%3E%0A%3CP%3EAdditionally%2C%20enabling%20Azure%20AD%20logins%20allow%20users%20to%20execute%20many%20MI%20features%20supported%20for%20SQL%20logins%20(see%20the%20documentation%20at%20the%20end%20of%20this%20blog).%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1851545989%22%20id%3D%22toc-hId-1851545989%22%3E%3CSTRONG%3EWhat%20is%20new%20in%20the%20GA%20release%3C%2FSTRONG%3E%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ENew%20Azure%20AD%20admin%20functionality%20for%20MI%3C%2FLI%3E%0A%3CLI%3EPowerShell%20and%20CLI%20commands%20to%20setup%20Azure%20AD%20admin%20for%20MI%3C%2FLI%3E%0A%3CLI%3EDatabase%20export%2Fimport%20support%20for%20Azure%20AD%20users%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId--700610972%22%20id%3D%22toc-hId--700610972%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1042199363%22%20id%3D%22toc-hId-1042199363%22%3ENew%20Azure%20AD%20admin%20functionality%20for%20MI%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FH2%3E%0A%3CP%3EThe%20table%20below%20summarizes%20the%20functionality%20for%20current%20(public%20preview)%20Azure%20AD%20admin%20for%20MI%20versus%20a%20new%20functionality%20delivered%20with%20GA%20for%20Azure%20AD%20logins%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22677%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22326%22%3E%3CP%3E%3CSTRONG%3ECurrent%20Azure%20AD%20admin%20for%20MI%20(public%20preview)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22351%22%3E%3CP%3E%3CSTRONG%3EGA%20functionality%20for%20Azure%20AD%20admin%20for%20MI%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22326%22%3E%3CP%3EBehaves%20in%20a%20similar%20way%20as%20Azure%20AD%20admin%20for%20SQL%20DB%20which%20enables%20Azure%20AD%20authentication%2C%20but%20Azure%20AD%20admin%20for%20MI%20cannot%20create%20Azure%20AD%20or%20SQL%20logins%20in%20the%20master%20db%20for%20MI%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22351%22%3E%3CP%3EAzure%20AD%20admin%20for%20MI%20has%20a%20sysadmin%20permission%20and%20can%20create%20Azure%20AD%20and%20SQL%20logins%20in%20master%20db%20for%20MI.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20create%20an%20Azure%20AD%20login%20for%20an%20Azure%20AD%20user%20joe%40contoso.com%20using%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20CREATE%20LOGIN%20%5Bjoe%40contoso.com%5D%20%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20FROM%20EXTERNAL%20PROVIDER%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Ewhere%20user%20joe%40contoso.com%20is%20a%20member%20of%20the%20Azure%20AD%20for%20the%20contoso.com%20domain.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EAs%20a%20sysadmin%2C%20Azure%20AD%20admin%20can%20grant%20sysadmin%20permissions%20(or%20lower)%20to%20other%20logins%20for%20MI.%20For%20example%3A%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSTRONG%3E%3CSTRONG%3EALTER%20SERVER%20ROLE%20sysadmin%20ADD%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20MEMBER%20%5Bjoe%40contoso.com%5D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20Azure%20AD%20login%20is%20created%2C%20an%20Azure%20AD%20user%20with%20this%20login%20can%20be%20created%20in%20the%20MI%20database.%20For%20example%3A%20%3CBR%20%2F%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20CREATE%20USER%20%3C%2FSTRONG%3E%3CSTRONG%3E%5Bjoe%40contoso.com%5D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20FROM%20LOGIN%20%5Bjoe%40contoso.com%5D%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22326%22%3E%3CP%3E%3CSTRONG%3EIs%20not%20present%3C%2FSTRONG%3E%20in%20the%20sys.server_principals%20view%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22351%22%3E%3CP%3E%3CSTRONG%3EIs%20visible%3C%2FSTRONG%3E%20in%20the%20sys.server_principals%20view%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22326%22%3E%3CP%3EAllows%20individual%20Azure%20AD%20guest%20users%20(%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fb2b%2Fadd-users-administrator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20)%20to%20be%20setup%20as%20Azure%20AD%20admin%20for%20MI%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22351%22%3E%3CP%3ERequires%20creation%20of%20an%20Azure%20AD%20group%20with%20guest%20users%20as%20members%20to%20setup%20this%20group%20as%20an%20Azure%20AD%20admin%20for%20MI.%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3EFor%20more%20information%20on%20GA%20functionality%20for%20Azure%20AD%20admin%20for%20MI%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-login-transact-sql%3Fview%3Dazuresqldb-mi-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECREATE%20LOGIN%20(Transact-SQL%3CSTRONG%3E)%3C%2FSTRONG%3E%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-user-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECREATE%20USER%20(Transact-SQL)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22text-align%3A%20left%3B%22%3EPlease%20note%3C%2FP%3E%0A%3CP%3EFor%20Azure%20AD%20admins%20for%20MI%20created%20prior%20to%20GA%2C%20but%20continue%20operating%20post%20GA%2C%20there%20is%20no%20functional%20change%20and%20the%20behavior%20described%20in%20the%20table%20above%20(for%20Azure%20AD%20admins%20in%20public%20preview)%20still%20applies.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20good%20practice%20for%20existing%20Azure%20AD%20admins%20for%20MI%20created%20prior%20to%20GA%2C%20and%20still%20operating%20post%20GA%2C%20re-set%20the%20Azure%20AD%20admin%20using%20the%20Azure%20portal%20to%20%E2%80%9CRemove%20admin%E2%80%9D%20and%20%E2%80%9CSet%20admin%E2%80%9D%20again%20for%20the%20same%20Azure%20AD%20user%20or%20group.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1509957598%22%20id%3D%22toc-hId--1509957598%22%3EEnable%20PowerShell%20and%20CLI%20commands%20to%20setup%20Azure%20AD%20admin%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESeveral%20PowerShell%20and%20CLI%20cmdlets%20have%20been%20created%20to%20manage%20an%20Azure%20AD%20admin%20for%20MI%2C%20%3CBR%20%2F%3Esuch%20as%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Faz.sql%2Fset-azsqlinstanceactivedirectoryadministrator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESet-AzSqlInstanceActiveDirectoryAdministrator%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Faz.sql%2Fremove-azsqlinstanceactivedirectoryadministrator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERemove-AzSqlInstanceActiveDirectoryAdministrator%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Faz.sql%2Fget-azsqlinstanceactivedirectoryadministrator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGet-AzSqlInstanceActiveDirectoryAdministrator%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EFor%20more%20details%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-aad-authentication-configure%23provision-an-azure-active-directory-administrator-for-your-managed-instance%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EProvision%20an%20Azure%20Active%20Directory%20administrator%20for%20your%20managed%20instance%3CBR%20%2F%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-232852737%22%20id%3D%22toc-hId-232852737%22%3EDatabase%20export%2Fimport%20support%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FH2%3E%0A%3CP%3EDatabase%20export%2Fimport%20using%20%3CSTRONG%3Ebacpac%20file%3C%2FSTRONG%3E%20is%20supported%20for%20Azure%20AD%20users%20for%20MI%3CBR%20%2F%3Eusing%20either%20SSMS%20V18.4%20or%20SQLPackage.exe%20V18.4%3CBR%20%2F%3E(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fssms%2Fdownload-sql-server-management-studio-ssms%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDownload%20SQL%20Server%20Management%20Studio%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ftools%2Fsqlpackage-download%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDownload%20and%20install%20sqlpackage%3C%2FA%3E).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20configurations%20are%20supported%20using%20database%20bacpac%20file%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EExport%2Fimport%20a%20database%20between%20different%20MIs%20within%20the%20same%20Azure%20AD%20domain%3C%2FLI%3E%0A%3CLI%3EExport%20a%20database%20from%20MI%20and%20import%20to%20SQL%20DB%20within%20the%20same%20Azure%20AD%20domain%3C%2FLI%3E%0A%3CLI%3EExport%20a%20database%20from%20SQL%20DB%20and%20import%20to%20MI%20within%20the%20same%20Azure%20AD%20domain%3C%2FLI%3E%0A%3CLI%3EExport%20a%20database%20from%20MI%20and%20import%20to%20SQL%20Server%20(version%202012%20or%20later)%3CUL%3E%0A%3CLI%3EIn%20this%20configuration%20all%20Azure%20AD%20users%20are%20created%20as%20SQL%20database%20principals%20(users)%20without%20logins.%20The%20type%20of%20these%20users%20is%20SQL%20(visible%20as%20SQL_USER%20in%20sys.database_principals).%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EThere%20permissions%20and%20roles%20remain%20in%20the%20SQL%20Server%20database%20metadata%20and%20can%20be%20used%20for%20impersonation%2C%20however%2C%20they%20cannot%20be%20used%20to%20accessed%20and%20logged%20in%20to%20the%20SQL%20Server%20using%20their%20credentials.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EThe%20migration%20of%20a%20database%20with%20Windows%20users%20and%20groups%20from%20a%20SQL%20Server%20to%20MI%20with%20Azure%20AD%20authentication%20is%20supported.%20For%20more%20information%20on%20this%20topic%20see%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FT-SQL-DDL-support-for-Windows-users-migration-from-SQL-Server%2Fba-p%2F937608%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3ET-SQL%20DDL%20support%20for%20Windows%20users%20migration%20from%20SQL%20Server%20databases%20to%20SQL%20Managed%20Instance%2C%20%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1975663072%22%20id%3D%22toc-hId-1975663072%22%3EAdditional%20references%20related%20to%20the%20Azure%20AD%20logins%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FH2%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-login-transact-sql%3Fview%3Dazuresqldb-mi-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECREATE%20LOGIN%20(Transact-SQL)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-user-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECREATE%20USER%20(Transact-SQL)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Falter-user-transact-sql%3Fview%3Dazuresqldb-mi-current%26amp%3Bbranch%3Dpr-en-us-12096%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EALTER%20USER%20(Transact-SQL)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-managed-instance-aad-security-tutorial%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETutorial%3A%20Managed%20instance%20security%20in%20Azure%20SQL%20Database%20using%20Azure%20AD%20server%20principals%20(logins)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FT-SQL-DDL-support-for-Windows-users-migration-from-SQL-Server%2Fba-p%2F937608%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3ETutorial%3A%20Migrating%20SQL%20Server%20on-premises%20Windows%20users%20and%20groups%20to%20Azure%20SQL%20Database%20managed%20instance%20using%20T-SQL%20DDL%20syntax%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-aad-authentication-configure%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EConfigure%20and%20manage%20Azure%20Active%20Directory%20authentication%20with%20SQL%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-970155%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22text-align%3A%20center%3B%22%20align%3D%22center%22%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-size%3A%2013.0pt%3B%20line-height%3A%20107%25%3B%22%3EGeneral%20availability%20for%20Azure%20AD%20server%20principals%20(AAD%20logins)%20for%20SQL%20Managed%20Instance%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-970155%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EManaged%20Instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

We are happy to announce a general availability (GA) for Azure AD server principals (Azure AD logins) for SQL managed instance (MI). This feature allows Azure AD users to create logins in the master database for MI, grant MI server level permissions for these logins and create Azure AD users with     logins for individual MI databases.

Additionally, enabling Azure AD logins allow users to execute many MI features supported for SQL logins (see the documentation at the end of this blog).  

 

What is new in the GA release

 

  • New Azure AD admin functionality for MI
  • PowerShell and CLI commands to setup Azure AD admin for MI
  • Database export/import support for Azure AD users

 

New Azure AD admin functionality for MI

The table below summarizes the functionality for current (public preview) Azure AD admin for MI versus a new functionality delivered with GA for Azure AD logins

 

 

Current Azure AD admin for MI (public preview)

GA functionality for Azure AD admin for MI

Behaves in a similar way as Azure AD admin for SQL DB which enables Azure AD authentication, but Azure AD admin for MI cannot create Azure AD or SQL logins in the master db for MI

Azure AD admin for MI has a sysadmin permission and can create Azure AD and SQL logins in master db for MI.

For example, create an Azure AD login for an Azure AD user joe@contoso.com using

       CREATE LOGIN [joe@contoso.com]
       FROM EXTERNAL PROVIDER

where user joe@contoso.com is a member of the Azure AD for the contoso.com domain.

As a sysadmin, Azure AD admin can grant sysadmin permissions (or lower) to other logins for MI. For example:

        ALTER SERVER ROLE sysadmin ADD
        MEMBER [joe@contoso.com]

 

Once the Azure AD login is created, an Azure AD user with this login can be created in the MI database. For example:
         CREATE USER [joe@contoso.com]

         FROM LOGIN [joe@contoso.com]

Is not present in the sys.server_principals view

Is visible in the sys.server_principals view

Allows individual Azure AD guest users ( see here ) to be setup as Azure AD admin for MI

Requires creation of an Azure AD group with guest users as members to setup this group as an Azure AD admin for MI.

For more information on GA functionality for Azure AD admin for MI see CREATE LOGIN (Transact-SQL) and CREATE USER (Transact-SQL)

 

Please note

For Azure AD admins for MI created prior to GA, but continue operating post GA, there is no functional change and the behavior described in the table above (for Azure AD admins in public preview) still applies.

 

As a good practice for existing Azure AD admins for MI created prior to GA, and still operating post GA, re-set the Azure AD admin using the Azure portal to “Remove admin” and “Set admin” again for the same Azure AD user or group.

 

Enable PowerShell and CLI commands to setup Azure AD admin

 

Several PowerShell and CLI cmdlets have been created to manage an Azure AD admin for MI,
such as:

For more details, see Provision an Azure Active Directory administrator for your managed instance

 

Database export/import support

Database export/import using bacpac file is supported for Azure AD users for MI
using either SSMS V18.4 or SQLPackage.exe V18.4
(Download SQL Server Management Studio and Download and install sqlpackage).

 

The following configurations are supported using database bacpac file:

  • Export/import a database between different MIs within the same Azure AD domain
  • Export a database from MI and import to SQL DB within the same Azure AD domain
  • Export a database from SQL DB and import to MI within the same Azure AD domain
  • Export a database from MI and import to SQL Server (version 2012 or later)
    • In this configuration all Azure AD users are created as SQL database principals (users) without logins. The type of these users is SQL (visible as SQL_USER in sys.database_principals).

There permissions and roles remain in the SQL Server database metadata and can be used for impersonation, however, they cannot be used to accessed and logged in to the SQL Server using their credentials.

The migration of a database with Windows users and groups from a SQL Server to MI with Azure AD authentication is supported. For more information on this topic see, T-SQL DDL support for Windows users migration from SQL Server databases to SQL Managed Instance,

 

Additional references related to the Azure AD logins

CREATE LOGIN (Transact-SQL)

CREATE USER (Transact-SQL)

ALTER USER (Transact-SQL)

Tutorial: Managed instance security in Azure SQL Database using Azure AD server principals (logins)

Tutorial: Migrating SQL Server on-premises Windows users and groups to Azure SQL Database managed in...

Configure and manage Azure Active Directory authentication with SQL