SQL Server Security Best Practices: Protecting Your Data

SQL Server Security Best Practices: Protecting Your Data

Introduction

Database security is critical for protecting sensitive data from unauthorized access and breaches. This guide covers SQL Server authentication modes, encryption techniques (TDE, Always Encrypted), SQL injection prevention, auditing, row-level security, and comprehensive best practices for securing enterprise databases.

Authentication and Authorization

Authentication Modes

Windows Authentication (Recommended):

-- Create Windows login
CREATE LOGIN [CONTOSO\JohnDoe] FROM WINDOWS;

-- Grant access to database
USE [AdventureWorks];
CREATE USER [CONTOSO\JohnDoe] FOR LOGIN [CONTOSO\JohnDoe];

-- Add to database role
ALTER ROLE db_datareader ADD MEMBER [CONTOSO\JohnDoe];

SQL Server Authentication:

-- Create SQL login with strong password policy
CREATE LOGIN AppUser 
WITH PASSWORD = 'StrongP@ssw0rd123!',
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;

-- Create database user
USE [AdventureWorks];
CREATE USER AppUser FOR LOGIN AppUser;

-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO AppUser;

Principle of Least Privilege

-- Create custom database role
CREATE ROLE OrdersReader;

-- Grant minimal permissions
GRANT SELECT ON dbo.Orders TO OrdersReader;
GRANT SELECT ON dbo.OrderDetails TO OrdersReader;

-- Add user to role
ALTER ROLE OrdersReader ADD MEMBER AppUser;

-- Deny sensitive columns
DENY SELECT ON dbo.Customers(CreditCardNumber) TO OrdersReader;

Server-Level Permissions

-- View server-level permissions
SELECT 
    pr.name AS PrincipalName,
    pr.type_desc AS PrincipalType,
    pe.permission_name,
    pe.state_desc
FROM sys.server_principals pr
INNER JOIN sys.server_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = 'AppUser';

-- Grant server permissions
GRANT VIEW SERVER STATE TO AppUser;
GRANT VIEW ANY DEFINITION TO AppUser;

-- Revoke dangerous permissions
REVOKE CONTROL SERVER FROM AppUser;

Encryption

Transparent Data Encryption (TDE)

Encrypts entire database at rest:

-- Create master key in master database
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyP@ssw0rd123!';
GO

-- Create certificate
CREATE CERTIFICATE TDECert 
WITH SUBJECT = 'TDE Certificate for AdventureWorks';
GO

-- Backup certificate (CRITICAL - store securely!)
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Backup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backup\TDECert.pvk',
    ENCRYPTION BY PASSWORD = 'CertP@ssw0rd123!'
);
GO

-- Create database encryption key
USE AdventureWorks;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

-- Enable TDE
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON;
GO

-- Verify encryption status
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    encryption_state,
    CASE encryption_state
        WHEN 0 THEN 'No encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
    END AS EncryptionStatus,
    percent_complete,
    encryptor_type
FROM sys.dm_database_encryption_keys;

Always Encrypted

Encrypts specific columns, data remains encrypted in memory:

-- Enable Always Encrypted in SSMS
-- Tools → Options → Query Execution → SQL Server → Advanced
-- Set "Enable Parameterization for Always Encrypted" = True

-- Create column master key (CMK)
CREATE COLUMN MASTER KEY CMK_Auto1
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/A1B2C3D4E5F6789012345678901234567890ABCD'
);

-- Create column encryption key (CEK)
CREATE COLUMN ENCRYPTION KEY CEK_Auto1
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_Auto1,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006100310062003200630033006400340065003500660036003700380039003000310032003300340035003600370038003900300031003200330034003500360037003800390030006100620063006400
);

-- Create table with encrypted columns
CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    SSN NVARCHAR(11) COLLATE Latin1_General_BIN2 
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = CEK_Auto1,
            ENCRYPTION_TYPE = DETERMINISTIC,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ),
    CreditCardNumber NVARCHAR(19) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = CEK_Auto1,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        )
);

-- Insert data (requires Always Encrypted-enabled connection)
INSERT INTO dbo.Customers (FirstName, LastName, Email, SSN, CreditCardNumber)
VALUES ('John', 'Doe', 'john.doe@contoso.com', '123-45-6789', '4111-1111-1111-1111');

Deterministic vs. Randomized:

• Deterministic: Same plaintext = same ciphertext (supports equality, grouping)
• Randomized: Same plaintext = different ciphertext (more secure, no operations)

Connection String Encryption

// .NET connection string with Always Encrypted
string connectionString = "Server=myserver;Database=AdventureWorks;Integrated Security=true;Column Encryption Setting=Enabled;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    // Queries automatically encrypt/decrypt
    SqlCommand command = new SqlCommand(
        "SELECT FirstName, LastName, SSN FROM dbo.Customers WHERE SSN = @SSN",
        connection
    );
    command.Parameters.AddWithValue("@SSN", "123-45-6789");
    
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}: {reader["SSN"]}");
    }
}

SQL Injection Prevention

Parameterized Queries

-- ❌ VULNERABLE - Dynamic SQL with concatenation
DECLARE @Username NVARCHAR(50) = 'admin'' OR ''1''=''1';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Username = ''' + @Username + '''';
EXEC sp_executesql @SQL;  -- Returns all users!

-- ✅ SECURE - Parameterized query
DECLARE @Username NVARCHAR(50) = 'admin'' OR ''1''=''1';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Username = @User';
EXEC sp_executesql @SQL, N'@User NVARCHAR(50)', @User = @Username;  -- Treats as literal

Input Validation

CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    -- Validate input
    IF @CustomerID IS NULL OR @CustomerID <= 0
    BEGIN
        RAISERROR('Invalid CustomerID', 16, 1);
        RETURN;
    END
    
    -- Safe parameterized query
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID;
END;

Whitelist Validation

CREATE PROCEDURE SearchProducts
    @SortColumn NVARCHAR(50) = 'ProductName',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    -- Whitelist allowed columns
    IF @SortColumn NOT IN ('ProductName', 'Price', 'CategoryID')
    BEGIN
        RAISERROR('Invalid sort column', 16, 1);
        RETURN;
    END
    
    -- Whitelist sort direction
    IF @SortDirection NOT IN ('ASC', 'DESC')
    BEGIN
        SET @SortDirection = 'ASC';
    END
    
    -- Build safe dynamic SQL
    DECLARE @SQL NVARCHAR(MAX) = 
        'SELECT ProductID, ProductName, Price, CategoryID 
         FROM dbo.Products 
         ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortDirection;
    
    EXEC sp_executesql @SQL;
END;

Auditing

SQL Server Audit

-- Create server audit
USE master;
GO
CREATE SERVER AUDIT SecurityAudit
TO FILE (
    FILEPATH = 'C:\Audit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);
GO

-- Enable audit
ALTER SERVER AUDIT SecurityAudit
WITH (STATE = ON);
GO

-- Create server audit specification (server-level events)
CREATE SERVER AUDIT SPECIFICATION LoginAudit
FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
GO

-- Create database audit specification (database-level events)
USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAccess
FOR SERVER AUDIT SecurityAudit
ADD (SELECT, UPDATE ON dbo.Customers BY public),
ADD (DELETE ON dbo.Orders BY public)
WITH (STATE = ON);
GO

-- Query audit log
SELECT 
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time >= DATEADD(DAY, -7, GETDATE())
ORDER BY event_time DESC;

Change Data Capture (CDC)

-- Enable CDC on database
USE AdventureWorks;
GO
EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC on table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Customers',
    @role_name = NULL,
    @supports_net_changes = 1;
GO

-- Query changes
DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10);

SET @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', DATEADD(HOUR, -1, GETDATE()));
SET @end_lsn = sys.fn_cdc_get_max_lsn();

SELECT 
    __$operation AS Operation,  -- 1=Delete, 2=Insert, 3=Before Update, 4=After Update
    __$start_lsn,
    CustomerID,
    FirstName,
    LastName,
    Email
FROM cdc.fn_cdc_get_all_changes_dbo_Customers(@begin_lsn, @end_lsn, 'all')
ORDER BY __$start_lsn;

Row-Level Security (RLS)

-- Create security policy for multi-tenant application
CREATE SCHEMA Security;
GO

-- Create inline table-valued function
CREATE FUNCTION Security.fn_CustomerSecurityPredicate(@CustomerID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @CustomerID = CAST(SESSION_CONTEXT(N'CustomerID') AS INT)
    OR IS_MEMBER('db_owner') = 1;
GO

-- Create security policy
CREATE SECURITY POLICY Security.CustomerSecurityPolicy
ADD FILTER PREDICATE Security.fn_CustomerSecurityPredicate(CustomerID) ON dbo.Orders,
ADD BLOCK PREDICATE Security.fn_CustomerSecurityPredicate(CustomerID) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
GO

-- Set session context (typically in application)
EXEC sp_set_session_context @key = N'CustomerID', @value = 1001;

-- User only sees their own orders
SELECT * FROM dbo.Orders;  -- Filtered by CustomerID = 1001

-- Bypass RLS (for administrators)
SELECT * FROM dbo.Orders WITH (SECURITY_BYPASS);

Dynamic Data Masking

-- Create table with masked columns
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    SSN NVARCHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'),
    Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()')
);

-- Insert data
INSERT INTO dbo.Employees (FirstName, LastName, Email, SSN, Salary)
VALUES ('John', 'Doe', 'john.doe@contoso.com', '123-45-6789', 75000.00);

-- Regular user sees masked data
-- Email: jXXX@XXXX.com
-- SSN: XXX-XX-6789
-- Salary: 0.00

-- Grant UNMASK permission
GRANT UNMASK TO AppUser;

-- Now sees actual data
SELECT * FROM dbo.Employees;

Security Best Practices

Disable sa Account

-- Disable sa account
ALTER LOGIN sa DISABLE;

-- Create dedicated admin account
CREATE LOGIN [CONTOSO\SQLAdmin] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\SQLAdmin];

Password Policies

-- Enforce strong password policy
ALTER LOGIN AppUser 
WITH PASSWORD = 'NewStrongP@ssw0rd123!',
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;

-- Review accounts with weak policies
SELECT 
    name,
    is_policy_checked,
    is_expiration_checked,
    create_date,
    modify_date
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR is_expiration_checked = 0;

Surface Area Reduction

-- Disable unnecessary features
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

-- Disable unused protocols (via SQL Server Configuration Manager)
-- Typically only enable TCP/IP, disable Named Pipes, Shared Memory (except local)

Regular Security Audits

-- Review server permissions
SELECT 
    pr.name AS LoginName,
    pe.permission_name,
    pe.state_desc
FROM sys.server_principals pr
INNER JOIN sys.server_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pr.type IN ('S', 'U')  -- SQL and Windows logins
ORDER BY pr.name;

-- Review database permissions
USE AdventureWorks;
GO
SELECT 
    USER_NAME(grantee_principal_id) AS UserName,
    OBJECT_NAME(major_id) AS ObjectName,
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE major_id > 0
ORDER BY UserName;

-- Identify orphaned users
SELECT 
    dp.name AS DatabaseUser,
    dp.type_desc,
    dp.create_date
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')
    AND sp.sid IS NULL
    AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');

Connection Encryption

-- Force encrypted connections (Server Configuration)
-- SQL Server Configuration Manager → SQL Server Network Configuration
-- → Protocols for [Instance] → Properties → Force Encryption = Yes

-- Verify encrypted connections
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    encrypt_option
FROM sys.dm_exec_connections
WHERE encrypt_option = 'TRUE';

Compliance and Regulations

GDPR Compliance

-- Right to be forgotten
CREATE PROCEDURE dbo.DeleteCustomerData
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Delete personal data
        DELETE FROM dbo.Orders WHERE CustomerID = @CustomerID;
        DELETE FROM dbo.Addresses WHERE CustomerID = @CustomerID;
        DELETE FROM dbo.Customers WHERE CustomerID = @CustomerID;
        
        -- Audit deletion
        INSERT INTO dbo.AuditLog (Action, CustomerID, Timestamp)
        VALUES ('GDPR_DELETE', @CustomerID, GETUTCDATE());
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Data Retention Policies

-- Archive old data
CREATE PROCEDURE dbo.ArchiveOldOrders
AS
BEGIN
    -- Move orders older than 7 years to archive
    INSERT INTO dbo.OrdersArchive
    SELECT * FROM dbo.Orders
    WHERE OrderDate < DATEADD(YEAR, -7, GETDATE());
    
    DELETE FROM dbo.Orders
    WHERE OrderDate < DATEADD(YEAR, -7, GETDATE());
END;

Key Takeaways

• Use Windows Authentication over SQL Authentication
• Implement principle of least privilege
• Enable TDE for data at rest encryption
• Use Always Encrypted for sensitive columns
• Prevent SQL injection with parameterized queries
• Enable SQL Server Audit for compliance
• Implement row-level security for multi-tenant scenarios
• Regularly review and audit permissions
• Enforce strong password policies
• Keep SQL Server patched and updated

Next Steps

• Audit current security configuration
• Implement TDE for production databases
• Enable SQL Server Audit
• Review and remediate excessive permissions
• Create security baseline and monitoring

Additional Resources

• SQL Server Security Best Practices
• Always Encrypted
• Row-Level Security
• SQL Server Audit

---

Secure by design. Protected by practice.