5

Securing Azure SQL Databases

In the previous chapter, we covered recipes for protecting the integrity of Azure VMs by ensuring that they were updated, antimalware was enabled, and disks were encrypted disks.

With public cloud provider platforms, the shared responsibility model means that while the provider is responsible for providing security and control mechanisms of the platform hosting that data that can be enabled, the customer is always responsible for correctly implementing and configuring those controls and ensuring appropriate governance and operations.

To avoid doubt, it is critical to call out that the customer is always responsible for the data stored on those platforms and its operation.

We can use an analogy of a rented property and your relationship with the landlord. While the landlord will be responsible for providing doors and windows and the controls such as locks, alarms, and a gated entrance for vehicle access with CCTV to monitor the property, you are responsible for ensuring that you have shut the doors, set the alarm, closed the gates, and operated the CCTV.

If you are not enabling and configuring these controls from the platform provider to secure your data, you are negligent in your duty of care for that data.

This chapter will teach you how to secure and protect Azure databases.

By the end of this chapter, you will have covered the following recipes to secure Azure databases:

  • Implementing a service-level IP firewall
  • Implementing a private endpoint
  • Implementing Azure AD authentication and authorization

If you wish to have a primer on securing databases or learn additional functionality, you can read the following Microsoft articles:

Technical requirements

For this chapter, it is already assumed that you have an Azure AD tenancy and an Azure subscription from completing the recipes in previous chapters of this cookbook. If you skipped straight to this section, the information to create a new Azure AD tenancy and an Azure subscription for these recipes is included in the following list of requirements.

For this chapter, the following are required for the recipes:

Implementing a service-level IP firewall

In many workload scenarios, the first line of protection in a defense-in-depth approach to security is a network layer firewall to act as a layer 3 network traffic packet filter.

This recipe will teach you how to restrict network access to your Azure SQL database. We will configure rules for the native Azure service-level IP firewall service to protect your Azure databases.

Getting ready

This recipe requires the following:

  • A device with a browser, such as Edge or Chrome, to access the Azure portal: https://portal.azure.com
  • Access to an Azure subscription, where you have access to the Owner role for the Azure subscription
  • Access to an Azure SQL database; we will step through this process in the following Getting ready tasks

Continue with the following Getting ready tasks for this recipe:

  • Creating an Azure SQL database

Getting ready task – creating an Azure SQL database

Perform the following steps:

  1. Sign in to the Azure portal: https://portal.azure.com.
  2. From the top menu of the Azure portal, in the Search box, type SQL databases, and click on SQL databases from the results:
Figure 5.1 – Searching for a resource

Figure 5.1 – Searching for a resource

  1. Click + Create from the top toolbar from the SQL databases page.
  2. From the Basics tab of the Create SQL Database page, under the Project details section, set your Subscription as required. Then, under Resource group, select Create new, enter a Name, and click OK.
  3. Under the Database details section, enter a Database name as required, and for Server, click Create new.
  4. From the Create SQL Database Server page, under the Server details section, set a Server name (this must be globally unique) and Location as required:
Figure 5.2 – Create SQL Database Server

Figure 5.2 – Create SQL Database Server

  1. Under the Authentication section, set Authentication method to Use SQL authentication, and then enter a Server admin login and Password as required. Then, click OK:
Figure 5.3 – Setting up authentication

Figure 5.3 – Setting up authentication

  1. Set Workload environment to Development to reduce running costs for this recipe:
Figure 5.4 – Setting the environment

Figure 5.4 – Setting the environment

  1. From the Networking tab, under the Network connectivity section, set Connectivity method to Public Endpoint:
Figure 5.5 – Setting network connectivity

Figure 5.5 – Setting network connectivity

  1. For this recipe, no other configuration needs to be reviewed or required; click Review + Create.
  2. Click Create on the Review + create tab.
  3. A notification will display that the resource deployment succeeded.
  4. Click on Go to resource so that you’re ready for the first task for this recipe.

This Getting ready task is complete. You are now ready to continue the main tasks for this recipe, which involve setting a service-level IP firewall.

How to do it…

This recipe consists of the following tasks:

  • Setting server-level firewall rules
  • Setting database-level firewall rules
  • Cleaning up resources

Task – setting server-level firewall rules

Perform the following steps:

  1. From the created Azure SQL database page, click Overview from the top of the left toolbar:
Figure 5.6 – Configuring access

Figure 5.6 – Configuring access

  1. From the Overview page, click Set server firewall from the top toolbar:
Figure 5.7 – Set server firewall

Figure 5.7 – Set server firewall

  1. From the Public access tab, under Public network access, click Selected networks:
Figure 5.8 – Selected networks

Figure 5.8 – Selected networks

  1. Under Firewall rules, click + Add a firewall rule:
Figure 5.9 – Add a firewall rule

Figure 5.9 – Add a firewall rule

  1. Add Rule name, Start IP, and End IP for your scenario:
Figure 5.10 – Setting the firewall information

Figure 5.10 – Setting the firewall information

  1. Click Save.

With that, you have set a server-level firewall rule. In the next task, we will set a database-level firewall rule.

Task – setting database-level firewall rules

Perform the following steps:

  1. From the created Azure SQL database page, click Query editor from the left menu:
Figure 5.11 – Query editor

Figure 5.11 – Query editor

  1. Enter your SQL server authentication credentials from the Query Editor page, then click OK:
Figure 5.12 – Authenticating to the database

Figure 5.12 – Authenticating to the database

  1. From the Query Editor area, enter EXECUTE sp_set_database_firewall_rule N'Example Cookbook DB Rule', '<your-device_publicIP>','<your-device_publicIP>'; as a query and hit Run:
Figure 5.13 – Running a query

Figure 5.13 – Running a query

  1. From the top toolbar of the Query Editor area, click on + New Query.
  2. From the newly opened Query pane, enter SELECT * FROM sys.database_firewall_rules ORDER BY name; as a query and hit Run:
Figure 5.14 – Running a query

Figure 5.14 – Running a query

  1. You will see the rule you created in the Results pane:
Figure 5.15 – Viewing the query’s result

Figure 5.15 – Viewing the query’s result

With that, you have set a database-level firewall rule. In the next task, we will clean up the resources that were created in this recipe.

Task – cleaning up resources

Perform the following steps:

  1. From the Search box area in the Azure portal, type resource groups and select Resource Groups from the listed Services results.
  2. From the Resource groups page, select the resource group we created for this recipe and click Delete resource group; this will delete all the resources that were created as part of this recipe:
Figure 5.16 – Delete resource group

Figure 5.16 – Delete resource group

With that, you have cleaned up the resources that were created in this recipe.

How it works…

For this recipe, we looked at implementing a service-level IP firewall. This allowed us to remove access from all networks and then explicitly define the public internet IP addresses where we wish to access our SQL servers and databases for remote access purposes by administrators.

As a getting ready task, we created an Azure SQL database to illustrate the public PaaS service we want to restrict network access to.

We wanted to show you how to ensure that connections can only be made from those networks and specific IPs we explicitly allow.

See also

Should you require further information, you can refer to the following Microsoft Learn articles:

Implementing a private endpoint

One of the foundations of securing resources is to reduce the attack surface area and minimize exposure to public network access.

Azure PaaS services’ inherent nature and concern are that they have public endpoints, which means any vulnerabilities are exposed and can be exploited. One of the best practices we should follow in our security model for cloud services is to limit public access to resources wherever possible and adopt a network model where private IP addressing is used wherever possible.

Azure Private Link is an Azure service that enables you to connect to public endpoint PaaS services such as Azure SQL database and Azure Storage from an Azure virtual network.

Using the Private Link capability, you can privately connect to a PaaS service by creating a private endpoint (in place of a public endpoint) to connect to.

The PaaS service endpoint is never exposed to the internet, and traffic to and from the service never traverses the public internet. As such, traffic stays private on the Microsoft backbone network.

This recipe will teach you how to implement a private endpoint to provide non-public secure endpoint access to your Azure databases.

Getting ready

This recipe requires the following:

  • A device with a browser, such as Edge or Chrome, to access the Azure portal: https://portal.azure.com
  • Access to an Azure subscription, where you have access to the Owner role for the Azure subscription
  • Access to an Azure SQL database instance; we will step through creating this as a Getting ready task
  • An Azure Virtual Network; we will step through creating this as a Getting ready task

Continue with the following getting ready tasks for this recipe:

  • Creating an Azure Virtual Network
  • Creating an Azure SQL database

Getting ready task – creating an Azure Virtual Network

Perform the following steps:

  1. Sign in to the Azure portal: https://portal.azure.com.
  2. In the search bar, type virtual networks; click on Virtual networks from the list of services shown.
  3. From the Virtual networks blade, click on the + Create option from the top menu of the blade, or use the Create virtual network button at the bottom of the blade. Set the Project and Instance details settings as required on the Basics tab.
  4. No further configuration is required for this recipe. Click Review + create.
  5. On the Review + create tab, click Create.
  6. You will receive a notification that the deployment succeeded.

This getting ready task is complete.

Getting ready task – creating an Azure SQL database

Perform the following steps:

  1. Sign in to the Azure portal: https://portal.azure.com.
  2. Navigate to the SQL databases page or from the top menu of the Azure Portal; in the Search box, type SQL databases, and click on SQL databases from the results.
  3. Click + Create from the top toolbar from the SQL databases page.
  4. From the Basics tab of the Create SQL Database page, under the Project details section, set your Subscription as required. Then, under Resource group, select Create new, enter a Name, and click OK.
  5. Under the Database details section, enter a Database name as required, and for Server, click Create new.
  6. From the Create SQL Database Server page, under the Server details section, set a Server name (this must be globally unique) and Location as required.
  7. Under the Authentication section, set Authentication method to Use SQL authentication, and then enter a username and password as required. Then, click OK.
  8. Set Workload environment to Development to reduce running costs for this recipe.
  9. No additional configuration is required for this recipe; click Review + Create.
  10. Click Create on the Review + create tab.
  11. A notification will display that the resource deployment succeeded.

This getting ready task is complete. You are now ready to continue the main tasks for this recipe of implementing a private endpoint.

How to do it…

This recipe consists of the following tasks:

  • Creating an Azure SQL private endpoint
  • Cleaning up resources

Task – creating an Azure SQL private endpoint

Perform the following steps:

  1. From the created Azure SQL database page, click Configure under Configure access from the Getting started page:
Figure 5.17 – Configuring access

Figure 5.17 – Configuring access

  1. From the Networking page, ensure that, from the Public access tab, Public network access is set to Disable:
Figure 5.18 – Disabling public network access

Figure 5.18 – Disabling public network access

  1. Navigate to the Private access tab and, under Private endpoint connections, click Create a private endpoint:
Figure 5.19 – Create a private endpoint

Figure 5.19 – Create a private endpoint

  1. From the Basics tab of the Create a private endpoint page, set the Project Details and Instance details properties as required:
Figure 5.20 – Setting information for the private endpoint

Figure 5.20 – Setting information for the private endpoint

  1. Click Next : Resource, and then click Next: Virtual Network.
  2. From the Virtual Network tab, ensure that you have selected the Virtual network and Subnet properties you want to deploy the private endpoint:
Figure 5.21 – Setting up a virtual network

Figure 5.21 – Setting up a virtual network

  1. Click Next: DNS, click Next: Tags, and then click Review + create.
  2. From the Review + create tab, click Create.

With that, you have implemented a private endpoint. In the next task, we will clean up the resources that were created in this recipe.

Task – cleaning up resources

Perform the following steps:

  1. From the Search box area in the Azure portal, type resource groups and select Resource Groups from the listed Services results.
  2. From the Resource groups page, select the resource group we created for this recipe and click Delete resource group; this will delete all the resources that were created as part of this recipe:
Figure 5.22 – Delete resource group

Figure 5.22 – Delete resource group

With that, you have cleaned up the resources that were created in this recipe.

How it works….

In this recipe, we looked at implementing Azure Private Link to create a private endpoint that can connect to our PaaS services from an Azure virtual network. The benefit is that we do not expose our PaaS services to the public internet, and traffic can remain private over the Microsoft backbone.

As a getting ready task, we created an Azure Virtual Network Azure SQL database to illustrate the public PaaS service we want to connect to privately.

We wanted to demonstrate the ability to configure a public PaaS service to ensure that the connection traffic stays on the Microsoft backbone so that it bypasses the public internet.

There’s more…

Now that we understand the concepts of private connectivity for public PaaS services such as Azure SQL database, we can explore this capability further. In the following Microsoft Learn article, you will learn how to privately connect a PaaS web application to a database’s private endpoint; this ensures traffic only passes via the virtual network to the database and never over the internet:

See also

Should you require further information, you can refer to the following Microsoft Learn articles:

Implementing Azure AD authentication and authorization

With cloud-based implementations of databases such as Azure SQL, we can centrally manage, control, and protect the identities of users who access the databases when we use the Azure Active Directory (Azure AD) Cloud Identity Provider service.

Microsoft provides Azure AD as a fully managed Identity Provider (IDP) platform provided as Software-as-a-Service (SaaS). Its primary function is to manage and control resource access through Authentication and Authorization.

Azure AD provides a mechanism to centrally authenticate users/groups for admin access to an Azure SQL database, without requiring local database accounts.

This recipe will teach you how to implement a centralized, controlled, and secure access management method for connecting to your Azure SQL databases using Azure AD authentication in place of local database accounts.

Getting ready

This recipe requires the following:

  • A device with a browser, such as Edge or Chrome, to access the Azure portal: https://portal.azure.com
  • Access to an Azure subscription, where you have access to the Owner role for the Azure subscription
  • Access to an Azure AD account that has a Global Administrator role
  • Access to an Azure SQL database; we will step through this process in the following Getting ready tasks

Continue with the following getting ready tasks for this recipe:

  • Creating an Azure SQL database

Getting ready task – creating an Azure SQL database

Perform the following steps:

  1. Sign in to the Azure portal: https://portal.azure.com.
  2. Navigate to the SQL databases page or from the top menu of the Azure portal; in the Search box, type SQL databases, and click on SQL databases from the results.
  3. Click + Create from the top toolbar of the SQL databases page.
  4. From the Basics tab of the Create SQL Database page, under the Project details section, set a Subscription as required. Then, under Resource group, select Create new, enter a Name, and click OK.
  5. Under the Database details section, enter a Database name as required, and for Server, click Create new.
  6. From the Create SQL Database Server page, under the Server details section, set a Server name (this must be globally unique) and Location as required.
  7. Under the Authentication section, set Authentication method to Use SQL authentication and enter a username and password as required. Then, click OK.
  8. Set Workload environment to Development to reduce running costs for this recipe.
  9. No additional configuration is required for this recipe; click Review + Create.
  10. Click Create on the Review + create tab.
  11. A notification will display that the resource deployment succeeded.

This getting ready task is complete. You are now ready to configure Azure AD authentication for accessing Azure SQL.

How to do it…

This recipe consists of the following tasks:

  • Configuring authentication to Azure AD for Azure SQL databases
  • Cleaning up resources

Task – configuring authentication to Azure AD for Azure SQL databases

Perform the following steps:

  1. Sign in to the Azure portal: https://portal.azure.com.
  2. Navigate to the SQL Servers page or from the top menu of the Azure portal; in the Search box, type SQL server, and click on SQL servers from the results.
  3. Open the page for your created SQL server and click Azure Active Directory under the Settings section:
Figure 5.23 – Azure Active Directory settings

Figure 5.23 – Azure Active Directory settings

  1. From the Azure Active Directory page, click Set admin from the top toolbar:
Figure 5.24 – Setting an Azure Active Directory admin

Figure 5.24 – Setting an Azure Active Directory admin

  1. Search for a user identity as required from your directory:
Figure 5.25 – Selecting a user

Figure 5.25 – Selecting a user

  1. Click the user identity and click Select.
  2. From the Azure Active Directory page, under Azure Active Directory authentication section only, check the Support only Azure Active Directory authentication to this server box.
  3. From the pop-up dialog box, click Yes:
Figure 5.26 – Enable Azure AD authentication only

Figure 5.26 – Enable Azure AD authentication only

  1. From the top toolbar, click Save:
Figure 5.27 – Saving your settings

Figure 5.27 – Saving your settings

This task is completed. In the next task, we will clean up the resources that were created in this recipe.

Task – cleaning up resources

Perform the following steps:

  1. From the Search box area in the Azure portal, type resource groups and select Resource Groups from the listed Services results.
  2. From the Resource groups page, select the resource group we created for this recipe and click Delete resource group; this will delete all the resources that were created as part of this recipe:
Figure 5.28 – Delete resource group

Figure 5.28 – Delete resource group

With that, you have cleaned up the resources that were created in this recipe.

How it works…

In this recipe, we looked at centrally managing the identity of database users using Azure AD authentication and removing access from local SQL authentication accounts.

As a getting ready task, we created an Azure SQL database to configure the Azure AD authentication mechanism.

See also

Should you require further information, you can refer to the following Microsoft Learn articles:

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset