diegosouzapw SQL Server Security
Security Audit Workflow
Follow these numbered steps for a full security assessment.
- Check authentication mode — Windows-only preferred.
- Audit sysadmin members — should be minimal named accounts, no service accounts.
- Review db_owner memberships — across all user databases.
- Check dangerous server permissions — CONTROL SERVER, ALTER ANY LOGIN.
- Identify SQL logins with weak policy settings — no expiration or policy check.
- Assess surface area — xp_cmdshell, CLR, OLE Automation, linked servers.
- Verify encryption — TDE for data at rest, TLS for connections.
- Review audit configuration — failed logins at minimum; sensitive tables audited.
- Check TRUSTWORTHY databases — should be OFF on all user databases.
Top Security Vulnerabilities with Detection Queries
1. Authentication mode and SA account
-- 1 = Windows auth only (preferred), 0 = Mixed mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;
-- SA should be DISABLED and renamed
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name IN ('sa', 'SA');
Remediation:
ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [sql_admin_renamed]; -- Obfuscate the well-known name
2. Sysadmin members
SELECT m.name AS member_name, m.type_desc, m.is_disabled
FROM sys.server_role_members srm
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id
WHERE r.name = 'sysadmin'
ORDER BY m.type_desc, m.name;
-- All members should be named human DBA accounts; no application accounts
3. db_owner members across all databases
EXEC sp_MSforeachdb '
USE [?];
SELECT DB_NAME() AS db_name, dp.name AS member_name, dp.type_desc
FROM sys.database_role_members drm
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
WHERE r.name = ''db_owner'' AND dp.name NOT IN (''dbo'')';
4. Dangerous server-level permissions
SELECT
sp.name AS login_name,
sp.type_desc,
srvp.permission_name,
srvp.state_desc
FROM sys.server_permissions srvp
JOIN sys.server_principals sp ON srvp.grantee_principal_id = sp.principal_id
WHERE srvp.permission_name IN (
'CONTROL SERVER', 'ALTER ANY LOGIN',
'ALTER ANY DATABASE', 'IMPERSONATE ANY LOGIN'
)
ORDER BY sp.name;
5. SQL logins with weak policy
SELECT name, is_policy_checked, is_expiration_checked, is_disabled,
create_date, modify_date
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR is_expiration_checked = 0;
-- Enforce: ALTER LOGIN [name] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
6. TRUSTWORTHY databases
SELECT name, is_trustworthy_on
FROM sys.databases
WHERE is_trustworthy_on = 1;
-- Only msdb should be TRUSTWORTHY; disable on all user databases:
-- ALTER DATABASE [YourDB] SET TRUSTWORTHY OFF;
7. Cross-database ownership chaining
SELECT name, is_db_chaining_on FROM sys.databases WHERE is_db_chaining_on = 1;
-- Fix: ALTER DATABASE [YourDB] SET DB_CHAINING OFF;
-- Instance level:
SELECT name, value_in_use FROM sys.configurations
WHERE name = 'cross db ownership chaining';
Permission Review
All database-level permissions
SELECT
dp.name AS principal_name,
dp.type_desc AS principal_type,
o.name AS object_name,
o.type_desc AS object_type,
p.permission_name,
p.state_desc AS permission_state
FROM sys.database_permissions p
LEFT JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
ORDER BY dp.name, o.name, p.permission_name;
Orphaned users (login no longer exists)
SELECT dp.name AS user_name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
AND sp.name IS NULL;
-- Fix orphaned user by mapping to correct login
ALTER USER [OrphanedUser] WITH LOGIN = [MatchingLogin];
Effective permissions for a user
EXECUTE AS USER = 'AppUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;
TDE Setup (5 Steps)
Transparent Data Encryption encrypts data files, log files, and backups at rest. It is transparent to applications.
Step 1 — Create master key in master database
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPass!';
Step 2 — Create TDE certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate for Production';
Step 3 — Back up the certificate (MANDATORY — losing this key = permanent data loss)
BACKUP CERTIFICATE TDECert
TO FILE = 'D:\Certs\TDECert.cer'
WITH PRIVATE KEY (
FILE = 'D:\Certs\TDECert.pvk',
ENCRYPTION BY PASSWORD = 'CertBackupPass!'
);
-- Store this backup OFF the server in a secure vault
Step 4 — Create database encryption key
USE [YourDatabase];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
Step 5 — Enable TDE and monitor progress
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;
-- Monitor encryption progress
SELECT
DB_NAME(database_id) AS database_name,
encryption_state_desc,
percent_complete
FROM sys.dm_database_encryption_keys;
-- encryption_state 3 = Encrypted (complete)
Surface Area Reduction
Disable features that are not in use. Each enabled feature is an attack surface.
-- Disable xp_cmdshell (OS command execution from SQL)
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
-- Disable OLE Automation Procedures
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
-- Disable CLR (if not using CLR assemblies)
EXEC sp_configure 'clr enabled', 0; RECONFIGURE;
-- Disable remote admin connections (DAC accessible locally only)
EXEC sp_configure 'remote admin connections', 0; RECONFIGURE;
-- Disable cross-database ownership chaining at instance level
EXEC sp_configure 'cross db ownership chaining', 0; RECONFIGURE;
-- Verify current configuration
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (
'xp_cmdshell', 'Ole Automation Procedures', 'clr enabled',
'remote admin connections', 'cross db ownership chaining'
);
Check linked servers (often a privilege escalation path)
SELECT name, product, provider, data_source,
is_remote_login_enabled, is_rpc_out_enabled
FROM sys.servers
WHERE is_linked = 1;
-- Audit each linked server: who can use it and what permissions does it run under?
Verify TLS encryption on connections
SELECT session_id, encrypt_option, net_transport, client_net_address
FROM sys.dm_exec_connections
WHERE encrypt_option = 'FALSE';
-- Any FALSE = unencrypted connection. Enforce encryption via SQL Server Configuration Manager.
SQL Server Audit Configuration
Audit captures who did what and when. At minimum, audit failed logins.
-- Step 1: Create server audit (write to file)
CREATE SERVER AUDIT [DBA_SecurityAudit]
TO FILE (
FILEPATH = N'D:\Audit',
MAXSIZE = 100 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000,
ALTER SERVER AUDIT [DBA_SecurityAudit] WITH (STATE = ON);
-- Step 2: Create server audit specification
CREATE SERVER AUDIT SPECIFICATION [DBA_ServerAuditSpec]
FOR SERVER AUDIT [DBA_SecurityAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);
-- Step 3: Query audit log
SELECT event_time, action_id, succeeded, server_principal_name,
database_name, object_name, statement
FROM sys.fn_get_audit_file('D:\Audit\DBA_SecurityAudit*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;
Least-Privilege Application Access Pattern
Never grant db_datareader / db_datawriter directly. Use custom roles scoped to what the application needs.
-- Create a minimal-permission application role
CREATE ROLE [AppRole_Orders];
GRANT SELECT ON dbo.Orders TO [AppRole_Orders];
GRANT SELECT ON dbo.Customers TO [AppRole_Orders];
GRANT EXECUTE ON dbo.usp_PlaceOrder TO [AppRole_Orders];
GRANT INSERT ON dbo.Orders TO [AppRole_Orders];
-- Add the application user to the role only
CREATE USER [AppServiceUser] FOR LOGIN [DOMAIN\AppServiceAccount];
ALTER ROLE [AppRole_Orders] ADD MEMBER [AppServiceUser];
-- No db_datareader, no db_owner, no sysadmin
References
- Permissions model — full permission hierarchy, GRANT/DENY/REVOKE syntax
- Encryption guide — TDE and Always Encrypted setup steps
- Audit configuration — SQL Server Audit setup and querying
- Examples — security audit findings, fixing orphaned users, enabling TDE, configuring login auditing