This blog post is related to my previous blog post on Azure SQL Managed Instances:

Azure SQL Managed Instance – full blown SQL Server in the cloud….?

Before you actually migrate your database you need to think about what changes to your application you may need to do.

Azure SQL Managed Instances do not utilise windows authentication – so your two methods of authenticating applications and users are:

• SQL Authentication:This authentication method uses a username and password.
• Azure Active Directory Authentication:This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. Use Active Directory authentication (integrated security) whenever possible.

Azure Active Directory (AAD) logins are the Azure version of on-premises database logins that you are using in your on-premises SQL Server instances. AAD logins enables you to specify users and groups from your Azure Active Directory tenant as true instance-scoped principals, capable of performing any instance-level operation, including cross-database queries within the same Managed Instance.

Note: Azure AD logins and users are supported as a preview feature for Azure SQL Managed Instances.

A new syntax is introduced to create AAD logins, which utilises  “FROM EXTERNAL PROVIDER”  i.e.

CREATE LOGIN [hamish@morphit.onmicrosoft.com] FROM EXTERNAL PROVIDER
GO

compare this to our normal method of creating a login for a windows domain account

CREATE LOGIN [MorphiT\Hamish] FROM WINDOWS; 
GO

One of the key things to understand with Azure SQL Managed Instances is that if you are

leveraging AAD for authentication then the SID for the server level login will not be

the same as on-premises. This will be a consideration when you are migrating your database and users from on-premises to Azure SQL Managed Instance.

Just like any database migration where SQL Authentication is used, you will need to handle the mismatch in SID between the database user and Instance Login.

This means that you may have to use ALTER USER to link the AAD login to the database user. Which in effect will link the database user to the server login.

You can also remove the user and re-add them in – however this is quite a destructive method and you will have no certainty that you will get the securables correct or as they were before you removed the user.

The biggest consideration is – will your application handle AAD – if not then you will have to use SQL Authentication.

If you decide to use SQL Authentication initially and then move to AAD then you will need to re-engineer your application to leverage AAD….

Yip.