Azure SQL Database Security

SQL Authentication vs. Azure Active Directory (Azure AD) Authentication

Azure SQL Database supports both SQL authentication and Azure AD authentication. Azure AD authentication is the recommended approach for enhanced security and centralized management.

Connecting with SQL Authentication (Conceptual Example)

-- This is a conceptual example and not runnable code directly.
-- In your application, you would use a connection string.

-- Example connection string format:
-- Server=tcp:your_server.database.windows.net,1433;Initial Catalog=your_database;Persist Security Info=False;User ID=your_user;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
            

Connecting with Azure AD Authentication (Conceptual Example)

Using Azure AD requires obtaining an access token. Libraries like Microsoft Authentication Library (MSAL) can assist with this.

// C# example using Microsoft.Data.SqlClient and MSAL (conceptual)
using Microsoft.Data.SqlClient;
using Microsoft.Identity.Client;
using System;
using System.Threading.Tasks;

public class AzureSqlAuth
{
    public static async Task ConnectWithAzureAdAsync()
    {
        string tenantId = "YOUR_TENANT_ID";
        string clientId = "YOUR_CLIENT_APP_ID";
        string authority = $"https://login.microsoftonline.com/{tenantId}";
        string scope = "https://database.windows.net/.default"; // Or specific resource if needed

        IPublicClientApplication pca = PublicClientApplicationBuilder
            .Create(clientId)
            .WithAuthority(authority)
            .Build();

        string[] scopes = { scope };
        AuthenticationResult authResult = await pca.AcquireTokenInteractive(scopes).ExecuteAsync();

        string accessToken = authResult.AccessToken;

        var builder = new SqlConnectionStringBuilder
        {
            DataSource = "your_server.database.windows.net",
            InitialCatalog = "your_database",
            AccessToken = accessToken,
            Encrypt = true,
            TrustServerCertificate = false,
            ConnectionTimeout = 30
        };

        using (var connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();
            Console.WriteLine("Successfully connected using Azure AD.");
            // Perform database operations here
        }
    }
}
            

Transparent Data Encryption (TDE)

TDE encrypts data at rest, including backup files. It's enabled by default for new Azure SQL databases. You can manage TDE with a service-managed key or a customer-managed key (CMK) using Azure Key Vault.

Enabling TDE with a Service-Managed Key (Azure Portal / PowerShell)

TDE is typically managed via the Azure portal or PowerShell/CLI. There isn't a direct T-SQL command to enable it from scratch, but you can check its status.

SELECT encryption_state, encryption_state_desc
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID();
            

To configure CMK, you would use Azure Key Vault and link it to your Azure SQL Server.

Azure SQL Auditing

Azure SQL Auditing tracks database events and writes them to an audit log in Azure Blob Storage, Azure Log Analytics, or Event Hubs. This is crucial for compliance and security analysis.

Enabling Auditing to Azure Blob Storage (T-SQL Example)

-- Ensure you have an Azure Storage Account and Container ready.
-- Replace placeholders with your actual details.

ALTER ROLE db_owner ADD MEMBER [your_audit_user]; -- Grant necessary permissions to the audit user

-- Enable auditing and specify the destination.
-- For Blob Storage:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'external scripts enabled', 1; RECONFIGURE;

CREATE SERVER AUDIT [MyServerAuditing]
TO FILE (
    FILEPATH = 'https://yourstorageaccount.blob.core.windows.net/sqlauditlogs/', -- Your container URL
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10
)
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE );
GO

CREATE DATABASE AUDIT SPECIFICATION [MyDatabaseAuditingSpec]
FOR SERVER AUDIT [MyServerAuditing]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SELECT_GROUP),
ADD (INSERT_GROUP),
ADD (UPDATE_GROUP),
ADD (DELETE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

ALTER SERVER AUDIT [MyServerAuditing] WITH (STATE = ON);
GO
            

You can also configure auditing via the Azure portal for a more user-friendly experience.

Azure Defender for SQL (Advanced Threat Protection)

Azure Defender for SQL provides intelligent, unified security for your databases. It detects anomalous activities and potential threats like SQL injection, brute force attacks, and suspicious data movement.

Key capabilities include:

• Vulnerability Assessment
• Advanced Threat Protection (SQL injection, brute force, etc.)
• Data Discovery and Classification

Firewall Rules

Azure SQL Database firewalls control access to your server and databases. You can set server-level and database-level firewall rules.

Creating a Server-Level Firewall Rule (T-SQL Example)

Use the `sp_set_firewall_rule` stored procedure to manage firewall rules.

-- Allow access from a specific IP address
EXEC sp_set_firewall_rule N'AllowSpecificIP', '203.0.113.1', '203.0.113.1';

-- Allow access from an IP range
EXEC sp_set_firewall_rule N'AllowIPRange', '203.0.113.0', '203.0.113.255';

-- Allow Azure services to access the server
EXEC sp_set_firewall_rule N'AllowAzureServices', '0.0.0.0', '0.0.0.0';
GO

-- To delete a rule:
-- EXEC sp_delete_firewall_rule N'AllowSpecificIP';
-- GO
            

It's generally recommended to manage firewall rules via the Azure portal or CLI for better visibility and management.

Dynamic Data Masking

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. It doesn't change the data in the database, only how it's presented in query results.

Masking a Column

-- Example: Masking an email column for a specific user role.

-- Create a role for users who should see masked data
CREATE ROLE LimitedDataAccess;
GRANT SELECT ON SCHEMA::dbo TO LimitedDataAccess;
REVOKE SELECT ON dbo.Customers(Email) TO LimitedDataAccess; -- Revoke direct access to the email column

-- Apply masking policy to the Email column
ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
GO

-- Grant the masking policy to the role
GRANT UNMASK TO LimitedDataAccess; -- This allows the role to see unmasked data IF they also have SELECT
-- However, for actual masking, the user must NOT be in a role that has UNMASK permission,
-- or the masking policy is applied directly to users/groups.

-- To grant UNMASK permission to a specific user or role to see the original data:
-- GRANT UNMASK TO LimitedDataAccess;
-- OR
-- GRANT UNMASK TO [YourUserOrGroupName];
-- GO

-- To remove masking:
-- ALTER TABLE dbo.Customers
-- ALTER COLUMN Email DROP MASKED;
-- GO
            

Common masking functions include: default(), email(), partial(), numeric_random().