Home
%3CLINGO-SUB%20id%3D%22lingo-sub-937608%22%20slang%3D%22en-US%22%3ET-SQL%20DDL%20support%20for%20Windows%20users%20migration%20from%20SQL%20Server%20databases%20to%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-937608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EWe%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20a%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ere%20happy%20to%20announce%20a%20public%20preview%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3Efor%20T-SQL%20DDL%20support%20for%20Windows%3C%2FSPAN%3E%20d%3CSPAN%20data-contrast%3D%22auto%22%3Eomain%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20users%20migration%20from%20SQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EServer%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eon-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Edatabases%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eto%20SQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Em%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eanaged%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ei%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Enstance%20(MI)%20with%20A%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ezure%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAD%20authentication.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThe%20migration%20process%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Efor%20databases%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Efrom%20SQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EServer%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eon-prem%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eises%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20to%20MI%20can%20be%20achieved%20in%20different%20ways%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E.%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3EH%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eowever%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20for%20SQL%20on-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Edatabases%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20that%20contain%20Windows%20users%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%2Fgroups%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20two%20main%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Emechanisms%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3Eare%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eproposed%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%229%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAutomated%20migration%20using%20Azure%20Database%20Migration%20Service%20(DMS)%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ecoming%20soon%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%229%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EManual%20migration%20using%20T-SQL%20DDL%20commands%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%20now%20in%20public%20preview%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3EPrerequisites%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThe%20following%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eprerequisites%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20apply%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%2210%22%20data-aria-posinset%3D%223%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThe%20Windows%20domain%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eused%20for%20SQL%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20Server%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20on-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Emust%20be%20federated%20with%20Azure%20AD%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%2210%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThe%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ebackup%2Frestore%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Emechanism%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20is%20used%20to%20m%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eigrate%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ethe%20database%20fr%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eo%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Em%20SQL%20on-premises%20to%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EMI%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%2210%22%20data-aria-posinset%3D%222%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAzure%20AD%20admin%20must%20be%20setup%20for%20MI%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%2210%22%20data-aria-posinset%3D%222%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThe%20Windows%20logins%20for%20users%20and%20groups%20existing%20in%20SQL%20Server%20must%20be%20created%20manually%20in%20the%20master%20database%20for%20MI%20using%20the%20new%20Azure%20AD%20notation%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Be.g.%20SQL%20Server%20login%20Domain%5CUserName%2C%20create%20a%20login%20in%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ethe%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Emaster%20database%20for%20MI%20using%20the%20following%20syntax%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ECREATE%20LOGIN%20%5BUserName%40Domain.com%5D%20FROM%20EXTERNAL%20PROVIDER%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EFor%20more%20information%20on%20Azure%20AD%20login%20creation%20see%26nbsp%3B%20%3C%2FSPAN%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%3E%3CSPAN%20data-contrast%3D%22none%22%3ECREATE%20LOGIN%20(Transact-SQL)%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20aria-level%3D%222%22%3E%3CSTRONG%3EThe%20new%20DDL%20syntax%20support%20for%20migration%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EOnce%20the%20database%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20originating%20from%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EServer%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eon-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eis%20restored%20on%20MI%2C%20t%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ehe%20following%20T-SQL%20DDL%20commands%20allow%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eusers%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eto%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eremap%20Windows%20users%20and%20groups%20to%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAzure%20AD%20users%20and%20groups%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E.%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EFor%20Windows%20users%20with%20logins%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%225%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EALTER%20USER%20%5BDomain%5CUserName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20WITH%20LOGIN%3D%20%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EUserName%40Domain.com%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-contrast%3D%22auto%22%3EMaps%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20SQL%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20Server%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20on-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Euser%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%5CUserName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20to%20Azure%20AD%20user%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EUserName%40Domain.com%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20in%20MI%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559737%26quot%3B%3A-360%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EFor%20Windows%20groups%20with%20logins%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%225%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EALTER%20USER%20%5BDomain%5CGroupName%5D%20WITH%20LOGIN%3D%5BGroupName%5D%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-contrast%3D%22auto%22%3EMaps%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EServer%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eon-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Egroup%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%5CGroupName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20to%20A%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ezure%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAD%20group%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EGroupName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%E2%80%AF%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ein%20MI%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EFor%20Windows%20users%20w%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eithout%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20logins%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20(contained%20users)%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%E2%80%AF%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%225%22%20data-aria-posinset%3D%222%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EALTER%20USER%20%5BDomain%5CUserName%5D%20FROM%20EXTERNAL%20PROVIDER%E2%80%AF%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-contrast%3D%22auto%22%3EMaps%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EServer%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eon-premises%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20user%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%5CUserName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20to%20Azure%20AD%20user%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EUserName%40Domain.com%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20in%20MI%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559737%26quot%3B%3A-270%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EFor%20Windows%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Egroups%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20w%2Fo%20logins%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%E2%80%AF%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20aria-setsize%3D%22-1%22%20data-leveltext%3D%22%EF%82%B7%22%20data-font%3D%22Symbol%22%20data-listid%3D%225%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EALTER%20USER%20%5BDomain%5CGroupName%5D%E2%80%AF%20FROM%20EXTERNAL%20PROVIDER%E2%80%AF%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CUL%3E%0A%3CLI%3E%3CSPAN%20data-contrast%3D%22auto%22%3EMaps%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20Server%3C%2FSPAN%3E%20%3CSPAN%20data-contrast%3D%22auto%22%3Eon-premises%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Egroup%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDomain%5CGroupName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20to%20A%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ezure%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EAD%20group%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EGroupName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20in%20MI%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EIn%20all%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ethe%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eabove%20cases%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EUserName%40Domain.com%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20and%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EGroupName%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20must%20exist%20in%20Azure%20AD%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E.%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20This%20means%20that%20the%20synchronization%20between%20the%20on-premises%20Windows%20Domain%20and%20Azure%20Active%20Directory%20has%20to%20be%20done%20beforehand.%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20aria-level%3D%222%22%3E%3CSTRONG%3EResults%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EBelow%20are%20the%20Windows%20users%20existing%20in%20the%20database%20after%20being%20migrated%20to%20MI%20and%20before%20running%20the%20ALTER%20USER%20command%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20(u%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Epper%20screenshot)%20and%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ea%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Efter%20running%20the%20ALTER%20USER%20command%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20(lower%20screenshot)%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%20where%20t%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ehe%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Emigrated%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3EWindows%20users%20are%20changed%20to%20the%20new%20Azure%20AD%20users%20with%20their%20new%20SIDs%20(see%20q%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Euery%20results%20below%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E).%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F144169iCA501455B4F6DF8E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Query.Results.png%22%20title%3D%22Query.Results.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSTRONG%3EPermissions%20and%20Role-membership%20migration%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAll%20Permissions%20and%20database-role%20memberships%20will%20be%20retained%20for%20the%20migrated%20database%20users.%3CBR%20%2F%3EHowever%2C%20if%20the%20logins%20on%20the%20SQL%20Server%20on-premises%20had%20been%20granted%20server%20scoped%20permissions%20or%20roles%2C%20those%20are%20not%20retained%20by%20these%20steps%20as%20they%20are%20contained%20in%20the%20master-database.%20In%20order%20to%20migrate%20those%2C%20you%20can%20either%20use%20DMS%20for%20the%20migration%2C%20or%20revert%20to%20T-SQL%20scripts.%3C%2FP%3E%0A%3CP%3EFor%20more%20information%20on%20this%20topic%2C%20including%20a%20step-by-step%20tutorial%2C%20please%20see%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Ftutorial-managed-instance-azure-active-directory-migration%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CSPAN%20style%3D%22font-family%3A%20'Segoe%20UI'%2Csans-serif%3B%20background%3A%20white%3B%22%3ETutorial%3A%20Migrating%20SQL%20Server%20on-premises%20Windows%20users%20and%20groups%20to%20Azure%20SQL%20Database%20managed%20instance%20using%20T-SQL%20DDL%20syntax%3C%2FSPAN%3E%3C%2FA%3E%20and%20%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%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-937608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3ET-SQL%20DDL%20support%20for%20Windows%20user%2Fgroup%20migration%20from%20SQL%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ES%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eerver%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Edatabases%20%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eto%20Managed%20Instance%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-937608%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

We are happy to announce a public preview for T-SQL DDL support for Windows domain users migration from SQL Server on-premises databases to SQL managed instance (MI) with Azure AD authentication.  

The migration process for databases from SQL Server on-premises to MI can be achieved in different ways. However, for SQL on-premises databases that contain Windows users/groups, two main mechanisms are proposed: 

  • Automated migration using Azure Database Migration Service (DMS): coming soon 
  • Manual migration using T-SQL DDL commands: now in public preview

Prerequisites 

The following prerequisites apply: 

  • The Windows domain used for SQL Server on-premises must be federated with Azure AD 
  • The SQL backup/restore mechanism is used to migrate the database from SQL on-premises to MI 
  • Azure AD admin must be setup for MI
  • The Windows logins for users and groups existing in SQL Server must be created manually in the master database for MI using the new Azure AD notation 
    •  e.g. SQL Server login Domain\UserName, create a login in the master database for MI using the following syntax: 
      CREATE LOGIN [UserName@Domain.com] FROM EXTERNAL PROVIDER 
      For more information on Azure AD login creation see  CREATE LOGIN (Transact-SQL) 

The new DDL syntax support for migration 

Once the database originating from SQL Server on-premises is restored on MI, the following T-SQL DDL commands allow users to remap Windows users and groups to Azure AD users and groups. 

 

For Windows users with logins: 

  • ALTER USER [Domain\UserName] WITH LOGIN= [UserName@Domain.com] 
    • Maps SQL Server on-premises Domain user [Domain\UserName] to Azure AD user [UserName@Domain.com] in MI 

For Windows groups with logins: 

  • ALTER USER [Domain\GroupName] WITH LOGIN=[GroupName] 
    • Maps SQL Server on-premises Domain group [Domain\GroupName] to Azure AD group [GroupName]in MI 

For Windows users without logins (contained users): 

  • ALTER USER [Domain\UserName] FROM EXTERNAL PROVIDER  
    • Maps SQL Server on-premises Domain user [Domain\UserName] to Azure AD user [UserName@Domain.com] in MI 

For Windows groups w/o logins: 

  • ALTER USER [Domain\GroupName]  FROM EXTERNAL PROVIDER  
    • Maps SQL Server on-premises Domain group [Domain\GroupName] to Azure AD group [GroupName] in MI 

In all the above cases [UserName@Domain.com] and [GroupName] must exist in Azure AD. This means that the synchronization between the on-premises Windows Domain and Azure Active Directory has to be done beforehand. 

 

Results 

Below are the Windows users existing in the database after being migrated to MI and before running the ALTER USER command (upper screenshot) and after running the ALTER USER command (lower screenshot), where the migrated Windows users are changed to the new Azure AD users with their new SIDs (see query results below). 

 

Query.Results.png

 

Permissions and Role-membership migration

All Permissions and database-role memberships will be retained for the migrated database users.
However, if the logins on the SQL Server on-premises had been granted server scoped permissions or roles, those are not retained by these steps as they are contained in the master-database. In order to migrate those, you can either use DMS for the migration, or revert to T-SQL scripts.

For more information on this topic, including a step-by-step tutorial, please see:

Tutorial: Migrating SQL Server on-premises Windows users and groups to Azure SQL Database managed in... and ALTER USER (Transact-SQL).