Follow below steps to allow Azure AD users and groups to connect to your Azure SQL database:

  1. create a User in Azure AD
  2. optionally: create a Group in Azure AD and add the User from step 1 to the group
  3. assign Active Directory admin to your Azure SQL DB
  4. log in to your Azure SQL DB using this AD admin
    If you don’t, you’ll get following error message:
    Msg 33159, Level 16, State 1, Line 5
    Principal ‘MI-NAV-READONLY’ could not be created.
    Only connections established with Active Directory accounts can create other Active Directory users.
  5. change to the database you want to give access to, otherwise you just create the user in master
    Note: the AD user is not visible in the Logins list (as it is a User). It is visible in the User list of the database in which you created it.
  6. optionally: use SELECT * FROM SYS.DATABASE_PRINCIPALS to verify the creation (or use the Users list in SSMS)
    Note: the sid created for the EXTERNAL_GROUP type of user is the same across databases, which facilitates when e.g. refreshing a DEV database from another environment.
  7. run the command : CREATE USER [AD-USER-OR-GROUP] FROM EXTERNAL PROVIDER
  8. specify the rights for the user/group, e.g. : EXEC sp_addrolemember ‘db_datareader’, ‘AD-USER-OR-GROUP’

More details can be found here : https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

Read More

Did you know you can use Navision on Azure SQL DB? Read my article on how to setup Navision with a Azure SQL backend.