Enter your search text in the box above

Select a result to preview

Modern Data Protection Strategies with SQL Server

#fabcon2026 #session #learning #microsoft #fabcon2026-session

Description

Today, we handle increasing volumes of sensitive data. DBAs and Data Engineers must protect their data from external attackers, employees, and privileged admins like themselves. This session will teach you how to protect your data while maintaining functionality and performance. The session focuses on using column-level encryption, row-level security, and auditing to secure your data.

Key Takeaways

My Notes

Action Items

Slides

📥 Download Slides

Modern Data
Protection Strategies
with SQL Server
John Sterrett – CEO of ProcureSQL
2/27/2026
Sound off.
The mic is all yours.
Influence the product roadmap.
Join the Fabric User Panel
Join the SQL User Panel
Share your feedback directly with our
Fabric product group and researchers.
Influence our SQL roadmap and ensure
it meets your real-life needs
https://aka.ms/JoinFabricUserPanel
https://aka.ms/JoinSQLUserPanel
About John Sterrett
john@procuresql.com
procuresql.com
linkedin.com/in/johnsterrett
Goal of Today’s Session
Learn how to secure your data with Auditing, Row-Level
Security, and Always Encrypted
Gain insight into pros and cons to utilizing advanced data
protection features.
Leverage built in cloud features provided by Azure SQL for
advanced data security.
Data Security Lifecycle
Classify
Encrypt
Audit
Secure
Classify Your
Sensitive
Data
Classify
Encrypt
You must identify and
classify your sensitive
data to know which data
must be protected.
Audit
Secure
Discover and Classify Sensitive Data
Adding Classified Columns
Data Classification after Saving Recommendations
Classify
SQL Audit
Identify the Who, What,
When, Where behind
data access, security
and schema changes
Audit
Encrypt
Secure
What do these compliances have in common?
ISO 27001
SOX (Sarbanes-Oxley Act)
GDPR
PCI DSS (Payment Card Industry Data Security Standard
HIPAA (Health Insurance Portability and Accountability Act
SQL Audit Enables You To….
Track login attempts, failed
logins, which can indicate
attempts of malicious attempts
Changes to database schema,
permissions, and security
configurations
Record Access to both data and
object changes in real-time
SQL Audit Decisions
What do we Audit?
Where do we store audit data?
What should happen if auditing fails?
How do we secure the Audit logs?
How do we organize audits?
What to Audit for HIPAA, PCI, GDRP?
Compliance
HIPAA
PCI DSS
GDPR
Common SQL Audit Actions
SELECT, INSERT, UPDATE, DELETE (data
access/modification) on PHI tables; login
success/failure; permission changes;
User account creation/modification/deletion; login
attempts; permission changes; data
access/modification
Logon activity; unauthorized access attempts; data
access and processing
Audit Action Groups for HIPAA, GDPR, PCI
Audit Action Group
Description
SUCCESSFUL_LOGIN_GROUP
Captures all successful login attempts. Important for tracking authorized access (HIPAA, GDPR,
PCI).
FAILED_LOGIN_GROUP
Captures failed login attempts, helping detect unauthorized access or brute force attacks
(HIPAA, GDPR, PCI).
SERVER_PRINCIPAL_CHANGE_GROUP
Tracks creation, alteration, or deletion of server-level principals (logins, users). Critical for
permission management (HIPAA, PCI).
SERVER_ROLE_MEMBER_CHANGE_GROUP
Monitors changes to server role memberships, ensuring role-based access control integrity
(HIPAA, PCI).
AUDIT_CHANGE_GROUP
Audits creation, modification, or deletion of audit objects and audit specifications, ensuring
audit trail integrity (HIPAA, PCI, GDPR).
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
Tracks changes to application role passwords, securing application-level access (HIPAA, PCI).
LOGIN_CHANGE_PASSWORD_GROUP
Captures password changes for logins, supporting credential management policies (HIPAA,
PCI).
SCHEMA_OBJECT_CHANGE_GROUP
Monitors DDL changes (CREATE, ALTER, DROP) on schema objects at the server level, important
for tracking structural changes (HIPAA, GDPR).
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
Tracks permission changes on database objects, ensuring proper access control (HIPAA, PCI,
GDPR).
DATABASE_PRINCIPAL_CHANGE_GROUP
Monitors changes to database principals (users, roles), supporting identity management
(HIPAA, PCI).
SQL Audit Action Groups Cheat Sheet
Power BI Dashboard Link
SQL Audit 101– SQL Server vs. Azure SQL DB
SQL Server
• To create, alter, or drop a server audit, principals
require the ALTER ANY SERVER AUDIT or the
CONTROL SERVER permission.
• Users with the ALTER ANY SERVER AUDIT permission
can create server audit specifications and bind them to
any audit.
• After a server audit specification is created, it can be
viewed by principals with the CONTROL SERVER or
ALTER ANY SERVER AUDIT permissions, the sysadmin
account, or principals having explicit access to the
audit.
Azure SQL Database
• Need Contributor role or higher on the database or server
resource
• Permissions to execute
'Microsoft.Sql/servers/extendedAuditingSettings/write’
• Permission to execute
'Microsoft.Sql/servers/databases/extendedAuditingSettings/
write'
• The following audit policies are included by default
• BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
• Additional changes can be made via API calls.
• Enabling auditing on the database in addition to enabling auditing
on the server doesn't override or change any of the settings of the
server auditing
SQL Audit Targets – Where Do We Store the Audit Data?
Targets include
Azure - Blob storage, Log Analytics, Event Hub
SQL Server – Files, Event Logs
Use file targets with appropriate size limits (MAXSIZE) and rollover
files (MAX_ROLLOVER_FILES) to prevent disk space issues.
Reserve disk space upfront (RESERVE_DISK_SPACE = ON) to avoid
audit failures due to insufficient space.
Creating A SQL Audit
Azure SQL Database
SQL Server
Creating A SQL Audit – T-SQL Basic Example
CREATE SERVER AUDIT
Audit_Compliance
TO FILE (
FILEPATH = 'C: \ AuditLogs \ ' )
WITH ( ON_FAILURE = CONTINUE );
GO
ALTER SERVER AUDIT
Audit_Compliance
WITH ( STATE = ON);
GO
CREATE SERVER AUDIT SPECIFICATION
AuditSpec_Compliance
FOR SERVER AUDIT Audit_Compliance
ADD ( SUCCESSFUL_LOGIN_GROUP ),
ADD ( FAILED_LOGIN_GROUP ),
ADD ( SERVER_PRINCIPAL_CHANGE_GROUP
),
ADD ( SERVER_ROLE_MEMBER_CHANGE_GROUP ),
ADD ( AUDIT_CHANGE_GROUP ),
ADD ( LOGIN_CHANGE_PASSWORD_GROUP ),
ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
);
GO
ALTER SERVER AUDIT SPECIFICATION
AuditSpec_Compliance
( STATE = ON);
GO
CREATE DATABASE AUDIT [ CustomerDataAudit
FOR SERVER AUDIT [ SQLAudit ]
ADD ( SELECT ON OBJECT :: [ dbo ] . [Customers]
ADD ( UPDATE ON OBJECT :: [ dbo ] . [Customers]
ADD ( INSERT ON OBJECT :: [ dbo ] . [Customers]
ADD ( DELETE ON OBJECT :: [ dbo ] . [Customers]
WITH
]
BY
BY
BY
BY
[public]
[public]
[public]
[public]
),
),
),
)
How to Audit Events – What Should We Monitor?
Log File Viewer – How do we view the Audit Data?
Sensitive Data Metrics Found in our SQL Audit
SQL Audit Best Practices
Define
Clear Audit Goals and Scope
Use
Server and Database Audit specifications – Limit Tracked Events
Review
Audit Logs Regularly
Secure
Secure Your Audit Logs
Backup
Your Audit Logs
Audit
Your Audit - Verify intended events are being logged
Classify
Row-Level
Security (RLS)
Encrypt
Audit
Securing Your Data at the
Row Level
Secure
Why Row-Level Security?
Applications need to limit a users access to only certain rows of
data in a database. Security needs to be embedded in the
database to work for ALL APPLICATIONS.
Control both read and write data at the row level
No app changes needed, works transparently when queries
execute
Centralized Security Logic within the database
Apps consume secured data
Excel, .NET, Power BI – Direct Query, etc.…
RLS – Real-World Examples
Health Care (Patient Data Access Controls)
Nurses can only view rows of their assigned patients
Doctors access broader data but are blocked from
data unless authorized
Patients can only see their data
Financial Services
Financial Advisors only see their client’s portfolios
Auditors access transition history for only the Financial
Advisors they audit
E-Commerce / Multi-Tenant
Vendors view only their sales records and customer
orders
Platform admins access data for vendors assigned to
them.
How does RLS Work?
Predicate-based access control added to regular access
Two types of security predicates
Filter predicates – silently filter SELECT, UPDATE and DELETE
operations to exclude rows that will not satisfy the predicate
Block predicates – block INSERT, UPDATE, DELETE
operations that will not satisfy the predicate
AFTER INSERT and AFTER UPDATE predicates can prevent
users from updating rows to values that violate the
predicate.
BEFORE UPDATE predicates can prevent users from
updating rows that currently violate the predicate.
BEFORE DELETE predicates can block delete operations.
How to implement RLS
CustomerID
Each row of your table has
column that determine which
user can access the data
Create inline table-value
function that defines row
level access criteria
Security policy adds security
predicates on tables using the
function provided
FirstName
LastName
SalesRep
John
Kon
John
CREATE FUNCTION RLS.CustomerPredicate (@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Access
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'
GO
CREATE SECURITY POLICY RLS.CustomerPolicy
ADD FILTER PREDICATE RLS.CustomerPredicate(SalesRep) ON
Sales.Customer,
ADD BLOCK PREDICATE RLS.CustomerPredicate(SalesRep) ON
Sales.Customer
GO
Authorization Methods with RLS
Any lookup defined by business rules can be used
Lookup Options
SESSION_CONTEXT() – Applications
Local Lookup table
SQL Roles
Specific users – admins as an example
RLS Example
(Group Activity)
Database Users
dbo.Sales - Table
User Name
Fred
Chris
Tom
Fred
Chris
Country
USA
USA
France
Spain
Germany
Sales
CEO
Fred
Chris
Tom
Database Roles
USA
France
RLS – User Lookup Example
CREATE FUNCTION Security.fn_SalesSecurity(@UserName AS
sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_SalesSecurity_Result
-- Logic for filter predicate
WHERE @UserName = USER_NAME() OR USER_NAME() = 'CEO’;
GO
CREATE SECURITY POLICY Security.UserFilter
ADD FILTER PREDICATE Security.fn_SalesSecurity(UserName)
ON dbo.Sales WITH (STATE = ON);
GO
EXECUTE AS USER = 'CEO’;
SELECT * FROM Sales;
REVERT;
GO
EXECUTE AS USER = 'Fred’;
SELECT * FROM Sales;
REVERT;
RLS – SQL Roles Example
ALTER
ALTER
ALTER
ROLE [USA] ADD MEMBER [CEO]
ROLE [FRANCE] ADD MEMBER [CEO]
ROLE [SPAIN]
ADD MEMBER [CEO]
ALTER
ALTER
ROLE [USA]
ROLE [USA]
ADD MEMBER [Fred]
ADD MEMBER [Chris]
ALTER ROLE [FRANCE] ADD MEMBER [Tom]
ALTER ROLE [SPAIN]
ADD MEMBER [Fred]
ALTER ROLE [GERMANY] ADD MEMBER [Chris]
CREATE FUNCTION Security.fn_SalesSecurity(@RoleName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN ( SELECT 1 AS AccessGranted
WHERE IS_ROLEMEMBER ( @RoleName ) = 1);
GO
EXECUTE AS USER = 'CEO’;
SELECT * FROM Sales;
REVERT;
GO
EXECUTE AS USER = ‘Fred’;
SELECT * FROM Sales;
REVERT;
GO
RLS – Lookup Table Example
CREATE TABLE RLS.UsersSuppliers (
UsersSuppliersID int NOT NULL CONSTRAINT
PK_RLSUsersSuppliers PRIMARY KEY CLUSTERED IDENTITY
,UserID nvarchar(255) NOT NULL
,SupplierID int NOT NULL )
--Grant the test user access
--to a single supplier ID
INSERT INTO RLS.UsersSuppliers (UserID, SupplierID)
VALUES ('RLSLookupUser’,4)
UserSuppliersID
UserID
RLSLookupUser
ALTER ROLE [USA] ADD MEMBER [CEO]
ALTER ROLE [FRANCE] ADD MEMBER [CEO]
ALTER ROLE [SPAIN]
ADD MEMBER [CEO]
ALTER ROLE [USA]
ALTER ROLE [USA]
ADD MEMBER [Fred]
ADD MEMBER [Chris]
ALTER ROLE [FRANCE] ADD MEMBER [Tom]
ALTER ROLE [SPAIN]
ADD MEMBER [Fred]
ALTER ROLE [GERMANY] ADD MEMBER [Chris]
CREATE FUNCTION Security.fn_SalesSecurity(@RoleName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN ( SELECT 1 AS AccessGranted
WHERE IS_ROLEMEMBER ( @RoleName ) = 1);
SuppliersID
GO
EXECUTE AS USER = 'CEO’;
SELECT * FROM Sales;
REVERT;
GO
EXECUTE AS USER = ‘Fred’;
SELECT * FROM Sales;
REVERT;
GO
RLS – Side Attacks
Malicious Security Policy Manager
Divide By Zero Attack
Cross-feature compatibility
Divide By Zero Attack
RLS – How to Identify Side Attacks?
Excessive Errors – 8134
(Divide By Zero)
SQL Server Side Trace
Server or Database
Audits (Why we started
with Audits ☺)
SQL Advanced Threat
Protection
Performance changes
Excessive CPU Usage
Extended Events
Excessive requests per
second
RLS Best Practices
It's highly recommended to create a separate schema for the RLS objects: predicate
functions, and security policies.
The security policy manager doesn't require SELECT permission on the tables they
protect.
Keep predicate functions short and sweet. Avoid using excessive table joins in
predicate functions to maximize performance.
Follow regular performance tuning best practices for predicates.
RLS – SQL Features That Don’t Play
More Details : Microsoft Learn RLS Cross-Feature
Compatibility
DBCC SHOW_STATISTICS
Filestream (Not Supported)
Memory-Optimized Tables
Indexed Views
Change Data Capture
Full-Text Search
Columnstore Indexes
Partitioned Views
Temporal Tables
RLS – Anti-Patterns
Using
Features that
can
introduce
data leakage
Highly
Transactional
Systems
Databases
without
Direct user
access
Less
Experienced
Teams
Staging or
Loading
Tables
Classify
Always
Encrypted (AE)
Encrypting sensitive data
from everyone (Yes, DBA’s
and System Admins too)
Encrypt
Audit
Secure
Why Should We Use Always Encrypted?
Feature
Encrypt
Data At
Protects Against
Key Management
Transparent Data Encryption
(TDE)
Rest (files)
Physical theft
Internal or EKM
App
Changed
Needed
No
Backup Encryption
Backup files Backup theft
Internal or EKM
No
Column Level Encryption
Column
Level
Unauthorized access to
column data
Internal or EKM
Yes
TLS
Transit
Network eavesdropping
N/A
No
Always Encrypted
Column
Level
DBAs, admins, memory
attacks
External (client
side)
Yes
AE – Encryption Keys
CREATE COLUMN MASTER KEY [CMK1]
WITH
(
KEY_STORE_PROVIDER_NAME

N'MSSQL_CERTIFICATE_STORE’
,
KEY_PATH = N'LocalMachine
/My/2379554....’
,
ENCLAVE_COMPUTATIONS ( SIGNATURE = 0x5B1A ...
COLUMN ENCRYPTION KEY [CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [AE_CMK1] ,
ALGORITHM = 'RSA_OAEP’ ,
ENCRYPTED_VALUE = 0x01700000016 ...
)
Two-level key
hierarchy
• Column encryption keys (CEK) –
encrypts data
• Column master keys (CMKs) –
encrypts CEKs
The database
stores metadata
about keys
• Enclave-enabled – CMKs have
ENCLAVE_COMPUTATIONS set
• Enclave-enabled CEKs are encrypted
with enclave-enabled CMKS
)
AE – Key Storage Decisions
Windows – Certificate Store
Control and Compliance
No Cloud Dependency
Existing Infrastructure
Utilization
Simple Implementation for
Smaller Deployments
No Additional Costs
Azure Key vault
Requires Azure Key Vault
Access
Separation of Duties
End to End Protection
Centralized Management
Scalable and Flexibility
Enhanced Security (RBAC)
Simple Key Rotation
Always Encrypted Types in the Beginning
Deterministic
Less Secure and Predictable
Great WHERE clause equality
JOINS
Indexes
Randomize
More Secure
Non-Searchable ( SQL 2016 days)
AE - Deterministic vs Randomized
Plaintext
Deterministic (Not Random)
Randomized
Always Encrypted with Secure Enclaves
AE without Secure Enclaves
AE with Secure Enclaves
SQL Bits – Demystifying Always Encrypted with security enclaves
Computation Over Encrypted Columns
Randomized
No scalar operations
Deterministic
Equality queries
Randomized & enclavedenabled keys
Range/LIKE queries, sorting
Always Encrypted - Required Code Changes
Connection String (Encrypted Setting=Enabled)]
Parametrization of Queries – No literals in filters
Explicit Data Type (Parameter Type must match
encrypted column type)
Enable Always Encrypted (1 of 5)
Enable Always Encrypted (2 of 5)
Enable Always Encrypted (3 of 5)
Enable Always Encrypted (4 of 5)
Online Encryption
Requires Secure
Enclaves
Enable Always Encrypted (5 of 5)
Connecting with SSMS 21
AE - Columns Accessed Without Keys
AE – Columns Decrypted With Key
Questions?
Feedback for slides
john@procuresql.com
procuresql.com
https://www.linkedin.com/in/johnsterrett