How to fix Error 5874 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 5874 is specific to the new Contained Availability Group (CAG) feature introduced in SQL Server 2025. A Contained Availability Group packages instance-level metadata (logins, server-level configuration, linked servers, etc.) inside the availability group itself so it can seamlessly fail over between replicas. Because of this containment model, a session that connects through the CAG’s listener or routing endpoint is scoped to that contained context — not to the physical SQL Server instance — and server-level configuration changes (such as sp_configure/RECONFIGURE operations) are blocked from that scope. To change server-level settings, you must connect directly to the underlying SQL Server instance rather than through the Contained Availability Group connection point.

Step-by-Step Resolution Guide

Step 1: Identify the Current Connection Context

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio on Windows Server 2025 or Windows 11.
  • Run SELECT @@SERVERNAME AS InstanceName, DB_NAME() AS CurrentDatabase; to see what you are actually connected to.
  • Run SELECT is_contained, name FROM sys.availability_groups; on the replica to confirm whether the session is routed through a Contained Availability Group listener.
  • If the server name returned matches the CAG listener name (not the physical instance/network name), you are in the wrong scope for this operation.

Step 2: Reconnect at the SQL Server Instance Level

  • Disconnect the current query session in SSMS (right-click the connection in Object Explorer and select Disconnect).
  • Open a new connection using the physical instance name instead of the listener name:
  • Default instance: ServerName
  • Named instance: ServerName\InstanceName
  • From the command line, you can validate this directly with sqlcmd:
  sqlcmd -S ServerName\InstanceName -E -Q "SELECT @@SERVERNAME"
  • Confirm the result returns the physical instance name, not the Contained Availability Group listener name or routing URL.

Step 3: Verify Your Permissions for Server-Level Changes

  • Confirm the login has the ALTER SETTINGS server-level permission (held implicitly by members of the sysadmin and serveradmin fixed server roles):
  SELECT permission_name, state_desc
  FROM sys.server_permissions sp
  JOIN sys.server_principals pr ON sp.grantee_principal_id = pr.principal_id
  WHERE pr.name = 'DOMAIN\YourLogin';
  • If the permission is missing, grant it while connected directly to the instance:
  USE master;
  GRANT ALTER SETTINGS TO [DOMAIN\YourLogin];

Step 4: Retry the Server Configuration Operation

  • From the instance-level connection established in Step 2, re-run the original configuration change, for example:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory (MB)', 8192;
  RECONFIGURE;
  • Replace the option name and value with the actual setting you were originally trying to change.
  • Watch the Messages pane for any new errors (e.g., permission or value-range errors) and resolve those before proceeding.

Step 5: Restore the Connection to Your Standard Context

  • Once the configuration change succeeds, close the instance-level session.
  • Reconnect through your normal application or client workflow (including the Contained Availability Group listener, if that is how your application normally connects) to resume standard operations.

Alternative or Verification Steps

Step 6: Verify the Change

  • From the instance-level connection, confirm the new value is active:
  SELECT name, value, value_in_use
  FROM sys.configurations
  WHERE name = 'max server memory (MB)';
  • value and value_in_use should both reflect the updated setting after RECONFIGURE has run.

Step 7: Check the Error Log if the Issue Persists

  • If Error 5874 continues to appear, review the SQL Server error log for related entries:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(Adjust MSSQL17.MSSQLSERVER to match your actual instance ID if using a named instance.)

  • You can also view this through SSMS under Management > SQL Server Logs > Current, or via PowerShell:
  Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 50
  • Double-check your application’s connection string or DSN to ensure it is not silently routing through the Contained Availability Group’s listener name or read-write routing URL, which would reproduce the same scoping issue.