Az - SQL

Reading time: 13 minutes

tip

Learn & practice AWS Hacking:HackTricks Training AWS Red Team Expert (ARTE)
Learn & practice GCP Hacking: HackTricks Training GCP Red Team Expert (GRTE)

Support HackTricks

Check the subscription plans!
Join the 💬 Discord group or the telegram group or follow us on Twitter 🐦 @hacktricks_live.
Share hacking tricks by submitting PRs to the HackTricks and HackTricks Cloud github repos.

Azure SQL

Azure SQL is a family of managed, secure, and intelligent products that use the SQL Server database engine in the Azure cloud. This means you don't have to worry about the physical administration of your servers, and you can focus on managing your data.

Azure SQL consists of four main offerings:

Azure SQL Server: A server is needed for the deployment and management of SQL Server databases.
Azure SQL Database: This is a fully-managed database service, which allows you to host individual databases in the Azure cloud.
Azure SQL Managed Instance: This is for larger scale, entire SQL Server instance-scoped deployments.
Azure SQL Server on Azure VMs: This is best for architectures where you want **control over the operating system **and SQL Server instance.

SQL Server Security Features

Network access:

Public endpoint (can limit access to specific networks).
Private endpoints.
It’s also possible to restrict connections based on domain names.
It’s also possible to allow Azure services to access it (like to use the Query editor in the portal or to allow an Azure VM to connect).

Authentication Methods:

Microsoft Entra-only authentication: You need to indicate the Entra principals that will have access to the service.
Both SQL and Microsoft Entra authentication: Traditional SQL authentication with username and password alongside Microsoft Entra.
Only SQL authentication: Only allow access via database users.

Note that if any SQL auth is allowed an admin user (username + password) needs to be indicated and if Entra ID auth is selected it’s also needed to indicate at least one principal with admin access.

Encryption:

It’s called “Transparent data encryption” and it encrypts databases, backups, and logs at rest.
As always, an Azure managed key is used by default but a customer managed encryption key (CMEK) could also be used. Managed Identities:
It’s possible to assign system and user managed MIs.
Used to access the encryption key (is a CMEK is used) and other services from the databases.
- For some examples of the Azure services that can be accessed from the database check this page of the docs
If more than one UMI is assigned, It’s possible to indicate the default one to use.
It’s possible to configure a federated client identity for cross-tenant access.

Some commands to access info inside a blob storage from a SQL database:

sql

-- Create a credential for the managed identity
CREATE DATABASE SCOPED CREDENTIAL [ManagedIdentityCredential]
WITH IDENTITY = 'Managed Identity';
GO

-- Create an external data source pointing to the blob storage to access
CREATE EXTERNAL DATA SOURCE ManagedIdentity
WITH (
   TYPE = BLOB_STORAGE,
   LOCATION = 'https://testsqlidentity.blob.core.windows.net/sqlcontainer',
   CREDENTIAL = ManagedIdentityCredential
);
GO

-- Read a file from ths storage and return it
SELECT *
FROM OPENROWSET(
   BULK 'message.txt',
   DATA_SOURCE = 'ManagedIdentity',
   SINGLE_CLOB
) AS DataFile;
GO

Microsoft Defender:

Useful for “mitigating potential database vulnerabilities, and detecting anomalous activities”
We will talk about Defender in its own lesson (it can be enabled in several other Azure services)

Backups:

Backup frequency is managed in the retention policies.

Deleted databases:

It’s possible to restore DBs that have been deleted from existing backups.

Azure SQL Database

Azure SQL Database is a fully managed database platform as a service (PaaS) that provides scalable and secure relational database solutions. It's built on the latest SQL Server technologies and eliminates the need for infrastructure management, making it a popular choice for cloud-based applications.

To create a SQL database it’s needed to indicate the SQL server where it’ll be hosted.

SQL Database Security Features

Always Up-to-Date: Runs on the latest stable version of SQL Server and Receives new features and patches automatically.
Inherited SQL Server security features:
- Authentication (SQL and/or Entra ID)
- Assigned Managed Identities
- Network restrictions
- Encryption
- Backups
- …
Data redundancy: Options are local, zone, Geo or Geo-Zone redundant.
Ledger: It cryptographically verifies the integrity of data, ensuring that any tampering is detected. Useful to financial, medical and any organization managing sensitive data.

A SQL database could be part of an elastic Pool. Elastic pools are a cost-effective solution for managing multiple databases by sharing configurable compute (eDTUs) and storage resources among them, with pricing based solely on the resources allocated rather than the number of databases.

Azure SQL Column Level Security (Masking) & Row Level Security

Azure SQL's dynamic data masking is a feature that helps protect sensitive information by hiding it from unauthorized users. Instead of altering the actual data, it dynamically masks the displayed data, ensuring that sensitive details like credit card numbers are obscured.

The Dynamic Data Masking affects to all users except the ones that are unmasked (these users need to be indicated) and administrators. It has the configuration option that specifies which SQL users are exempt from dynamic data masking, with administrators always excluded.

Azure SQL Row Level Security (RLS) is a feature that controls which rows a user can view or modify, ensuring each user only sees the data relevant to them. By creating security policies with filter or block predicates, organizations can enforce fine-grained access at the database level.

Azure SQL Managed Instance

Azure SQL Managed Instances are for larger scale, entire SQL Server instance-scoped deployments. It provides near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, which provides a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers.

Azure SQL Virtual Machines

Azure SQL Virtual Machines allows to control the operating system and the SQL Server instance, as a VM will be spawn in the VM service running the SQL server.

When a SQL Virtual Machine is created it’s possible to selected all the settings of the VM (as shown the VM lesson) that will be hosting the SQL server.

This means that the VM will be accessing some VNet(s), might have Managed Identities attached to it, could have file shares mounted… making a pivoting from the SQL to the VM super interesting.
Moreover, it’s possible to configure an app id and secret to allow the SQL to access an specific key vault, which could contain sensitive info.

It's also possible to configure things like automatic SQL updates, automatic backups, Entra ID authentication and most of the features of the other SQL services.

Enumeration

bash

# List Servers
az sql server list # managed identities are enumerated here too
## List Server Usages
az sql server list-usages --name <server_name> --resource-group <resource_group>
## List Server Firewalls
az sql server firewall-rule list --resource-group <resource_group> --server <server_name>
## List of Azure Active Directory administrators in a server.
az sql server ad-admin list --resource-group <resource_group> --server <server_name>
## Gets an advanced threat protection
az sql server advanced-threat-protection-setting show --resource-group <resource_group> --name <server_name>
## Get server's auditing policy.
az sql server audit-policy show --resource-group <resource_group> --name <server_name>
## Gets a server's secure connection policy.
az sql server conn-policy show --resource-group <resource_group> --server <server_name>
## Gets a list of server DNS aliases for a server.
az sql server dns-alias list --resource-group <resource_group> --server <server_name>
## List of server keys.
az sql server key list --resource-group <resource_group> --server <server_name>
## Gets a server encryption protector.
az sql server tde-key show --resource-group <resource_group> --server <server_name>

# List Databases in a SQL server
az sql db list --server <server_name> --resource-group <resource_group> #--output table
## Get details of a specific database
az sql db show --name <database_name> --server <server_name> --resource-group <resource_group>
## List database usages
az sql db list-usages --name <database_name> --server <server_name> --resource-group <resource_group>
## List of operations performed on the database.
az sql db op list --database <database_name> --server <server_name> --resource-group <resource_group>
## List sql database classification
az sql db classification list --name <database_name> --server <server_name> --resource-group <resource_group>
## List long-term retention backups for a SQL database
az sql db ltr-backup list --database <database_name> --server <server_name> --resource-group <resource_group>
## List long-term retention policy
az sql db ltr-policy --name <database_name> --server <server_name> --resource-group <resource_group>
## List long-term retention policy
az sql db str-policy --name <database_name> --server <server_name> --resource-group <resource_group>
## List the replicas of a database and their replication status
az sql db replica list-links --name <database_name> --server <server_name> --resource-group <resource_group>
## List deleted SQL databases
az sql db list-deleted --server <server_name> --resource-group <resource_group>
## List database usages
az sql db list-usages --name <database_name> --server <server_name> --resource-group <resource_group>
## List restorable dropped databases in a SQL server
az sql db list-deleted --server <server_name> --resource-group <resource_group>
## List advanced threat protection setting show
az sql db advanced-threat-protection-setting --name <database_name> --server <server_name> --resource-group <resource_group>

# List all elastic pools in a SQL server
az sql elastic-pool list --server <server_name> --resource-group <resource_group> #--output table
## List all databases in a specific elastic pool
az sql elastic-pool show --name <elastic_pool_name>  --server <server_name> --resource-group <resource_group>
## List of databases in an elastic pool.
az sql elastic-pool list-dbs --name <elastic_pool_name>  --server <server_name> --resource-group <resource_group>

# List all managed Instances
az sql mi list
az sql mi show --resource-group <res-grp> --name <name>
az sql midb list
az sql midb show --resource-group <res-grp> --name <name>

# Lis all sql VM
az sql vm list
az sql vm show --resource-group <res-grp> --name <name>

# List schema by the database
az rest --method get \
  --uri "https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<serverName>/databases/<databaseName>/schemas?api-version=2021-11-01"

# Get tables of a database with the schema
az rest --method get \
  --uri "https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<serverName>/databases/<databaseName>/schemas/<schemaName>/tables?api-version=2021-11-01"

# Get columns of a database
az rest --method get \
  --uri "https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<serverName>/databases/<databaseName>/columns?api-version=2021-11-01"

# Get columns of a table
az rest --method get \
  --uri "https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<serverName>/databases/<databaseName>/schemas/<schemaName>/tables/<tableName>/columns?api-version=2021-11-01"

# Get DataMaskingPolicies of a database
az rest --method get \
  --uri "https://management.azure.com/subscriptions/9291ff6e-6afb-430e-82a4-6f04b2d05c7f/resourceGroups/Resource_Group_1/providers/Microsoft.Sql/servers/getstorageserver/databases/masktest/dataMaskingPolicies/Default?api-version=2021-11-01"

az rest --method get \
    --uri "https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<serverName>/databases/<databaseName>/dataMaskingPolicies/Default/rules?api-version=2021-11-01"

bash

# List Servers
Get-AzSqlServer -ResourceGroupName "<resource-group-name>"

# List All Databases in a SQL Server
Get-AzSqlDatabase -ServerName "<server_name>" -ResourceGroupName "<resource_group>"
# Get Details of a Specific Database
Get-AzSqlDatabase -Name "<database_name>" -ServerName "<server_name>" -ResourceGroupName "<resource_group>"

# List Operations Performed on the Database
Get-AzSqlDatabaseActivity -DatabaseName "<database_name>" -ServerName "<server_name>" -ResourceGroupName "<resource_group>"

# List SQL Database Classification
Get-AzSqlDatabaseSensitivityClassification -DatabaseName "<database_name>" -ServerName "<server_name>" -ResourceGroupName "<resource_group>"

# List Long-Term Retention Backups for a SQL Database
Get-AzSqlDatabaseLongTermRetentionBackup -ResourceGroupName "<resource_group>" -Location "<location>"
# List Replicas of a Database and Their Replication Status
Get-AzSqlDatabaseReplicationLink -DatabaseName "<database_name>" -ServerName "<server_name>" -ResourceGroupName "<resource_group>"
# List Deleted SQL Databases
Get-AzSqlDeletedDatabaseBackup -ServerName "<server_name>" -ResourceGroupName "<resource_group>"

# List All Elastic Pools in a SQL Server
Get-AzSqlElasticPool -ServerName "<server_name>" -ResourceGroupName "<resource_group>"
# List All Databases in a Specific Elastic Pool
Get-AzSqlElasticPoolDatabase -ElasticPoolName "<elastic_pool_name>" -ServerName "<server_name>" -ResourceGroupName "<resource_group>"

# List all managed Instances
Get-AzSqlInstance
Get-AzSqlInstance -ResourceGroupName <ResourceGroupName> -Name <ManagedInstanceName>

# List All Databases in a SQL Managed Instance
Get-AzSqlInstanceDatabase -ResourceGroupName <ResourceGroupName> -InstanceName <ManagedInstanceName>

# Lis all sql VM
Get-AzSqlVM

Additionally if you want to enumerate the Dynamic Data Masking, and Row Level policies, within the database, you can query:

sql

--Enumerates the masked columns
SELECT 
    OBJECT_NAME(mc.object_id) AS TableName,
    c.name AS ColumnName,
    mc.masking_function AS MaskingFunction
FROM sys.masked_columns AS mc
JOIN sys.columns AS c 
    ON mc.object_id = c.object_id 
    AND mc.column_id = c.column_id

--Enumerates Row level policies
SELECT
    sp.name AS PolicyName,
    sp.is_enabled,
    sp.create_date,
    sp.modify_date,
    OBJECT_NAME(sp.object_id) AS TableName,
    sp2.predicate_definition AS PredicateDefinition
FROM sys.security_policies AS sp
JOIN sys.security_predicates AS sp2
    ON sp.object_id = sp2.object_id;

Connect and run SQL queries

You could find a connection string (containing credentials) from example enumerating an Az WebApp:

bash

function invoke-sql{
    param($query)
    $Connection_string = "Server=tcp:supercorp.database.windows.net,1433;Initial Catalog=flag;Persist Security Info=False;User ID=db_read;Password=gAegH!324fAG!#1fht;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    $Connection = New-Object System.Data.SqlClient.SqlConnection $Connection_string
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $Command.Connection = $Connection
    $Command.CommandText = $query
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
        $Reader.GetValue(0)
    }
    $Connection.Close()
}

invoke-sql 'Select Distinct TABLE_NAME From information_schema.TABLES;'

You can also use sqlcmd to access the database. It is important to know if the server allows public connections az sql server show --name <server-name> --resource-group <resource-group>, and also if it the firewall rule let's our IP to access:

bash

sqlcmd -S <sql-server>.database.windows.net -U <server-user> -P <server-passworkd> -d <database>

Learn & practice AWS Hacking:HackTricks Training AWS Red Team Expert (ARTE)
Learn & practice GCP Hacking: HackTricks Training GCP Red Team Expert (GRTE)

Support HackTricks

Check the subscription plans!
Join the 💬 Discord group or the telegram group or follow us on Twitter 🐦 @hacktricks_live.
Share hacking tricks by submitting PRs to the HackTricks and HackTricks Cloud github repos.

HackTricks Cloud

Az - SQL

Azure SQL

SQL Server Security Features

Azure SQL Database

SQL Database Security Features

Azure SQL Column Level Security (Masking) & Row Level Security

Azure SQL Managed Instance

Azure SQL Virtual Machines

Enumeration

Connect and run SQL queries

References

Privilege Escalation

Post Exploitation

Persistence