Azure SQL Database Security: 9 Features You Should Know

Databases are where organizations hold their “crown jewels” – their data. If you’re running or looking to run SQL on Azure, Azure provides security for the physical, logical, and data layers of services. Basic Azure SQL database security can be enabled using a variety of
native security features. Part of these features are enabled according to your requirements and many of them are enabled by default. Here is a run-down of the top 9 database security features available to you in Azure SQL.

1. Firewall rules

A Firewall can prevent all incoming connections to a database server except the explicitly defined IP addresses. Azure firewalls also manage virtual network rules. There are two types of firewall rules: Server-level rules and Database-level rules.

1. Server-level firewall: These rules enable access to an entire Azure SQL Server / logical server. Server-level rules can be created using the Azure portal or PowerShell when the creator has rights of subscription owner or subscription contributor rights. When using T-SQL to define the rule you must connect to the DB instance using a server-level principal login or Azure AD administrator. A best practice is to use server-level firewall rules by administrators when you have many databases that have the same access requirements and you don’t want to spend time configuring each database individually.
2. Database-level firewall: These rules can be created once a server-level firewall is enabled. These rules can be created for an individual database and the rules are saved in the same database (unlike server-level rules that are saved in the master database). Azure SQL limits database-level firewall rules to 128 rules. It is recommended to use database-level rules to make the database more secure and portable.

2. Virtual Network

Virtual Network rules are a feature of firewall security that relates to defined subnets in virtual networks. The goal is to restrict the incoming traffic to Azure SQL Databases and Azure Data warehouse. It ensures that even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the subnets you specify is routed via the Microsoft network.

To implement the security feature, virtual network service endpoint and virtual network rules are required. Also, virtual network rules are part of the configuration of the virtual network service and to configure rules, we need to have a virtual network service endpoint. Virtual network service endpoint represents virtual network subnets having attributes including the name of Azure service with which subnets communicate.

3. Advanced Threat Protection

SQL Advanced Threat Protection (ATP) provides important security capabilities such as Data Discovery and Classification, Vulnerability Assessment, and Threat Detection.

1. Data Discovery and Classification: The purpose of this capability is to provide visibility where sensitive data resides.
2. Vulnerability Assessment: Helps to assess the vulnerabilities in databases creating visibility into security state and configuration. These rules play a significant role in meeting the security compliance requirements.
3. Threat Detection: Enables you to detect and respond to threats by providing security alerts based on suspicious database activities, potential vulnerabilities, SQL injection attacks, or anomalous activities.

4. Access control

Azure SQL Databases and Azure Data warehouse utilize Azure firewall rules, authentication, and authorization to limit the connectivity and users’ actions.

1. Authentication supports two types: SQL Authentication using login credentials for authentication and Azure Active Directory Authentication using Azure AD’s managed identities for authentication.
2. Authorization defines how users are granted privileges to access the databases/Data warehouse. The user’s privileges are controlled by role membership and object-level permission in the user’s account.

5. Azure AD authentication

Azure Active Directory (AD) authenticates the connection using Azure AD identities. Azure AD authentication is a single-point central ID management system used to manage database users and permission across various applications on-premises or cloud. Benefits include many of the below:

1. Stop the proliferation of user identities across database servers.
2. Manage password rotation in a single place.
3. Manage database permissions using external groups.
4. Eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
5. Azure AD authentication uses contained database users to authenticate identities at the database level.
6. Supports token-based authentication for applications connecting to SQL Database.
7. Azure AD authentication supports ADFS (domain federation) or native user/password authentication for a local Azure Active Directory without domain synchronization.
8. Azure AD supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA).

6. Dynamic data masking

Dynamic data masking is a policy-based capability that can be used to hide sensitive data from certain users. It performs masking on the result of a query, without needing to touch or alter the database/table/data. For example, it allows a DBA to perform the action on the database without violating compliance regulations stating that the DBA should not be able to see PII/NPI type data. It can be configured by Azure database admin, server admin, or users having roles of security officers. Caveats:

1. SQL users excluded from masking: Some SQL users or AAD identities get unmasked data from SQL queries.
2. Masking rules: These specify designated fields which need to be masked. The designated fields can be defined using a database schema name, table name, and column name. Other fields will then not be masked.
3. Masking functions: Set of methods that control the masking of a database field.

7. Always encrypted

The “Always Encrypted” feature protects sensitive data (data during the movement, at rest, and while in use) such as social security numbers or credit card numbers. It allows you to encrypt the data in the client app without revealing the encryption key to SQL servers or Databases. Only the applications or servers which have access to the keys can view the plaintext files of data. The keys for encrypted data are stored in the Azure Key Vault or Windows certificate store.

8. Auditing

Azure SQL auditing tracks all the events and writes them to an audit log. It helps maintain regulatory compliances and can be used to give insight into the anomalies and discrepancies. Auditing in an SQL database can be utilized in many ways:

1. Retain an audit trail of selected events
2. Report
3. Analyze

Azure SQL auditing can be enabled at the database level or server level. When server auditing is enabled, it audits all the databases within the server but, the database level auditing audits the specific database. It’s recommended to enable auditing at the database level to the environment more portable.

Azure Monitor provides access to all the monitoring data from any tier in your Azure environment. Azure Monitor collects logs for most Microsoft Azure services, including Azure Audit, and streams the data to an Azure Event Hub. With Azure Monitor’s diagnostic settings, you can set up your resource-level diagnostic logs and metrics to be streamed to any of three destinations including a storage account, an Event Hubs namespace, or Log Analytics.

Azure Event Hubs is a highly scalable event processing service, capable of processing millions of events per second in real-time. Sending to an Event Hubs namespace is a convenient way for streaming Azure logs from any source to external SIEM tools. Data extracted from various tiers of Azure are classified as follows:

1. Application monitoring data: This data includes application logs, performance traces, and user telemetry.
2. Guest OS monitoring data: Data such as Linux syslog and windows system events.
3. Azure resource monitoring data: Data about operations of Azure resources.
4. Azure subscription monitoring data: Data such as health logs and Azure resource manager audits.
5. Azure tenant monitoring data: Data from Azure Active Directory audits and sign-ins.

Importantly, Azure Events allow you to pass the activity data to database security products such as Imperva Sonar.

9. TDE with Azure SQL

Transparent Data Encryption (TDE) performs real-time encryption and decryption on the database, backups, and transaction log of data-at-rest leaving the application unchanged. Azure enables this by default on all the new databases but not on an old database which can be enabled manually. The master database is utilized to perform TDE operations-based user groups so TDE is not applied to the master database. Transparent data encryption uses a database encryption key for encrypting the database storage. There are two types of key protection.

1. Service-managed transparent data encryption: By default, the database encryption key uses a built-in server certificate for securing the key.
2. Bring your own key (asymmetric key): Asymmetric key is stored in the key vault. With BYOK support you can have full control i.e. when and who will access keys.

Learn more about how the Imperva Data Security Fabric works to augment Basic Azure SQL database security.