SQL Server Security Best Practices: Protecting Your Data

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:**

```sql
-- 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 
```text
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 (
```text
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 
```text
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:

Architecture Overview: Enable Always Encrypted in SSMS

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 (
```sql
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))
{
```sql
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
```text
@CustomerID INT```
AS
BEGIN
```sql
-- 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
```text
@SortColumn NVARCHAR(50) = 'ProductName',
@SortDirection NVARCHAR(4) = 'ASC'```
AS
BEGIN
```sql
-- 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 (
```text
FILEPATH = 'C:\Audit\',
MAXSIZE = 100 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF```
)
WITH (
```text
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 
```text
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
```text
@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 
```text
__$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)

Figure: SQL Server security – server roles, logins, and database permissions.

-- 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)
```text
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 (
```sql
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 
```text
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 
```text
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 
```text
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 
```text
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')
```text
AND sp.sid IS NULL
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');

### Connection Encryption

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

-- Verify encrypted connections
SELECT 
```text
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
```text
@CustomerID INT```
AS
BEGIN
```sql
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
```sql
-- 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;

Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/sql/
• https://learn.microsoft.com/azure/azure-sql/
• https://learn.microsoft.com/fabric/database/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/sql/
• Sample repositories: https://github.com/microsoft/sql-server-samples
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

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.