Azure SQL configure Azure AD user authentication (Manual)
Posted On August 15, 2019
When moving your applications to the cloud, it makes sense to start using Azure Services to get the best service, highest availability (SLA) and worry free maintenance provided by Azure. The next step is to use Azure AD identities with Azure SQL Database.
Within a few steps you will have Azure AD user authentication setup.
Step 1: To apply the change we will need to login with an AAD (synced) user that is admin on the SQL Server. On your SQL Server go to Active Directory admin and configure an desired account to be admin. We will login to the SQL server with this account.
Step 2: Open SQL Management Studio (Version 18 or later, download link: SQL Management Studio and connect to your Azure SQL server with the FQDN and using Active Directory Password Authentication or Active Directory Universal with MFA support.
Once you have been logged on you should see your server and database.
Change the Username/groupname in the query below to the Azure AD UPN (email@example.com) or the Azure AD group display name (SG – SQL Users). Open a New Query and run the command on the Master Database
CREATE USER "<username/groupname>" FROM EXTERNAL PROVIDER;
Step 4: Now open again a New Query window and run the above command again on the database where you want to assign permissions.
Step 5: In the same Query Windows as in step 4, select the right role membership, fill in the same User or group from step 3 and 4, and run the query again.
EXEC sp_addrolemember 'db_datareader', '<username/groupname>'; or EXEC sp_addrolemember 'db_datawriter', '<username/groupname>';
You are now done. If you need to add a defenition, this works in the same way: