Enable Secure Database Authentication Using Azure AD
In this IT Pro Challenge, learners are introduced to skills required to secure database authentication using AAD. Exercises in this lab teach users how to enable AAD, firewall, and virtual network security. The topics covered in this lab are critical for learners to be effective in system administrator roles in IT.
Microsoft Azure SQL Database gives a relational database setting for cloud and business applications. To better preserve client data, firewalls block network access to the database server until access is given based on IP address or Azure Virtual network traffic origin.
In this hands-on lab, learners will learn how to secure the Azure SQL Database and use azure active directory effectively to enable AAD authentication. The other guided challenges in this series are "Enable Database Auditing with Azure SQL Database" and "Enable Advanced Database Security with Azure SQL Database."
Understand the Scenario
In this virtual lab, you are a system administrator for a company that is migrating its database services from its data center to Azure. Your job is to create an Azure SQL Database and use security best practices to enable Azure Active Directory (AAD) authentication. You will also deploy an Azure Virtual Machine (VM)that hosts Microsoft SQL Server and allow AAD authentication to the Azure SQL Database, as a proof of concept. To accomplish this task, learners will use an Azure resource group that initially contains no resources. They will create the necessary resources to complete the challenge with the help of corp-datalod12281544 resource group.
Create an Azure SQL Database and enable AAD authentication
Azure Active Directory (AAD) is Azure's favored multi-tenant cloud directory service. It is an authenticating security principal that enables the application of various kinds to validate and manage Kusto services consistently. In this first section of the lab, you will create an Azure SQL database and enable AAD authentication. To accomplish this, you will:
- Create an Azure SQL Database on a new logical Server within the East US region and create a Server admin. For Compute + storage, use Standard S0 with 10 DTUs and 250GB.
- For the database, on the Query editor, attempt to log in by using SQL Server authentication.
- Enter the Client IP address from the error message and change the Firewall and virtual network settings, so the
IP address you recorded earlier is allowed.
- On the Query editor blade for the new database, use SQL Server authentication to test a query to select all the rows from the SalesLT.Customer table and verify the number of rows under the Messages tab and save the query as query.sql in the folder of your choice.
- On the Query editor blade, use Active Directory authentication to attempt to log in.
- On the new logical Server, set the Active Directory admin and verify the number of rows under the Messages tab.
Create an Azure VM with SQL Server and verify AAD authentication
A virtual machine is an image, which functions as an actual computer. In other words, it is building a computer within a computer. In this section of the lab, you will:
- Create an Azure VM and configure the VM to use Windows Server 2016 Datacenter and include Microsoft SQL Server 2017 Developer. Set the size of the VM to Standard B2ms with HDD managed disks and enable RDP access. Create a Server admin and turn off the Auto Shutdown option and disable all monitoring options.
- For SQL Server, use TCP Port 1433 and enable SQL Authentication and set SQL connectivity to Private.
- Connect to an RDP session for the Azure VM, and in Server Manager, disable IE Enhanced Security Configuration for the local server.
- Use SSMS to connect to the logical server by using Active Directory authentication. Access the database on the logical server and test a query to select all the rows from the SalesLT.Customer table and close SSMS and minimize the RDP session.
Finally, you will check and verify that the virtual machine was created successfully. You can connect to the database via SQL Server authentication and to the database via Azure Active Directory authentication.
Secure the Azure SQL Database by using firewall and virtual network security
Virtualization security is the common standards, methods, and rules that secure the stability of a virtualization environment. In this section of the lab, you learn that using the logical server setting, Allow Azure services to access the server is not best practice, as it allows access from any Azure services. Therefore, you now want to harden security for the logical server. To accomplish this, you will change the Firewall and virtual network settings so that "Allow Azure services to access the server" is Off and restore the RDP session. Next, you will use SSMS to attempt to connect to the logical server again by using Active Directory authentication and add the Client IP to the firewall rules. Finally, in the Azure portal, for the logical server, you will change the Firewall and virtual network settings by deleting the existing firewall rule and adding the existing virtual network and subnet to enable a service endpoint for secure access only from the virtual network.
Lab Summary Conclusion
After completing the "Enable Secure Database Authentication Using Azure AD" virtual lab, you will have accomplished the following:
- Created an Azure SQL Database and enabled AAD authentication.
- Created an Azure VM with SQL Server and verified AAD authentication.
- Secured the Azure SQL Database using Firewall and Virtual Network Security.