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

by

Troubleshooting Guide: SQL Server Error 3814

Why This Error Happens

Error 3814 occurs when SQL Server’s system catalog contains a remote login mapping (sys.remote_logins) that points to a local login which no longer exists or has become mismatched, typically due to a login being dropped and recreated without updating the corresponding linked server/remote login mapping. This commonly surfaces during an upgrade to SQL Server 2025 because the upgrade process validates all remote login mappings before proceeding, and it will halt if it finds orphaned or inconsistent entries. The root cause is almost always metadata corruption or drift between sys.server_principals and sys.remote_logins left over from a prior server migration, restore, or manual login management.

Step-by-Step Resolution Guide

Step 1: Identify the Broken Remote Login Mapping

  • Connect to the SQL Server instance using SQL Server Management Studio (SSMS) or sqlcmd with administrative credentials.
  • Run the following query to locate the problematic mapping:
  SELECT rl.remote_name, rl.local_principal_id, sp.name AS local_login_name, s.name AS server_name
  FROM sys.remote_logins rl
  LEFT JOIN sys.server_principals sp ON rl.local_principal_id = sp.principal_id
  JOIN sys.servers s ON rl.server_id = s.server_id;
  • Note any rows where local_login_name returns NULL — this indicates the orphaned mapping causing Error 3814.

Step 2: Drop the Invalid Remote Login

  • Using the remote_name and server_name values identified in Step 1, drop the broken mapping:
  EXEC sp_droplogin 'remote_name';

or, if it is tied to a linked server, use:

  EXEC sp_dropremotelogin @remoteserver = 'server_name', @loginame = 'remote_name';
  • If the command fails due to dependency locks, restart the SQL Server service via services.msc on Windows Server 2025 and retry.

Step 3: Recreate the Remote Login Correctly

  • Create a fresh local login first (if it does not already exist):
  CREATE LOGIN [remote_name] WITH PASSWORD = 'StrongPassword123!';
  • Re-map it to the remote server using:
  EXEC sp_addremotelogin @remoteserver = 'server_name', @loginame = 'remote_name', @remotename = 'remote_name';
  • Confirm the mapping was created cleanly by re-running the diagnostic query from Step 1.

Step 4: Re-run the SQL Server 2025 Upgrade

  • Launch SQL Server Setup (setup.exe) from the installation media or the Microsoft SQL Server Installation Center.
  • Select Upgrade and proceed through the wizard; the pre-upgrade validation check should now pass without triggering Error 3814.
  • Monitor the setup log located at C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\ for confirmation that the remote login check succeeded.

Alternative or Verification Steps

  • After the upgrade completes, run SELECT * FROM sys.remote_logins; again to confirm no orphaned entries remain and that all local_principal_id values resolve to valid logins in sys.server_principals.
  • If the error persists, use sp_helpremotelogin to audit all remote login mappings server-wide, and consider scripting out and dropping/recreating the entire linked server object via sp_dropserver and sp_addlinkedserver as a full reset.