Data exfiltration is a technique that is also sometimes described as data theft or data extrusion, that describes the unauthorized extraction of data from the original source. This unauthorized extraction can be executed either manually or automatically by the malicious attacker.
As part of your Network Infrastructure, you might have tightened your security to make sure you have all the bells and whistles to lock down your Azure SQL Managed Instance to be accessed only by your application and not exposed to the Internet or any other traffic. However, this doesn’t stop a malicious admin from taking a backup or creating a linked server to another resource outside your enterprise subscription for extracting the data. This action would be data exfiltration. In a typical on-premises infrastructure, you can lock down network access completely to make sure that the data never leaves your network. However, in a cloud setup, there is a possibility that someone with elevated privileges can export data or perform some other malicious activity targeting their own resources outside your organization, compromising your enterprise data. Hence, it is very important to understand the different data exfiltration scenarios and make sure that you are taking the right steps to monitor for and prevent such activities.
Here are of the different exfiltration scenarios and the associated preventive actions and controls.
Backups to Unauthorized Locations
By default, Azure SQL Managed Instance has automatic backups that are stored on Azure storage, fully encrypted, keeping you compliant, and providing most of the functionalities that you would need. Additionally, Managed Instance also enables you to take your own COPY_ONLY backups where you get an option to take backups to a URL with the COPY_ONLY flag. This means that a malicious user can take a backup of the database to their personal storage account. Best practices to prevent this from happening are listed below.
Preventive Actions
- Restricted Admin Access
Follow the principle of least privilege to make sure that you always grant only the minimum necessary permissions to your DBAs and other privileged users on your Azure SQL Managed Instance.
Below is an example of the permissions granted and denied for a restricted admin role:
|
Authorization |
|
Permission |
|
GRANT |
|
CONNECT ANY DATABASE |
|
GRANT |
|
SELECT ALL USER SECURABLES |
|
GRANT |
|
VIEW SERVER STATE |
|
GRANT |
|
VIEW DATABASE STATE |
|
GRANT |
|
ALTER ANY CONNECTION |
|
GRANT |
|
VIEW ANY DEFINITION |
|
GRANT |
|
VIEW ANY DATABASE |
|
GRANT |
|
ALTER ANY CONNECTION |
|
GRANT |
|
ALTER SERVER STATE |
|
GRANT |
|
ALTER ANY EVENT SESSION |
|
GRANT |
|
ALTER TRACE |
|
DENY |
|
ALTER ANY DATABASE |
|
DENY |
|
ALTER ANY LINKED SERVER |
|
DENY |
|
ALTER ANY LOGIN |
|
DENY |
|
ALTER ANY ENDPOINT |
|
DENY |
|
ALTER ANY DATABASE |
|
DENY |
|
ALTER ANY CREDENTIAL |
|
DENY |
|
ALTER ANY SERVER AUDIT |
|
DENY |
|
ALTER ANY SERVER ROLE |
|
DENY |
|
ALTER SETTINGS |
|
DENY |
|
IMPERSONATE ANY LOGIN |
|
DENY |
|
ADMINISTER BULK OPERATIONS |
For your power users who need sysadmin access, here are some guidelines:
- It is recommended to have all the privileged users in Azure SQL Managed instance have AAD server logins on the Managed SQL Instance. Preferably create an AAD Group as a sysadmin on the SQL Server.
- Set Azure AD conditional access on those accounts so that they can only be accessed from within certain regions, using specific devices, or similar.
- Enable Privileged Identity Management (PIM) to log onto secured jump boxes within the same virtual network as the SQL Managed Instance, followed by a connection to the Azure SQL Managed Instance.
Reference: Start using Privileged Identity Management
- Use Transparent Data Encryption (TDE)
TDE helps protect Azure SQL Managed Instance against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. TDE is either enabled by using service-managed keys (where Microsoft manages the default key) or customer-managed keys (where you as a customer point to a key in Azure Key vault).
COPY_ONLY backups are not possible because Microsoft manages the key. They can only work with customer-managed keys, which means that for someone to restore the backup, the target SQL instance needs to have access to the original key (TDE Protector) in Azure Key Vault. Azure Key Vault’s access policy is managed by Azure AD and hence it acts as security boundary by making sure that any Azure SQL instance which is not part of the Access policy is denied access, preventing data exfiltration.
Reference: How-to restore across different SQL Managed instances when using TDE with Customer Managed Keys
- Assign service endpoint policies to control where the data may go
Virtual network service endpoint policies for Azure Storage may be configured on the Azure SQL Managed Instance's subnet, granting you explicit preventive action over which Azure Storage resources may be accessed. When associated with the subnet, service endpoint policies will deny all connection attempts from within your subnet to all Azure Storage resources except those you have configured the policies to allow.
Note: This feature is in public preview.
Reference: Configure service endpoint policies for Azure SQL Managed Instance.
Controls
- Setting Auditing Alerts
You need to make sure that you have enabled auditing on the Azure SQL Managed Instance to capture all backup and restore activities. Auditing can write to your storage accounts or to Azure Monitor logs. By writing to Azure Monitor logs, you can create Alerts and notifications to make sure that you get alerted on all the backup and restore activities to or from unauthorized locations.
Reference: SQL Managed Instance auditing - Azure SQL Managed Instance | Microsoft Docs
- Azure Policy to enforce TDE
Azure Policy enforces different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. Azure Policy meets this need by evaluating your resources for non-compliance with assigned policies. A built-in policy called “SQL managed instance TDE protector should be encrypted with your own key” can be used to enforce the encryption of databases using customer-managed keys located in Azure Key Vault.
- Microsoft Defender for SQL - Threat Protection and Vulnerability Assessments for Data Exfiltration
Microsoft Defender for Cloud provides a set of advanced SQL security capabilities, including vulnerability assessment and Advanced Threat Protection. These capabilities help track potential database vulnerabilities and protect from threats from applications and other usage anomalies. Threat Protection offers an option called “Data exfiltration” which will alert when any kind of exfiltration anomalies happen on the Managed Instance.
References: Microsoft Defender for SQL - Azure SQL Database | Microsoft Docs
Copying Data through Linked Servers
Linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance to read data from remote data sources and execute commands against remote database servers (for example, OLE DB data sources). A malicious user can exfiltrate data out of a Managed Instance by creating a linked server to another SQL Server instance. Below are some ways to prevent this from happening.
Preventive Actions
- Restricted Admin Access
Follow the principle of least privilege to make sure that you always grant only the minimum necessary permissions to your DBAs and other privileged users on your Azure SQL Managed Instance.
Controls
- Setting Auditing Alerts: You need to make sure that you have enabled auditing on the Azure SQL Managed Instance to capture all linked server activities. Auditing can write to your storage accounts or to Azure Monitor Logs. By writing to Azure Monitor logs, you can create alerts and notifications to make sure that you get alerted on all the linked server activities.
Reference: SQL Managed Instance auditing - Azure SQL Managed Instance | Microsoft Docs
Outbound Calls through CLR Integration
CLR enables scenarios in which code can access resources outside the server. A malicious user can exfiltrate data out from a Managed instance through the managed code.
Preventive Actions
- Turn off CLR Integration:
Executing this query will disable CLR on Azure SQL Managed Instance
execute sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
execute sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
- Restricted Admin Access: Follow the principle of least privilege to make sure that you always grant only the minimum necessary permissions to your DBAs and other privileged users on your Azure SQL Managed Instance.
Controls
- Setting Auditing Alerts: Enable auditing on the Azure SQL Managed Instance to capture all sp_configure activities. Auditing can write to your storage accounts or to Azure Monitor Logs. By writing to Azure Monitor logs, you can create alerts and notifications to make sure that you get alerted on all the sp_configure activities happening on the SQL MI instance.
Reference: It's just SQL: CLR in Azure SQL Database Managed Instance | Microsoft Docs
Sending Data through Database Mail
The Database Mail feature enables you to send emails directly from Managed Instance. A malicious user can leverage this feature to exfiltrate data out from the Managed Instance. The following are ways to prevent this from happening.
Preventive Actions
- Turn off Database Mail:
Executing this query will disable Database Mail on Azure SQL Managed Instance:
execute sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
execute sp_configure 'Database Mail XPs', 0;
GO
RECONFIGURE
GO
- Restricted Admin Access: Follow the principle of least privilege to make sure that you always grant only the minimum necessary permissions to your DBAs and other privileged users on your Azure SQL Managed Instance.
Controls
- Setting Auditing Alerts: Enable auditing on the Azure SQL Managed Instance to capture all sp_configure activities. Auditing can write to your storage accounts or to Azure Monitor Logs. By writing to Azure Monitor logs, you can create alerts and notifications to make sure that you get alerted on all the sp_configure activities happening on the SQL MI instance.
Reading Data from Audit Logs
There could be scenarios where you write the audit logs to a storage account which you want to retain for long term or compliance purposes. As such you do not want anyone to tamper with those logs. The following are ways to prevent and control this from happening.
Preventive Actions
- Locking storage firewall to Managed Instance subnet
Configure firewall rules to limit access to your storage account to only those requests that originate from the Managed Instance’s subnet.
Reference: Configure Azure Storage firewalls and virtual networks | Microsoft Docs
- Assign to control where the data may go
Virtual network service endpoint policies for Azure Storage may be configured on the Azure SQL Managed Instance's subnet, granting you explicit control over which Azure Storage resources may be accessed. When associated with the subnet, service endpoint policies will deny all connection attempts from within your subnet to all Azure Storage resources except those you have configured the policies to allow.
Note: This feature is in public preview.
Reference: configure service endpoint policies for Azure SQL Managed Instance.
Controls
- Storage Access Control: One of the security risks is exposing this storage account which contains your SQL Managed Instance Audit logs which can be subjected to tampering or deletions. Hence, it is very important to lock down this storage account so that only your SQL Managed instance has access to it and blocked from everybody else.
Go to the Storage account which contains your SQL Server audit logs and enable VNET endpoint on the storage account. Once you enable this VNET endpoint on the storage account, only SQL MI will be able to write to the storage account. You need to exclusively add other VNET\Subnets if you want to give whitelisted access to other privileged users or use SQL MI or query the audit logs accordingly.
Transactional Replication
Transactional replication in Azure SQL Managed Instance allows customer to replicate selected portions of data on a table level to other remote destinations, supporting the same Transactional Replication protocol. For this purpose, a storage on Azure must be used and this gives a potential attacker a path to extract the data, which should be prevented and controlled.
The following are ways to prevent and control this from happening.
Preventive Actions
- Restricted Admin Access
Follow the principle of least privilege to make sure that you always grant only the minimum necessary permissions to your DBAs and other privileged users on your Azure SQL Managed Instance.
- Assign to control where the data may go
Virtual network service endpoint policies for Azure Storage may be configured on the Azure SQL Managed Instance's subnet, granting you explicit preventive action over which Azure Storage resources may be accessed. When associated with the subnet, service endpoint policies will deny all connection attempts from within your subnet to all Azure Storage resources except those you have configured the policies to allow.
Note: This feature is in public preview.
Reference: configure service endpoint policies for Azure SQL Managed Instance.
Controls
- Storage Access Control: One of the security risks is exposing this storage account which contains your SQL Managed Instance Transaction Replication data which can be exported and can be replicated to a different destination. Hence, it is very important to lock down this storage account so that only your SQL Managed instance has access to it and blocked from everybody else.
Go to the Storage account which contains your SQL Server audit logs and enable VNET endpoint on the storage account. Once you enable this VNET endpoint on the storage account, only SQL MI will be able to write to the storage account. You need to exclusively add other VNET\Subnets where the Transactional Replication distributor and publisher are located or other VNET\Subnets, if you want to give whitelisted access to other privileged users or use SQL MI accordingly.
- Setting Auditing Alerts: Enable auditing on the Azure SQL Managed Instance to capture Transactional Replication configuration and published article activities. Auditing can write to your storage accounts or to Azure Monitor Logs. By writing to Azure Monitor logs, you can create alerts and notifications to make sure that you get alerted on all the transactional replication activities happening on the SQL MI instance.
In this article we have described some of the most popular Data Exfiltration methods and the respective preventive actions & control mechanisms for each of them. This list is not complete yet and we shall be updating it in the future, but for now - start with protecting your and your customers data right away.
