SQL Server 2016 Ends in July. Here's What Will Break.

SQL Server 2016 goes end of life on July 14, 2026. That's five months from now.

If you're still running 2016 in production, you're not alone. Based on one vendor's assessment, SQL Server 2016 is still 20% of production deployments. That's a lot of DBAs who need to get moving pretty much now.

Here's the problem: Many of those upgrades are going to break. Not because the engine upgrade fails, but because SQL Server 2025 changed the defaults. Silently. In ways that won't show up until you're already committed.

The Deadline

After July 14, 2026:

• No more security patches
• No more support calls
• No longer compliant

You can buy Extended Security Updates (ESU) for up to three more years. But ESU only covers critical security patches — no bug fixes, no features, and the cost increases each year. It's a holding pattern, not a solution.

The real solution is to upgrade. And if you're planning on 2016 to 2025, you're about to learn that Microsoft quietly raised the security baseline while we weren't looking.

What Changed in 2025

SQL Server 2025 ships with MSOLEDBSQL 19, and its defaults are different from what you've been using. The new provider enforces:

• Encrypt = True by default
• TrustServerCertificate = False by default
• Strict certificate chain validation

Translation: if your environment uses self-signed certificates, internal CAs that aren't in the trust store, or no encryption at all — connections will fail. Unavoidably.

This affects linked servers, replication, log shipping, and any application still using legacy connection strings. The old SQL Native Client (SQLNCLI) used to suppress or ignore these errors. MSOLEDBSQL 19 does not.

Three Things That Will Break

1. Linked Servers

If you configured linked servers using SQLNCLI (which was the default for years), they'll fail after upgrade with errors like:

Msg 7303, Level 16, State 1
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" 
for linked server "LinkedServerName".

TCP Provider: The certificate chain was issued by an authority that is not trusted.

The fix is to either install proper certificates or reconfigure the linked server with TrustServerCertificate=yes — which defeats the security improvement but at least gets you running.

2. Replication

If your publisher is SQL Server 2025 and your distributor is remote without a trusted certificate, replication will fail. You'll see:

OLE DB provider "MSOLEDBSQL19" for linked server "repl_distributor" returned message 
"Client unable to establish connection".

Msg -2146893019, Level 16, State 1
SSL Provider: The certificate chain was issued by an authority that is not trusted.

This hits transactional, snapshot, peer-to-peer, and merge replication. Replication Monitor in SSMS will also fail if it can't validate the certificate chain.

The workaround (if you can't deploy trusted certificates yet):

EXEC sp_changedistributor_property 
    @property = N'trust_distributor_certificate', 
    @value = N'yes';

3. Full-Text Search

SQL Server 2025 introduces a new full-text index version. Existing catalogs stay on version 1 (unchanged since 2005) unless you manually upgrade them. After the engine upgrade, your full-text queries will fail:

Msg 30010, Level 16, State 2
An error has occurred during the full-text query. Common causes include: 
word-breaking errors or timeout, FDHOST permissions/ACL issues, 
service account missing privileges, malfunctioning IFilters...

The fix is to rebuild your full-text indexes — or if you need to keep using the old version temporarily:

ALTER DATABASE SCOPED CONFIGURATION SET FULLTEXT_INDEX_VERSION = 1;

But version 1 is deprecated. This is a temporary workaround, not a long-term solution.

And if you're using Database Mail, there's another bug that forced Microsoft to pull back a fix. Be sure to check Database Mail status before you upgrade.

What to Check Now

Before you upgrade anything, audit these:

Component	What to Check
Linked Servers	Provider (SQLNCLI vs MSOLEDBSQL), encryption settings, certificate trust
Replication	Remote distributor topology, certificate chain on all nodes
Log Shipping	Remote monitor server, same TLS requirements as linked servers
Full-Text Indexes	Current index version, rebuild time estimates
Connection Strings	Legacy providers, missing Encrypt/TrustServerCertificate params
SSIS Packages	Execute SQL Task / SMO tasks using Dts.Runtime API — update references, rebuild

Run this to find your linked server providers:

SELECT 
    s.name AS linked_server,
    s.provider,
    s.data_source
FROM sys.servers s
WHERE s.is_linked = 1;

Any row showing SQLNCLI or SQLNCLI11 needs attention before upgrade.

The Upgrade Path

SQL Server 2025 supports direct in-place upgrades from 2016. That doesn't mean it's easy — it means it's possible.

The safe approach:

1. Audit linked servers, replication, full-text, and SSIS before touching anything
2. Deploy certificates or workarounds for each component
3. Test in a non-production environment — not just the upgrade, but every downstream connection
4. Plan for full-text index rebuilds (this takes time on large catalogs)
5. Schedule the production upgrade with sufficient buffer time for surprises

If you haven't started yet, you need to get moving. Five months sounds like plenty of time until you recognize that your replication topology spans three datacenters and nobody knows where the certificates are.

The Bottom Line

The engine upgrade isn't the hard part. Discovering what breaks is.

Microsoft raised the security baseline in 2025. That's a good thing — in the long run. But if you're jumping from 2016, you're skipping several versions of gradual tightening and jumping right into the deep end.

Test it soon. July is right around the corner.

More to Read:

sqlfingers: SQL Server 2025 Current State
Brent Ozar: Known Issues So Far in SQL Server 2025
MSSQLTips: SQL Server 2025 Upgrade Lessons Learned
endoflife.date: Microsoft SQL Server

Azure Says Yes. SQL Server Says No.

Azure IAM says you're a Contributor. SSMS says this:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'dm_exec_query_stats', 
database 'ContosoDB', schema 'sys'.

Welcome to cloud permissions, where 'Contributor' doesn't mean you can contribute and 'Reader' doesn't mean you can read.

In my last post, I explained the management plane vs data plane split. This post is the promised follow-up for the minimum permission combinations for common DBA tasks. aka, what you need, how to verify it, and how to fix it when it fails.

The Two-Layer Reality

Quick refresher. Azure has two separate permission systems:

Layer	Controls	Assigned Where
Management Plane (ARM)	Portal visibility, configuration, scaling	Azure IAM
Data Plane (SQL)	Queries, data access, DMVs	Inside the database

These two systems don't talk to each other. Azure doesn't tell SQL Server what you can do. SQL Server doesn't check Azure IAM. You need both configured — separately.

The Permission Matrix

Because you need permissions in both systems, here's what each task requires:

Task	Management Plane (ARM)	Data Plane (SQL)
See database in portal	Reader	—
Query tables	—	db_datareader
Modify data	—	db_datawriter
Query DMVs (performance)	—	VIEW DATABASE STATE
Query server-level DMVs	—	##MS_ServerStateReader##
Create/alter objects	—	db_ddladmin
Manage users in database	—	db_securityadmin
Create logins (server level)	—	##MS_LoginManager##
Scale database / change tier	SQL DB Contributor	—
Configure firewall rules	SQL Server Contributor	—
View metrics in portal	Monitoring Reader	—
Configure alerts	Monitoring Contributor	—
Access blob storage (manual exports)	Reader	Storage Blob Data Contributor

Portal tasks need ARM roles. Query tasks need SQL roles. Some tasks need both.

Note: This matrix applies to both Azure SQL Database and Azure SQL Managed Instance. MI supports a few additional features (ie., SQL Agent, db_backupoperator) but the core permission model is the same.

Permission Combinations by Role

Here's what common DBA roles actually need — both layers combined:

DBA Role	Management Plane (ARM)	Data Plane (SQL)
Monitor only	Reader, Monitoring Reader	VIEW DATABASE STATE
Troubleshoot + kill sessions	Reader, Monitoring Reader	VIEW DATABASE STATE, ALTER ANY CONNECTION
Operational DBA	Reader, Monitoring Reader	db_datareader, db_ddladmin, VIEW DATABASE STATE, ALTER ANY CONNECTION
Full control	SQL DB Contributor, Monitoring Contributor	db_owner

Start with the minimum. Add permissions as the job requires.

Verify Your Access

When your cloud team grants access and walks away, don't assume it's all good. Test all layers before you call it done.

Check	How to Verify
Can see resource in portal?	Navigate to Azure SQL in portal
Can connect via SSMS?	Test connection with your credentials
Can query user tables?	SELECT TOP 1 * FROM any_table
Can query DMVs?	SELECT * FROM sys.dm_exec_requests
Can see portal metrics?	Open Monitoring blade, check for data

Not sure what permissions you actually have? Run this in your Azure SQL Database:

-- What database permissions do I have?
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');

-- Am I in any server roles?
SELECT 
    r.name AS role_name,
    CASE WHEN IS_SRVROLEMEMBER(r.name) = 1 THEN 'Yes' ELSE 'No' END AS member
FROM sys.server_principals r
WHERE r.type = 'R' AND r.name LIKE '##MS_%##';

(The ##MS_ server roles are specific to Azure SQL Database.)

If any check fails, you know exactly which layer is missing.

When It Doesn't Work

Azure says connected. SQL Server says denied.

Symptom: Database appears in portal. SSMS connects. SELECT fails.

Cause: Missing data plane role.

Fix (Data Plane):

-- Run in the target database
ALTER ROLE db_datareader ADD MEMBER [your_user];

You can see tables. You can't see performance.

Symptom: sys.dm_exec_query_stats returns permission denied or empty results.

Cause: Missing VIEW DATABASE STATE (or VIEW SERVER STATE for server-level DMVs).

Fix (Data Plane):

-- Database level
GRANT VIEW DATABASE STATE TO [your_user]; 

-- Server level (for cross-database DMVs)
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [your_login];

NOTE:  Reminder, role-based perms are smarter.  
       Create a role, enlist your users, grant perms to said role, 
       add role to ##MS_ServerStateReader##.

Contributor doesn't mean what you think.

Symptom: SQL DB Contributor role assigned. Metrics blade is empty or shows errors.

Cause: Contributor doesn't include monitoring permissions.

Fix (Management Plane): Add Monitoring Reader role in Azure IAM.

The Bottom Line

Like I said before, these two systems don't talk to each other, and they don't share the same permission system. This is how the cloud works. Once you stop expecting them to agree, the Access Denied mysteries disappear.

Keep the matrix handy. Verify before you trust. Be sure you're fixing the right layer.

References

Microsoft: Server-level roles in Azure SQL Database
Microsoft: Database-level roles
Microsoft: Authorize database access
Microsoft: Monitor performance using DMVs
sqlfingers: Management Plane vs Data Plane
sqlfingers: Cloud Security for SQL Server

Is Local Admin Required to Manage SQL Server?

No. But let's talk about what that really means.

The Question

"Does a DBA need local administrator membership to manage SQL Server?"

The answer is simple: Local admin group membership is not required. In fact, best practices dictate that Database Administrators (DBAs) and SQL service accounts should not have local administrator rights on the host server.  This change was introduced as far back as SQL Server 2008 with a 'secure by design, secure by default, and secure in deployment' strategy.

What Microsoft Says:

"The following improvements in SQL Server 2008 decrease the surface and attack area for SQL Server and its databases by instituting a policy of 'Least Privileged' and increase the separation between the Windows Administrators and the SQL Server administrators: By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role."

The BUILTIN\Administrators group no longer gets sysadmin by default.

The Real Requirements

Without local admin group membership, a DBA needs explicit grants in six areas:

Category	What's Required
Group Memberships	Remote Desktop Users, Performance Monitor Users, Event Log Readers, Distributed COM Users
NTFS Permissions	Modify on data/log/backup drives; Read on SQL binaries
WMI Permissions	Execute Methods, Enable Account, Remote Enable on SQL namespaces
DCOM Permissions	Remote Launch, Remote Activation, Remote Access
Service Control	Granted via GPO or sc sdset
Registry Access	Read on SQL Server hive

All of this is doable, but every bit of it requires additional, separate configurations. For step-by-step instructions, see SQL Server DBA Permissions Without Local Admin.

The Pain Points

Many DBAs will push back on this - and they're not wrong:

SQL Server Configuration Manager (SSCM) is the only supported tool for managing SQL Server services, and by default, it requires local admin. Without it, you need several workarounds:

• Full Control on specific WMI namespaces
• Registry access to SQL Server keys
• Service control permissions granted separately

Service restarts don't work from SSMS without explicit grants. Right-click → Restart is grayed out. You'll need GPO-based service permissions or sc sdset commands applied to each instance.

SSMS shows a question mark instead of the green arrow if WMI/DCOM isn't configured. Cosmetic, but it signals incomplete access.

Patches and CUs require local admin. Period. If you don't have it, you must work with the Windows team to patch your servers.

Troubleshooting slows down. Event Viewer, perfmon, quick file access - all require explicit grants or group memberships that local admin would have provided automatically.

The Counter-Argument

Here's what your colleagues will say: "PoLP applies to the role, not to individual permissions. If my job is responsible for full-stack SQL Server management, then local admin IS the minimum privilege for that role."

They're not wrong. As Andreas Wolter notes in his Principle of Least Privilege, it is much tougher to implement than you may expect.

The real PoLP violations aren't "DBA has local admin." They're:

• Using the same account for admin work and daily tasks
Microsoft: "Ensure all critical admin roles have a separate account" — Azure identity best practices

• Shared credentials with elevated rights
NIST 800-53 AC-2(9) restricts shared/group accounts

• Permanent access when just-in-time would suffice
Microsoft: "on-demand, just-in-time administrator access" — Privileged Access Roadmap

• Stale privileges that haven't been reviewed
NIST 800-53 AC-6(7) requires periodic review

For as long as I've been doing this, I think the security question shouldn't be whether the DBA has local admin. It should be whether that access is scoped, audited, justified, and revocable.

The Bottom Line

If your environment...	Then...
Has compliance requirements (PCI, SOX, HIPAA)	Grant explicit permissions, document everything
Has dedicated Windows and SQL teams	Separate the roles, use the workarounds
Is a small shop where DBA = sysadmin or where full server access is needed	Local admin is practical and defensible
Is a managed services engagement	Follow client requirements

Local admin membership is not required to administer SQL Server. But removing it means a lot of extra work:

• Configuring WMI, DCOM, GPO, NTFS, and registry permissions explicitly
• Accepting that some tools won't work the same way
• Coordinating with Windows admins for patches and installs

If your security posture demands separation, it can be done. If operational simplicity matters more, local admin is not unreasonable.

Either answer is defensible. Pretending it's simple isn't.

References

Why SQL Server Admins Don’t Need Local Admin Rights: A Zero Trust Approach
SQL Server DBA Permissions Without Local Administrator
SQL Server 2008 R2 Security Changes
How to Start or Stop SQL Services without OS Admin Rights

Which Indexes Are Missing? — 2025 Edition

Fifteen years ago, I wrote a little post about the missing index DMVs. Six major SQL Server releases later, it's time for an update.

The Old Query: Still Kicking

Here's the gist of what we had back then:

SELECT 
    avg_user_impact average_improvement_percentage, 
    avg_total_user_cost average_cost_of_query_without_missing_index, 
    'CREATE INDEX idx_' + [statement] +  
    ISNULL(equality_columns, '_') +
    ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') + 
    ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b
  ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
    ON b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40

This still runs fine. If you're on SQL Server 2005-2017, or you just need a quick-and-dirty list, it gets the job done.

But it has a couple blind spots:

• You see suggestions, but not which queries need them
• No idea if the suggestion is from current or stale statistics

What's New Since 2010

The Big One: sys.dm_db_missing_index_group_stats_query

Introduced in v2019, this links missing index suggestions to actual queries via query_hash. No more guessing which query is crying for help — you can see it directly.

Freshness Check: last_user_seek and last_user_scan

The freshness columns -- last_user_seek and last_user_scan -- were always there, but I didn't include them in my 2010 post.  I should have.  They tell you exactly when the optimizer last wanted that index.  A suggested index based on current stats is very different from one using old/outdated statistics.

Cleaner T-SQL

We've got CONCAT, CONCAT_WS, and FORMAT now. No more nested ISNULL chains.

The 2025 Query

/*
    Missing Index Analysis
    sqlfingers.com | 2025
    Requires: SQL Server 2019+
*/

SELECT 
    ROW_NUMBER() OVER (
        ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC) priority,
    CAST(s.avg_user_impact AS INT) [impact_%],
    FORMAT(s.user_seeks, 'N0') seeks,
    FORMAT(s.user_scans, 'N0') scans,
    FORMAT(s.last_user_seek, 'yyyy-MM-dd HH:mm') last_seek,
    DB_NAME(d.database_id) db,
    OBJECT_SCHEMA_NAME(d.object_id, d.database_id) [schema],
    OBJECT_NAME(d.object_id, d.database_id) [table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    CONCAT(
        'CREATE NONCLUSTERED INDEX [IX_',
        OBJECT_NAME(d.object_id, d.database_id), '_',
        REPLACE(REPLACE(REPLACE(
            CONCAT_WS('_', d.equality_columns, d.inequality_columns),
            '[', ''), ']', ''), ', ', '_'),
        '] ON [', OBJECT_SCHEMA_NAME(d.object_id, d.database_id), '].[', 
        OBJECT_NAME(d.object_id, d.database_id), '] (',
        CONCAT_WS(', ', d.equality_columns, d.inequality_columns), ')',
        IIF(d.included_columns IS NOT NULL, 
            CONCAT(' INCLUDE (', d.included_columns, ')'), ''),
        ';'
    ) create_statement
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g 
  ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats s 
    ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC;

Bonus: Find the Query in Need

This one is fun. We can use sys.dm_db_missing_index_group_stats_query to see exactly which query wants the index:

SELECT 
    CAST(sq.avg_user_impact AS INT) [impact_%],
    OBJECT_NAME(d.object_id, d.database_id) [table],
    d.equality_columns,
    d.inequality_columns,
    LEFT(t.query_sql_text, 150) query_snippet
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g 
  ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats_query sq 
    ON g.index_group_handle = sq.group_handle INNER JOIN sys.query_store_query q 
      ON sq.query_hash = q.query_hash INNER JOIN sys.query_store_query_text t 
        ON q.query_text_id = t.query_text_id
WHERE d.database_id = DB_ID()
ORDER BY sq.avg_user_impact DESC;

Now we're not just seeing such-n-such index on tablename.   We get the actual SELECT statement that is suffering.   Pure gold.

Quick Caveats

• DMV stats reset on restart — let your server run a full business cycle before trusting these numbers
• Not every suggestion is gold — the optimizer doesn't consider write overhead or maintenance cost. You must always fully review and test the recommended indexes rather than trusting them blindly.
• Look for overlaps — if you see 3 suggestions for the same table, you might consolidate into 1

More to Read:

Original 2010 Post: Which indexes are missing?
Microsoft Docs: sys.dm_db_missing_index_group_stats_query

Happy indexing.

SQL Server Cloud Permissions: Management Plane vs Data Plane

My last post ended with a promise to explain the management plane vs. data plane split in practical terms. The short story? There are two separate permission systems — one for managing resources, one for accessing data — and they don't talk to each other. The good news: you already understand this model. You just don't recognize it yet.

The Pattern You Already Know

In SQL Server, we have server-level permissions and database-level permissions. A login can exist at the server level but have zero access to any database. A user can exist in a database but not map to any login. They are separate layers, and you need both configured correctly for anyone to be able to do anything.

Cloud works the same way — just with different names and a portal that hides all the pieces.

SQL Server	Cloud Equivalent	What It Controls
Server-level	Management plane	Can you see it? Configure it?
Database-level	Data plane	Can you use it? Read/write data?

When someone says 'I have Contributor access but can't read the blob' — that's like having a SQL Server login with no database user mapped. You can connect to the instance, but you can't touch the data. Different layer, same pattern.

Why the Naming Hurts

The problem isn't the model. The problem is the names.

In SQL Server, db_datareader lets you read data. db_datawriter lets you write data. These names make sense. In Azure, Contributor sounds like it should let you contribute... something. Maybe data? Nope. It lets you contribute configuration changes to the resource. Want to contribute actual data? You need Storage Blob Data Contributor.

Here's the mental model that helped me:

Management plane roles answer: 'What can you do to this resource?'
Create it, delete it, configure it, see it in the portal, change its settings.

Data plane roles answer: 'What can you do with this resource?'
Read files, write data, execute queries, upload documents.

The Practical Combinations

Let's get specific. For Azure Blob Storage — the scenario from my last post — here's what each combination actually gets you:

ARM Role	Data Role	Result
Reader	none	Can see storage account in portal, no data access
none	Storage Blob Data Contributor	Can read/write via CLI or API, invisible in portal
Reader	Storage Blob Data Reader	Can see in portal, read-only data access
Reader	Storage Blob Data Contributor	Can see in portal, full data access
Contributor	none	Can configure storage account, no data access
Contributor	Storage Blob Data Contributor	Full configuration + full data access

Notice the pattern: Reader + Storage Blob Data Contributor is often the sweet spot for end users. They can find the resource and work with the data, but they can't accidentally reconfigure or delete the storage account itself.

Azure SQL Has the Same Split

This isn't just a storage thing. Azure SQL Database has the same model:

Layer	Role Example	What It Does
Management	SQL DB Contributor	Manage servers/databases in portal, firewall rules, scaling
Data	Entra ID user + db role	Connect and run queries, read/write data

A user with SQL DB Contributor can manage databases in the portal — change performance tiers, adjust backup retention — but if they're not added as a user inside the database, they can't run SELECT * FROM Customers. Same pattern. Management vs Data.

The Five-Second Diagnostic

When someone says "I have the right permissions but I'm getting Access Denied", ask two questions:

1. Can they see the resource in the portal?
No → Missing management plane role (ie., Reader)

2. Can they see it but not use it?
Yes → Missing data plane role (ie., Storage Blob Data Contributor)

Microsoft's January 2026 Patch Tuesday: CVE-2026-20803: SQL Server Privilege Escalation

Microsoft's January 2026 Patch Tuesday included a security fix for SQL Server: CVE-2026-20803, an elevation of privilege vulnerability with CVSS score 7.2 (Important).

The vulnerability is classified as CWE-306: Missing Authentication for Critical Function. An attacker who already has high-level privileges on the SQL Server instance could exploit this flaw to escalate further — gaining debugging privileges, dumping system memory, and potentially extracting sensitive data or credentials.

The Details

Attribute	Value
CVE ID	CVE-2026-20803
Severity	Important (CVSS 7.2)
Attack Vector	Network
Attack Complexity	Low
Privileges Required	High
User Interaction	None
Impact	High (Confidentiality, Integrity, Availability)
Exploitability	Exploitation Less Likely

The CVSS vector is: CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:H/I:H/A:H

What Does This Mean in Practice?

This is not a random attacker on the internet vulnerability. The attacker needs high-level privileges — a compromised service account, an insider threat, or lateral movement from an already-breached system, among others.

Once exploited, the attacker could gain debugging access and dump memory — which could expose connection strings, credentials, decrypted data in memory, and other sensitive information that shouldn't be accessible even to privileged users.

The 'Exploitation Less Likely' rating means Microsoft hasn't seen active exploitation and doesn't expect it to be trivially weaponized. But 'less likely' is not 'impossible'.

Which Versions Are Affected?

SQL Server 2022 and SQL Server 2025 are affected. Microsoft has released patches through both the GDR (security-only) and CU (cumulative update) tracks.

Version	Update Track	KB	Patched Build
SQL Server 2025	GDR	KB5073177	17.0.1050.2
SQL Server 2022	CU22 + GDR	KB5072936	16.0.4230.2
SQL Server 2022	GDR	KB5073031	16.0.1165.1

GDR vs. CU: Which Patch Do I Need?

If you're not familiar with Microsoft's SQL Server servicing model:

• GDR (General Distribution Release) — Security fixes only. Minimal changes. Use this if you're on a baseline RTM build and want to stay conservative.
• CU (Cumulative Update) — Security fixes plus all other bug fixes and improvements. Use this if you're already on the CU train.

Check your current build in comparison to the table above and review the KB articles for the correct patch:

SELECT @@VERSION;

How to Patch

Download the appropriate update from the links above or from the Microsoft Update Catalog.

Before patching:

• Back up your databases
• Test in a non-production environment first
• Schedule a maintenance window — the SQL Server service will restart

After patching, verify the new build number:

SELECT 
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;

Should You Panic?

No. The attack requires high privileges to begin with, and there's no evidence of active exploitation. But 'exploitation less likely' is not the same as 'never going to happen'.

Patch during your next maintenance window. Don't wait six months.

If your SQL Server instances are exposed to the internet or if you have concerns about insider threats or compromised service accounts, you may wish to prioritize this patch.

A Note on Timing

This security patch (released January 13) is separate from CU23 for SQL Server 2022 and CU1 for SQL Server 2025 (both released January 15). Those cumulative updates have their own problems — specifically, a Database Mail bug that broke alerting.

The security patches (KB5073177, KB5072936, KB5073031) do not have the Database Mail bug. If you need to patch for CVE-2026-20803 but can't risk breaking Database Mail, use the GDR security update rather than the CU.

More to Read:

Microsoft Security Response Center: CVE-2026-20803
KB5073177: Security Update for SQL Server 2025 GDR
KB5073031: Security Update for SQL Server 2022 GDR
KB5072936: Security Update for SQL Server 2022 CU22+GDR

Database Mail Bug Forces Microsoft to Pull SQL Server 2025 CU1... and 2022 CU23

Microsoft pulled both SQL Server 2025 CU1 and SQL Server 2022 CU23 last week. The reason: Database Mail is completely broken after installing either update.

Could not load file or assembly 'Microsoft.SqlServer.DatabaseMail.XEvents, 
Version=17.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' 
or one of its dependencies. The system cannot find the file specified.

If you've already installed one of these updates, your alert emails aren't going out. Your job failure notifications are silent. That monitoring you set up years ago? Dead.

Which Updates Are Affected?

Version	Broken Update	Status
SQL Server 2025	CU1 (KB5074901)	Pulled from download
SQL Server 2022	CU23 (KB5074819)	Pulled from download

The RTM builds of both versions are fine. If you're on SQL Server 2025 RTM or SQL Server 2022 CU22 or earlier, you're not affected — as long as you don't update.

How to Check If You're Affected

Run this to check your current build:

SELECT @@VERSION;

Or just run this to test your Database Mail:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'youraddress@example.com',
    @subject = 'Database Mail Test',
    @body = 'Testing after CU install';

If it fails with the assembly error above, you've been hit.

The Fix: Uninstall the CU

Microsoft's official guidance is blunt: "If you already installed this update, uninstall it to restore Database Mail functionality."

To uninstall a Cumulative Update:

Windows Server

Go to Control Panel → Programs and Features → View installed updates. Find the SQL Server CU, right-click, and uninstall. You'll need to restart the SQL Server service (and possibly the server).

Microsoft has detailed uninstall instructions here: Uninstall a Cumulative Update from SQL Server

Emergency Workaround (If You Can't Uninstall Yet)

If you're stuck on the broken CU and can't perform an immediate uninstall, there's a community workaround floating around. Some DBAs have reported success copying three DLL files from a working (pre-CU) instance:

From a working SQL Server 2022 instance (pre-CU23), copy these files from C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn:

• DatabaseMail.exe
• DatabaseMailengine.dll
• DatabaseMailprotocols.dll

Replace the same files on the broken instance. Restart the SQL Server Agent service.

I haven't personally tested this workaround, and it's not officially supported. Use at your own risk. The proper fix is to uninstall the CU.

Check Your Unsent Mail Queue

If Database Mail was broken for any length of time, you may have queued messages that never went out. Check with:

SELECT 
    mailitem_id,
    profile_id,
    recipients,
    subject,
    send_request_date,
    sent_status,
    sent_date
FROM msdb.dbo.sysmail_allitems
WHERE sent_status <> 'sent'
ORDER BY send_request_date DESC;

After you restore Database Mail functionality, these queued messages should attempt to send. If they don't, you may need to manually resend critical notifications.

The Bigger Picture

This is CU1 for SQL Server 2025. The product went GA in November. Two months later, the first cumulative update breaks a core feature that's been in the product for over 20 years.

As one commenter on Brent Ozar's blog put it: "I started skeptical yet secretly hopeful MS would get its act together for this version, but my days of being unimpressed with Nadella's Microsoft quality standards are coming to a middle."

The lesson here isn't new, but it bears repeating: test your CUs before deploying to production. Even CU1. Especially CU1.

Microsoft will presumably release a fix soon. Until then, hold off on both SQL Server 2025 CU1 and SQL Server 2022 CU23 if you use Database Mail. Which is, I think, pretty much most of us.

More to Read:

Microsoft: SQL Server 2025 CU1 (KB5074901) — includes Database Mail warning
Microsoft: SQL Server 2022 CU23 (KB5074819)
Brent Ozar: SQL Server 2025 CU1 is Off to a Rough Start
Microsoft Q&A: DatabaseMail.exe fails after SQL Server 2022 CU22 to CU23
Microsoft: How to Uninstall a Cumulative Update

Cloud Security for SQL Server: The Complexity Nobody Warned You About

Cloud-based SQL Server security isn't simpler. It's different — and the learning curve is brutal if you grew up on-prem.

If you've spent years working Windows authentication, SQL logins, role memberships, and the occasional certificate, you may assume cloud security is more of that, just with a portal. Not. The SQL Server/Cloud permission models are layered differently, the terminology shifts depending on the platform, and the people who 'own' security are spread across teams that don't always speak the same language.

The Problem: Two Permission Systems, One Confusing Experience

The core issue exists across all major cloud platforms: There is a split between management plane permissions (who can see and configure resources in the portal) and data plane permissions (who can actually read/write the data).

Platform	Mgmt Plane	Data Plane
Azure	ARM roles	Storage Data roles
AWS	IAM policies	Resource/bucket policies
GCP	Cloud IAM	Data access scopes

The result is the same everywhere: A user can have 'Contributor' access to a resource and still get 'Access Denied' when they try to use it. The terminology varies, the confusion is universal.

And it gets worse. Identity sync delays can mean waiting hours for permission changes to take effect. Role inheritance doesn't behave like on-prem Active Directory. Informal titles like 'tenant admin' or 'cloud admin' mean different things to different organizations. And sometimes even different things to different people in the same organization.

Microsoft's own documentation acknowledges that 'Achieving Separation of Duties (SoD) is challenging for security-related or troubleshooting tasks'. That's not a criticism. It's an honest admission that the model is complex, even for the people who built it.

A Real-World Example

A customer reached out with what seemed a simple ask: "We have a new Blob Storage account and we can't assign permissions so I can add documents to it."

Their security admin had created the storage account and container. He tried to grant the requesting user access, but it didn't work. The user couldn't see it in the portal, let alone add documents. They had a tight timeline — this was for a proof-of-concept analytics portal and they needed it sorted fast.

My first question: "When you say he 'was not able to grant access' does that mean he tried and it failed, or he granted it and you still can't see anything?"

The answer: "It was more the former. He was trying to add the role assignments and it didn't work as we expected."

Then my customer said: "John (name change) has tenant admin to Azure, so I don't think it will be a limitation on his side."

Here's the problem with that assumption.

What 'Tenant Admin' Actually Means

If by 'tenant admin' they meant Global Administrator in Entra ID (formerly Azure AD), that role gives full control over users in the directory. It does not automatically grant permission to manage Azure resources like a storage account. To do that, the Global Admin would need to perform an 'elevation' step to get User Access Administrator at the root scope, or someone would need to explicitly assign them Owner on the resource.

More likely, though, is that they just hadn't assigned the right combination of roles.

To successfully access a blob container in the Azure portal, a user needs permissions in two distinct systems:

Role Type	What It Does
ARM Role (ie., Reader)	Lets you see the storage account in the portal and navigate to its resources
Storage Data Role (ie., Storage Blob Data Contributor)	Lets you read/write/delete data within the blob container

If you only assign Reader, they can see it but can't touch the data. If you only assign Storage Blob Data Contributor, the user can't see the storage account in the portal. You need both.

We got on a call with the customer, their cloud admin, and our cloud expert. Within minutes, our cloud guy found the hole — a missing role assignment — applied it in the portal, and the problem resolved immediately.

Cause? They just didn't know about the dual-permission model. And why would they? It's not very intuitive. The terminology is inconsistent. And 'Contributor' sure sounds like it would let you contribute.

Even the Experts Draw a Line

Here's something worth noting: some of the most respected SQL Server voices in the industry explicitly exclude security and permissions work from their practice contractually. Not because they can't learn it or don't know it -- but because the complexity and risk warrant specialization. When top-tier SQL Server experts are steering clear of security questions entirely, that tells you something about the depth of this space.

The idea that 'the cloud handles security for you' is a widespread, largely misleading myth. Cloud platforms remove some infrastructure tasks — automated backups, patching, failover — but they don't validate that your permission model makes sense. They don't catch the gap between what you think a role grants and what it actually grants. And they certainly don't ensure your DBA and cloud teams are aligned on who can do what.

Where Does the DBA Fit?

Cloud security is most definitely not DBA-only territory, but DBAs should understand it well enough to:

• Recognize when something is a permission issue vs a configuration issue

• Speak the same language as their cloud teams

• Catch issues that slip through the DBA to Cloud Team handoff

The most effective DBAs I've worked with aren't the ones who know everything about cloud IAM. They're the ones who know enough to ask the right questions — and have good communications with their cloud counterparts so the questions get answered.

Coming Next

This post is just a primer. An acknowledgment that the problem exists and that you're not alone if you've been frustrated by it. In my follow-up, I'll provide actionable guidance for DBAs navigating cloud security alongside their cloud teams, including:

• Understanding the management plane vs. data plane split in practical terms

• Minimum permission combinations for common DBA tasks

• Necessary permission combinations for common user tasks at the frontend

• How to diagnose 'Access Denied' when the portal says you have access

• A checklist for DBAs before handing off to cloud teams (and vice versa)

What's Tripped You Up?

I'd love to hear from you. What cloud security issues have caught you off guard? What did you learn in your first SQL Server+Cloud 'Access Denied' surprise? Drop a comment if you can. Your pain points will help shape the follow-up.

More to Read:

Microsoft: Azure SQL Database Security Best Practices
Microsoft: Assign an Azure role for access to blob data
Azure SQL and Entra ID authentication, tips from the field

SQL Server 2025: Current State

SQL Server 2025 went GA on November 18th. CU1 dropped on January 15th. Two months in production. Time to check the scoreboard.

I dug through Microsoft's known issues, read what the experts are saying, and tested a few things myself. These are my thoughts on the good, the broken, and where I'm on hold.

CU1: Don't Install It (Yet)

CU1 (KB5074901) landed a week ago with 16 fixes. It also breaks Database Mail.

Could not load file or assembly 'Microsoft.SqlServer.DatabaseMail.XEvents, 
Version=17.0.0.0' or one of its dependencies. 
The system cannot find the file specified.

Microsoft's official guidance: "If you use Database Mail and already downloaded this update, don't install it until a fix is available. If you already installed this update, uninstall it."   Nice. Welcome to CU1.

---

What Brent Ozar Says

Brent published his known issues roundup in December. His take: "Knock on wood, this is shaping up to be one of the better, more reliable releases so far."

High praise from someone who's watched a lot of SQL Server launches. His highlights:

Query Store + PSPO on readable secondaries = access violations. If you enable Query Store without disabling PSPO, you can crash the instance. Fix: disable PSPO.

Auditing won't write to the security log. Workaround: write to a file instead. Or, as Brent puts it, "if you need your auditing to be legally defensible, you need to use a third party appliance that sits in between SQL Server and the rest of the network, capturing all network packets." Agreed.

Full-text won't index documents larger than 25MB. Registry edit required to lift the limit.

Won't install without TLS 1.2. If your sysadmins disabled TLS 1.2 everywhere because 'it's deprecated', you'll need to have a discussion.

Won't install with more than 64 cores per CPU. Virtualization workaround: configure more sockets, fewer cores per socket.

PowerShell breaks with strict encryption. The irony: people proactive enough to use PowerShell are also the ones who enforce strict encryption.

SQL Auth logins are slower. SQL Server 2025 uses PBKDF2 for password hashing, otherwise known as 100,000 iterations of SHA-512. Security improvement and NIST compliance -- but every SQL auth adds ~150ms overhead. If you're using connection pooling, you may not notice. If you're not pooling and monitoring login latency, this will surface. VladDBA did the math: brute-forcing that took 45 seconds on 2022 now takes an estimated 154,008 seconds. This is not a small point.

SQLNCLI is gone. Microsoft followed through on a deprecation. The replacement, MSOLEDBSQL, enforces strict TLS certificate validation by default. If your linked servers were set up years ago with self-signed certs and 'trust me' settings, they will break. Options: fix your certificates properly, use TrustServerCertificate=Yes (less secure), or use sysadmin logins / Kerberos.

---

Aaron Bertrand's Upgrade Surprises

Aaron Bertrand shared three gotchas from real-world upgrades. None prevented the upgrade from completing. All could ruin your day.

1. Setup restarts your instance during the health check. Not during the upgrade, but during the pre-check. If you like to prep everything and click 'Upgrade' at an exact moment, you can't do this anymore. The moment you proceed past a certain point, your instance goes down. Aaron's words: "I can no longer advocate doing that as it is more intrusive than it used to be."

2. Full-text indexes must be rebuilt. If you don't, your full-text queries fail. There's a workaround — set FULLTEXT_INDEX_VERSION = 1 and copy legacy binaries from an older instance — but that's a bandaid. Plan rebuild time. One commenter on Brent's post reported that rebuilding crashes the instance with a memory dump. They have an open case with Microsoft.

3. Linked servers fail with TLS errors. Same MSOLEDBSQL issue as above. Same fix options. Plan ahead.

---

Vector Search: Cool, But Not Production-Ready

This is the headline feature. Microsoft wants SQL Server to be your vector database. Semantic queries without leaving T-SQL -- but it's not ready yet. Here's what the fine print tells us:

Still in preview. Even though 2025 is GA, you must enable PREVIEW_FEATURES to use vector indexes.

Table goes read-only. No inserts, no updates while the index exists. Data changes require dropping the index, modifying data, and rebuilding manually.

Predicates apply AFTER vector search. You can't filter first, then search. It searches everything, then filters. Scale implications are significant.

Other limitations: MAXDOP is ignored. Requires single-column INTEGER primary key. No partitioning. Not replicated to subscribers. DiskANN limited to Standard/Enterprise.

The read-only limitation alone is a showstopper for most production scenarios. Test it. Learn it. Don't go into it hastily.

This is where I'm on hold.

---

The Bottom Line

Two months in, SQL Server 2025 is okay. Better than some launches. No catastrophic data corruption bugs. No hard stop recalls. The known issues are annoying but manageable — except CU1. Don't install CU1. Yet.

If you're planning an upgrade:

• Test in lower environments first. Aaron's team found all three issues before hitting production.
• Check your linked servers. SQLNCLI is gone. TLS validation is enforced.
• Inventory your full-text catalogs. Plan rebuild time.
• Start your maintenance window early. Setup is more intrusive now.
• Give it a couple more CUs. Let someone else find the next Database Mail.

Stay sharp. Verify everything. Trust nothing blindly.

More to Read:

• Microsoft: SQL Server 2025 Known Issues
• Brent Ozar: Known Issues So Far
• Aaron Bertrand: Upgrade Lessons Learned
• VladDBA: PBKDF2 Hashing Deep Dive
• Microsoft: CU1 (KB5074901) - Read Before Installing

What the LLM Doesn't Know About Your Database

Copilot has read more SQL documentation than you ever will. It has ingested Stack Overflow, Microsoft Learn, thousands of GitHub repos, and probably all those posts you bookmarked in 2019 and never finished reading.

It still doesn't know your database.

And that gap — between what an LLM learned and what it knows about you — is where things get interesting.

What the Training Data Contains

Large Language Models learn by processing massive datasets — the 'training data'. For coding assistants like Copilot, that includes:

• Public documentation (Books Online, Microsoft Learn, Oracle docs)
• Open-source repositories (GitHub, GitLab)
• Q&A sites (Stack Overflow, DBA Stack Exchange)
• Blog posts, tutorials, articles
• Sample databases (AdventureWorks, Northwind, WideWorldImporters)

This is genuinely impressive. Ask Copilot to write a query that calculates running totals with a window function, and it'll nail it. Ask it to explain the difference between SNAPSHOT and READ COMMITTED SNAPSHOT isolation levels, and it'll do a decent job.

General knowledge? It's got plenty.

Your knowledge? Zero.

What the Training Data Doesn't Contain

Here's what wasn't in the dataset:

• Your schema
• Your table names
• Your business logic
• Why CustomerID is an INT in one table and a VARCHAR(20) in another
• That dbo.Orders_OLD_DONOTUSE is actually still in use
• The fact that Status = 3 means 'canceled' but Status = 7 also means 'canceled' because someone added it in 2017 during a migration and no one cleaned it up
• Why you can't drop that index even though it very clearly is redundant
• The stored procedure that runs fine in dev but brings production to its knees because of parameter sniffing

The LLM has never seen your environment. It has no context window that includes your Monday morning standup, your incident reports, or that Slack message from 2022 where someone explained why the ETL job has a 45-minute delay built in.

It's working from first principles and pattern matching. That's powerful — until it isn't.

The Hallucination Problem

When an LLM doesn't know something, it doesn't say 'I don't know'. It guesses and it assumes. Confidently. This is called a hallucination. Ask Claude or ChatGPT a question about your data that they cannot possibly answer. They will give you fluff and when you question it -- they will say they made an assumption. They will say they guessed. And they will apologize for it.

In conversation, hallucinations are awkward. In SQL, they're dangerous.

Here's an example. You ask Copilot:

"Write a query to get all active customers from the last 90 days"

It produces:

SELECT CustomerID, CustomerName, Email
FROM dbo.Customers
WHERE IsActive = 1
  AND CreatedDate >= DATEADD(DAY, -90, GETDATE());

Looks clean. Runs fine. Except:

• Your table is called tblCustomer, not dbo.Customers
• The column is ActiveFlag, not IsActive
• 'Active' in your system means Status IN (1, 4, 6) -- it's not a boolean
• You track activity by LastOrderDate, not CreatedDate

The LLM gave you a syntactically perfect solution for a database that is not yours. It hallucinated your schema, your column names, and your business rules — all with no questions. Pure assumption.

This is not a bug. This is how LLMs work. They predict the most likely next token based on patterns in the training data. When the actual answer isn't in the training data, they produce the most plausible-sounding alternative. Again, they assume. They guess. They 'make it up'.

Plausible isn't the same as correct.

Context Windows and Their Limits

Modern LLMs have a context window — the amount of text they can 'see' at once. GPT-4 can handle around 128K tokens. Claude can go higher. Sounds like a lot.

It's not enough.

Your production database probably has hundreds of tables, thousands of columns, stored procedures, views, functions, triggers -- and a decade of accumulated decisions. Even if you could paste your entire schema into the prompt, the model doesn't understand it the way you do. It sees text. It doesn't see relationships, intent or history.

And context windows reset. Every new conversation starts fresh. The LLM doesn't remember what you told it yesterday — unless you tell it again.

RAG: The Workaround (And Its Limits)

Retrieval-Augmented Generation — RAG — is the industry's current answer to this problem. Instead of relying solely on training data, RAG systems retrieve relevant documents at query time and inject them into the prompt.

In theory, you could point a RAG system at your schema, your documentation, your runbooks, and the LLM would then have context about your environment, rather than just generic SQL knowledge.

SQL Server 2025 even has native vector search to support this pattern — storing embeddings alongside your relational data so you can do semantic lookups without bolting on a separate vector database.

But RAG has limits too:

• It only knows what you've documented
• It can't retrieve knowledge that was never written down
• It still hallucinates when the retrieval misses
• It doesn't understand why — just what

You can feed it your schema. You can't feed it the hallway conversation from 2019 where someone explained why that column is nullable.

What This Means for DBAs

None of this means AI is useless. It means AI is a tool — and like any tool, it works better when you understand what it can and can't do.

Use it for syntax, not semantics

LLMs are great at 'how do I write a query that does X?' They're bad at 'should I write a query that does X?' That's still your job.

Always validate the output

Treat AI-generated SQL like code from a junior dev who's never seen your system. Review it. Test it. Never trust it blindly.

Provide context aggressively

The more you tell the LLM about your schema, your conventions, and your constraints -- the better the output. Paste in table definitions. Explain the business rules. Don't make it guess.

Document your environment

If you want AI to help future DBAs (or future you), write things down. The model can only retrieve what exists. That tribal knowledge in your head? Useless to a RAG system until you externalize it.

The Bottom Line

LLMs are trained on the world's public knowledge. Your database isn't public. Your business logic isn't in the training data. Your hard-won understanding of why things are the way they are — that's not something a neural network can learn from Stack Overflow.

The AI knows SQL. It doesn't know your SQL.

That's not a flaw in the technology. It's just the gap where your expertise lives. The LLM can help you work faster. It can't replace the context that makes your work valuable.

At least, not until someone figures out how to train a model on ten years of Slack messages, three org chart changes, and those really great whiteboard drawings that got erased in 2021.

I'm not holding my breath.

More to Read

Microsoft Learn: What's New in SQL Server 2025
sqlfingers: Death of the DBA (Again)
sqlfingers: New Year's Resolution for DBAs: Make Peace with Copilot