Azure SQL configure Azure AD user authentication (Manual)
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.
Step 3: Now we will be going to add/create the AAD user/group in SQL. If you need to create a SQL user, please use the following manual: Azure SQL, create users and assign permissions
Change the Username/groupname in the query below to the Azure AD UPN (username@domain.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:
GRANT VIEW Definition TO '<username/groupname>'
About The Author
