SQL Server 2025 Security Hardening on a Windows Server VPS (2026 MSP Guide)

powershell# Check what TLS protocols Windows Schannel allows
$base = 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols'
foreach ($p in 'TLS 1.0','TLS 1.1','TLS 1.2','TLS 1.3') {
    $path = "$base\$p\Server"
    $val = (Get-ItemProperty -Path $path -Name Enabled -ErrorAction SilentlyContinue).Enabled
    Write-Host "$p Server Enabled: $val"
}

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell'"

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT
    sl.name AS LoginName,
    sl.is_disabled,
    CASE WHEN srm.role_principal_id IS NOT NULL THEN 'YES' ELSE 'no' END AS IsSysadmin
FROM sys.sql_logins sl
LEFT JOIN sys.server_role_members srm
    ON sl.principal_id = srm.member_principal_id
    AND srm.role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE name = 'sysadmin')
WHERE sl.name = 'sa'
UNION ALL
SELECT
    sp.name,
    sp.is_disabled,
    CASE WHEN srm.role_principal_id IS NOT NULL THEN 'YES' ELSE 'no' END
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm
    ON sp.principal_id = srm.member_principal_id
    AND srm.role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE name = 'sysadmin')
WHERE sp.type IN ('U','G') AND sp.is_disabled = 0
"@

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query "ALTER LOGIN sa DISABLE;"

# Verify
Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT name, type_desc, is_disabled, modify_date
FROM sys.server_principals
WHERE name = 'sa'
"@

sql-- Rename first, then disable the renamed account
ALTER LOGIN sa WITH NAME = sa_disabled_20260430;
ALTER LOGIN sa_disabled_20260430 DISABLE;

sql-- Create the application database (skip if it already exists)
CREATE DATABASE RaffApp01;

-- Create the server-level login with strong policy enforcement
CREATE LOGIN app_raffapp01
WITH PASSWORD = 'TempStrongPassword2026!ReplaceMe',
     DEFAULT_DATABASE = RaffApp01,
     CHECK_EXPIRATION = ON,
     CHECK_POLICY = ON;

-- Map login to a database user inside RaffApp01
USE RaffApp01;
CREATE USER app_raffapp01 FOR LOGIN app_raffapp01;

-- Grant only what an app needs: read + write within this DB
ALTER ROLE db_datareader ADD MEMBER app_raffapp01;
ALTER ROLE db_datawriter ADD MEMBER app_raffapp01;

-- Optional: allow EXECUTE on stored procedures (if your app uses procs)
-- GRANT EXECUTE TO app_raffapp01;

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT
    sl.name AS Login,
    sl.is_disabled,
    sl.default_database_name,
    sl.is_policy_checked,
    sl.is_expiration_checked,
    CASE WHEN srm.role_principal_id IS NOT NULL THEN 'YES' ELSE 'no' END AS IsSysadmin
FROM sys.sql_logins sl
LEFT JOIN sys.server_role_members srm
    ON sl.principal_id = srm.member_principal_id
    AND srm.role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE name = 'sysadmin')
WHERE sl.name = 'app_raffapp01'
"@

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Database "RaffApp01" -Query @"
SELECT dp.name AS DBUser, USER_NAME(rm.role_principal_id) AS RoleMembership
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members rm ON dp.principal_id = rm.member_principal_id
WHERE dp.name = 'app_raffapp01'
"@

powershell# Create the audit folder first
New-Item -ItemType Directory -Path "C:\SQL\Audit" -Force

sql-- 1. Create the audit target (where logs go)
CREATE SERVER AUDIT Raff_SecurityAudit
TO FILE (
    FILEPATH = 'C:\SQL\Audit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);

-- 2. Create the audit specification (what events to log)
CREATE SERVER AUDIT SPECIFICATION Raff_SecurityAudit_Spec
FOR SERVER AUDIT Raff_SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);

-- 3. Enable the audit target
ALTER SERVER AUDIT Raff_SecurityAudit
WITH (STATE = ON);

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT name, type_desc, is_state_enabled, create_date FROM sys.server_audits"

Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT sas.name, sas.is_state_enabled, sasd.audit_action_name
FROM sys.server_audit_specifications sas
JOIN sys.server_audit_specification_details sasd
    ON sas.server_specification_id = sasd.server_specification_id
ORDER BY sas.name, sasd.audit_action_name
"@

powershell# Trigger a deliberate failed login
try {
    Invoke-Sqlcmd -ServerInstance "localhost" -Username "sa" -Password "WrongPassword123!" -Query "SELECT 1" -ErrorAction Stop
} catch {
    Write-Host "Failed login triggered (expected)" -ForegroundColor Yellow
}

# Wait for the audit queue to flush (we set QUEUE_DELAY = 1000ms)
Start-Sleep -Seconds 3

# Read the audit log
Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT TOP 20
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    client_ip,
    application_name,
    statement
FROM sys.fn_get_audit_file('C:\SQL\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('LGIF', 'LGIS')
ORDER BY event_time DESC
"@

sql-- All failed logins in the last 24 hours, grouped by source IP
SELECT
    client_ip,
    server_principal_name,
    COUNT(*) AS FailedAttempts,
    MIN(event_time) AS FirstAttempt,
    MAX(event_time) AS LastAttempt
FROM sys.fn_get_audit_file('C:\SQL\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'
  AND event_time > DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY client_ip, server_principal_name
ORDER BY FailedAttempts DESC;

-- All schema changes in the last 7 days
SELECT
    event_time,
    server_principal_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\SQL\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('CR', 'AL', 'DR')
  AND event_time > DATEADD(DAY, -7, GETUTCDATE())
ORDER BY event_time DESC;

powershell$tcpKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp'
Get-ItemProperty -Path $tcpKey | Select-Object Enabled, ListenOnAllIPs, DisplayName

powershell# Enable TCP/IP
Set-ItemProperty -Path $tcpKey -Name Enabled -Value 1

# Restart SQL Server service for the change to take effect
Restart-Service -Name MSSQLSERVER -Force

# Verify
Get-ItemProperty -Path $tcpKey | Select-Object Enabled, ListenOnAllIPs, DisplayName

# Confirm SQL is now listening
Test-NetConnection -ComputerName localhost -Port 1433 -InformationLevel Quiet

powershell# Create the rule with a placeholder Any scope first
New-NetFirewallRule `
    -DisplayName "SQL Server (TCP 1433)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 1433 `
    -RemoteAddress Any `
    -Action Allow `
    -Profile Any

# Immediately tighten — replace 203.0.113.10 with YOUR admin/office public IP
Set-NetFirewallRule `
    -DisplayName "SQL Server (TCP 1433)" `
    -RemoteAddress "203.0.113.10/32"

# Verify
Get-NetFirewallRule -DisplayName "SQL Server (TCP 1433)" |
    Get-NetFirewallAddressFilter |
    Select-Object @{n='Rule';e={'SQL Server (TCP 1433)'}}, RemoteAddress, LocalAddress

sql.customeracme.com.   A   <Raff Server public IP>

powershell# From an admin PowerShell session
cd "C:\Program Files\win-acme"
.\wacs.exe `
    --target manual `
    --host sql.customeracme.com `
    --validation http-01 `
    --validationport 80 `
    --store certificatestore `
    --certificatestore My `
    --installation script `
    --script "scripts\ImportSqlServer.ps1"

powershellGet-ChildItem -Path Cert:\LocalMachine\My |
    Where-Object Subject -like "*sql.customeracme.com*" |
    Select-Object Subject, Thumbprint, NotAfter

powershell$thumbprint = "PASTE_THUMBPRINT_HERE_LOWERCASE"
$regPath = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
Set-ItemProperty -Path $regPath -Name Certificate -Value $thumbprint

# Grant the SQL service account read access to the private key
$cert = Get-ChildItem -Path Cert:\LocalMachine\My\$thumbprint
$keyPath = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$($cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName)"
$acl = Get-Acl $keyPath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule("NT Service\MSSQLSERVER", "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl $keyPath $acl

# Restart SQL Server to pick up the new cert
Restart-Service -Name MSSQLSERVER -Force

powershell# Check the SQL error log — it'll log which certificate it loaded
Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" |
    Select-String -Pattern "certificate"

sql-- 1. Master key in master DB
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword2026!';

-- 2. Certificate in master DB
CREATE CERTIFICATE Raff_TDE_Cert WITH SUBJECT = 'Raff TDE Certificate';

-- 3. BACKUP THE CERTIFICATE IMMEDIATELY — losing it means losing your data
BACKUP CERTIFICATE Raff_TDE_Cert
TO FILE = 'C:\SQL\Backups\Raff_TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SQL\Backups\Raff_TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'StrongCertPrivateKeyPassword2026!'
);

-- 4. Enable TDE on a specific database
USE RaffApp01;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE Raff_TDE_Cert;

ALTER DATABASE RaffApp01 SET ENCRYPTION ON;

sql-- Use the same Raff_TDE_Cert from the TDE section, OR create a backup-specific one
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword2026!';
CREATE CERTIFICATE Raff_Backup_Cert WITH SUBJECT = 'Raff Backup Encryption';

-- Backup the certificate to off-server storage immediately
BACKUP CERTIFICATE Raff_Backup_Cert
TO FILE = 'C:\SQL\Backups\Raff_Backup_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SQL\Backups\Raff_Backup_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'StrongCertPrivateKeyPassword2026!'
);

-- Encrypted backup
BACKUP DATABASE RaffApp01
TO DISK = 'C:\SQL\Backups\RaffApp01_Encrypted.bak'
WITH COMPRESSION,
     ENCRYPTION (
         ALGORITHM = AES_256,
         SERVER CERTIFICATE = Raff_Backup_Cert
     );

powershellInvoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT @@VERSION, SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('ProductUpdateLevel')"

SQL SERVER 2025 HARDENING — RAFF SERVER DEPLOYMENT CHECKLIST

INSTANCE BASICS
[ ] SQL Server 2025 installed on Windows Server 2025
[ ] Windows pending-restart cleared before install
[ ] Latest Cumulative Update applied (target: CU1 or later)
[ ] Mixed Mode auth (only if app requires SQL logins; otherwise Windows-only)
[ ] Volume Maintenance Tasks privilege granted to SQL service
[ ] SQL Server Agent set to Automatic startup

ACCOUNT HARDENING
[ ] sa account disabled
[ ] sa account renamed (optional but recommended for internet-exposed instances)
[ ] Windows admin account verified working as backup sysadmin
[ ] Per-application logins created with CHECK_POLICY = ON, CHECK_EXPIRATION = ON
[ ] Each app login has only db_datareader + db_datawriter (not db_owner, not sysadmin)
[ ] No app login has cross-database permissions

NETWORK HARDENING
[ ] TCP/IP enabled at SuperSocketNetLib registry level
[ ] SQL service restarted after enabling TCP/IP
[ ] Windows Firewall rule for TCP 1433 created
[ ] Firewall rule scoped to specific admin IPs (not Any)
[ ] No public exposure of TCP 1433 to 0.0.0.0/0

ENCRYPTION
[ ] Self-signed certificate replaced with trusted CA cert (Let's Encrypt or internal CA)
[ ] Certificate has FQDN + NetBIOS name in SAN
[ ] SQL service granted read access to certificate private key
[ ] win-acme renewal task scheduled (if using Let's Encrypt)
[ ] TDE configured if compliance requires (with certificate backup off-server)
[ ] Backup encryption configured (with certificate backup off-server)

AUDITING
[ ] Server Audit created with file target on dedicated path (C:\SQL\Audit\)
[ ] Server Audit Specification created with login, role, permission, schema, principal action groups
[ ] Both audit objects enabled (STATE = ON)
[ ] Failed login test event captured in audit log (verified via sys.fn_get_audit_file)
[ ] Audit log retention strategy defined (centralized storage / SIEM forwarding)

OPERATIONAL
[ ] Database backups configured with encryption + off-server storage
[ ] Backup encryption certificate backed up to off-server secure storage
[ ] Database Master Key backed up off-server
[ ] CU patching cadence agreed with client (30/60-day SLA)
[ ] SQL Server build documented in client runbook
[ ] Emergency contacts and recovery procedures documented
[ ] Audit log query templates saved for incident response

CLIENT HANDOFF
[ ] sa password and admin credentials in client password manager (not yours)
[ ] Database master key password in client password manager
[ ] Certificate private key password in client password manager
[ ] Documentation of all customizations vs Microsoft defaults
[ ] First-90-day support window defined

powershellWrite-Host "`n=== SQL Server 2025 Hardening Verification ===" -ForegroundColor Cyan
Write-Host "Run as Administrator on the Raff Server`n" -ForegroundColor DarkGray

# 1. sa account disabled
Write-Host "1. sa account state" -ForegroundColor Cyan
$saState = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT is_disabled FROM sys.server_principals WHERE name = 'sa'"
if ($saState.is_disabled) {
    Write-Host "   PASS: sa is disabled" -ForegroundColor Green
} else {
    Write-Host "   FAIL: sa is ENABLED — disable with ALTER LOGIN sa DISABLE;" -ForegroundColor Red
}

# 2. xp_cmdshell off
Write-Host "`n2. xp_cmdshell state" -ForegroundColor Cyan
$xp = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell'"
if ($xp.value_in_use -eq 0) {
    Write-Host "   PASS: xp_cmdshell is disabled" -ForegroundColor Green
} else {
    Write-Host "   FAIL: xp_cmdshell is ENABLED — disable via sp_configure 'xp_cmdshell', 0; RECONFIGURE;" -ForegroundColor Red
}

# 3. App logins are NOT sysadmin
Write-Host "`n3. SQL logins with sysadmin role (excluding sa)" -ForegroundColor Cyan
$sysadmins = Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT sl.name
FROM sys.sql_logins sl
JOIN sys.server_role_members srm ON sl.principal_id = srm.member_principal_id
JOIN sys.server_principals sp ON srm.role_principal_id = sp.principal_id
WHERE sp.name = 'sysadmin' AND sl.name <> 'sa'
"@
if ($sysadmins) {
    Write-Host "   FAIL: SQL logins with sysadmin (review and remove unless intentional):" -ForegroundColor Red
    $sysadmins | ForEach-Object { Write-Host "          - $($_.name)" -ForegroundColor Red }
} else {
    Write-Host "   PASS: no non-sa SQL logins are sysadmin" -ForegroundColor Green
}

# 4. SQL logins missing password policy enforcement
Write-Host "`n4. SQL logins without CHECK_POLICY = ON" -ForegroundColor Cyan
$weakLogins = Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT name FROM sys.sql_logins
WHERE is_policy_checked = 0
  AND name NOT LIKE '##%'
  AND name <> 'sa'
"@
if ($weakLogins) {
    Write-Host "   WARN: SQL logins without password policy:" -ForegroundColor Yellow
    $weakLogins | ForEach-Object { Write-Host "          - $($_.name)" -ForegroundColor Yellow }
} else {
    Write-Host "   PASS: all non-sa SQL logins enforce password policy" -ForegroundColor Green
}

# 5. SQL Server Audit enabled
Write-Host "`n5. SQL Server Audit state" -ForegroundColor Cyan
$audits = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT name, is_state_enabled FROM sys.server_audits"
if ($audits -and ($audits | Where-Object is_state_enabled -eq $true)) {
    Write-Host "   PASS: at least one Server Audit is running" -ForegroundColor Green
    $audits | ForEach-Object { Write-Host "          - $($_.name): enabled=$($_.is_state_enabled)" -ForegroundColor DarkGray }
} else {
    Write-Host "   FAIL: no Server Audit is enabled" -ForegroundColor Red
}

# 6. Audit specification covers login events
Write-Host "`n6. Audit specification action groups" -ForegroundColor Cyan
$auditSpecs = Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT sasd.audit_action_name
FROM sys.server_audit_specifications sas
JOIN sys.server_audit_specification_details sasd ON sas.server_specification_id = sasd.server_specification_id
WHERE sas.is_state_enabled = 1
"@
$expected = @('FAILED_LOGIN_GROUP','SUCCESSFUL_LOGIN_GROUP','SERVER_ROLE_MEMBER_CHANGE_GROUP','SCHEMA_OBJECT_CHANGE_GROUP')
$captured = $auditSpecs.audit_action_name
$missing = $expected | Where-Object { $_ -notin $captured }
if (-not $missing) {
    Write-Host "   PASS: critical action groups captured" -ForegroundColor Green
} else {
    Write-Host "   WARN: action groups missing from audit:" -ForegroundColor Yellow
    $missing | ForEach-Object { Write-Host "          - $_" -ForegroundColor Yellow }
}

# 7. TCP/IP enabled at protocol level
Write-Host "`n7. SQL Server TCP/IP protocol" -ForegroundColor Cyan
$tcpKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp'
$tcp = Get-ItemProperty -Path $tcpKey -ErrorAction SilentlyContinue
if ($tcp.Enabled -eq 1) {
    Write-Host "   PASS: TCP/IP is enabled" -ForegroundColor Green
} else {
    Write-Host "   FAIL: TCP/IP is DISABLED — remote clients cannot connect" -ForegroundColor Red
}

# 8. Firewall rule for 1433 has restricted scope
Write-Host "`n8. Firewall rule scope for TCP 1433" -ForegroundColor Cyan
$fwRules = Get-NetFirewallPortFilter -Protocol TCP |
    Where-Object LocalPort -eq 1433 |
    ForEach-Object { $_ | Get-NetFirewallRule | Where-Object { $_.Enabled -eq 'True' -and $_.Direction -eq 'Inbound' -and $_.Action -eq 'Allow' } }

if (-not $fwRules) {
    Write-Host "   WARN: no inbound Allow rule for TCP 1433 (remote clients will be blocked)" -ForegroundColor Yellow
} else {
    foreach ($rule in $fwRules) {
        $scope = ($rule | Get-NetFirewallAddressFilter).RemoteAddress
        if ($scope -eq 'Any') {
            Write-Host "   FAIL: rule '$($rule.DisplayName)' allows TCP 1433 from ANY remote IP" -ForegroundColor Red
        } else {
            Write-Host "   PASS: rule '$($rule.DisplayName)' scoped to $scope" -ForegroundColor Green
        }
    }
}

# 9. SQL Server certificate (is it self-signed or CA-issued?)
Write-Host "`n9. SQL Server TLS certificate" -ForegroundColor Cyan
$certThumb = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib' -Name Certificate -ErrorAction SilentlyContinue).Certificate
if (-not $certThumb) {
    Write-Host "   WARN: no explicit certificate configured — SQL is using auto-generated self-signed cert" -ForegroundColor Yellow
    Write-Host "         Modern drivers reject self-signed by default. Replace with Let's Encrypt or internal CA." -ForegroundColor DarkGray
} else {
    $cert = Get-ChildItem -Path Cert:\LocalMachine\My\$certThumb -ErrorAction SilentlyContinue
    if ($cert.Issuer -eq $cert.Subject) {
        Write-Host "   WARN: configured certificate is self-signed (Issuer = Subject)" -ForegroundColor Yellow
    } else {
        Write-Host "   PASS: certificate issued by external CA" -ForegroundColor Green
        Write-Host "         Issuer: $($cert.Issuer)" -ForegroundColor DarkGray
        Write-Host "         Subject: $($cert.Subject)" -ForegroundColor DarkGray
        Write-Host "         Expires: $($cert.NotAfter)" -ForegroundColor DarkGray
    }
}

# 10. Recent failed login attempts (last 24h) — useful for spotting attacks
Write-Host "`n10. Failed logins in the last 24 hours" -ForegroundColor Cyan
try {
    $failedLogins = Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
SELECT
    client_ip,
    server_principal_name,
    COUNT(*) AS FailedAttempts
FROM sys.fn_get_audit_file('C:\SQL\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'
  AND event_time > DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY client_ip, server_principal_name
ORDER BY FailedAttempts DESC
"@ -ErrorAction Stop

    if (-not $failedLogins) {
        Write-Host "   PASS: no failed login attempts in last 24 hours" -ForegroundColor Green
    } else {
        Write-Host "   INFO: failed login activity detected:" -ForegroundColor Yellow
        $failedLogins | Format-Table -AutoSize
    }
} catch {
    Write-Host "   SKIP: audit log not readable (audit may not be enabled or path is wrong)" -ForegroundColor DarkGray
}

Write-Host "`n=== Verification complete ===" -ForegroundColor Cyan
Write-Host "Re-run weekly to detect drift.`n" -ForegroundColor DarkGray

powershell# Save the script as C:\SQL\Scripts\Verify-SQLHardening.ps1
# Then schedule it weekly:
$action = New-ScheduledTaskAction `
    -Execute "PowerShell.exe" `
    -Argument "-NoProfile -ExecutionPolicy Bypass -File C:\SQL\Scripts\Verify-SQLHardening.ps1 *> C:\SQL\Audit\hardening-check.log"
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Monday -At 6am
Register-ScheduledTask `
    -TaskName "SQL Hardening Verification" `
    -Action $action `
    -Trigger $trigger `
    -RunLevel Highest `
    -User "SYSTEM"