6. Security

Security is a major concern for organizations when migrating to the cloud, making them hesitant to actually do so. The major security concerns with the cloud include ones about physical server security, data security at rest and in motion, and data infiltration. Microsoft provides strong security protection at the physical, logical, and data layers of Azure services. Microsoft datacenters are among the most secure datacenters in the world.

Azure SQL Database and SQL Managed Instance provide multiple layers of security to control access to databases using SQL Server or Active Directory (AD) authentication as well as firewall rules, which limit access to data through role-based permissions and row-level security.

SQL Database and SQL Managed Instance provide proactive security using Advanced Threat Protection, granular access control, and strong authentication. In addition to this, dynamic data masking and row-level security can be used to secure data.

Transparent...

Network security

SQL Database limits access to databases through firewall rules, which are authentication techniques that require users to log in to a database with a valid username and password. Firewall rules are not valid for SQL Managed Instance, so you need to configure network security group (NSG) inbound and outbound security rules. SQL Database and SQL Managed Instance further control access to underlying data through role-based permissions and row-level security. We'll now look at different access control methods in detail.

Firewall rules

SQL Database uses firewall rules to limit access to authorized IPs and block access to unauthorized IPs. This is the first level of access control provided by SQL Database. Firewall rules can be created at the server level and the database level.

When a SQL database is provisioned, it's inaccessible to everyone. To make it accessible, you first need to add a server-level firewall rule. A firewall allows an IP or a range...

Managing server-level firewall rules using the Azure portal

In this section, you will learn how to create, delete, and update server-level firewall rules from the Azure portal, by performing the following steps:

1. Log in to the Azure portal (https://portal.azure.com) using your Azure credentials.
2. Find and open the toyfactorytemp Azure SQL server to manage the firewall for it.
3. From the Azure SQL server Overview page, select the Set server firewall option:
   

   Figure 6.2: Setting the firewall

4. On the Firewall settings page, notice that no firewall rules have been configured:
   

   Figure 6.3: The Firewall settings page

   Also, notice that it automatically detects and displays the public IP address of the computer from which the portal has been opened.

5. To add the client IP address, select Add client IP from the top menu:
   

   Figure 6.4: Adding the client IP address

   A firewall rule with the same start and end IP as the client IP address is added. You can change the rule name if you wish...

Authentication

Authentication refers to how a user identifies themselves when connecting to a database. There are two types of authentication mechanisms: SQL Server authentication and Azure AD authentication.

SQL authentication

This is similar to what we have in on-premises SQL servers; that is, it requires a username and password. When provisioning a SQL database, you have to provide a server admin login with a username and password. The server admin user has admin access to the SQL server and it's mapped to the dbo user in each user database and therefore has dbowner access on all databases in a particular SQL server.

There can be only one server admin account in a SQL database.

Azure AD authentication

Azure AD authentication allows users to connect to a SQL database and managed instance by using the identities stored in Azure AD.

Azure AD

When you create an Azure account, it creates a default directory for your account. This is where you can add users and...

Authorization

Authorization refers to any sort of access control mechanism. In the context of SQL Server, it starts at the server scope or database scope for contained users. For example, a user may have access to read one set of tables and to read-write another set of tables.

The authorization is done by adding the user to the relevant server-level or database-level roles.

Roles have a set of permissions applied to them; for example, the db_datareader database-level role allows users to read tables from a database.

Let's look at server-level administrative roles available with SQL Database.

Server-level administrative roles

There are two server-level administrative roles that reside in the master database: dbcreators and loginmanagers for SQL Database. Server roles are not supported for Azure AD admin or Azure AD users at the time of writing this book.

dbcreators

Members of database creators (dbmanager) are allowed to create new SQL databases. To create...

Activity: Audit COPY_ONLY backup events on SQL Managed Instance using audit logs

In the previous activity, we saw the steps to enable a server audit for SQL Managed Instance. In this activity, we will use the server audit to track user-initiated COPY_ONLY database backups.

SQL Managed Instance has the ability to take database backups with the COPY_ONLY option on Azure Blob Storage. By default, all the databases are protected using a service-managed Transparent Data Encryption (TDE) key and COPY_ONLY backups are not allowed.

But there could be scenarios where a user who has higher access on an instance can disable service-managed TDE and take a COPY_ONLY backup of a database. You can track these events using audit logs.

Steps to configure an audit for backup and restore events

We have already seen how to configure a storage container for audit logs in a previous demo. Here we will create a server audit specification to track backup events.

You can skip the following steps...

Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts the user data at rest and therefore protects the database from offline malicious activity. TDE is enabled by default in newly deployed SQL databases and managed instances. TDE encrypts/decrypts the database, transaction log, and database backups in real time without any change in the application.

TDE works by encrypting each page before writing it to disk and decrypting each page when reading it from the disk. The encryption is done using a symmetric key known as a database encryption key (DEK). The DEK is protected by a TDE protector, which is either a service-managed certificate or a customer-managed asymmetric key stored in a key vault.

For more details on TDE, refer to https://docs.microsoft.com/azure/azure-sql/database/transparent-data-encryption-tde-overview.

Azure Defender for SQL

Azure Defender for SQL groups together the advanced SQL security capabilities, vulnerability assessment and Advanced Threat Protection. Azure Defender for SQL is priced at ~$15/per server/month and has a one-month free trial.

To enable Azure Defender for SQL from the Azure portal, navigate to the SQL Server page and then select Security Center from the Security section.

Enable Azure Defender for SQL by clicking on the toggle button. Provide an Azure storage account for the vulnerability assessment and an email address to send alerts. You can choose to run periodic recurring vulnerability scans by enabling the Periodic recurring scans option:

Figure 6.84: Enabling Azure Defender for SQL

We can enable the Auditing feature for better threat investigation. We can choose to enable specific, or all, ADVANCED THREAT PROTECTION features.

The two security features under Azure Defender for SQL are as follows:

• Vulnerability...

Securing data traffic

SQL Database and SQL Managed instance data traffic is always encrypted if the client driver supports SSL/TLS encryption. Data between a managed instance, a SQL database, and an Azure VM or any Azure service never leaves the Azure backbone network. All the communication within Azure happens using this Azure backbone. For on-premises connections, Microsoft recommends setting up Azure ExpressRoute, which helps to avoid sending data over the internet. For public endpoint access, Microsoft peering configuration is required for an ExpressRoute circuit for public communication.

Let's look at how to enforce a minimum Transport Layer Security (TLS) version for SQL Database or SQL Managed Instance.

Enforcing a minimal TLS version for SQL Database and SQL Managed Instance

A minimum TLS version allows users to control the version of TLS used by SQL Database and SQL Managed Instance.

Currently, SQL Database and SQL Managed Instance support TLS 1.0, 1.1...

Activity: Setting a minimum TLS version using the Azure portal and PowerShell for SQL Managed Instance

The process of setting up a minimal TLS version for SQL Database and SQL Managed Instance using the Azure portal is almost the same. In this activity, we are using the SQL Managed Instance Networking tab to enforce a minimum TLS version for SQL Managed Instance, but the same can be done for SQL Database using the Firewalls and Virtual Networks tab.

Using the Azure portal

A minimum TLS version can be easily set using the Azure portal, but we should test application compatibility before enabling it in production:

1. Log in to the Azure portal: https://portal.azure.com.
2. Navigate to SQL Managed Instance and under Security, click on Networking:
   

   Figure 6.85: Networking tab

3. Select a minimum TLS version and click on Save to apply.

Using PowerShell

Use can use these PowerShell commands to set the minimum TLS version:

#setting up variable as per our environment...

Configuring and securing public endpoints in SQL Managed Instance

A managed instance can be deployed to an Azure virtual network for the secure access of data within a private network. A public endpoint on a managed instance allows access to data from outside the virtual network. Using a public endpoint, you can access a managed instance from an on-premises network, a multi-tenant Azure service such as a web app, or Power BI.

In this activity, you will learn how to:

• Configure/manage a public endpoint on a managed instance using the Azure portal.
• Configure/manage a public endpoint on a managed instance using PowerShell.
• Secure public endpoint connections.

Let's look at the following steps to configure a public endpoint on SQL Managed Instance using the Azure portal and PowerShell cmdlets.

This is a two-step process, where you need to enable the public endpoint access and allow public endpoint TCP port 3342 in the managed instance NSG inbound rule. TCP...

Activity: Implementing RLS

In this section, we will look at how to implement RLS using our example of ToyStore Ltd. Mike has been asked to implement RLS so that every customer is able to view and edit only their records. The CustomerAdmin user, however, should be allowed to view and edit all customer records. Follow these steps to complete the activity:

1. Execute the following query to create the dpl.Customers table and populate it with sample records:

   CREATE TABLE Customers (
   CustomerID int identity, Name sysname, CreditCardNumber varchar(100), Phone varchar(100), Email varchar(100)
   )
   Go
   INSERT INTO Customers VALUES('Mike',0987654312345678,9876543210,'mike@
   outlook.com'), ('Mike',0987654356784567,9876549870,'mike1@outlook.
   com'), ('Mike',0984567431234567,9876567210,'[e-mail]'), (' [e-mail] ',0987654312345678,9876246210,'[e-mail]'),
   ('[e-mail] ',0987654123784567,9876656870...

Activity: Implementing DDM

With RLS implemented in the previous activity, Mike has ensured that the customer can only view their own data; however, to take data security to the next level, he wants to mask some of the sensitive data that is shared by the customer. In order to do this, he has to implement DDM. In this activity, we'll implement DDM to mask the credit card number, phone number, and email ID of a customer:

1. Execute the following query to create a new user and grant select access to the user on the dpl.Customers table:

   CREATE USER TestUser WITHOUT LOGIN; GO
   GRANT SELECT ON dpl.Customers TO TestUser

2. Execute the following query to mask the CreditCardNumber, Phone, and Email columns using different masking functions:

   ALTER TABLE dpl.Customers ALTER COLUMN Phone VARCHAR(100) MASKED WITH (FUNCTION = 'default()')
   GO 
   ALTER TABLE dpl.Customers ALTER COLUMN Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') 
   GO 
   ALTER TABLE dpl.Customers ALTER...

Activity: Implementing Azure Defender for SQL to detect SQL injection and brute-force attacks

Earlier in the chapter, we learned that Advanced Threat Protection automatically detects and alerts you about security issues such as SQL injection, brute-force attacks, and anonymous access.

In this two-part activity, we'll simulate SQL injection and a brute-force attack and study the email alerts raised by Advanced Threat Protection:

1. To configure email alerts for Advanced Threat Protection, open the Azure portal and then open the SQL server you want to configure alerts for.

   Under the Security section, select Security Center. Provide the email address that will receive the notifications, under the ADVANCED THREAT PROTECTION SETTINGS heading, as shown here:

   

   Figure 6.103: Security Center page

2. Click Save to save the settings.

Part 1: Simulating SQL injection

To simulate an SQL injection attack, perform the following steps:

1. Connect to the toystore database...

Summary

Security is one of the deciding factors for an organization when opting to put their data in the cloud.

To connect to a SQL database and a managed instance, the machine's IP address or the client IP address should exist in the firewall settings or NSG rules. If not, the connection request will be denied.

Access to SQL Database can be restricted to one or more virtual networks using service endpoints. Private endpoints for SQL Database further strengthen security by assigning a private IP address to a SQL database from the customer's virtual network.

SQL Managed Instance can be deployed in a virtual network and offers strong security isolation, but SQL Database and SQL Managed Instance support similar secure connection options. SQL Managed Instance also gives an option to opt for a public endpoint connection, which can be secured by using NSG and firewall rules.

SQL Database and SQL Managed Instance allow SQL and Azure AD authentication. An organization...