aex.red

Windows SQL Server Database Review

Database Assessment Guide: DBA: SQL Audit Checklist For Internal Security Review

Automated Checks

PowerUpSQL https://github.com/NetSPI/PowerUpSQL https://github.com/NetSPI/PowerUpSQL/wiki

Set-=Install-Module -Name .\PowerUpSQL.ps1
# Or
cd .\PowerUpSQL-master\PowerUpSQL-master\

# From remote off domain:
runas.exe /noprofile /netonly /user:DOMAIN\Username powershell.exe

# General server info:
Get-SQLServerInfo -Verbose -Instance "10.0.0.1,[Port]" -Verbose

# Database config overview
Get-SQLDatabaseThreaded -Instance "ServerName,Port" -Verbose -NoDefaults

# Audit for issues 
Invoke-SQLAudit -Instance "10.0.0.1,[Port]" -Verbose > SQLAudit.txt

Advanced Queries

# Find sensitive columns that don't have encryption enabled
$Targets | Get-SQLDatabaseThreaded –Verbose –Threads 10 -NoDefaults | Where-Object {$_.is_encrypted –eq “FALSE”} | Get-SQLColumnSampleDataThreaded –Verbose –Threads 10 –Keyword “card, password” –SampleSize 2 –ValidateCC -NoDefaults

Invoke-SQLVulnerabilityScan (Only available on older servers, 2012-2017?)

# Install and Import
Install-Module -Name SqlServer -Scope CurrentUser -Force [-AllowClobber]
Import-Module SqlServer

Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "MyComputer\MainInstance" -Database "master" -ScanId "MyScan"

# Loop all DBs script
$serverInstance = "YourServerName"

Import-Module SqlServer

$databases = Invoke-Sqlcmd -ServerInstance $serverInstance -Query "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'"

foreach ($db in $databases) {
    $dbName = $db.name
    $scanId = [guid]::NewGuid()
    $reportPath = "C:\temp\$dbName-$scanId.json"
    Invoke-SqlVulnerabilityAssessmentScan `
        -ServerInstance $serverInstance `
        -Database $dbName `
        -ScanId $scanId `
        -ExportReport $reportPath `
        -Verbose
}

Invoke-SQLAssessment (Still available on newer servers - not a VA though)

# Install and Import
Install-Module -Name SqlServer -Scope CurrentUser -Force [-AllowClobber]
Import-Module SqlServer

# Connect, run + show results 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "YourServer\Instance"
$results = Invoke-SqlAssessment -InputObject $server
$results | Format-Table -AutoSize

Nessus / CIS

TODO

Manual Auditing

  • List databases and description:
EXEC sp_databases
  • Is the default database Master? Low-level users can gather more info this way.
SELECT name,type,type_desc,is_disabled,default_database_name from master.sys.server_principals WHERE default_database_name = 'master'

  • Test databases should be on test servers, not production.

  • List various roles:

EXEC sp_helprolemember db_owner
EXEC sp_helprolemember db_securityadmin
EXEC sp_helprolemember db_accessadmin
EXEC sp_helpsrvrolemember sysadmin
EXEC sp_helpsrvrolemember serveradmin
EXEC sp_helpsrvrolemember securityadmin
EXEC sp_helprotect [application rolename]
  • Does PUBLIC role have any (SELECT) permissions? Database level (run per DB):
SELECT dp.class_desc, dp.permission_name, dp.state_desc, OBJECT_NAME(major_id) AS object_name FROM sys.database_permissions dp JOIN sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id WHERE pr.name = 'public';
Server level (from master DB):

SELECT sp.class_desc, sp.permission_name, sp.state_desc FROM sys.server_permissions sp JOIN sys.server_principals pr ON sp.grantee_principal_id = pr.principal_id WHERE pr.name = 'public';
  • Any stored procedures enabled on any DBs?
EXEC sp_MSforeachdb 'USE [?]; IF DB_ID() NOT IN (1,2,3,4) BEGIN SELECT ''?'' AS database_name, SCHEMA_NAME(schema_id) AS schema_name, name AS procedure_name, create_date, modify_date FROM sys.procedures; END';

xp_name & sp_name are usually the riskiest ones.

  • SMO/DMO XPs enabled (Extended Stored Procedures)?
SELECT CAST(value AS INT) 'Config_Value' FROM [master].sys.configurations WHERE name = 'smo and dmo xps' SELECT CAST(value AS INT) 'Config_Value' FROM [master].sys.configurations WHERE name = 'agent xps'
  • Remote diagnostics (DAC) enabled? 0 is disabled.
EXEC sp_configure 'remote admin connections';

Authentication

  • Mixed mode enabled? SQL Server Management Studio > Object Explorer > Properties > Security > Server Authentication

  • List SQL Auth users:

SELECT * FROM sys.sql_logins
  • List all Windows Auth users:
USE master; SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP');
  • If SQL logins enabled, check password policy + expiration are enabled:
SELECT name, is_policy_checked, is_expiration_checked FROM sys.sql_logins WHERE is_disabled = 0 AND (is_policy_checked = 0 OR is_expiration_checked = 0);

  • Is Password Expiration Flag set? Local Accounts only SQL Server Management Studio (Object Explorer Panel) > Security > Logins > User > Properties > Enforce Password Expiration

  • SA account disabled? Otherwise renamed and strong password:

SELECT name, is_disabled
FROM sys.sql_logins
WHERE name = 'sa'
  • Auditing all logins? Server Properties > Security > Login Auditing

Service Configuration

  • Check permissions on SQL Server directory:
icacls "C:\Program Files\Microsoft SQL Server"
  • Check permissions for SQL Binn & COM directories:
icacls "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn"
icacls "C:\Program Files\Microsoft SQL Server\80\Tools\Binn"
icacls "C:\Program Files\Microsoft SQL Server\80\COM"`

  • Scheduled Task permissions?

  • SQL service running as Service Account, not SYSTEM?

Data Protection
  • Forced encryption (communication) enabled? SQL Configuration Manager > SQL Server Network Configuration > Right-click Protocols > Properties > ForceEncryption: Yes

Database encrypted? 0 means no.

SELECT name, is_encrypted from sys.databases;

  • Sensitive data masked? Is Dynamic Data Masking enabled for fields such as ‘password’ `TODO - might need a script to look for key words as columns names?

  • Backups encrypted? Copy over a .bak file and open in SSMS locally.

  • Endpoints encrypted with AES?

SELECT name, type_desc, is_encryption_enabled FROM sys.database_mirroring_endpoints
UNION
SELECT name, type_desc, encryption_algorithm_desc FROM sys.service_broker_endpoints;

Graph View