Azure AD Authentication For Azure SQL Server Managed Databases

Introduction

In this article, we are going to cover how we can grant access to Azure SQL Server-managed Databases. To do this we need the SQL Server along with configured database in Azure. Let's first create the SQL Server in order to work with the SQL database.

Table of Contents

• Azure SQL Server Setup 
• Azure SQL Database Setup
• Configure the Azure Active Directory Admin
• Azure Active Directory Authentication using SSMS
• Grant access for other users
• Conclusion

Azure SQL Server Setup

Step 1 

Open the Azure Portal, click here to access the portal, and log in with the credentials. To work with SQL Server we need a resource group to be already set up for a particular region (US, Europe, etc).

Step 2 

Type the SQL Server in the search bar to navigate to the SQL setup page. 

Step 3

Click on the Create button in the top left corner to access the Azure SQL server page.

Step 4

If the resource group has already been created choose the exact resource group from the drop-down list or else there is an option Create new in the down where we can create a new resource group by accessing that

1. Make sure you enter the server configuration details 
2. Administrator account (if required )

Click on the Review + Create button to set up the Server configuration inside the resource group and it will take a min to be configured in Azure.

Let's create the SQL database under that SQL Server. To do that on the right side there is a button named create Database click on that to make configuration for creating the SQL database.

Azure SQL Database Setup

At first, it will initially take the project details from the subscription itself like the resource group so that we need to just add the database configuration details to create a SQL database.

Once after clicking on the Create button, it will initialize the process and then the database will be created under the server. Below you can see in the figure.

In order to set up the Azure Active Directory Authentication, we need to configure the Active Directory Admin for the SQL Server we have deployed under the Azure Resource group.

Configure the Azure Active Directory Admin

Open SQL Server and in the left side menu under the settings, we will find the option as Active Directory Admin. Click on that and it will take you to the next page to set up the admin configuration. Click on the Set Admin and search for the admin email to whom you want to be added as the admin and once you select and save the admin details it will be shown the Active Directory page, see the below image.

Now we have set up the admin who can centrally access the SQL server and also have the privilege to grant access to others as well. Open the SQL Server management studio to check whether we are able to access it with admin credentials

Azure Active Directory Authentication using SSMS

Open the SSMS (SQL Server Management Studio), add the server name and choose the Azure Active Directory - Universal with MFA (Multi-Factor Authentication). Add the admin email Id to access the server and once we click on the connect button it will take us through the Microsoft Authentication in order to access the Database. We will use the admin credentials for Microsoft Authentication, see the attached image below

It will authenticate via Azure Active Directory from Microsoft and then it will ask to set up the firewall against the SQL server to grant permission for this system IP address. Once you click on Ok you will have an access to the Database which we have created before.

Note
No need to change any settings in the Network Firewall it is automatically taken care of by the Azure itself.

Grant access for other users

After logging in with admin credentials, you will find multiple options for granting access to the SQL server and the SQL database, which will be covered in this article.

• SQL Server Basic Authentication 
• Azure Active Directory Authentication

SQL Server Basic Authentication 

To Grant access for others, we need to create the user under the server level. Then we will add the roles for that specific user to access the database to grant permission for the reading and write operations. I have added the base username and password for the demo purpose you can choose as per your requirements.

 Step 1

-- switch to master
-- create a new sql login and give them a password
create login Appuser with password = 'admin@123';

Step 2

-- connect to Database
-- create a new SQL user for that login
create user Appuser for login Appuser;

Step 3

-- untill you run the below two commands, Appuser has no access to read or write data.
alter role db_datareader add member Appuser;
alter role db_datawriter add member Appuser;

We have successfully created a new user to access the database. See the below image for accessing the SQL server with newly created credentials.

Azure Active Directory Authentication

To grant access to a particular tenant inside your organization here is the easiest way that we can grant access for fellow members as individual contributors. This can only work when the tenant should be matched with the same organization.

Step 1

/*switch to master */
create user "[email protected]" from external provider

Step 2

/*Switch to database and execute the for reader or writer access - Azure AD*/
exec sp_addrolemember 'db_datareader', '[email protected]'
exec sp_addrolemember 'db_datawriter', '[email protected]'
Go 

Step 3

grant execute on schema :: dbo To "[email protected]"

Conclusion

The above article demonstrates the Azure Active Directory Setup with the SQL Server Basic Auth for Azure SQL Server managed Databases

Thank you for reading, please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

Keep Learning ...!