SQL Server 2025 Security Modernization: What DBAs Need to Know

Security has always been a core strength of SQL Server, but SQL Server 2025 makes a meaningful shift — not just adding features, but changing defaults. For the first time, encryption is enforced from the first byte of every connection. Password hashing meets NIST standards. Managed identities replace stored credentials for Azure-connected workloads. These are not optional hardening steps you apply after deployment — they are the new baseline.

This article walks through exactly what changed, why it matters, the breaking changes DBAs need to plan for, and a hands-on workshop covering beginner through advanced implementation.

Security Changes at a Glance

TDS 8.0 and TLS 1.3: Encryption by Default New in 2025

Previous versions of SQL Server negotiated encryption after the initial connection setup — the TDS prelogin phase was optionally encrypted depending on client and server settings. TDS 8.0 changes the model entirely.

TDS 8.0 applies not just to client connections but also to internal SQL Server communication paths: Always On, replication, linked servers, log shipping, and SQL Agent. All of these paths now require encryption by default.

PBKDF2 Password Hashing New in 2025

From SQL Server 2012 through 2022, SQL login passwords were hashed using SHA-512 with a 32-bit random salt — a single-pass hash. This was strong for its time but does not hold up well against modern GPU-accelerated offline attacks.

SQL Server 2025 replaces this with PBKDF2 (RFC2898) using SHA-512 with 100,000 iterations. The same password that took milliseconds to check now takes seconds per attempt when being brute-forced — making offline attacks against stolen hash databases orders of magnitude more expensive.

Login-Specific Security Cache Invalidation New in 2025

SQL Server maintains a security cache that stores permission lookups for users and logins. Before SQL Server 2025, any permission change on the server triggered a global cache invalidation — every current session’s permissions were flushed and had to be re-evaluated from scratch.

On servers with high connection counts (20,000–25,000 active sessions), this caused significant CPU spikes every time a DBA added a user, changed a role, or updated permissions. SQL Server 2025 makes cache invalidation login-specific — only the affected login’s cache entries are invalidated, leaving all other sessions unaffected.

-- Verify security cache behavior -- SQL Server 2025
-- This DMV shows cached permission entries per login
SELECT
    login_name,
    COUNT(*) AS cached_permission_entries
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.is_user_process = 1
GROUP BY login_name
ORDER BY cached_permission_entries DESC;

Modern Authentication with Microsoft Entra

SQL Server 2025 continues the shift away from traditional SQL authentication by strengthening Microsoft Entra (formerly Azure Active Directory) integration. With Entra authentication, access to SQL Server is tied directly to corporate identity controls — MFA, device compliance, sign-in risk evaluation, and conditional access policies all apply automatically.

-- Create a Microsoft Entra login
-- Requires the instance to be Arc-connected or Azure-hosted
CREATE LOGIN [user@yourcompany.com] FROM EXTERNAL PROVIDER;

-- Verify login type
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type_desc = 'EXTERNAL_LOGIN'
ORDER BY name;

-- Create a corresponding database user
USE YourDatabase;
CREATE USER [user@yourcompany.com] FOR LOGIN [user@yourcompany.com];

When a user leaves the organization, their Entra account is disabled or deleted by HR — and access to SQL Server is automatically revoked without any DBA action. This eliminates one of the most common audit findings: active SQL logins belonging to former employees.

Managed Identity for Outbound Connections New in 2025

For SQL Server 2025 instances connected to Azure via Arc, managed identities replace stored credentials for all outbound connections to Azure services. This means SQL Server can authenticate to Azure Storage (for backups), Azure Key Vault (for encryption keys), and Microsoft Fabric (for mirroring) without any password or secret stored anywhere on the server.

-- Backup directly to Azure Blob Storage using managed identity
-- No storage account key or SAS token required
BACKUP DATABASE [YourDatabase]
TO URL = 'https://yourstorageaccount.blob.core.windows.net/backups/YourDatabase_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5,
     CREDENTIAL = 'ManagedIdentityCredential';

-- Verify managed identity is configured
SELECT name, type_desc
FROM sys.server_principals
WHERE type_desc = 'EXTERNAL_LOGIN'
AND name LIKE '%managed%';

Zero Trust Principles in Practice

SQL Server 2025 aligns with zero trust security — access is never implicitly trusted, even inside the network perimeter. Every connection, query, and permission evaluation is treated as potentially hostile unless explicitly verified. In practice this means:

• No implicit trust from network location — encryption is required regardless of whether traffic is internal or external
• Explicit authentication — Entra MFA and conditional access apply to all human logins
• Least-privilege by design — no broad roles like sysadmin for application accounts; permissions granted explicitly through roles
• Continuous verification — auditing and Defender for SQL provide ongoing visibility into access patterns
• Assume breach — PBKDF2 hashing ensures that even a stolen credential database is not immediately exploitable

Finer-Grained Access Control

SQL Server 2025 makes it easier to combine role-based security, row-level security, and dynamic data masking into a cohesive access model at scale.

Role-Based Security

-- Grant permissions to roles, not directly to users
CREATE ROLE reporting_role;
GRANT SELECT ON SCHEMA::Sales TO reporting_role;
GRANT SELECT ON SCHEMA::Reporting TO reporting_role;

-- Add users to roles -- permissions follow automatically
ALTER ROLE reporting_role ADD MEMBER [analyst@yourcompany.com];
ALTER ROLE reporting_role ADD MEMBER [manager@yourcompany.com];

Row-Level Security

-- Predicate function: users only see rows matching their login
CREATE FUNCTION dbo.fn_SalesRegionAccess (@RegionId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_access
    WHERE @RegionId IN
    (
        SELECT RegionId
        FROM dbo.UserRegionMap
        WHERE UserName = USER_NAME()
    );
GO

-- Bind the security policy to the table
CREATE SECURITY POLICY SalesRegionPolicy
ADD FILTER PREDICATE dbo.fn_SalesRegionAccess(RegionId)
ON dbo.Sales
WITH (STATE = ON);
GO

-- Verify the policy is active
SELECT name, is_enabled
FROM sys.security_policies
WHERE name = 'SalesRegionPolicy';

Dynamic Data Masking

-- Mask sensitive columns for non-privileged users
ALTER TABLE dbo.Customers
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customers
ALTER COLUMN PhoneNumber
ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

-- Grant UNMASK to privileged roles only
GRANT UNMASK TO reporting_role;

Auditing, Monitoring, and Compliance

SQL Server 2025 expands audit action groups to cover REST calls, AI function invocations, and vector operations — keeping pace with the new capabilities the engine offers. Audit logs can now be stored in cold or archived storage, reducing the cost of long-term compliance retention.

-- Create a server audit writing to the file system
CREATE SERVER AUDIT SecurityAudit
TO FILE
(
    FILEPATH          = 'C:\SQLAudit\',
    MAX_FILE_SIZE     = 100 MB,
    MAX_ROLLOVER_FILES = 20
)
WITH (ON_FAILURE = CONTINUE);
GO

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

-- Capture login activity (both successful and failed)
CREATE SERVER AUDIT SPECIFICATION SecurityAuditSpec
FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);  -- Catch privilege escalation

ALTER SERVER AUDIT SPECIFICATION SecurityAuditSpec WITH (STATE = ON);
GO

-- Query the audit log for failed logins in the last 24 hours
SELECT
    event_time,
    action_id,
    server_principal_name,
    client_ip,
    statement
FROM sys.fn_get_audit_file('C:\SQLAudit\*', NULL, NULL)
WHERE action_id = 'LGF'  -- Failed login
AND event_time >= DATEADD(HOUR, -24, GETDATE())
ORDER BY event_time DESC;

Breaking Changes to Plan For

Hands-On Workshop: Modern Security from Beginner to Advanced

Beginner: Identify and Migrate Legacy SQL Logins

-- Identify all SQL logins and external (Entra) logins
SELECT
    name,
    type_desc,
    is_disabled,
    create_date,
    modify_date
FROM sys.server_principals
WHERE type IN ('S', 'U', 'E')  -- SQL login, Windows login, External login
ORDER BY type_desc, name;

-- Find SQL logins that are NOT disabled -- active legacy auth
SELECT name, create_date
FROM sys.server_principals
WHERE type = 'S'       -- SQL authentication
AND is_disabled = 0
AND name NOT IN ('sa', '##MS_PolicyEventProcessingLogin##',
                 '##MS_PolicyTsqlExecutionLogin##')
ORDER BY name;

-- Create Entra login (requires Arc-connected instance or Azure hosting)
CREATE LOGIN [analyst@yourcompany.com] FROM EXTERNAL PROVIDER;

-- Create corresponding database user
USE SecurityLab;
CREATE USER [analyst@yourcompany.com] FOR LOGIN [analyst@yourcompany.com];

-- Assign to role -- never grant permissions directly to users
ALTER ROLE db_datareader ADD MEMBER [analyst@yourcompany.com];

-- Verify the user's role membership
SELECT dp.name AS user_name, rp.name AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON dp.principal_id = drm.member_principal_id
JOIN sys.database_principals rp ON rp.principal_id = drm.role_principal_id
WHERE dp.name = 'analyst@yourcompany.com';

Intermediate: Least Privilege with Roles and Row-Level Security

-- Create purpose-specific roles
CREATE ROLE role_sales_read;
CREATE ROLE role_sales_write;
CREATE ROLE role_reporting;

-- Grant at schema level -- all objects in the schema inherit
GRANT SELECT                  ON SCHEMA::Sales     TO role_sales_read;
GRANT SELECT, INSERT, UPDATE  ON SCHEMA::Sales     TO role_sales_write;
GRANT SELECT                  ON SCHEMA::Reporting TO role_reporting;

-- Add users to roles
ALTER ROLE role_sales_read  ADD MEMBER [analyst@yourcompany.com];
ALTER ROLE role_sales_write ADD MEMBER [salesapp@yourcompany.com];

-- Verify no direct permissions exist on sensitive tables
SELECT
    dp.name      AS principal_name,
    dp.type_desc AS principal_type,
    o.name       AS object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals  dp ON dp.principal_id = p.grantee_principal_id
JOIN sys.objects               o  ON o.object_id    = p.major_id
WHERE dp.type NOT IN ('R')  -- Exclude roles themselves
AND p.class = 1             -- Object-level permissions
ORDER BY principal_name, object_name;

-- Create a user-to-region mapping table
CREATE TABLE dbo.UserRegionMap
(
    UserName NVARCHAR(128) NOT NULL,
    RegionId INT           NOT NULL,
    CONSTRAINT PK_UserRegionMap PRIMARY KEY (UserName, RegionId)
);

-- Populate with initial mappings
INSERT dbo.UserRegionMap VALUES
    ('analyst@yourcompany.com', 1),
    ('analyst@yourcompany.com', 2);

-- Create the filter predicate
CREATE FUNCTION dbo.fn_SalesAccess (@RegionId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_access
    WHERE @RegionId IN
    (
        SELECT RegionId FROM dbo.UserRegionMap
        WHERE UserName = USER_NAME()
    )
    OR IS_MEMBER('role_sales_write') = 1;  -- Write role sees all rows
GO

-- Apply the policy
CREATE SECURITY POLICY SalesPolicy
ADD FILTER PREDICATE dbo.fn_SalesAccess(RegionId)
ON dbo.Sales
WITH (STATE = ON);
GO

-- Test: impersonate the analyst and confirm row filtering
EXECUTE AS USER = 'analyst@yourcompany.com';
SELECT COUNT(*) AS visible_rows FROM dbo.Sales;  -- Should be subset only
REVERT;

Advanced: Zero Trust Auditing and Baseline

-- Server audit to file system
CREATE SERVER AUDIT SecurityAudit
TO FILE
(
    FILEPATH           = 'C:\SQLAudit\',
    MAX_FILE_SIZE      = 100 MB,
    MAX_ROLLOVER_FILES = 20
)
WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT SecurityAudit WITH (STATE = ON);
GO

-- Comprehensive audit specification
CREATE SERVER AUDIT SPECIFICATION SecurityAuditSpec
FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP);

ALTER SERVER AUDIT SPECIFICATION SecurityAuditSpec WITH (STATE = ON);
GO

-- Query for suspicious patterns: multiple failed logins
SELECT
    server_principal_name,
    client_ip,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt
FROM sys.fn_get_audit_file('C:\SQLAudit\*', NULL, NULL)
WHERE action_id  = 'LGF'
AND event_time >= DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name, client_ip
HAVING COUNT(*) >= 5
ORDER BY failed_attempts DESC;

-- Confirm all connections are encrypted
SELECT
    session_id,
    encrypt_option,
    auth_scheme,
    net_transport,
    client_net_address
FROM sys.dm_exec_connections
WHERE session_id > 50  -- Skip system sessions
ORDER BY encrypt_option;

-- Any row showing encrypt_option = 'FALSE' is a compliance risk
-- On SQL Server 2025 with TDS 8.0, all rows should show TRUE

-- Check which authentication methods are in use
SELECT
    auth_scheme,
    COUNT(*) AS connection_count
FROM sys.dm_exec_connections
WHERE session_id > 50
GROUP BY auth_scheme
ORDER BY connection_count DESC;

Security Baseline Checklist

Use this checklist as a repeatable standard for every SQL Server 2025 production deployment:

• Microsoft Entra authentication required for all human access — no SQL logins without documented exception and business justification
• All SQL logins in use reviewed — disable any that are unused or belong to former employees
• No sysadmin or db_owner for application service accounts
• All permissions granted through roles at schema level — no direct object-level grants to individual users
• Row-level security applied to all tables containing customer or sensitive data
• TDS 8.0 verified — all active connections show encrypt_option = TRUE
• Linked server and replication configurations tested with new encryption defaults before production upgrade
• System.Data.SqlClient inventory completed — all affected scripts and SSIS packages migrated to Microsoft.Data.SqlClient
• Server audit enabled and capturing at minimum: failed logins, successful logins, role membership changes
• Defender for SQL enabled for threat detection if licensed
• Managed identity configured for all Azure-connected workloads via Arc — no static credentials in configuration files
• Quarterly access review process documented and scheduled

References

• Microsoft Tech Community – Secure by Default: What’s New in SQL Server 2025 Security
• Microsoft Docs – What’s New in SQL Server 2025
• Microsoft Docs – Microsoft Entra Authentication with SQL Server
• Microsoft Docs – Row-Level Security
• Microsoft Docs – SQL Server Audit
• Microsoft Docs – Dynamic Data Masking
• Microsoft Docs – Authentication and Access Control
• Microsoft – Zero Trust Guidance
• MSSQLTips – SQL Server 2025 New Features and Enhancements
• Gethyn Ellis – SQL Server 2025 New Features for DBAs