SQL Server gMSA: Why So Many 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:

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

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;

or

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. 

---

See also

• Choosing an authentication mode
• Best Practices to Secure the SQL Server sa Account
• An old SQLServerCentral thread describing how to rename sa via a system table hack
• SQL Server's Unseen Orphan Users: Understanding Database Ownership Anomalies

SQL Server instance metadata inventory with PowerShell and SMO

The purpose of this article is to introduce a PowerShell script that I wrote and published on GitHub. The script is a quick and practical way to grab instance‑level configuration from a bunch of servers and dump it into CSV files you can actually use. Instead of clicking through SSMS on every box, you run one PowerShell script, let SMO do the heavy lifting, and end up with three inventory files you can filter, pivot, or feed into whatever reporting you already have.

The approach builds on the ideas in a longer and older post, SQL Server Metadata using Powershell ,but this version is aimed at people who just want a reliable tool they can run on a schedule and hand to the rest of the team

---

Scope

The scope of the  PowerShell script in the GitHub repo SQL Instance Metadata Export Script  is intentionally narrow: it focuses on and gathers only instance‑level metadata, and not database or objects level metadata. The goal is a simple inventory you can trust, not a full CMDB.

---

Output Files

The script writes three CSV files and a README.txt file, each one covering a different slice of instance‑level metadata across all the servers you pass in.

Info CSV:  Contains attributes from Server.Information (SMO), including version, edition, collation, operating system details, and other identifying properties for each instance.

Settings CSV:  Contains attributes from Server.Settings (SMO), including instance-level configuration values that would otherwise need to be obtained from SSMS or dynamic management views (DMVs).

Config CSV: Contains attributes from Server.Configuration (SMO), providing a view of sp_configure style options, including both ConfigValue and RunValue. The script outputs two rows per instance, identified by ValueType = ConfigValue or RunValue, to distinguish persisted values from those currently in effect..

README.txt: Contains a human‑readable summary of the export, including what each CSV file contains, how the metadata was collected (PowerShell + SMO, authentication details), the script’s start and end timestamps, total duration, and a list of processed and failed SQL Server instances.

All three CSV files also include:

• ServerInstance
• HostName
• IPAddress
• SQLVersionString
• NumberOfDatabases
• AlwaysOnStatus

That extra context is there so that when you’re doing baselines, you don’t have to join back to another inventory just to figure out which host you’re looking at.

---

How the script behaves

Under the covers, the script does roughly this:

• Loads the SqlServer PowerShell module so SMO is available.
• Loops through every value you pass into -ServerInstance (or via the pipeline).

• For each instance:

• Connects using Windows auth by default, or SQL auth if you pass -SqlCredential.
• Pulls Server.Information, Server.Settings, and Server.Configuration.
• Builds a wide PSCustomObject for each category using ordered hashtables, so the CSV columns are stable.

• Collects all rows in memory and writes three CSVs once at the end.

---

Usage

Here are the core patterns you’re likely to use day‑to‑day.

Single instance, default file names

.\Export-InstanceMetadata.ps1 -ServerInstance "ProdSql01"

This creates 3 csv files and a README.txt into your current folder:

• SQLInstanceMetadata_Info.csv
• SQLInstanceMetadata_Settings.csv
• SQLInstanceMetadata_Config.csv
• SQLInstanceMetadata_README.txt

Multiple instances, specific output folder

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "SQL01","SQL02\INST1","SQL03" `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

Same three files, but every instance you passed in is represented. -Verbose gives you enough noise to see which instances connected cleanly and which ones failed without drowning you in debug.

Custom prefix for per‑environment baselines

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "ProdSql01","ProdSql02" `
    -OutputFolder "C:\SQL\Inventory" `
    -FileBaseName "SQLProd" `
    -Verbose

Now your files are:

• SQLProd_Info.csv
• SQLProd_Settings.csv
• SQLProd_Config.csv
• SQLProd_README.txt

This makes it easy to keep separate snapshots for Prod, Test, DR, etc. without renaming files after the fact.

Using SQL authentication

$cred = Get-Credential  # SQL login
.\Export-InstanceMetadata.ps1 `
    -ServerInstance "VendorHostedSql" `
    -SqlCredential $cred `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

Same behavior, but you’re not relying on domain auth. Handy for vendor‑hosted or isolated environments where Windows auth isn’t an option.

Feeding instance names from a file

Get-Content .\instances.txt |
    .\Export-InstanceMetadata.ps1 `
        -OutputFolder "C:\SQL\Metadata" `
        -FileBaseName "SQLInventory" `
        -Verbose

This example shows the primary way to use the script. If you already manage your SQL Server list somewhere (CMS, text file, whatever), you can reuse that list here instead of editing the script every time.

---

Why it’s useful for senior DBAs

This script is mainly about giving you cheap, repeatable visibility:

• Baselines: Capture a snapshot before and after a change window and stick the CSVs in source control or with the CAB artefacts.

• Drift: Load the Config CSV into Power BI or Excel and you can spot weird max server memory, max degree of parallelism, or other oddball settings across the organization in a few clicks.

• Inventory Questions: “How many instances are still on 2016?” “Where are all the Developer Edition installs?” These become filters instead of ad‑hoc T‑SQL in random RDP sessions.

---

Import CSVs into one Excel workbook with separate tabs

There is no official Microsoft PowerShell module for Excel files, except for using the Excel COM Object. Therefore, I have used CSV files as the output format so far.

However, if you prefer, you can use PowerShell code to combine the 3 separate CSVs into a single Microsoft Excel workbook with tabs. This approach uses a PowerShell module called ImportExcel, which is specifically designed for working with Excel files. Although it is a third-party module and not an official Microsoft product, it is widely adopted and recommended by Microsoft for PowerShell automation.

Install-Module ImportExcel -Scope CurrentUser

# Import CSVs into one Excel workbook with separate tabs
Import-Csv "C:\Path\SQLInstanceMetadata_Config.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Config"
Import-Csv "C:\Path\SQLInstanceMetadata_Info.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Info"
Import-Csv "C:\Path\SQLInstanceMetadata_Settings.csv" | Export-Excel "C:\Path\Documents\SQLServerMetaData.xlsx" -WorksheetName "Settings"

---

See also

• SQL Server Metadata using Powershell
• Powershell script to get list of databases on a server
• Powershell one liner to export data directly from SQL Server to Excel
• GitHub repository script

Track SQL Server Configuration Changes Using the Error Log

If you work with SQL Server long enough, you or someone will eventually want to know, “Did anyone change that setting?” And if you cannot prove it one way or the other, well you might just be in luck. 

The good news is that SQL Server already tracks configuration changes in a few places. You just need to know where to look and what each option can (and cannot) do for you. 

The easiest option: standard reports

The simplest place to start is the SSMS GUI. Right‑click the server → Reports → Standard Reports, and look for the one that shows configuration history.

This standard report reads from SQL Server’s default trace, which being the "default" setting, would have been already running. That means you don't need to setup and enable it. It is just there ready to view, unless of course it was purposely disabled/stopped.

However, the default trace comes with an important limitation: it is small by design. It is typically about 20 MB total, spread across up to 5 rollover files. You cannot change that size, so older entries will eventually roll off as new activity fills the trace.

If you want your own custom tracking, Extended Events is usually a better choice than building a new trace. Microsoft has largely moved the ecosystem toward Extended Events, and that’s where you should focus any new work.

When you need better control: Use Auditing and Extended Events features

SQL Server Auditing and Extended Events can also capture configuration changes, but there is one big catch: you must set them up before the change happens.

If you only think about tracking after the fact, auditing and Extended Events won’t help you. Once a setting is changed, they cannot retroactively tell you when it happened (unless they were already recording).

That is why many DBAs still start with the default trace or the SQL Server error log. Those options are already there, and they can answer the question right now, even if you did not plan for it.

The SQL Server error log

Another powerful, often overlooked place to check is the SQL Server error log. Every time a configuration option is changed with sp_configure, SQL Server usually writes a message into the error log that includes the phrase “Configuration option”.

Because of that, the error log becomes a built‑in change history for key settings, as long as the log files are still present. You can search this log in several ways:

• In SSMS, by opening the SQL Server logs.

• In a text editor, by opening the .log files directly.

• Programmatically, using sys.xp_readerrorlog from T‑SQL.

The sys.xp_readerrorlog procedure is especially handy because it lets you search and filter the log without leaving SQL Server. Microsoft documents it here: sp-readerrorlog

Please note: You may notice that Microsoft documents sp_readerrorlog as the supported way to read SQL Server error logs, while this post uses sys.xp_readerrorlog instead. In practice, both procedures read the same underlying log files and return the same core information; the main difference is that sp_readerrorlog is a wrapper that first checks your permissions and then calls xp_readerrorlog under the hood. The supported version also accepts a slightly simpler parameter list, while xp_readerrorlog exposes extra parameters such as a start date, end date, and sort order, which makes it more flexible for ad‑hoc troubleshooting and filtered searches. Because of this extra flexibility, many DBAs, including examples in this blog post, continue to rely on xp_readerrorlog in day‑to‑day work, even though it remains an undocumented extended stored procedure.

---

Examples: 

Here is a basic example that searches the current SQL Server error log for configuration change:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This returns all entries in the current log file that contain Configuration option, ordered newest first.

If you want to narrow it by time, you can pass a start‑time filter:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    '2026-05-10 16:00:00', -- StartTime: start datetime filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

Now you only see configuration‑change lines from that specific point onward.

You can also search for a specific setting by adding a second text filter. For example, this looks for configuration messages that also contain the word Optimize:

USE master;
GO

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: primary text to search for
    N'Optimize', -- SearchString2: secondary text to search for
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

These patterns make it easy to quickly corner the exact change you are investigating.

What happens after a restart

Here is an important detail that trips people up: every time SQL Server restarts, it starts a new error log file. The previous file is renamed to errorlog.1, the one before that becomes errorlog.2, and so on.

By default, SQL Server keeps up to 7 of these archived log files, but you can configure it to keep more (up to 99). If needed, you can also manually rotate the log without restarting by running sp_cycle_errorlog. This is useful if you want to “start fresh” and still keep older logs around.

Because of this cycling behavior, older configuration changes may move into errorlog.1, errorlog.2, and so on. The good news is that sys.xp_readerrorlog can read those archived logs too. The bad news is that the oldest logs will eventually be removed unless you copy that data somewhere else.

So while the error log is a great tool for recent history, it is still not a permanent, long‑term auditing solution.

Usefulness beyond configuration changes

sys.xp_readerrorlog is not only useful for tracking configuration changes. You can also use it to search for errors, warnings, startup messages, and other events.

For example, say you want to check for severity 16 errors containing a specific error number over the last 24 hours:

USE master;
GO

DECLARE @start_time datetime = GETDATE() - 1;

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Severity: 16', -- SearchString1: primary text to search for
    N'41145',  -- SearchString2: secondary text to search for
    @start_time, -- StartTime: start datetime filter (yesterday)
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This kind of pattern is very handy when troubleshooting a server and you want to quickly focus on a specific kind of error.

---

The bottom Line:

Here is how I usually think about this in practice:

Need to do a quick check?    Use the Standard Reports in SSMS. They read from the default trace and give you an easy, visual way to see recent changes.

Need more detail, but don’t want to set up a new system?  Use sys.xp_readerrorlog against the SQL Server error log. It is fast, already available, and works great for recent configuration changes.

For long‑term auditing and compliance: Use the Extended Events or SQL Server Auditing. These tools require planning and setup, but they are the right choice if you need reliable, long‑lasting change history.

So if someone tells you that a setting was never touched, check the default trace first. If that has already rolled off, search the error log. Only then, if you need a permanent answer, invest in a more structured auditing solution.

---

See also

• Dry-run xp_delete_file Before Actually Deleting Files
• Powershell script to get list of databases on a server
• Powershell one liner to export data directly from SQL Server to Excel

Beware of Generic SQL Server License Keys (What to Use Instead)

I (think) realize that content here is probably more applicable to mid-to-large organizations than small enterprises or solo DBAs. Here's why:

• Regular compliance reporting to Microsoft (VLSC/EA) is standard for Enterprise Agreements
• They often utilize scanning tools like Flexera One, SAM etc. for core usage across VM farms
• Software Assurance and virtualization rights management across multiple hosts
• Audit preparedness with dedicated licensing specialists or consultants
• Focus on Always On clusters and high-VM-density scenarios
• Most importantly, they have the budget to cover these licensing costs, and they often pass them on to customers with a healthy markup!

---

Microsoft products, including SQL Server, provide publicly available generic keys for evaluation or trial use (these are different from the free evaluation versions offered through the official Microsoft Evaluation Center).. 

These generic keys never expire, that's the trap. They give you fully functional production SQL Server forever... without any licensing proof. Your cores run happily until audit day.

They're legit for testing, 100% illegal for production. Don't be the DBA who turns 'temporary' into 'audit nightmare.

Microsoft never publishes these exact keys in docs, but they do get leaked online through channels like evaluation ISOs, partner training, and OEM installers. Scammers harvest them and sell 'free trial' as 'lifetime production licenses.

Search Google for SQL Server Public License Keys

---

It can be very tempting to grab generic, public license keys you find online when you need to spin up a SQL instance fast. For example 2Q48Q-PB48J-DRCVN-GB844-X2H4Q for SQL Server 2022 works like a charm. Hey, we're legit and honest, we'll report those cores in our next VLSC submission. No big deal, right? 

These setup keys still install SQL Server perfectly. Something like HX782-X7RHN-BVHGT-8HB24-2KGXG for SQL Server 2025  floating around sketchy sites or message boards will activate your install, but they don't prove any core entitlements. It's just a public setup key. It'll get you running, but it leaves you non-compliant.

There are scam sites that sell these SQL Server licenses using public keys for as little as $5-$500 as "lifetime licenses." They work, until Microsoft audit compliance/telemetry (not same as SQL Server Telemetry - CEIP Service) flags them and your scanned core usage doesn't match any legitimate VLSC entitlements.

Even worse, some shady ISOs have these keys pre-baked. If you download and use that ISO, it auto-fills everything. Just check "I have Software Assurance," click Next... and dig your into even deeper hole..

Microsoft's compliances/telemetry picks these up, and when your scanned usage reports don't match legitimate license keys/entitlements from your Volume Licensing Service Center (VLSC), audits get messy. The right move is to login into the Microsoft M365 admin center , and downloading edition-specific keys tied directly to your EA. They come as a CSV with product IDs that align perfectly with what you're entitled to.

Ideal solution, is to do regular scanning plus VLSC reporting to get audit-proof. The free scanning Microsft MAP Toolkit days are gone (unsupported, but you can still download it from Microsoft site), so Flexera One (not cheap) or something similar is often being used for enterprise needs. But VLSC keys? That's your real compliance bedrock.

Bottom line: Never use generic keys on production servers.