Lab: SQL Server Security Assessment (SQL DBSAT)

Run a 57-check security audit against any SQL Server platform — on-premises, Azure SQL Database, or Azure SQL Managed Instance. Get a scored HTML report with prioritized findings, equivalent to Oracle's DBSAT.

DP-300 Domain: 2 — Implement a Secure Environment (15-20%)

Architecture

Assessment Categories

10 Security Categories

🔐

Authentication & Access (7 checks)

Mixed mode, sa account, password policy, orphaned users, guest access, sysadmin membership

🔒

Encryption (4 checks)

TDE status, TLS connections, backup encryption, key rotation age

👥

Privileges & Roles (5 checks)

db_owner count, CONTROL SERVER, impersonation chains, cross-DB chaining

📋

Auditing & Compliance (4 checks)

Login auditing level, SQL Server Audit, Defender for SQL, Common Criteria

🛡️

Surface Area (5 checks)

xp_cmdshell, OLE Automation, linked servers, TRUSTWORTHY, AUTO_CLOSE

📦

Patch Level (2 checks)

End-of-life detection (2008-2016), cumulative update currency

🔍

Sensitive Data (3 checks)

40+ PII patterns, data classification labels, Dynamic Data Masking

⚙️

Database Config (4 checks)

Ownership, EXECUTE AS, unsafe CLR assemblies, contained databases

☁️

Azure SQL Security (12 checks)

AAD-only auth, firewall analysis, connection stats, geo-replication, ledger

🛡️

Advanced Protection (11 checks)

Always Encrypted, RLS, UNMASK grants, dynamic SQL risks, schema ownership

Prerequisites

• PowerShell 5.1+ or 7+
• SqlServer module (auto-installed if missing)
• Azure CLI (for Azure SQL targets with AAD auth)

# Install SqlServer module if needed
Install-Module SqlServer -Force -AllowClobber -Scope CurrentUser

Lab Steps

Deploy Target Database

Create an Azure SQL Database with sample data (or use an existing server)

Clone SQL DBSAT

Get the assessment tool from the repository

Run the Assessment

Execute the scan against your target database

Analyze the Report

Review findings, understand severity levels, identify remediation priorities

Remediate Critical Findings

Fix the highest-severity issues and re-run the assessment

Step 1: Deploy an Azure SQL Database

$resourceGroup = "rg-security-lab"
$location = "westeurope"
$serverName = "sql-seclab-$(Get-Random -Maximum 9999)"
$adminPassword = "<YOUR_STRONG_PASSWORD>"

# Create resource group
az group create --name $resourceGroup --location $location

# Create SQL Server
az sql server create `
    --resource-group $resourceGroup `
    --name $serverName `
    --admin-user sqladmin `
    --admin-password $adminPassword `
    --location $location

# Create database with sample data
az sql db create `
    --resource-group $resourceGroup `
    --server $serverName `
    --name SecurityLabDB `
    --service-objective S0 `
    --sample-name AdventureWorksLT

# Allow your IP through firewall
$myIp = (Invoke-RestMethod -Uri https://api.ipify.org)
az sql server firewall-rule create `
    --resource-group $resourceGroup `
    --server $serverName `
    --name AllowMyIP `
    --start-ip-address $myIp `
    --end-ip-address $myIp

Step 2: Clone SQL DBSAT

git clone https://github.com/ehabmostafa_microsoft/sql-dbsat.git
cd sql-dbsat

Step 3: Run the Assessment

# Option A: Azure AD authentication (recommended)
.\Invoke-SqlSecurityAssessment.ps1 `
    -ServerInstance "$serverName.database.windows.net" `
    -Database "SecurityLabDB" `
    -AuthMode AAD

# Option B: SQL Authentication
$cred = Get-Credential  # Enter sqladmin credentials
.\Invoke-SqlSecurityAssessment.ps1 `
    -ServerInstance "$serverName.database.windows.net" `
    -Database "SecurityLabDB" `
    -AuthMode SQL `
    -Credential $cred

The tool generates an HTML report in the current directory:

SQL_DBSAT_sql-seclab-1234.database.windows.net_20260311_120000.html

Step 4: Analyze the Report

Open the HTML report in a browser. Key sections:

Section	What to Look For
Overview Dashboard	Overall risk score (0-100), finding distribution by severity
Critical Findings	Items requiring immediate action (red badges)
High Findings	Important security gaps (orange badges)
Sensitive Data	PII columns found (emails, phone numbers, addresses)
Recommendations	Prioritized remediation steps for each finding

🏢 Real-World DBA Note

In customer engagements, the security assessment report is the first deliverable. Run it against the customer's existing SQL Server to establish a baseline, then re-run after remediation to show improvement.

Step 5: Remediate Common Findings

Example: Enable TDE (if not enabled)

-- Azure SQL DB has TDE enabled by default
-- For on-prem, enable manually:
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE [SecurityLabDB] SET ENCRYPTION ON;

Example: Enable auditing

az sql server audit-policy update `
    --resource-group $resourceGroup `
    --name $serverName `
    --state Enabled `
    --storage-account "<storage_account_name>"

Re-run the assessment to verify improvements:

.\Invoke-SqlSecurityAssessment.ps1 `
    -ServerInstance "$serverName.database.windows.net" `
    -Database "SecurityLabDB" `
    -AuthMode AAD

Verification

Compare the before/after scores:

# The report filename includes the timestamp
# Compare the Overall Risk Score between runs
Get-ChildItem *.html | Sort-Object LastWriteTime | Select-Object Name, LastWriteTime

Common Failures

Symptom	Likely Cause	Fix
DBSAT export fails: "insufficient privileges"	Service account missing SELECT on sec views	Grant VIEW SERVER STATE + VIEW DATABASE STATE
Report shows "TDE not enabled" but it is	Scan ran against wrong DB context	Verify USE <db> in script; re-run per-DB
Auditing finding remains after enabling	Audit policy enabled but no destination set	Set Log Analytics workspace or storage account as audit target
Vulnerability Assessment doesn't auto-remediate	VA reports only; remediation is manual	Apply baselines, then re-run scan to confirm
Defender for SQL alerts noisy	Default rules too sensitive for dev	Tune alert suppression; scope Defender to prod subscriptions

⚠️ Watch Out

A clean DBSAT report doesn't mean compliant. DBSAT checks SQL-level controls; it doesn't validate network isolation, Key Vault rotation, or Entra ID integration. Layer DBSAT + Microsoft Defender for SQL + Azure Policy for full coverage.

Variations to Try

• Run DBSAT on on-prem SQL then on Azure SQL DB / MI — compare findings
• Enable Microsoft Defender for SQL — trigger a known anomaly (failed login flood)
• Apply Azure Policy initiative for SQL security baseline — measure drift
• Pair with Vulnerability Assessment — export baseline, re-scan monthly
• Add Always Encrypted to a column flagged as sensitive — measure app impact

Production Application

• Quarterly compliance audit → DBSAT scan, attach report to audit evidence pack.
• Continuous monitoring → Microsoft Defender for SQL + Sentinel rules + Action Groups.
• Regulated workload (HIPAA, PCI, GDPR) → DBSAT + VA + TDE + Always Encrypted + Auditing to Log Analytics.
• Anti-pattern: don't ship DBSAT raw HTML to auditors — triage findings first, document false positives, attach remediation evidence per finding.

Clean Up

az group delete --name rg-security-lab --yes --no-wait

Key Takeaways

🧠 Smart order (weak cards first)

What are the top 3 security checks for Azure SQL Database?

Click to reveal answer

1. **AAD-only authentication** — disable SQL auth in production. 2. **Private Endpoints** — no public firewall rules. 3. **Defender for SQL** — enables vulnerability assessment and advanced threat protection.

1 / 1

🧠 Smart order (weak cards first)

Why is read-only assessment important?

Click to reveal answer

SQL DBSAT executes only SELECT queries. It never modifies data or configurations. This is critical for production environments — you can run it safely against live databases without risk of data corruption or configuration changes.

1 / 1

🧠 Smart order (weak cards first)

What does a risk score of 75+ mean?

Click to reveal answer

A score of 75+ indicates critical security gaps. These are typically: mixed-mode auth with weak sa password, no TDE, disabled auditing, excessive sysadmin membership, or exposed xp_cmdshell. Each finding maps to a CIS benchmark or Microsoft security baseline.

1 / 1

🏢 Real-World DBA Note

Source code: The full SQL DBSAT tool with 57 checks, HTML report generator, and cross-platform support is at github.com/ehabmostafa_microsoft/sql-dbsat.