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

by

Microsoft SQL Server 2025 Troubleshooting Guide: Error 2764

Why This Error Happens

Error 2764 occurs when the sp_setapprole system stored procedure is executed with a password that does not match the password stored for the specified application role. This is typically caused by case-sensitivity mismatches (if the database collation is case-sensitive), trailing whitespace in connection strings, or the password being encrypted with a different algorithm/key than expected (e.g., ODBC encryption being passed to a client that doesn’t support it). It can also occur if the application role password was recently changed via sp_approlepassword but the connection string or application cache still holds the old credential.

Step-by-Step Resolution Guide

Step 1: Verify the Exact Password and Role Name

  • Open SQL Server Management Studio (SSMS) and connect to the instance via a sysadmin account.
  • Run SELECT name FROM sys.database_principals WHERE type = 'A'; to confirm the exact role name and check for typos.
  • Check the application’s configuration file (e.g., web.config or appsettings.json) for hidden spaces or incorrect encoding in the password string.

Step 2: Reset the Application Role Password

  • If the password is suspected to be corrupted or forgotten, reset it using a sysadmin login.
  • Execute the following T-SQL command in a query window:
  EXEC sp_approlepassword 'YourAppRoleName', 'NewStrongPassword123!';
  • Update the connection string in the application’s source code or configuration file to match this new value exactly.

Step 3: Check Encryption Style Compatibility

  • Determine if the client driver supports the ODBC encryption style required by sp_setapprole.
  • If using a legacy driver on Windows Server 2025, update the connection to use the none encryption style for testing purposes:
  EXEC sp_setapprole 'YourAppRoleName', 'YourPassword', 'none';
  • Ensure the SQL Server Native Client or Microsoft ODBC Driver 18+ is installed on the Windows 11 client machine to support modern encryption standards.

Step 4: Audit for Password Rotation Scripts

  • Check if a scheduled SQL Server Agent Job or external script is automatically rotating this password via sp_approlepassword.
  • Navigate to SQL Server Agent > Jobs in SSMS and review the job history for any “Success” status that correlates with the timestamp of the error.
  • Disable the job temporarily to rule out a race condition between the password change and the application’s login attempt.

Alternative or Verification Steps

  • Verification: Run the sp_setapprole command manually in a new SSMS query window using the exact credentials from the application’s config file; a successful execution confirms the password is correct and the issue lies within the application’s connection pooling or caching mechanism.
  • Alternative: If the application role is no longer required or is legacy, consider migrating to a more modern permission model using Windows Authentication groups or CERTIFICATE-based signed modules to bypass the need for an application role password entirely.