Azure AD authentication for MSSQL

Configure and manage Azure AD authentication for MSSQL

A few manual steps are required to enable authentication via Azure AD.

Configure Azure AD Authentication for SQL VM

1. Go to Azure Active Directory in the Azure Portal

2. Go to “Roles and administrators” and search for the role “Directory readers”

   

3. Choose the role “Directory Readers” and add the managed identity of the MSSQL VM

   

   

4. Go to “SQL virtual machine” and select “Security Configuration”

   

5. As “Managed identity type” choose “User-assigned managed identity” and enable “eplan-mssql-dev”

   

Allow Azure AD group members to access the database instance

1. Create a group in Azure AD and add members to this group. Azure AD > Groups > New Group

   

2. Login to the VM
3. Start Commandline (CMD)
4. At the prompt enter sqlcmd
5. Create a login for the previously created Azure AD group

1> CREATE LOGIN eplanreader FROM EXTERNAL PROVIDER;
2> go

Login as Database Administrator

1. Login into the VM as Administrator
2. Start “SQL Server management Studio Management Studio” (SSMS)
3. Login with Authentication “Azure Active Directory - Integrated”