Security of databases have been always a major focus and concern – after all, databases is where organizations hold their “crown jewels”, their data. In Azure SQL databases can be secured using a variety of Azure capabilities. Azure provides security for the physical, logical and data layers of services. Azure SQL has many security features for the databases running on it, part of them enabled according to your requirements and many of them enabled by default.
Firewall rules: A Firewall can prevent all incoming connections to database server except the explicitly defined IP addresses. Azure firewalls also manage virtual network rules. There are two types of set of firewall rules – Server-level rules and Database-level rules.
- 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.
- Database-level firewall: These rules can be created once 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 database more secure and portable.
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 Datawarehouse. 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 virtual network service endpoint. Virtual network service endpoint represents virtual network subnets having attributes including name of Azure service with which subnets communicates.
Advanced Threat Protection:
SQL Advanced Threat Protection (ATP) provides important security capabilities such as Data Discovery and Classification, Vulnerability Assessment and Threat Detection.
- Data Discovery and Classification: The purpose of this capability is to provide visibility where sensitive data resides.
- Vulnerability Assessment: Helps assessing the vulnerabilities in databases creating visibility into security state and configuration. These rules play a significant role in meeting the security compliance requirements.
- 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.
Azure SQL Databases and Azure Datawarehouse utilize Azure firewall rules, authentication, and authorization to limiting the connectivity and users’ actions.
- Authentication supports two types: SQL Authentication using login credentials for authentication and Azure Active Directory Authentication using Azure AD’s managed identities for authentication.
- Authorization defines how users are granted privileges to access the databases/Datawarehouse. The user’s privileges are controlled by role membership and object level permission in the user’s account.
Azure AD authentication
Azure Active Directory authenticates the connection using Azure AD identities. Azure AD authentication is 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 following below:
- Stop the proliferation of user identities across database servers.
- Manage password rotation in a single place.
- Manage database permissions using external groups.
- Eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
- Azure AD authentication uses contained database users to authenticate identities at the database level.
- Supports token-based authentication for applications connecting to SQL Database.
- Azure AD authentication supports ADFS (domain federation) or native user/password authentication for a local Azure Active Directory without domain synchronization.
- Azure AD supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA).
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:
- SQL users excluded from masking: Some SQL users or AAD identities get unmasked data from SQL query.
- 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.
- Masking functions: Set of methods that control the masking of database field.
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 number. 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 in Windows certificate store.
Azure is 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 to the anomalies and discrepancies. Auditing in SQL database can be utilized in many ways:
- Retain an audit trail of selected events
Azure SQL auditing can be enabled at 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 database level to the environment more portable.
Azure Monitor provides an 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 SEIM tools. Data extracted from various tiers of Azure are classified as follows:
- Application monitoring data: This data includes application logs, performance traces and user telemetry.
- Guest OS monitoring data: data such as Linux syslog and windows system events.
- Azure resource monitoring data: data about operations of azure resources.
- Azure subscription monitoring data: data such as health logs and Azure resource manager audits.
- 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 DCAP Central and jSonar’s Database Security 2.0 Software-as-a-Service.
TDE with Azure SQL
Transparent Data Encryption (TDE) performs real-time encryption and decryption on database, backups and transaction log of data-at-rest leaving the application unchanged. Azure makes enables this by default on all the new databases but not on 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 database encryption key for encrypting the database storage. There are two types of key protection.
- Service – managed transparent data encryption: By default, database encryption key uses built-in server certificate for securing the key.
- Bring your own key (asymmetric key): Asymmetric key is stored in key vault. With BYOK support you can have full control i.e. when and who will access keys.