Azure Synapse Analytics Security Best Practices¶

Home > Security > Security Best Practices

This document provides comprehensive security best practices for Azure Synapse Analytics implementations, helping you build secure analytics environments that protect your data assets while enabling productivity and insights.

Introduction¶

Security is a critical aspect of any data analytics platform. Azure Synapse Analytics provides extensive security features that, when properly implemented, create a defense-in-depth approach to protect your data and analytics workloads. This guide covers best practices across all layers of security:

• Network security and isolation
• Identity and access management
• Data protection and encryption
• Monitoring and threat protection
• Secure development and deployment

Network Security Best Practices¶

Implement Network Isolation¶

1. Deploy Managed VNet

Always enable the managed virtual network during workspace creation to isolate and control data flow:

# Enable managed VNet during workspace creation
New-AzSynapseWorkspace `
  -ResourceGroupName "myresourcegroup" `
  -Name "mysynapseworkspace" `
  -Location "eastus" `
  -DefaultDataLakeStorageAccountName "mystorageaccount" `
  -DefaultDataLakeStorageFilesystem "myfilesystem" `
  -SqlAdministratorLoginCredential (Get-Credential) `
  -ManagedVirtualNetwork "default" `
  -AllowAllConnections $false

1. Use Private Endpoints

Connect to Synapse workspace and associated resources through private endpoints:

# Create private endpoint for Synapse SQL
$privateEndpointConnection = @{
  Name = "synapse-sql-connection"
  PrivateLinkServiceId = $workspace.Id
  GroupId = "Sql"
}

New-AzPrivateEndpoint `
  -ResourceGroupName "myresourcegroup" `
  -Name "synapse-sql-endpoint" `
  -Location "eastus" `
  -Subnet $subnet `
  -PrivateLinkServiceConnection $privateEndpointConnection

1. Configure IP Firewall Rules

Restrict public access to your Synapse workspace:

# Add IP firewall rule
Update-AzSynapseFirewallRule `
  -WorkspaceName "mysynapseworkspace" `
  -Name "AllowedIPRange" `
  -StartIpAddress "203.0.113.0" `
  -EndIpAddress "203.0.113.255"

1. Secure Integration Runtimes

For Azure Integration Runtimes, use VNet-injection. For Self-hosted Integration Runtimes, deploy within a secured corporate network:

# Create a managed VNet integration runtime
$runtime = Set-AzSynapseIntegrationRuntime `
  -WorkspaceName "mysynapseworkspace" `
  -Name "ManagedVnetIR" `
  -Type "Managed" `
  -ManagedVirtualNetwork "default" `
  -Location "EastUS"

1. Use Service Endpoints

Configure service endpoints on your VNet to securely access Azure services:

# Configure service endpoint on subnet
$subnet = Get-AzVirtualNetworkSubnetConfig `
  -VirtualNetwork $vnet `
  -Name "default"

Set-AzVirtualNetworkSubnetConfig `
  -Name "default" `
  -VirtualNetwork $vnet `
  -AddressPrefix "10.0.0.0/24" `
  -ServiceEndpoint "Microsoft.Sql"

$vnet | Set-AzVirtualNetwork

Network Traffic Filtering and Monitoring¶

1. Implement Network Security Groups (NSGs)

Control network traffic with detailed rules:

# Create NSG with restrictive rules
$nsgRule = New-AzNetworkSecurityRuleConfig `
  -Name "Allow-SQL" `
  -Protocol "Tcp" `
  -Direction "Inbound" `
  -Priority 100 `
  -SourceAddressPrefix "VirtualNetwork" `
  -SourcePortRange "*" `
  -DestinationAddressPrefix "*" `
  -DestinationPortRange "1433" `
  -Access "Allow"

New-AzNetworkSecurityGroup `
  -ResourceGroupName "myresourcegroup" `
  -Location "eastus" `
  -Name "SynapseNSG" `
  -SecurityRules $nsgRule

1. Enable NSG Flow Logs

Monitor network traffic for security analysis:

# Enable NSG flow logs
$nsg = Get-AzNetworkSecurityGroup -Name "SynapseNSG" -ResourceGroupName "myresourcegroup"

Set-AzNetworkWatcherFlowLog `
  -NetworkWatcher $networkWatcher `
  -TargetResourceId $nsg.Id `
  -StorageAccountId $storageAccount.Id `
  -EnableFlowLog $true `
  -FormatType "JSON" `
  -FormatVersion 2

1. Implement Azure DDoS Protection

Enable DDoS protection on your virtual network:

# Enable DDoS protection
$vnet = Get-AzVirtualNetwork -Name "myVNet" -ResourceGroupName "myresourcegroup"

$ddosProtectionPlan = Get-AzDdosProtectionPlan -ResourceGroupName "myresourcegroup" -Name "myDdosProtectionPlan"

$vnet.DdosProtectionPlan = New-Object Microsoft.Azure.Commands.Network.Models.PSResourceId
$vnet.DdosProtectionPlan.Id = $ddosProtectionPlan.Id
$vnet.EnableDdosProtection = $true

$vnet | Set-AzVirtualNetwork

Identity and Access Management Best Practices¶

Implement Azure Active Directory Integration¶

1. Use Azure AD Authentication

Configure Azure AD authentication for all components:

# Set Azure AD admin for SQL pools
Set-AzSynapseSqlActiveDirectoryAdministrator `
  -WorkspaceName "mysynapseworkspace" `
  -ResourceGroupName "myresourcegroup" `
  -DisplayName "AzureAD Admin Group" `
  -ObjectId "00000000-0000-0000-0000-000000000000"

1. Implement Conditional Access

Apply conditional access policies for Synapse workspaces:

1. Navigate to Azure AD > Security > Conditional Access
2. Create a new policy targeting Synapse workspaces
3. Configure conditions: user/group assignments, cloud apps (Azure Synapse Analytics)
4. Set access controls: require MFA, compliant devices

5. Enable the policy

6. Use Multi-Factor Authentication

Enable MFA for all administrative accounts:

1. Navigate to Azure AD > Security > MFA
2. Configure per-user MFA or conditional access policies
3. Apply to all accounts with administrative access to Synapse

Apply Principle of Least Privilege¶

1. Implement Granular RBAC

Assign specific roles based on job functions:

# Assign Synapse RBAC roles
$synapseSqlAdmin = "6e4bf58a-b8e1-4cc3-bbf9-d73143322b78" # Synapse SQL Administrator role
$synapseApache = "c3a6d2f1-a26f-4810-9b0f-591308d5cbf1" # Apache Spark Administrator role

New-AzSynapseRoleAssignment `
  -WorkspaceName "mysynapseworkspace" `
  -RoleId $synapseSqlAdmin `
  -ObjectId "00000000-0000-0000-0000-000000000000"

New-AzSynapseRoleAssignment `
  -WorkspaceName "mysynapseworkspace" `
  -RoleId $synapseApache `
  -ObjectId "00000000-0000-0000-0000-000000000001"

1. Implement SQL Role-Based Access Control

Use SQL-level security for granular data access:

-- Create database users
CREATE USER [analyst@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [reader@contoso.com] FROM EXTERNAL PROVIDER;

-- Assign database roles
ALTER ROLE db_datareader ADD MEMBER [reader@contoso.com];

-- Create custom role
CREATE ROLE data_analyst;
GRANT SELECT, EXECUTE ON SCHEMA::analytics TO data_analyst;
ALTER ROLE data_analyst ADD MEMBER [analyst@contoso.com];

1. Use Privileged Identity Management

Implement just-in-time privileged access:

1. Navigate to Azure AD > Privileged Identity Management
2. Configure Azure resources > Add role assignments
3. Add eligible assignments for Synapse roles
4. Configure role settings with appropriate activation requirements
5. Set up approval workflows for sensitive roles

Secure Service Principals and Managed Identities¶

1. Use Managed Identities

Leverage managed identities to eliminate stored credentials:

# Enable system-assigned managed identity
Update-AzSynapseWorkspace `
  -Name "mysynapseworkspace" `
  -ResourceGroupName "myresourcegroup" `
  -AssignIdentity

# Get the identity
$workspace = Get-AzSynapseWorkspace -Name "mysynapseworkspace" -ResourceGroupName "myresourcegroup"
$identityPrincipalId = $workspace.Identity.PrincipalId

# Assign permissions to the managed identity
New-AzRoleAssignment `
  -ObjectId $identityPrincipalId `
  -RoleDefinitionName "Storage Blob Data Contributor" `
  -Scope "/subscriptions/<subscription-id>/resourceGroups/myresourcegroup/providers/Microsoft.Storage/storageAccounts/mystorageaccount"

1. Secure Service Principals

If using service principals, follow these practices:

• Create dedicated service principals for each application/service
• Implement certificate-based authentication
• Rotate credentials regularly
• Apply least-privilege RBAC assignments
• Monitor service principal activities

# Create service principal with certificate
$cert = New-SelfSignedCertificate `
  -CertStoreLocation "cert:\CurrentUser\My" `
  -Subject "CN=SynapseSP" `
  -KeySpec KeyExchange

$keyValue = [System.Convert]::ToBase64String($cert.GetRawCertData())

$sp = New-AzADServicePrincipal `
  -DisplayName "SynapsePipelineSP" `
  -CertValue $keyValue `
  -EndDate $cert.NotAfter `
  -StartDate $cert.NotBefore

Data Protection Best Practices¶

Implement Encryption¶

1. Enable Transparent Data Encryption (TDE)

Ensure TDE is enabled for all SQL pools:

-- Enable TDE for dedicated SQL pool
ALTER DATABASE [MySQLPool] SET ENCRYPTION ON;

1. Use Customer-Managed Keys (CMK)

Implement customer-managed keys for storage and workspace encryption:

# Configure customer-managed keys
$keyVault = Get-AzKeyVault -VaultName "mykeyvault" -ResourceGroupName "myresourcegroup"
$key = Get-AzKeyVaultKey -VaultName $keyVault.VaultName -Name "mykey"

Update-AzSynapseWorkspace `
  -Name "mysynapseworkspace" `
  -ResourceGroupName "myresourcegroup" `
  -KeyName $key.Name `
  -KeyVaultName $keyVault.VaultName

1. Enable Always Encrypted

Protect sensitive columns using Always Encrypted:

-- Create column master key
CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/0123456789ABCDEF0123456789ABCDEF01234567'
);

-- Create column encryption key
CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
    COLUMN_MASTER_KEY = [CMK_Auto1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x01234...
);

-- Create table with encrypted columns
CREATE TABLE [dbo].[Patients](
   [PatientId] [int] IDENTITY(1,1),
   [SSN] [char](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
      ENCRYPTION_TYPE = DETERMINISTIC,
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
      COLUMN_ENCRYPTION_KEY = [CEK_Auto1]
   ),
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL
);

Implement Data-Level Security¶

1. Use Dynamic Data Masking

Mask sensitive data from non-privileged users:

-- Apply dynamic data masking
ALTER TABLE [dbo].[Customers]
ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)');

ALTER TABLE [dbo].[Customers]
ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE [dbo].[Customers]
ALTER COLUMN [Phone] ADD MASKED WITH (FUNCTION = 'default()');

1. Implement Row-Level Security (RLS)

Control row access based on user context:

-- Create filter predicate function
CREATE FUNCTION [Security].[tenantAccessPredicate](@TenantId INT)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);

-- Apply security policy to table
CREATE SECURITY POLICY [Security].[tenantAccessPolicy]
ADD FILTER PREDICATE [Security].[tenantAccessPredicate]([TenantId])
ON [dbo].[CustomerData],
ADD BLOCK PREDICATE [Security].[tenantAccessPredicate]([TenantId])
ON [dbo].[CustomerData];

-- Enable the security policy
ALTER SECURITY POLICY [Security].[tenantAccessPolicy] WITH (STATE = ON);

-- Set session context when connecting
EXEC sp_set_session_context @key = N'TenantId', @value = 42;

1. Implement Column-Level Security (CLS)

Restrict access to specific columns:

-- Deny access to specific columns
DENY SELECT ON [dbo].[Employees]([Salary], [SSN]) TO [Analyst];

-- Grant access to specific columns
GRANT SELECT ON [dbo].[Employees]([EmployeeId], [FirstName], [LastName], [Department]) TO [Analyst];

1. Data Classification and Sensitivity Labels

Implement data discovery and classification:

-- Add classification
ADD SENSITIVITY CLASSIFICATION TO
    [dbo].[Customers].[SSN] WITH (LABEL='Highly Confidential', INFORMATION_TYPE='National ID');

ADD SENSITIVITY CLASSIFICATION TO
    [dbo].[Patients].[Diagnosis] WITH (LABEL='Confidential', INFORMATION_TYPE='Medical');

-- View current classifications
SELECT * FROM sys.sensitivity_classifications;

Secure Data Storage and Movement¶

1. Use Azure Key Vault for Secrets Management

Store all credentials and secrets in Azure Key Vault:

# Create linked service using Key Vault
$keyVaultLinkedService = @{
    name = "AzureKeyVaultLinkedService"
    properties = @{
        type = "AzureKeyVault"
        typeProperties = @{
            baseUrl = "https://mykeyvault.vault.azure.net/"
        }
    }
}

$linkedService = New-AzSynapseLinkedService `
  -WorkspaceName "mysynapseworkspace" `
  -Name "AzureKeyVault" `
  -DefinitionFile (ConvertTo-Json $keyVaultLinkedService -Depth 20)

1. Secure Data Movement

Ensure encryption in transit for all data movement:

• Use private endpoints for data sources
• Use HTTPS/SSL for all external connections
• Implement ExpressRoute for on-premises connectivity

• Use TLS 1.2+ for all communications

• Implement Storage Security

Secure ADLS Gen2 storage:

# Configure Storage Account with secure transfer
New-AzStorageAccount `
  -ResourceGroupName "myresourcegroup" `
  -Name "mystorageaccount" `
  -Location "eastus" `
  -SkuName "Standard_LRS" `
  -Kind "StorageV2" `
  -EnableHierarchicalNamespace $true `
  -MinimumTlsVersion "TLS1_2" `
  -EnableHttpsTrafficOnly $true `
  -AllowBlobPublicAccess $false

Monitoring and Threat Protection Best Practices¶

Implement Comprehensive Monitoring¶

1. Enable Diagnostic Logging

Capture detailed diagnostics for all components:

# Enable diagnostic settings
$workspaceId = (Get-AzOperationalInsightsWorkspace -ResourceGroupName "myresourcegroup" -Name "myworkspace").ResourceId

Set-AzDiagnosticSetting `
  -ResourceId (Get-AzSynapseWorkspace -Name "mysynapseworkspace" -ResourceGroupName "myresourcegroup").Id `
  -Name "synapsediagnostics" `
  -WorkspaceId $workspaceId `
  -Enabled $true `
  -Category @("SynapseRbacOperations", "GatewayApiRequests", "BuiltinSqlReqsEnded", "IntegrationPipelineRuns", "IntegrationActivityRuns", "IntegrationTriggerRuns")

1. Configure Activity Log Alerting

Create alerts for critical operations:

# Create activity log alert
$actionGroupId = (Get-AzActionGroup -ResourceGroupName "myresourcegroup" -Name "SecurityTeam").Id

$condition = New-AzActivityLogAlertCondition `
  -Field "category" `
  -Equal "Administrative" `
  -Field "operationName" `
  -Equal "Microsoft.Synapse/workspaces/firewallRules/write"

New-AzActivityLogAlert `
  -Name "SynapseFirewallChange" `
  -ResourceGroupName "myresourcegroup" `
  -Condition $condition `
  -Scope "/subscriptions/<subscription-id>/resourceGroups/myresourcegroup/providers/Microsoft.Synapse/workspaces/mysynapseworkspace" `
  -ActionGroupId $actionGroupId

1. Use Microsoft Sentinel

Configure Microsoft Sentinel for advanced security monitoring:

• Connect Synapse workspace logs to Sentinel
• Implement analytical rules for threat detection
• Create custom dashboards for security monitoring
• Configure automated response with playbooks

# Deploy Sentinel ARM template
New-AzResourceGroupDeployment `
  -Name "SentinelDeployment" `
  -ResourceGroupName "myresourcegroup" `
  -TemplateFile "sentinel-synapse-connector.json"

Implement Advanced Threat Protection¶

1. Enable Microsoft Defender for Cloud

Activate Microsoft Defender for Cloud for Synapse workspaces:

# Enable Defender for Synapse
Set-AzSecurityPricing `
  -Name "SqlServers" `
  -PricingTier "Standard"

1. Configure SQL Auditing

Enable comprehensive auditing:

# Configure SQL Auditing
Set-AzSynapseSqlPoolAudit `
  -ResourceGroupName "myresourcegroup" `
  -WorkspaceName "mysynapseworkspace" `
  -Name "SQLPool01" `
  -AuditActionGroup @("SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "DATABASE_OPERATION_GROUP") `
  -LogAnalyticsTargetState "Enabled" `
  -WorkspaceResourceId $workspaceId

1. Implement Vulnerability Assessment

Regular vulnerability scanning and assessment:

# Enable Vulnerability Assessment
$storageAccount = Get-AzStorageAccount -ResourceGroupName "myresourcegroup" -Name "securitystorage"

Update-AzSynapseSqlPoolVulnerabilityAssessmentSetting `
  -ResourceGroupName "myresourcegroup" `
  -WorkspaceName "mysynapseworkspace" `
  -Name "SQLPool01" `
  -StorageAccountName $storageAccount.StorageAccountName `
  -ScanResultsContainerName "vulnerability-assessment"

1. Configure ATP for SQL Pools

Enable Advanced Threat Protection for SQL Pools:

# Enable ATP for SQL Pool
Update-AzSynapseSqlPoolAdvancedThreatProtectionSetting `
  -ResourceGroupName "myresourcegroup" `
  -WorkspaceName "mysynapseworkspace" `
  -Name "SQLPool01" `
  -NotificationRecipientsEmails "security@contoso.com" `
  -EmailAdmins $true `
  -ExcludedDetectionType "None"

Secure Development and Deployment Best Practices¶

Secure CI/CD Practices¶

1. Implement Pipeline Security

Follow secure CI/CD practices:

• Use separate development, testing, and production environments
• Implement approval workflows for production deployments
• Validate resources with Azure Policy before deployment

• Scan code for security issues during CI process

• Secure Resource Deployment

Use Infrastructure as Code with security validations:

# Deploy Synapse resources with ARM template
New-AzResourceGroupDeployment `
  -Name "SecureSynapseDeployment" `
  -ResourceGroupName "myresourcegroup" `
  -TemplateFile "secure-synapse-template.json" `
  -TemplateParameterFile "secure-synapse-params.json"

1. Implement Secrets Management

Use secure practices for managing secrets in pipelines:

• Use key rotation policies
• Implement just-in-time secrets access
• Audit all secrets access
• Use managed identities where possible

Secure Code Development¶

1. Implement Secure SQL Practices

Prevent SQL injection and other vulnerabilities:

-- Use parameterized queries
CREATE PROCEDURE [dbo].[GetUserData]
    @UserId INT
AS
BEGIN
    SELECT * FROM [dbo].[Users] WHERE [UserId] = @UserId;
END

1. Secure Spark Development

Follow secure development practices for Spark:

# Input validation
def process_data(input_path):
    # Validate input
    if not input_path.startswith('abfss://container@storage.dfs.core.windows.net/'):
        raise ValueError("Invalid input path")

    # Process data
    df = spark.read.parquet(input_path)

    # Sanitize outputs
    df = df.select(col("column1"), col("column2"))

    return df

1. Secure Notebook Development

Implement security in Jupyter notebooks:

• Don't store credentials in notebooks
• Use Key Vault-linked services for connections
• Implement proper error handling
• Validate all inputs and parameters

• Sanitize outputs for display

• Implement Code Reviews

Establish security-focused code review processes:

• Create a security review checklist
• Use automated code scanning tools
• Conduct peer reviews for security aspects
• Require approval from security team for sensitive areas

Secure Operations Best Practices¶

Implement Security Baselines¶

1. Document Security Standards

Create and maintain security baselines for all components:

• Network configuration standards
• Identity management standards
• Data protection standards

• Monitoring configuration standards

• Perform Regular Security Assessments

Conduct periodic security reviews:

• Vulnerability assessments
• Configuration drift analysis
• Penetration testing

• Compliance assessments

• Implement Security Patching

Keep all components updated:

• Apply security patches promptly
• Test patches in non-production environments
• Document patch management procedures
• Monitor for new security advisories

Incident Response¶

1. Create Incident Response Plan

Develop procedures for security incidents:

• Detection procedures
• Containment strategies
• Eradication steps
• Recovery procedures

• Post-incident analysis

• Implement Security Playbooks

Create automated response workflows:

# Deploy Logic App for security automation
New-AzResourceGroupDeployment `
  -Name "SecurityPlaybookDeployment" `
  -ResourceGroupName "myresourcegroup" `
  -TemplateFile "security-playbook.json"

1. Conduct Regular Drills

Practice responding to security incidents:

• Tabletop exercises
• Simulated breach scenarios
• Recovery testing
• Cross-team coordination exercises

Special Considerations for Hybrid Environments¶

Secure Hybrid Connectivity¶

1. Implement ExpressRoute

Use dedicated connections for hybrid scenarios:

# Configure ExpressRoute for Synapse
New-AzExpressRouteCircuit `
  -Name "SynapseExpressRoute" `
  -ResourceGroupName "myresourcegroup" `
  -Location "eastus" `
  -SkuTier "Standard" `
  -SkuFamily "MeteredData" `
  -ServiceProviderName "Equinix" `
  -PeeringLocation "Washington DC" `
  -BandwidthInMbps 200

1. Secure Self-hosted Integration Runtimes

Implement security for on-premises integration runtimes:

• Deploy in a secure network segment
• Implement network-level protection
• Update regularly for security patches
• Monitor runtime activities

• Implement host-based security

• Secure Credential Management

Manage credentials securely in hybrid scenarios:

• Use Key Vault for credential storage
• Implement credential rotation
• Audit credential access
• Use managed identities where applicable

Security Checklist¶

Use this checklist to ensure comprehensive security implementation:

Network Security¶

• Managed virtual network enabled
• Private endpoints configured for all services
• IP firewall rules restricted to necessary ranges
• NSGs implemented with least-privilege rules
• Service endpoints configured for Azure services
• Network traffic monitoring enabled

Identity and Access Management¶

• Azure AD authentication configured
• MFA enabled for all administrative accounts
• Conditional access policies implemented
• RBAC implemented with least-privilege principle
• PIM configured for privileged access
• Service principals secured with certificates and least-privilege

Data Protection¶

• TDE enabled for all SQL pools
• CMK configured for workspace encryption
• Data masking implemented for sensitive fields
• RLS policies configured for multi-tenant data
• CLS implemented for column-level protection
• Always Encrypted configured for sensitive columns

Monitoring and Threat Protection¶

• Diagnostic settings enabled for all components
• Microsoft Defender for Cloud activated
• SQL auditing configured with appropriate retention
• Vulnerability assessment enabled
• Advanced Threat Protection enabled
• Activity log alerts configured for security events

Secure Development and Deployment¶

• Secure CI/CD pipelines implemented
• Code scanning integrated into development workflow

• Secrets managed securely in Key Vault

• Infrastructure deployed using templates with security validations

• Separate environments for development, testing, and production

Related Topics¶

• Security Compliance Guide
• Network Security Configuration
• Data Protection Best Practices
• Monitoring and Logging Guide
• DevOps Security Best Practices

External Resources¶

• Azure Security Best Practices
• Azure Synapse Analytics Security White Paper
• Microsoft Security Blog