SQL Server Security – Best Practices

Protecting your data is critical. This guide provides a comprehensive set of best practices to secure SQL Server installations, from authentication to continuous monitoring.

Authentication Critical

• Prefer Windows Authentication over SQL Authentication whenever possible.
• Enforce strong password policies for SQL logins: minimum length 12, complexity, and expiration.
• Use Azure Active Directory authentication for cloud workloads.
• Disable the sa account or rename it and assign a strong password.

-- Example: Enforce password policy
ALTER LOGIN [MyLogin] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

Authorization

Grant the least privileges necessary.

• Use role‑based access control (RBAC) with built‑in or custom database roles.
• Avoid granting sysadmin or db_owner unless absolutely required.
• Apply EXECUTE AS for modules that need elevated rights temporarily.

-- Create a role with limited permissions
CREATE ROLE db_datareader_limited;
GRANT SELECT ON SCHEMA::dbo TO db_datareader_limited;

Encryption

Encrypt data at rest and in transit.

• Enable Transparent Data Encryption (TDE) for databases.
• Use Always Encrypted for sensitive columns.
• Force TLS 1.2+ for all client connections.

-- Enable TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
ALTER DATABASE MyDB SET ENCRYPTION ON;

Auditing & Monitoring

• Enable SQL Server Audit to capture login attempts, permission changes, and data access.
• Integrate with Azure Monitor or a SIEM solution for real‑time alerts.
• Regularly review sys.dm_exec_sessions and sys.dm_exec_requests for suspicious activity.

-- Create a server audit
CREATE SERVER AUDIT MyAudit
TO FILE (FILEPATH = 'C:\Audit\' );
ALTER SERVER AUDIT MyAudit WITH (STATE = ON);

Secure Configuration

• Disable unnecessary features (e.g., XP_CMDSHELL, OLE Automation).
• Apply the latest cumulative updates and security patches.
• Restrict network access: use firewalls and limit the IP ranges that can connect.

-- Disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Common Vulnerabilities

Additional Resources

• Encryption Overview
• Authentication Guide
• Auditing Best Practices
• Microsoft Security Documentation