Follow below steps to allow Azure AD users and groups to connect to your Azure SQL database:
- create a User in Azure AD
- optionally: create a Group in Azure AD and add the User from step 1 to the group
- assign Active Directory admin to your Azure SQL DB
- 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.
- 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.
- 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.
- run the command : CREATE USER [AD-USER-OR-GROUP] FROM EXTERNAL PROVIDER
- 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
Did you know you can use Navision on Azure SQL DB? Read my article on how to setup Navision with a Azure SQL backend.