Ensuring 360° database security in Azure SQL Elastic Pool

With the increase in PaaS based cloud deployments for production applications, it is important to consider the data tier security responsibilities of the clients, the DevOps, the infrastructure and the development team, especially with data being one of the most important asset of the organization. Additionally, data tier security is a first class citizen for the applications in the sensitive domains of banking, finance, health and e-governance where regulatory compliance such as ISO 27001, GDPR, HIPAA and NIST is indispensable.

The need for Elastic Pools

To begin with, SQL Server Elastic Pool is a PaaS based cloud offering by Azure which provides a collection of SQL databases in a single SQL Server where the databases share a pre-specified compute, memory and storage space. The elastic pool based approach is inclined towards the use cases of SaaS applications where multi-tenant database architecture is the top notch requirement for the data layer. In SaaS based multi-tenant applications, each client would have its own data tier performance requirements for which the SaaS providers would tweak the databases in terms of size and compute. More often that not, the providers would either end up over provisioning or under provisioning the compute and storage which may in turn lead to more cost or less performance respectively. With Elastic pools, the SaaS application providers can group their clients with different subscriptions to be in different pools of databases with specified compute which can be increased on demand, providing them a scalable and budget friendly approach.

The whole nine yards of Database Security

Broadly, the security landscape for SQL databases in an Elastic Pool is divided into network security (a.k.a. data in transit), information security (a.k.a. data at rest) and security auditing and compliance monitoring which is detailed in the below mentioned info graphic.

Network Security for Azure SQL Databases in Elastic Pool

The cloud provides an open to internet environment where protecting the application components from illegal access is important for its smooth functioning. Even a single loop hole in the networking infrastructure can result in an attack which could cause losses of billions to the businesses on cloud. As data layer forms an integral part of the n-tier architecture, it is on critical list to protect and prevent it from malicious attacks over the network. By incorporating the below mentioned methodologies of ensuring network security, it is possible to safe guard the databases in the pool from network level threats.

1. SQL Server Firewall

Ensuring controlled network level access from external entities to SQL Server Database is vital for its security. In a data driven application, it is crucial that external entities such as the API Endpoints (hosted on Azure Functions or Web App), the data migration and processing pipelines such as Azure Data Factory is able to communicate with the SQL Server for the applications to run smoothly. In Azure, it is possible to allow specific IPs to access the database using SQL Server Firewall. Once the firewall has been configured, all the unwanted IP addresses will be blocked from accessing the database right at the entry level hence preventing any further damage to the database.

2. Service Endpoints

API endpoints (both on Azure Functions as well as Web App) can be hosted in a Virtual Network which provides private address space that restricts both in bound as well as out bound internet access to the application components. In such cases, service endpoints could be configured to enable the required services (Azure functions, Web Apps and Azure Data Factory) to communicate with the SQL Server over the Microsoft Backbone network. Service endpoints extends the Virtual Network capabilities to the Azure PaaS services so that PaaS services can be deployed and communicated within a private network environment on Microsoft Backbone network.

3. SSL Connection

The SQL Server must be configured with a certificate issued from a Certified Authority to ensure that all the communication between the external entities and the SQL Server is encrypted using Transport Layer Security (TLS). This may incur additional compute utilization as all the encrypted packets needs to be decrypted at both the ends, however it is an acceptable trade-off for security.

4. Network Security Group (NSG)

At times, it can be ambiguous to configure a Network Security Group as well as an Azure SQL Firewall as both of them serves the same purpose i.e. to block unwanted network traffic. However, it is vital to note the subtle differences between them. An NSG is configured to block unwanted traffic to a subnet inside of the Virtual Network whereas a Firewall is configured to block unwanted traffic from outside the Virtual Network i.e. another VNET or the Internet. In scenarios where the SQL Server is inside a Virtual Machine, the RDP access to it can be blocked using a NSG so that it is only possible to RDP to the SQL Server VM through Azure Bastion.

5. Private Link

Customers in the domains of banking, finance and health do not end their search for over-the-wire security by just being content with TLS. Applications in sensitive domains do require access to the Azure resources completely on the Microsoft Backbone network. Hence, configuring Private Links on Azure SQL Databases allows one way traffic on Microsoft Backbone network from the VNET to Azure Services, which helps the application in overcoming all the shortcomings of TLS and establish a network which can be completely trusted. Private Link and Azure Service Endpoints may sound ambiguous in the first go, but they are not. Private Link creates a separate Private IP address for Azure SQL Server whereas Service Endpoints enables VNET integration of PaaS services through public IPs and provides network security through NSGs.

Information Security for Azure SQL Databases in Elastic Pool

In order to achieve compliance with regulatory requirements such as ISO 27001, GDPR, HIPAA or NIST, the security requirements are not only restricted to the network, but it is also required at the data or the information layer as well. The data in the database should be secured enough to an extent that people who have administrative access to the database could not access or read sensitive data like credit card details, patient health reports, passwords and so on. This could be achieved by following methodologies mentioned below.

1. Transparent Data Encryption (TDE)

TDE ensures that the data at rest is encrypted when it is stored in disks. This is enabled by default in all the Azure SQL databases whether in pool, a managed instance or in a VM. This feature ensures that the data is encrypted and decrypted in real-time whenever the data is stored or requested from the database. While the default private key for TDE is provided by Azure, clients could opt for a Bring Your Own Key (BYOK) solution in which a custom key is provided by the clients which is retrieved from the Azure Key Vault. Custom key is required at times when clients prefer to have their own key management regulatory requirements such as key protection, key size configurations, key access management, key backup and the key rotations.

2. Access Management

Identity and Access Management (IAM) is an important aspect to manage data security as it dictates who gets to access what part and features of the database. Accounts should be grouped as administrative and developers (create more if the organization structure requires it) and they should be given rights to the database as per their roles and responsibilities in the project or in the organization. This could further be simplified by integrating Azure Active Directory with the SQL Server which allows Multi Factor Authentication (MFA) for administrative accounts. The security of the activities performed by administrative personnel can be taken to the next level by including a Privilege Access Workstations (PAW) in the architecture which forces all the administrative tasks to be performed in a secured environment, in terms of hardware, software and network.

3. Always Encrypted Columns

Regulatory requirements such as GDPR and HIPAA tightly controls information security by specifying what is visible in plain text even in the databases that stores it. Sensitive information such as Credit Card Numbers, Social Security Number, Bank Balances of customers, patient health reports are not supposed to be stored in plain text in the database as administrative accounts could have complete access to the production databases. In order to protect such sensitive information, Always Encrypted Columns (AEC) enables the applications to store only encrypted information in the database for columns with sensitive data. The data is only visible in plain text the middle tier (API endpoints) of the application as the middle tier has access to the private key that is used to encrypt/decrypt the data. For columns on which search (equality match) needs to be performed at database level, deterministic encryption should be used whereas for columns which are used for record keeping and unique identification, randomized encryption should be preferred.

4. Row level multi-tenancy

Its a general practice in multi-tenant SaaS applications to store multiple clients's data in a single database. This enables the SaaS provider to save cost by not providing individual data stores for each client subscription. However, it is the responsibility of the SaaS service providers to ensure that the data of a Client A is not accessible to Client B under any circumstances for data integrity. Row level multi-tenancy ensures that different tenants on the same database are allowed to access the information only stored by them. This is possible by passing in the tenant_id with each query performed on the database. Also, the database schema should be configured for associating tenant_id with each record in the database.

5. Azure Key Vault

As per the regulatory requirements laid down by ISO 27001, the applications codebase should not be allowed to store the connection strings to external entities such as databases. In order to achieve this requirement, Azure Key Vault should be used to store the database connection strings as well as the keys for the private keys of always encrypted columns. Key Vault ensures that the private keys are stored in Hardware Security Modules backed up by at least 3 copies of it. This ensures the security of the connection strings as any external entity having access to the codebase does not get an indirect access to application's database.

Auditing and Monitoring for Azure SQL Databases in Elastic Pool

No matter how well the security team has done their job on securing the databases, it is necessary to audit the current security methodologies applied on the database and also to ensure that the employed security features for the database is as per the benchmark levels during the applications life time. New security threats are introduced everyday and the cloud providers do provide frequent security patches to cover the existing loop holes, if any. Hence it is important to audit and assess the security compliance of the database and its data at frequent intervals. By incorporating the below mentioned methodologies of activity auditing and compliance monitoring, it is possible to detect and mitigate various security vulnerabilities .

1. Server Level Auditing

Server level auditing on Azure SQL Server in elastic pool ensures that all the activities such as successful/failed authentication attempts and audit trails of the data are securely logged in an append blob in an Azure Storage Account. Not only logging, but it also opens up opportunities to visualize the trends of database access and also to report any unusual activity that is performed on the SQL server or the SQL database using Azure Log Analytics.

2. Advance Threat Protection (ATP)

Prevention of security attacks begins with the detection of the same and hence detecting suspicious activities on the database is an important aspect of the security landscape. With ATP, it is possible to identify and track illegal database access attempts, brute force attacks on SQL Credentials, unknown geographical location of access, potential vulnerabilities, SQL Injection and trends of change in query patterns. By configuring ATP the administrators would receive anomalies alerts when any of such activity is detected so that the Security Response Team could take preventive or recovery measures against such attacks.

3. Vulnerability Assessment

In an organization where a large pool of users are accessing the databases for performing their operations, it is important to set the benchmarks for the database security to ensure that the development, infrastructure or the DevOps team are not breaching the database security guidelines of the organization. Additionally, it is also necessary to have reporting capabilities to identify breaches of security standards in any of the databases in the pool. The Azure SQL Server Vulnerability Assessment tool provides a standard mechanisms to set the baseline of the security landscape of the database and allows to run timely scans to ensure that the security baselines are not breached. Along with that, it also provides suggestions and remedies in order to overcome security vulnerabilities and implement security measures. Hence, it is a good to have toolbox in the security landscape to identify and mitigate security vulnerabilities well in advance.

To summarize

Azure SQL provides end to end security solutions that can be applied based on the use cases and the domains in which the application functions and also the regulatory requirements that application is supposed to comply with. In order to comply with security regulatory requirements, it is significant to control the data security both at rest and while in transit (over the wire). In addition to that, auditing and monitoring the security features of the database is also an important aspect to implement a 360 degrees database protection.