Mission: SQL Homeostasis

Tuesday, September 15, 2026

Why sys.fn_dblog Is Undocumented And Why It's Still There

Why sys.fn_dblog Is Undocumented And Why It's Still There Why sys.fn_dblog Is Still Undocumented — And Still There

This article is about why things are the way they are, not how to do it. There are no step-by-step examples here on purpose, the further reading at the end covers the hands-on side.

sys.fn_dblog is an undocumented SQL Server function that reads the active portion of the transaction log. Every DBA eventually ends up looking for it. Maybe you're chasing a mystery delete. Maybe you're just curious about internal structures, what SQL Server actually writes when you update a single column. Either way, someone on a forum suggests this line. Or these days, more likely, your favorite AI tool does:

SELECT * FROM sys.fn_dblog(NULL, NULL);

You run it. Out comes a wall of columns with names like Current LSN, Operation, Context, AllocUnitName, Page ID, Log Record Fixed Length. It feels like you just pried the lid off the engine. And then you go looking for the documentation, and there isn't any.

That's not an oversight. It's been that way for two decades, on purpose, and the reasons are interesting.

First, why DBAs keep wanting this

The desire to read the transaction log is almost universal among DBAs, and it comes from a handful of very human places:

Forensics. "Who deleted those 1734 rows at 1:19 PM, and can I prove it?" If auditing wasn't enabled and there's no trigger, the log is the only remaining witness.

Recovery without a full restore. Sometimes you need to reconstruct a handful of rows, not restore the entire database to a scratch server. If the lost change is still in the active log, you may be able to inspect the log records and manually reconstruct some deleted or updated rows. Keep your expectations in check, though this rarely works out as cleanly as it sounds.

Point-in-time precision. Finding the exact LSN just before the bad transaction so you can RESTORE ... WITH STOPBEFOREMARK at the right boundary.

Understanding replication, CDC, and Availability Groups. All of them are log readers under the hood. Watching the log makes their behavior stop feeling like magic.

Log growth mysteries. Something is holding the log hostage and log_reuse_wait_desc is only telling you what, not who.

And honestly: education. A huge slice of fn_dblog usage is pure curiosity. Seeing that a single-row update produces LOP_MODIFY_ROW against a specific slot on a specific page, and that a page split fans out into a whole cluster of log records, teaches you more about SQL Server in ten minutes than a week of reading. That's a legitimate reason to poke at it. Just not on production.

A short history

The original way in was DBCC LOG, and it dates back to the SQL Server 6.x/7.0/2000 era. The syntax was as terse as everything else in the DBCC family:

DBCC LOG (dbid | 'DBName', 3)

Example:

The second parameter controlled verbosity, 0 for the bare minimum (operation, context, transaction ID), rising through 1, 2, 3 up to 4 for the full dump, as documented across community write-ups of the command. Later variants accepted extra arguments to filter by LSN, transaction ID, page ID, object ID, or record count (DBCC command reference list).

It is useful but awkward. It returned a fixed result set you couldn't join to, filter properly, or aggregate.

Paul Randal, who worked on the storage engine team, explained why so much of this lives under DBCC in the first place: adding a DBCC command is far easier than building a proper, supported T-SQL surface, so DBCC became the natural home for "reach in and touch an internal data structure" features built for the dev team's own use. That's the key insight: these things were never designed as user features. We're borrowing the engineers' tools!

Then SQL Server 2005 arrived with table-valued functions, and the internal log dump got a much nicer wrapper: sys.fn_dblog. Same idea, but now it's a relational rowset. You can WHERE, JOIN, GROUP BY, and dump it into a temp table.

A small family grew up around it, each solving a different limitation:

sys.fn_dblog(start_lsn, end_lsn): Reads only the active portion of the online log for the current database, with optional LSN bounds (NULL, NULL for everything available).

sys.fn_dump_dblog:Reads log backups and detached .ldf files, which is what you need once the records you want have already been truncated out of the live log. It's slower, takes a long list of mostly-NULL parameters, and is the tool Paul Randal walks through for locating a dropped object's LSN and then restoring with STOPBEFOREMARK (SQLskills).

sys.fn_full_dblog: First arrived in the SQL Server 2017 timeframe as a more capable alternative: eight parameters instead of two, adding database ID, page targeting, and backup account/container, which lets you query across databases with a CROSS APPLY against sys.databases. It returns the same ~130 columns. And it is also undocumented, nobody publicly documents what those backup parameters actually do.

Trace flag 2536 is the classic companion, used to make the inactive portion of the log visible too.

Notice what never arrived, for any of them: a documentation page.

Why: The log's on-disk format is an implementation detail

Microsoft's public documentation describes the transaction log logically, a serial stream of log records, each stamped with an ever-increasing LSN, physically divided into virtual log files (VLFs). Community internals work adds the next layer: a three-level hierarchy of VLFs containing log blocks containing the actual log records.

What's documented is the architecture. What's never documented is the byte layout, the log block header fields, the log record header, the per-operation payload encoding, how a LOP_MODIFY_ROW packs its before/after fragments, how the VLF header stores parity and sequence numbers.

And that's exactly the stuff that shifts between major versions. Every release brings storage-engine work that touches the log: new operation types for new features, changes to what gets logged and how, compression and encryption of what's on disk, and adjustments driven by the AG and CDC log readers. The log format is one of the least frozen structures in the SQL Server, because the log is where nearly every new engine feature has to leave its footprint.

fn_dblog isn't a translation layer that hides all this. It's a thin projection of internal structures. So when the internals move, its output moves with it:

The column list changes. It's roughly 116 columns on SQL Server 2008 R2, 129 on later builds, and 130+ depending on version.

Operation and context values evolve as features are added.

Payload semantics vary. An UPDATE doesn't necessarily record the whole before-and-after row; it can record just the changed fragment, which is why "reconstructing the old rows" is harder than it looks.

Documenting fn_dblog would mean committing to a contract Microsoft has no intention of freezing. The moment it's documented, it's supported; the moment it's supported, the storage engine team loses the freedom to reshape the log. Given the choice between "publish a stable log format" and "keep improving the log," the engine team picks the second one every time. Community consensus on the DBA side says the same thing plainly: it's undocumented, unsupported, can change or disappear at any version, and you can't get an official answer about what the columns mean. Microsoft's own forum guidance is blunt: the log exists for internal use, and reading it directly is not officially supported.

The transaction log file is not intended for direct reading by users but for internal use. What you are asking is a level 500 actions (internals) and it is not officially supported. Microsoft Q&A.

If you want another indicator that this is policy rather than neglect, look at sys.fn_full_dblog. In 2017 Microsoft shipped a newer, more capable log reader, more parameters, cross-database reach and documented exactly as much of it as its predecessor: nothing. Twelve years after fn_dblog appeared, with a clean opportunity to draw the line somewhere else, the answer was the same. The log's contents are not a public interface, and adding better internal tools doesn't change that.

So why not remove it? Because Microsoft still needs it. Support engineers, the product group, and internal recovery scenarios all rely on being able to dump the log. It stays because it's useful to them, we're just allowed to look. That's the whole bargain: available, never promised.

Feature by feature, the log format kept evolving

If you want concrete evidence that the log format isn't a fixed target, look at what's been bolted into it over the last decade. Each of these changed what gets written, or what a log reader sees:

In-Memory OLTP (2014). Memory-optimized tables share the same log file but log very differently: no write-ahead logging in the traditional sense, multiple row changes merged into a single log record, and no log records at all for index modifications since indexes are rebuilt at recovery (sqlserver-help.com). A tool that assumes one log record per row modification is already wrong.

Accelerated Database Recovery (2019). ADR versions physical modifications into a Persistent Version Store and only undoes non-versioned operations, which lets recovery skip the traditional undo phase, and because the PVS itself must be recoverable, all operations against it are logged, increasing log volume, New record types, new semantics, same function signature.

Columnstore, TDE, log compression for AGs, minimally logged bulk operations each one either adds record shapes or removes information from the log entirely.

None of these arrived with a "here's what changed in the log format".

What Microsoft does document and why the difference matters

Here's the tell that this isn't laziness. Microsoft has been steadily adding documented, supported views over the log, they just stop at metadata and aggregates, never record contents:

sys.dm_db_log_info (SQL Server 2016 SP2+) returns VLF-level information, the supported replacement for DBCC LOGINFO.

sys.dm_db_log_stats returns summary-level log health attributes including log_backup_time, which is genuinely useful on AG secondaries and needs only VIEW DATABASE STATE rather than sysadmin.

log_reuse_wait_desc in sys.databases tells you what's preventing truncation.

Microsoft's intention is clear: how much log, how many VLFs, what's blocking reuse, and when it was last backed up are all fair game and will be kept stable. What the individual records say is not, and never will be.

Meanwhile, Microsoft does ship fully supported ways to consume log content, they just don't let you read it raw. The Replication Log Reader Agent monitors the log and moves marked transactions into the distribution database , and CDC, change tracking, and replication are all supported on Always On Availability Groups . Those are the sanctioned log readers. fn_dblog is the unsanctioned one.

Replication Log Reader

Restrictions and Limitations

Things that bite people, and that no documentation page will warn you about:

It's sysadmin-gated. Querying it fails with "User does not have permission to query the virtual table, DBLog" (Msg 9010) for anyone who isn't sysadmin; plain GRANT SELECT on the function isn't enough (DBA Stack Exchange). Certificate-signed module signing is the usual workaround when a tool genuinely needs it which is exactly why ETL vendors document db_owner plus SELECT on master.sys.fn_dblog as an alternative to full sysadmin. Sysadmin-for-forensics is a real governance conversation.

You're racing truncation. fn_dblog only sees the active portion. In SIMPLE recovery a CHECKPOINT clears it; in FULL a log backup does. Once the records are gone, only fn_dump_dblog against backups can help which is why a huge .ldf can still return almost nothing. Note: Preserving log evidence during an actual incident has enough moving parts to deserve its own post, I'll cover it separately someday.

Volume and cost. On a busy database the active log can hold millions of records. Filter on LSN ranges, Operation, and AllocUnitName; don't SELECT * and hope.

fn_dump_dblog is not free. It's markedly slower than fn_dblog, and it's well known in the field for holding onto resources within the session, so treat it as something you run deliberately on a scratch instance, not casually on a production box.

TDE and encryption cut you off. Encrypted log content limits what any log reader, Microsoft's or a vendor's, can hand back.

PaaS closes the door. Azure SQL Database does not expose the transaction log, and log access is restricted on managed platforms generally, which is why log-based CDC tools fall back to other capture methods there. As workloads move to PaaS, the log-reading skill gets less portable, not more.

Change data capture (CDC) with Azure SQL Database

Names are not stable either. Even the "friendly" columns aren't a contract. Reading AllocUnitName and joining out to system metadata works, until an internal name format shifts.

The third-party angle

If Microsoft won't decode the log for you, vendors will try. There's a long lineage of commercial log readers: ApexSQL Log (later a Quest product), Lumigent Log Explorer, Red Gate's SQL Log Rescue, and others offering graphical row-level audit trails and undo/redo script generation from online logs and log backups (Quest/ApexSQL).

How do they do it? Broadly, two approaches, usually combined:

Read the .ldf and backup files directly and parse the binary structures themselves. ApexSQL Log, for instance, doesn't install anything on the SQL Server engine; it installs a Windows service to enable remote reading of the online log files and analyzes native or compressed log and backup content (ApexSQL FAQ).

Lean on the same undocumented surfaces we have fn_dblog and fn_dump_dblog then enrich the raw records by joining against system metadata to turn page IDs, slot IDs, and allocation unit names back into recognizable tables, columns, and values.

Both paths hit the same wall: the format is proprietary and undocumented, so everything is reverse-engineered. That has consequences worth knowing before you buy:

Version lag. Every new major release means re-reverse-engineering. Support ships months late, or not at all.

Partial reconstruction. Because the log records deltas rather than full row images in many cases, and because some operations are minimally logged, a complete audit trail isn't always achievable. The tools do impressively well, then hit gaps they can't fill.

Feature blind spots. In-Memory OLTP's merged log records, ADR's version-store records, columnstore, and encryption each degrade what a reverse-engineered parser can reconstruct.

No schema time machine. Decoding an old log record requires knowing the table's schema as it was then. If columns were added or dropped since, reconstruction gets shaky fast which is a limitation shared by every tool in the category.

And the commercial risk is real. ApexSQL Log hasn't added SQL Server 2022 support, and the product line has been headed for discontinuation (r/SQLServer discussion). Note where the surviving change-capture ecosystem went instead: modern pipelines like Debezium For SQL Server and most cloud connectors consume CDC or change tracking, the documented interfaces, rather than parsing .ldf bytes.

Practical Guidance

If you want to use fn_dblog, use it the way it deserves to be used:

Play on a scratch instance, not production. Restore a copy and investigate there whenever you can.

Treat it as a lens, not a source of truth. Never build a report, application, or automated job on its column list. It will break on your next upgrade, with no support recourse.

Preserve evidence first. Before you investigate a suspected data loss: take a log backup to a safe location, then pause routine log backups and log-shrink jobs so the active log stops rolling over.

Run: SELECT * INTO OtherDB.dbo.LogDump FROM sys.fn_dblog(NULL, NULL);

For real auditing, use documented features. SQL Server Audit, Extended Events, temporal tables, or CDC/change tracking. They exist precisely so you don't have to read the log.

For real recovery, use backups. Log backups plus STOPAT / STOPBEFOREMARK is the supported path. fn_dblog is great for finding the LSN to stop before; it's a poor substitute for the restore itself.

Write down your version. If you keep a runbook that queries the log, record the exact build it was validated on, and re-verify after every upgrade. That single habit turns an unsupported query from a liability into a managed risk.

Learn from it freely. Run an insert, an update, a delete, a page split, a rollback, and watch what appears. Best mental model of logging you'll ever build, and it costs nothing on a test database.

Conclusion

sys.fn_dblog sits in a peculiar, permanent middle ground: too useful for Microsoft to remove, too volatile for Microsoft to document. It survives because the on-disk log format is an implementation detail that changes with major versions, log block headers, record headers, operation encodings, feature-driven additions from In-Memory OLTP to ADR and publishing a stable interface over it would freeze a structure the engine team needs to keep changing.

DBCC LOG was the first crack in that wall. fn_dblog made the view a lot clearer. Third-party tools spent twenty years reverse-engineering the rest with real skill and real limits. And Microsoft's answer, consistently, has been to document the log's shape while keeping its contents private, and to hand you CDC and replication when you need the contents for real.

So go look inside the log. Just don't build anything load-bearing on the view.

Thursday, September 10, 2026

How to Check TDE Progress and Elapsed Time in SQL Server

Transparent Data Encryption (TDE) protects SQL Server data and log files while they are stored on disk. SQL Server handles encryption and decryption automatically, allowing applications and users to continue working normally. Database backups are also encrypted and cannot be restored without the correct certificate or key.

What does TDE protect against?

TDE protects data while it is at rest in database files, transaction log files, backups, disks, and storage snapshots. If someone steals or copies those files, they cannot simply restore or attach the database and read its contents without the correct certificate or key.

When SQL Server reads an encrypted page from disk into the buffer cache, it automatically decrypts the page so the database engine can use it. This means TDE does not protect data already in memory, nor does it prevent SQL injection, compromised administrator accounts, misuse by authorized users, unencrypted exports, or interception of data travelling across the network.

Who should use TDE?

If your database contains sensitive or personal information, TDE is worth considering. It is especially useful when encryption at rest is required by standards such as HIPAA or PCI DSS, or by your organization’s security policies.

However, don’t enable TDE everywhere without a plan. It introduces some performance overhead and requires careful management of encryption keys and certificates. Use it where the security and data-protection requirements justify the extra work.

Why can a TDE scan take so long?

TDE encryption and decryption scans can take hours, even on a moderately sized SQL Server database.

The scan runs in the background and must read and rewrite every database page. Its speed depends heavily on storage performance, and SQL Server provides no supported MAXDOP, priority, or speed setting to make it finish faster. TDE may be an online operation, but it is not always a “start it and forget it” job. On a large database, the scan can keep storage busy for hours.

Does Enterprise Edition make TDE faster?

Not directly and usually not by much. TDE works the same way in SQL Server Standard and Enterprise editions. Enterprise can use greater CPU, memory, and high-availability capabilities, but it does not provide a different form of TDE or a special setting that makes the scan faster.

Why should you monitor TDE progress?

The initial TDE scan, which encrypts the existing data pages at rest, can take much longer than expected. Even a modest 300–400 GB database may take hours when server activity is high or storage is struggling to keep up. Watching the percentage creep forward can feel endless, especially during your first TDE implementation.

The wait becomes more stressful when the next step in your change plan depends on the scan finishing. For example, you may be waiting to take the final encrypted backup, add the database to an Availability Group, or run post-implementation scripts and validation checks. A delay can affect both your schedule and your blood pressure!

Naturally, you want answers: How much is complete? How long has it been running? Is it making steady progress? Is the scan running normally, suspended, or aborted? Without that information, your carefully planned change window can quickly turn into a guessing game.

How can you check TDE progress and elapsed time?

The following practical query helps answer those questions. By viewing the percentage complete alongside the elapsed time, you can get a useful sense of its average pace and whether it is moving normally. It is not a precise finish-time prediction because workloads and storage activity can change, but it helps you see whether the scan is making steady progress or may need attention.

/*
    PURPOSE
    -------
    Shows the progress, status, and duration of current TDE encryption,
    decryption, key-change, and protection-change operations.

    Why this query is different
    ---------------------------
    Most TDE monitoring queries show only the current status and 
    percentage complete from sys.dm_database_encryption_keys. This 
    query also matches the DMV results with the most recent 
    scan-start entry in the SQL Server error log, allowing it to show 
    the start time and elapsed duration without requiring a monitoring 
    table, SQL Agent job, or external tool.

    HOW IT WORKS
    ------------
    1. Reads TDE scan-start messages from the current SQL Server 
       error log and stores them in a temporary table.

    2. Queries sys.dm_database_encryption_keys for databases with an 
       active, suspended, or aborted TDE operation.

    3. Matches each database to its most recent "Beginning database
       encryption scan" error-log entry.

    4. Calculates the duration between that entry and the current 
       server time. Duration is displayed in seconds, minutes, and 
       HH:MM:SS format.

    IMPORTANT LIMITATIONS
    ---------------------
    - Only the current SQL Server error log is searched.

    - If the error log rolled over after the scan started, the start 
      time and duration will be NULL.

    - If a scan was suspended and resumed, duration begins with the 
      most recent scan-start entry. It does not include time from 
      earlier runs.

    - encryption_scan_modify_date is reported in UTC, while the 
      error-log and collection times normally use the SQL Server 
      host's local time.

    REQUIREMENTS
    ------------
    SQL Server 2019 or later because the encryption scan state 
    columns were introduced with SQL Server 2019.
*/


USE master;
GO

SET NOCOUNT ON;

DROP TABLE IF EXISTS #TdeErrorLog;

CREATE TABLE #TdeErrorLog
(
    LogDate     datetime,
    ProcessInfo nvarchar(50),
    [Text]      nvarchar(max)
);

INSERT INTO #TdeErrorLog
EXEC sys.xp_readerrorlog
     0,                                     -- Current error log
     1,                                     -- SQL Server error log
     N'Beginning database encryption scan',
     NULL,
     NULL,
     NULL,
     N'desc';

SELECT
    d.name AS database_name,
    dek.encryption_state,
    dek.encryption_state_desc,
    dek.percent_complete,

    dek.encryption_scan_state,
    dek.encryption_scan_state_desc,

    scan_start.operation_start_time,
    GETDATE() AS collection_time,

    duration.duration_seconds,
    CAST(duration.duration_seconds / 60.0
         AS decimal(18,2)) AS duration_minutes,

    CASE
        WHEN duration.duration_seconds IS NULL THEN NULL
        ELSE CONCAT
        (
            duration.duration_seconds / 86400, N'd ',
            RIGHT(N'00' + CONVERT(nvarchar(2),
                (duration.duration_seconds % 86400) / 3600), 2), N':',
            RIGHT(N'00' + CONVERT(nvarchar(2),
                (duration.duration_seconds % 3600) / 60), 2), N':',
            RIGHT(N'00' + CONVERT(nvarchar(2),
                duration.duration_seconds % 60), 2)
        )
    END AS formatted_duration,

    -- This DMV value is documented as UTC
    dek.encryption_scan_modify_date
        AS scan_state_modified_utc,

    dek.key_algorithm,
    dek.key_length,
    dek.encryptor_type

FROM sys.dm_database_encryption_keys AS dek

INNER JOIN sys.databases AS d
    ON d.database_id = dek.database_id

OUTER APPLY
(
    SELECT TOP (1)
        el.LogDate AS operation_start_time
    FROM #TdeErrorLog AS el
    WHERE CHARINDEX
          (
              N'''' + d.name + N'''',
              el.[Text]
          ) > 0
    ORDER BY el.LogDate DESC
) AS scan_start

OUTER APPLY
(
    SELECT
        CASE
            WHEN scan_start.operation_start_time IS NOT NULL
            THEN DATEDIFF_BIG
                 (
                     SECOND,
                     scan_start.operation_start_time,
                     GETDATE()
                 )
        END AS duration_seconds
) AS duration

-- Only active, suspended, or aborted TDE operations
WHERE dek.encryption_state IN
(
    2,  -- Encryption in progress
    4,  -- Key change in progress
    5,  -- Decryption in progress
    6   -- Protection change in progress
)
ORDER BY d.name;

DROP TABLE IF EXISTS #TdeErrorLog;
GO

What happens during a TDE scan?

Now that we can monitor the scan, it helps to understand what SQL Server is doing behind the scenes.

Even though it may appear so, TDE encryption and decryption scans are not necessarily single-threaded. When you enable TDE, SQL Server performs a few checks and starts a background encryption worker. This allows the original command to finish while the real work continues in the background.

The encryption worker creates disk workers to scan the database files. Each worker processes 8 KB database pages in batches of 32. The pages are loaded into memory, marked as changed, and logged so the operation can also be replayed on an Availability Group secondary.

After the scan finishes, SQL Server performs a checkpoint. During encryption, the changed pages are encrypted and written back to disk. Decryption and encryption-key rotation use the same basic scanning process, although the pages are handled according to the requested operation.

SQL Server generally creates one disk worker per storage volume, not one worker per processor. Ten data files on one volume may still use only one worker, while files spread across ten volumes could use ten workers. This helps control the impact on storage, but it also explains why some TDE scans appear almost single-threaded.

TDE scan parallelism is managed internally by SQL Server and is not controlled by MAXDOP. Even when multiple workers are active, the scan can still be limited by storage performance.

Because every page must be read, processed, and written back, a TDE scan consumes storage, memory, and CPU resources. It may therefore compete with the database’s normal workload.

A 2019 Microsoft article about TDE scan internals describes SQL Server creating approximately one disk worker per storage volume. The core scan process remains relevant to SQL Server 2025, but worker allocation and batching are internal implementation details that could change in future versions or cumulative updates.

Can you make a TDE scan run faster?

TDE scans depend heavily on storage speed. If several databases on the same storage volume are being processed at once, their scans will compete for I/O and may all take longer.

Adding more CPU or changing MAXDOP is unlikely to help. Adding extra data files on the same storage volume will not necessarily make the scan more parallel either.

What should you avoid while the scan is running?

While a scan is running, try to reduce other storage-heavy work such as backups, CHECKDB, index maintenance, and ETL processes. Avoid moving database files or changing the storage layout because some file operations are restricted during a TDE scan.

Should you redesign your storage for TDE?

That depends. For future large databases, placing files on separate physical storage volumes may allow SQL Server to use additional workers. However, this only helps when the volumes provide genuinely independent throughput, rather than separate drive letters backed by the same storage pool. In the year 2026, with shared SAN and cloud storage now common and IT shifting toward cloud and AI platforms, a dedicated physical SQL Server with its own direct-attached storage is typically reserved for rare or specialized requirements.

That said, there is no need to redesign every database just for an occasional TDE scan. A more practical approach is to provide sufficient storage performance and run only one major TDE scan per shared volume at a time.

What Triggers a Full TDE Scan?

It helps to know which operations trigger a full TDE scan, so you know when to keep an eye on its progress. A full scan occurs when SQL Server must encrypt, decrypt, or re-encrypt every page in the database. Three main operations cause this:

Enabling TDE: SQL Server reads every database page, encrypts it, and writes it back to storage.

Disabling TDE: SQL Server performs the journey in reverse, reading and rewriting every page without TDE encryption.

Regenerating the database encryption key (DEK): SQL Server creates a new DEK and re-encrypts every page with it. This includes changing the DEK’s encryption algorithm

There is an important difference between regenerating the DEK and changing the certificate that protects it. During the initial TDE setup, the certificate is created and stored in the instance’s master database before the DEK is created in the user database. Changing this certificate or asymmetric key normally re-encrypts only the DEK, not every database page. This is much less work and does not require a full database scan.

Pausing, resuming, or restarting SQL Server during a scan does not start a brand-new scan. SQL Server saves the progress and continues the existing operation when it resumes

Monday, August 17, 2026

SQL Server gMSA: Why So Many DBAs Still Aren't Using It in 2026

SQL Server gMSA: Why DBAs Still Aren't Using It in 2026

Group Managed Service Accounts (gMSAs) solve the classic service-account password problem - just look at the glowing comments on this Reddit thread.

It's a wonderful solution to a genuinely difficult and essential security problem. When an experienced DBA first learns about gMSA, they search the internet and invariably land on a handful of excellent how-to posts that are clear, step-by-step and easy to follow. That's the old approach. In 2026, internet search and the blogosphere that fed it are practically dead. Now it's AI generating the step-by-step instructions, walking DBAs through the entire implementation, and troubleshooting whatever errors come up along the way. And yet here I am, writing this blog post that almost nobody will read😭, more likely to be read by AI crawlers than by actual DBAs🤣.

And yet none of that changes the one thing that was never a knowledge problem in the first place: getting a production AD team to actually implement it. My skepticism here is practical, not technical. I rarely have the kind of sway over the people who run Active Directory in a production environment to get gMSAs approved and implemented, even when we're on good terms and even when I have buy-in from upper management. And that's before you even factor in the technical challenges on top of it.

Sure, I can spin up a whole IT infrastructure from scratch - VMs, networking, DNS, Active Directory - all of it, in my own lab. That makes for a great demo showing how easy gMSAs are to set up and why every SQL Server should be running on them. But a lab with no change-control process, no separate AD team, and no production risk isn't my day job. Reality looks a lot different.

In short, it's not that DBAs don't want gMSAs, most of us do. It's that getting there requires technical prerequisites to fall into place and organizational cooperation we often don't fully control, and together those two things keep adoption surprisingly low even a decade after SQL Server 2014 first added support.

Technical challenges

Hard dependency on Active Directory/KDS availability at startup

Every time SQL Server starts, it has to phone home to a domain controller running the Key Distribution Service just to retrieve the current password - there's no cached fallback. Take away that domain controller for any reason - DC down or inaccessible for some reason, network issues, a remote site having connectivity issues etc. and the SQL instance simply won't start. SQL Server sits at the top of its own encryption hierarchy, waiting indefinitely for credentials it has no way to get on its own (SQLskills).

Setup overhead before you even touch SQL Server

Before you can open SQL Server Configuration Manager and pick a gMSA, three prerequisites have to already be in place: domain and forest functional levels at Windows Server 2012 or later, the Active Directory PowerShell module, and a KDS root key. That last one isn't instant - Microsoft enforces a 10-hour wait after the root key is created to let it replicate across every domain controller before the first gMSA can even be issued (Microsoft Learn). If you were hoping to pilot this in an afternoon, plan again. That 10-hour clock alone kills many adoption attempts before they get started.

SPN auto-registration doesn't always work as advertised

Permission misconfigurations cause SPN registration to silently fail - which makes SQL Server fall back to NTLM instead of Kerberos, the opposite of the security improvement gMSA was supposed to deliver, and a very common source of hours-long troubleshooting (AutomateSQL, LinkedIn/Luke Campbell).

No support for SQL Server Agent proxies/credentials

You can't create a SQL Server credential via SSMS with a gMSA because it requires a password, and since AD deliberately withholds the plaintext password from you, Agent proxy jobs that rely on that credential fail outright. This gap persists as of SQL Server 2022 (Stack Overflow).

15-character SamAccountName limit

gMSA names are capped at 15 characters via New-ADServiceAccount, which clashes with most shops' naming standards for service accounts, especially when following a "one gMSA per service per server" pattern (ServerFault). It's the same 15-character NetBIOS ceiling that also caps Always On Availability Group listener names. Two separate naming headaches with one shared root cause😢.

Availability Group / Listener SPN constraints

All AG replicas must use the identical service account, and the AG Listener's SPN can only be bound to one account at a time. This is a Kerberos design constraint, not a bug, and it remains fully in effect today regardless of SQL Server version. What has genuinely been unreliable across versions, including current ones, is gMSA's promised automatic SPN registration for the listener object specifically. Many DBAs still end up registering that SPN manually with setspn, even on SQL Server 2022 (Microsoft Learn).

Cluster service itself doesn't support gMSA

Only services running on top of the Windows Failover Cluster (the SQL Server service, Agent, etc.) can use a gMSA - the cluster service resource itself cannot (r/sysadmin).

Brief outages during password rotation in some real-world cases

Despite the "no restart needed" promise, some environments report a several-minute authentication gap when the password rotates (default every 30 days) if the dependent service doesn't refresh its cached credential fast enough (Microsoft Q&A).

Not so transparent failure modes

When something does go wrong, there's often nothing in the ERRORLOG or Event Viewer, the instance just hangs in a "Starting" state, forcing admins to run sqlservr.exe -c from a console to see the real error, which is a much higher troubleshooting bar than a normal domain account failure (SQLSmartSolutions).

Why DBAs don't widely adopt it

Cross-team dependency

Creating and linking gMSAs requires AD-side actions (creating the KDS root key, the account, the security group, adding computer objects) that most DBAs can't do themselves. In shops where the AD/Windows team is siloed from the DBA team, as usually is the case, this coordination overhead alone kills momentum before the technical benefits are even seen.

Simple lack of awareness

A managed-services provider that has used gMSA "by default" for years still writes that "in a decade of Health Checks, we still rarely see them deployed," attributing it largely to DBAs not knowing the option exists or assuming it's too complex (SQL Solutions Group).

"I tried it once and it broke"

The two most-cited reasons in practitioner discussions are almost verbatim "I tried it once and SPNs broke" and "I wasn't sure it would work with our AG." A single bad first experience, often the SPN registration issue is enough to make teams revert to conventional domain accounts and never revisit it (SQL Solutions Group).

Perceived setup tedium versus a "working" status quo

Even gMSA proponents concede "the initial configuration can be quite tedious," and if a shop already has a functioning (if imperfect) password-rotation runbook, the switching cost doesn't feel worth it (r/SQLServer).

Incomplete coverage forces a hybrid model anyway

Because Agent proxies/credentials still need a real password, shops adopting gMSA for the engine service still end up maintaining conventional accounts elsewhere - undercutting the "never manage a password again" pitch that's the main selling point.

Historical AG limitations linger in institutional memory

Even though gMSA support for Availability Groups has been in place since SQL Server 2016, and the tooling around it has matured considerably since, many DBAs' mental model of gMSA is still frozen at "doesn't fully work with Always On." So it never gets reconsidered for HA/DR designs, where it would otherwise be most useful (Microsoft Learn).

In short: gMSA is technically solid and has been supported since SQL Server 2014, but its dependency on AD/KDS at every startup, SPN fragility, incomplete Agent-proxy support, and the cross-team AD coordination it demands mean it often feels not worth the headaches and efforts and not so transparent failure modes to most production DBAs.

Mitigating strategy: What Many DBAs Often Do Instead

In the absence of a solid gMSA implementation, DBAs typically fall back on one of two approaches to manage the risk.

Option 1: Rotate the password on a regular interval. On a standalone instance, SQL Server Configuration Manager can update the account password without any downtime. That clean approach disappears the moment you're on a Failover Cluster Instance or an Always On Availability Group, though, the passive nodes still need their password updated through the Windows Service Control Manager, and that method does require a restart (Microsoft Learn). In practice, that means rotating a domain service account password on a clustered environment still means a scheduled maintenance window on every node, on every SQL Server in your environment. For a shop running dozens of clustered instances, that's not a quick task. It's a recurring change control burden, which is exactly why rotation intervals quietly stretch from "every 30, 60, or 90 days" to "whenever we get around to it."

Option 2: Never change the password and mitigate the risk a different way. Instead of rotating the password at all, many DBAs get IT Security's sign-off to leave it static and lean on compensating controls instead:

Use a long, complex password that resists brute-force attempts.
Use a separate service account per SQL Server instance or cluster, so a single compromised credential doesn't cascade across the environment.
Disable interactive logon for the account entirely.
Grant only the OS- and network-level permissions the account actually needs and nothing more.

Tuesday, August 4, 2026

SQL Server sa Account Security: Rename, Disable & Best Practices

In the Windows administration world, it's pretty much a standard practice to rename the local administrator account (BUILTIN\Administrator) to something else.

In the SQL Server world, it's not as common to rename the sa account. In fact, some DBAs may not be even aware this is possible, mainly because the recommended security best practice is to avoid running your SQL Server instance in Mixed Mode Authentication in the first place, and under Windows Authentication mode, the sa account is disabled by default.

Now there's some debate over whether renaming the sa login is worth the trouble, or whether it's just more effort than it's worth. The honest answer is "both", it's a worthwhile habit against the attacks that actually happen at scale, but a limited one that won't stop anyone who already has a foothold in your environment. That's because the account's SID never changes no matter what you rename it to, so anyone with enough access to query system metadata can look up the new name in seconds. For that reason, if you're running Mixed Mode Authentication, your sa account security strategy should also include other layered measures: disabling the account, enforcing a strong password, and auditing any connection attempts made using sa.

The Real Best Practice: Prefer Windows Authentication

Microsoft's own guidance is unambiguous: use Windows Authentication wherever feasible, and treat Mixed Mode as a fallback for cases that genuinely require it, legacy applications, non-domain environments, or third-party tools that only support SQL logins..

The reasoning is simple: Windows Authentication passes an already-validated Kerberos/NTLM token rather than a username and password over the wire, inherits Active Directory's account lockout, complexity, and expiration policies for free, and eliminates an entire class of local SQL Server accounts that would otherwise need independent password management. Mixed Mode, by contrast, means SQL Server itself has to store and defend credentials, and once you're in that mode, sa exists as an enabled, sysadmin privileged account by default and becomes the single most attractive target in the instance. In short:

Use Windows Authentication mode wherever the application allows it, this removes the sa conversation almost entirely, since sa is created disabled by default under Windows-only mode.

If Mixed Mode is unavoidable, then everything below about disabling, renaming, and password-hardening sa becomes relevant. Here, renaming sa is a tactic for damage control within Mixed Mode..

When Did SQL Server Start Allowing This?

Before SQL Server 2005, which I call "the good old days", there was no supported way to rename sa. The only "solution" floating around forums involved directly editing system tables in master, a dangerous move that Microsoft never supported and that could leave an instance in an unrecoverable state.

That changed with SQL Server 2005, which introduced the ALTER LOGIN statement specifically to let DBAs disable and rename sa as part of a broader security model overhaul. Every version since then (2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, 2022, and 2025) supports the exact same syntax, and it also works in Azure SQL Database.

The early SQL Server 2008 setup had a bug where upgrading an instance with a renamed sa login could fail during the upgrade scripts. This was fixed by 2008 R2 and isn't a concern on any version in active use today.

How to Actually Do It

The T-SQL is almost anticlimactic given how much debate surrounds it:

-- Rename sa to something non-obvious
ALTER LOGIN sa WITH NAME = [Morgan];
GO

-- Confirm the SID hasn't changed (it never does)
SELECT name, sid FROM sys.sql_logins WHERE sid = 0x01;

Please note:

You can't do this from the SSMS GUI, the name field is grayed out on the sa login's Properties page. T-SQL is the only route.

The login's SID stays 0x01 no matter what you rename it to, which means SQL Agent job ownership, object ownership, and other SID-based references all continue to resolve correctly after the rename, no orphaned ownership to fix.

Pick a name that doesn't scream "I'm the renamed sa account", avoid anything like sa_old, admin2, or sysadmin_backup.

The Case For It

It defeats the laziest class of attacker. A meaningful share of brute-force and credential-stuffing tools targeting SQL Server hardcode the username sa because it's a known, universal default. Renaming it means those tools fail at the first step, they never get to test passwords at all.

It's a recognized industry practice, not just my humble opinion. Renaming sa shows up as a hardening recommendation across community best-practice guides and vendor documentation, usually grouped with disabling sa and enforcing a strong password as the standard trio of sa specific controls.

It costs essentially nothing. It's a single ALTER LOGIN statement, doesn't break anything ownership-related because the SID persists, and can be reversed instantly if needed.

It layers cleanly with your other controls. It doesn't replace disabling sa or setting a vaulted, high entropy password.. It's a Defense-in-depth so an attacker has to defeat all of them, not just one.

Its Limitations

The SID gives it away, and this is permanent, by design. This is the crux of the criticism, and it's worth understanding precisely why. sa always carries SID 0x01, and ALTER LOGIN has no option to change a login's SID at all, the syntax simply doesn't expose one. Anyone who can query sys.sql_logins, which requires sysadmin, securityadmin, or metadata visibility permissions like VIEW ANY DEFINITION/VIEW SERVER STATE, can unmask it in one line:

SELECT name FROM sys.sql_logins WHERE sid = 0x01;

SELECT name FROM sys.server_principals WHERE sid = 0x01;

Could you get around this by dropping and recreating the login with a new SID? No, and this is worth mentioning because it does come up. The only way a login gets a different SID is to drop it and recreate it from scratch, which would orphan every database user, job, and object mapped to the old SID and require remapping each one with ALTER USER ... WITH LOGIN. But this workaround isn't even available for sa specifically, because the built-in sa login can't be dropped in the first place, it's baked into the engine as the owner of master and tempdb and other system-level dependencies. So the SID 0x01 is permanently fixed for the life of the instance, renaming changes the label, never the identity.

It's obscurity, not access control. Renaming doesn't reduce what the account can do, doesn't change its permissions, and doesn't stop impersonation based attacks (EXECUTE AS) that target the account by SID or by role membership rather than by name.

It can complicate troubleshooting, monitoring dashboards, and third party tools and applications, some of which still hardcodes the literal name sa (and yes, I've been burned by this before).

So Why Do It Anyway?

Here's the thing, most attackers going after sa aren't sophisticated. They're automated scanners, credential-stuffing bots, and opportunistic scripts that got lucky after some other breach and are now poking around your network as fast as possible, using off-the-shelf tooling. Renaming sa doesn't stop a determined human who's already inside your system, but against that lazier, high-volume crowd, it works surprisingly well. You're closing off an easy path for a few seconds of effort. Just keep in mind this whole conversation only matters once you've already decided Mixed Mode Authentication is a business necessity. Otherwise sa isn't even in play.

Where people get it wrong is treating renaming as the whole solution or worse, as more important than the more effective controls like preferring Windows Authentication whenever you can, disabling the account, giving it a long random password locked away in a vault, and auditing anytime someone tries to use it. Renaming is the icing. It's not the cake.

Security Best Practices for the sa Account:

Here's the most comprehensive list I could put together:

Default to Windows Authentication; only enable Mixed Mode when a specific, documented requirement demands it

Disable sa after setting its password (the actual control)

Assign a long, random, vaulted password to sa regardless of rename/disable status

Rotate the vaulted password on a defined cadence, and immediately after any emergency use

Rename sa to a non-obvious name (optional but recommended)

Remember the SID (0x01) is permanent and unmaskable to anyone with metadata-read access, so don't rely on the rename as a real access barrier

Never use sa for application or service connectivity

Audit all authentication attempts against the sa SID, regardless of its current display name

Document the current name and rationale somewhere your on-call team can find it during an incident

Periodically check that no rogue login is named sa. This is a commonly cited but easy-to-miss check since sa is a well-known target, someone (attacker or well-meaning app installer) could create a new login literally named sa that isn't the real account.

Search This Blog