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

by

Troubleshooting Guide: SQL Server Error 5140 – “Failed to lock credential object”

Why This Error Happens

This error typically occurs when SQL Server 2025 cannot acquire an internal lock on a credential object, often due to concurrent access attempts from multiple sessions trying to modify or use the same credential simultaneously. It can also stem from a corrupted credential entry in the system catalog (sys.credentials) or insufficient permissions on the Windows Credential Manager/DPAPI store used by the SQL Server service account. In some cases, this is triggered when a Managed Identity or proxy account credential becomes orphaned after a service account password change or domain policy update.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Credential and Active Sessions

  • Run SELECT * FROM sys.credentials; to list all credential objects and locate the one referenced in the error (the %.*ls placeholder in the message).
  • Query sys.dm_tran_locks to check for blocking sessions holding locks on credential-related resources.
  • Use KILL <session_id> cautiously if a stale session is holding the lock indefinitely.

Step 2: Restart the SQL Server Service to Clear Stale Locks

  • Open Services.msc on Windows Server 2025 and locate SQL Server (MSSQLSERVER) or the named instance.
  • Right-click and select Restart (ensure this is done during a maintenance window).
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
  • This clears any in-memory lock table corruption tied to the credential object.

Step 3: Drop and Recreate the Corrupted Credential

  • Script out the existing credential identity using sp_helpcredential before deletion.
  • Drop the credential: DROP CREDENTIAL [CredentialName];
  • Recreate it with the correct identity and secret:
    CREATE CREDENTIAL [CredentialName] WITH IDENTITY = 'DomainOrAccount', SECRET = 'StrongPassword';
  • Re-link the credential to any associated logins using ALTER LOGIN [LoginName] WITH CREDENTIAL = [CredentialName];

Step 4: Verify Windows-Level Permissions and DPAPI Access

  • Open Local Security Policy (secpol.msc) and confirm the SQL Server service account has the “Log on as a service” right.
  • Check that the service account profile under C:\Users\<ServiceAccount>\AppData\Roaming\Microsoft\Protect is not corrupted or missing (DPAPI master key storage).
  • If using Group Managed Service Accounts (gMSA), run Test-ADServiceAccount <gMSAName> in PowerShell to confirm connectivity to Active Directory.

Alternative or Verification Steps

  • To verify the fix, re-run the operation that triggered Error 5140 (e.g., executing a job or query referencing the credential) and check the SQL Server Error Log for recurrence.
  • As an alternative, if the issue persists, restore the master database from a known good backup taken before the credential corruption occurred, since credential objects are stored at the instance level in master.