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

by

Why This Error Happens

This warning occurs because a database has been marked as “contained” (CONTAINMENT = PARTIAL), but the server-level configuration option contained database authentication is still disabled (set to 0). SQL Server 2025 restricts the creation of users with passwords inside contained databases unless this server-wide flag is explicitly enabled, as it introduces a security boundary that bypasses the traditional login-mapping model. Essentially, the database schema supports containment, but the instance-level security gate has not been opened to allow it.

Step-by-Step Resolution Guide

Step 1: Verify the Current Server Configuration

  • Open SQL Server Management Studio (SSMS) or a query tool connected to the instance.
  • Run the following T-SQL command to check the current state of the configuration:
EXEC sp_configure 'contained database authentication';
  • Look at the run_value column. If it returns 0, this confirms the root cause.

Step 2: Enable Contained Database Authentication

  • Execute the following commands to enable the “advanced options” view and turn on the specific setting:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO
  • Note: Unlike many sp_configure changes, this specific setting does not require a service restart; it takes effect immediately upon reconfiguration.

Step 3: Validate the Database-Specific Containment Setting

  • Ensure the specific database you are working with is actually set to PARTIAL containment (required for contained users).
  • Check the setting via the GUI: Right-click the Database -> Properties -> Options -> Verify “Containment type” is set to Partial.
  • Alternatively, verify via query:
SELECT name, containment, containment_desc 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

Step 4: Create or Re-Map the Contained User

  • If the user previously failed to create due to the error, re-attempt creating the contained user with a password directly in the context of the specific database (not master):
USE YourDatabaseName;
GO
CREATE USER ContainedUser WITH PASSWORD = 'StrongP@ssw0rd!';

Alternative or Verification Steps

  • Verification: Attempt to log in using the newly created contained user credentials via a new connection in SSMS, selecting the specific database name in the “Connect to Database” field under Connection Properties. If the login succeeds without being prompted for a Windows Login mapping, the fix is successful.
  • Alternative: If enterprise security policies strictly prohibit enabling contained database authentication at the server level (due to compliance/hardening standards), do not override this setting. Instead, migrate the strategy to use traditional SQL Logins mapped to Windows Server 2025 Active Directory accounts or standard SQL Server logins mapped at the instance level.