Troubleshooting Guide: SQL Server Error 13214 – Destination Certificate Issuer Name Mismatch

Why This Error Happens

Error 13214 occurs during Database Mirroring or Always On Availability Group endpoint authentication when the certificate presented by the destination (partner) server does not match the issuer name expected by the initiating server. This typically happens because the certificates on each SQL Server instance were generated independently rather than from a shared trusted Certificate Authority (CA), or because the certificate was recreated/renewed on one node without updating the corresponding public key on the partner node. It can also occur if the system clock between Windows Server 2025 nodes has drifted, causing certificate validity checks to fail.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Details on Both Instances

• Open SQL Server Management Studio (SSMS) on both the principal and mirror/secondary instances.
• Run the following query on each instance to inspect the certificate issuer and expiration:

  SELECT name, subject, issuer_name, start_date, expiry_date 
  FROM sys.certificates;

• Confirm that the issuer_name and subject fields align with what each endpoint expects from its partner.

Step 2: Re-Export and Re-Import the Correct Public Key Certificates

• On the source server, back up the certificate used for the mirroring endpoint:

  BACKUP CERTIFICATE Mirror_Cert
  TO FILE = 'C:\Certs\Mirror_Cert.cer';

• Copy the .cer file securely to the partner server (e.g., via a shared folder or robocopy).
• On the destination server, drop the outdated certificate and recreate it using the correct file:

  CREATE CERTIFICATE Mirror_Cert_Partner
  FROM FILE = 'C:\Certs\Mirror_Cert.cer';

Step 3: Synchronize System Time Across Nodes

• Open PowerShell as Administrator on each Windows Server 2025 node.
• Run w32tm /resync to force time synchronization with the domain controller or NTP source.
• Confirm both servers are within a few seconds of each other using w32tm /stripchart /computer:<PartnerServerName>.

Step 4: Recreate the Mirroring/AG Endpoint Authentication

• Drop and recreate the database mirroring endpoint on the affected instance:

  DROP ENDPOINT Mirroring_Endpoint;
  CREATE ENDPOINT Mirroring_Endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022)
  FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE Mirror_Cert_Partner, ROLE = ALL);

• Restart the SQL Server service on both nodes to reload the endpoint security context.

Step 5: Validate Trust Chain in Windows Certificate Store

• Open certlm.msc on both servers and check the Trusted Root Certification Authorities store.
• Ensure both SQL Server service accounts have read access to the certificate’s private key via Manage Private Keys.

Alternative or Verification Steps

• Re-attempt the mirroring session or Availability Group join operation and check the SQL Server error log for successful endpoint authentication (absence of Error 13214).
• If the issue persists, use netsh advfirewall to confirm port 5022 (or your custom mirroring port) is not blocked, as firewall interference can sometimes produce a secondary certificate handshake failure that mimics this issuer name error.

Troubleshooting Guide: SQL Server Error 13215 – Destination Certificate Serial Number Mismatch

Why This Error Happens

Error 13215 occurs during Service Broker or Database Mirroring/Always On endpoint authentication when the certificate presented by the destination server does not match the serial number SQL Server expected based on its local metadata cache. This typically happens after a certificate has been rotated, renewed, or re-issued on the destination instance, but the initiating server still references the old certificate’s serial number stored in sys.certificates or sys.endpoints. It can also occur if the certificate was restored from a backup/export process that altered its serial number metadata, or if DNS/endpoint mapping is pointing to the wrong physical instance after a failover.

Step-by-Step Resolution Guide

Step 1: Verify the Current Certificate Serial Numbers on Both Endpoints

• Run the following on both the source and destination SQL Server 2025 instances to compare values:

  SELECT name, serial_number, thumbprint, expiry_date 
  FROM sys.certificates;

• Confirm that the serial_number on the destination matches exactly what the source expects in sys.endpoints (via master.sys.endpoints and crypt_properties).
• Note any mismatch — this confirms a stale or rotated certificate is the root cause.

Step 2: Re-Export and Re-Import the Correct Certificate

• On the destination server, export the current authentication certificate using:

  BACKUP CERTIFICATE [Cert_Name] TO FILE = 'C:\Certs\DestCert.cer';

• Copy DestCert.cer to the source server via a secure path (avoid shared folders with inherited ACL issues).
• On the source server, drop the outdated certificate reference and recreate it:

  DROP CERTIFICATE [Old_Cert_Name];
  CREATE CERTIFICATE [Cert_Name] 
  FROM FILE = 'C:\Certs\DestCert.cer';

• Ensure the certificate START_DATE and EXPIRY_DATE are valid and not expired.

Step 3: Drop and Recreate the Affected Endpoint

• If the serial number mismatch persists after re-importing, the endpoint metadata itself may be cached incorrectly:

  DROP ENDPOINT [Endpoint_Name];
  CREATE ENDPOINT [Endpoint_Name]
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022)
  FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE [Cert_Name],
      ENCRYPTION = REQUIRED ALGORITHM AES,
      ROLE = ALL
  );

• Restart the SQL Server service on the destination instance via Services.msc (Windows Server 2025) or Restart-Service MSSQLSERVER in PowerShell to force endpoint re-initialization.

Step 4: Validate Windows Firewall and Certificate Store Permissions

• Confirm port 5022 (or your custom mirroring port) is not blocked:

  Test-NetConnection -ComputerName <DestinationServer> -Port 5022

• Verify the SQL Server service account has read access to the certificate in the local certificate store (certlm.msc → Personal → Certificates → Manage Private Keys).

Alternative or Verification Steps

• After completing the steps above, re-run the mirroring/Service Broker session and check sys.dm_exec_connections or the SQL Server Error Log for successful endpoint handshake without Error 13215 reappearing.
• If the issue persists, as a fallback, regenerate a brand-new self-signed certificate on both instances with synchronized expiry dates, then re-establish the endpoint relationship from scratch rather than attempting to reuse the old certificate chain.

Why This Error Happens

Error 13213, referencing the “source certificate serial number,” occurs when SQL Server 2025 attempts to validate a certificate used for Transparent Data Encryption (TDE) or a Database Mirroring/Always On endpoint, but the certificate’s serial number on the destination server does not match what the source server expects. This typically happens because the certificate was not exported and restored correctly between instances, the private key file is missing or corrupted, or a certificate was regenerated on one server without updating the corresponding server(s). As a result, SQL Server cannot decrypt the database or complete endpoint authentication because the trust chain is broken.

Step-by-Step Resolution Guide

Step 1: Identify the Certificate in Use

• Run the following query on the source instance to find the certificate backing the encrypted database or endpoint:
• SELECT name, certificate_id, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates;
• Cross-reference the thumbprint/serial number with the one referenced in the SQL Server Error Log entry for Error 13213.

Step 2: Re-Export the Certificate and Private Key

• On the source server, back up the certificate along with its private key using:
• BACKUP CERTIFICATE YourCertName TO FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!');
• Copy both the .cer and .pvk files securely to the destination Windows Server 2025 / Windows 11 machine (avoid using outdated or cached copies from previous exports).

Step 3: Restore the Certificate on the Destination Instance

• Drop the mismatched certificate if it already exists:
• DROP CERTIFICATE YourCertName;
• Re-create it from the exported files:
• CREATE CERTIFICATE YourCertName FROM FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', DECRYPTION BY PASSWORD = 'StrongPassword123!');
• Ensure the database master key is open in the current session before running this (OPEN MASTER KEY DECRYPTION BY PASSWORD = '...').

Step 4: Validate Endpoint or Database Encryption State

• If the error relates to a Database Mirroring/Always On endpoint, verify both partner servers reference the matching certificate thumbprint:
• SELECT name, certificate_id FROM sys.database_mirroring_endpoints;
• If it relates to TDE, confirm the Database Encryption Key (DEK) on the restored database references the newly restored certificate, not an orphaned one.

Step 5: Restart SQL Server Services

• Restart the SQL Server (MSSQLSERVER) and SQL Server Agent services via services.msc or:
• Restart-Service -Name 'MSSQLSERVER' -Force
• This clears cached certificate handles and forces SQL Server to reload the corrected certificate chain.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_database_encryption_keys; to confirm encryption_state = 3 (encrypted, no errors) and that no “Decryption” or “PendingAcquireCertificate” status remains.
• If the error persists, check the Windows Event Viewer (Application log) for related certificate store errors and confirm the SQL Server service account has read permissions on the certificate file location under C:\Certs\.

Troubleshooting Guide: SQL Server Error 13211 — Key Exchange Key Issue

Why This Error Happens

Error 13211 in Microsoft SQL Server 2025 typically occurs when the Key Exchange Key (KEK) used for Extensible Key Management (EKM) or Always Encrypted column master key operations cannot be located, accessed, or validated. This usually stems from a misconfigured or missing EKM provider, an inaccessible Hardware Security Module (HSM)/Azure Key Vault endpoint, or insufficient permissions on the certificate/key used to protect the Database Encryption Key (DEK). It can also surface after a server migration (e.g., moving from an older OS to Windows Server 2025) where the key’s cryptographic provider binding was not re-established correctly.

Step-by-Step Resolution Guide

Step 1: Verify the EKM Provider and Cryptographic Provider Binding

• Run SELECT * FROM sys.cryptographic_providers; in SQL Server Management Studio to confirm the provider DLL is registered and enabled.
• Check that the provider’s .dll file still exists in the expected path (commonly under C:\Program Files\<Vendor>\EKM\) and has not been removed or corrupted during an OS upgrade to Windows Server 2025.
• Ensure EKM_PROVIDER is enabled via sp_configure 'EKM provider enabled', 1; followed by RECONFIGURE;.

Step 2: Validate Key Vault / HSM Connectivity and Credentials

• If using Azure Key Vault, confirm the SQL Server service account has an active, non-expired Service Principal credential via CREATE CREDENTIAL or ALTER CREDENTIAL.
• Test outbound network connectivity from the Windows Server 2025 host to the vault endpoint (port 443) using Test-NetConnection <vault-name>.vault.azure.net -Port 443 in PowerShell.
• Check Windows Firewall rules and any proxy settings that may block TLS handshakes to the external key management service.

Step 3: Re-Associate the Key Exchange Key with the Encryption Key

• Open a query window and run OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'; to confirm the local master key still opens correctly.
• If the KEK itself is missing, re-import it using CREATE ASYMMETRIC KEY referencing the correct provider key name, ensuring the PROVIDER_KEY_NAME matches exactly what is stored in the HSM/vault.
• Restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) to force the engine to reload the cryptographic provider cache.

Step 4: Check Permissions on the Certificate and Service Account

• Confirm the SQL Server service account has “CONTROL” permission on the associated certificate or asymmetric key via GRANT CONTROL ON CERTIFICATE::<CertName> TO [ServiceAccount];.
• On Windows 11 (for developer/test instances), verify the account also has read access to the certificate’s private key file under C:\ProgramData\Microsoft\Crypto\Keys\.

Alternative or Verification Steps

• To verify the fix, re-run the operation that triggered Error 13211 (e.g., backup with encryption, or an Always Encrypted query) and confirm it completes without throwing the key exchange error.
• As an alternative, if the original KEK is unrecoverable, drop and recreate the Database Encryption Key using a new Column Master Key, then re-encrypt affected columns or databases using ALTER DATABASE ENCRYPTION KEY.

Troubleshooting Guide: Error 13212 — Source Certificate Issuer Name Mismatch

Why This Error Happens

Error 13212 occurs when SQL Server 2025’s Service Broker or Database Mirroring/Always On endpoint cannot validate the issuer name embedded in the source certificate against the expected Certificate Authority (CA) chain on the remote instance. This typically happens because the certificate was generated with an incorrect AUTHORITY clause, has expired, or the issuing CA’s root certificate is missing from the Windows Server 2025 Trusted Root store. It can also surface when certificates are copied between instances without properly re-establishing the public key binding on the destination server.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Issuer Name and Expiration

• Run the following query on the source instance to inspect certificate metadata:
• SELECT name, issuer_name, expiry_date, start_date FROM sys.certificates;
• Confirm the issuer_name matches exactly what the destination server expects (case-sensitive, no trailing whitespace).
• Check that expiry_date has not passed — expired certificates will always trigger 13212 regardless of other settings.

Step 2: Re-Export and Re-Import the Certificate Correctly

• On the source server, back up the certificate with both the public key and private key:
• BACKUP CERTIFICATE YourCertName TO FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', ENCRYPTION BY PASSWORD = 'StrongP@ss1');
• Transfer both files securely to the destination server (avoid network shares with altered ACLs).
• On the destination, create the certificate using the exact same AUTHORIZATION and issuer parameters:
• CREATE CERTIFICATE YourCertName AUTHORIZATION [dbo] FROM FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', DECRYPTION BY PASSWORD = 'StrongP@ss1');

Step 3: Validate the Windows Certificate Store Chain

• Open certlm.msc on both Windows Server 2025 machines.
• Navigate to Trusted Root Certification Authorities > Certificates and confirm the issuing CA is present and valid.
• If using an internal PKI, re-import the Root CA chain via:
• certutil -addstore "Root" C:\Certs\RootCA.cer
• Restart the SQL Server service to force endpoint re-validation:
• net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Recreate the Service Broker/Mirroring Endpoint

• Drop and recreate the affected endpoint referencing the corrected certificate:
• DROP ENDPOINT Endpoint_Mirroring;
• CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE YourCertName, ENCRYPTION = REQUIRED ALGORITHM AES);
• Ensure port 5022 (or your custom mirroring port) is not blocked by Windows Defender Firewall.

Alternative or Verification Steps

• Confirm resolution by querying sys.dm_exec_connections on both instances to verify the session establishes without encrypt_option errors.
• If the issue persists, generate a fresh self-signed certificate on both ends with matching START_DATE/EXPIRY_DATE values as a clean-slate alternative, bypassing any corrupted CA chain dependencies.

Troubleshooting Guide: Error 13209 “KEY SALT” in Microsoft SQL Server 2025

Why This Error Happens

Error 13209 related to “KEY SALT” typically occurs when SQL Server 2025’s encryption subsystem encounters a mismatch or corruption in the cryptographic salt value used for column-level or Always Encrypted data protection. This often happens after a certificate rotation, a failed key regeneration process, or when the master key hierarchy becomes desynchronized between the database and the Windows Certificate Store. In some cases, this is triggered by an incomplete patch installation on Windows Server 2025 that left encryption provider DLLs in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Verify the Database Master Key and Certificate Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword'; to confirm the key opens without error.
• Check certificate validity with:

  SELECT name, pvt_key_encryption_type_desc, thumbprint 
  FROM sys.certificates;

• If the thumbprint is null or mismatched, the certificate backup may be corrupted.

Step 2: Regenerate the Column Encryption Key Salt

• Navigate to Object Explorer > Security > Always Encrypted Keys in SSMS.
• Right-click the affected Column Master Key and select Rotate to force a new salt generation.
• Alternatively, run the PowerShell cmdlet:

  Invoke-SqlColumnMasterKeyRotation -InputObject $cmk -NewColumnMasterKeyName "CMK_New"

• Ensure the new key is re-associated with all dependent Column Encryption Keys (CEKs).

Step 3: Reset the Windows Certificate Store Cache

• On Windows Server 2025 / Windows 11, open certlm.msc (Local Machine Certificate Store).
• Navigate to Personal > Certificates and confirm the SQL Server service account has read access to the private key.
• Right-click the certificate > All Tasks > Manage Private Keys and grant Read permission to NT SERVICE\MSSQLSERVER.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Clear Corrupted Encryption Provider Cache

• Navigate to the installation path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Locate and rename the EncryptionProviderCache.dat file (if present) to .bak.
• Restart the SQL Server service to force regeneration of the cache file.

Alternative or Verification Steps

• Verify the fix by re-running the query or application operation that originally triggered Error 13209; confirm no exception is logged in SQL Server Error Log (ERRORLOG file in the Log subfolder).
• If the error persists, restore the master key and certificates from a known-good backup using RESTORE MASTER KEY and CREATE CERTIFICATE ... FROM FILE, then re-run Step 2 to regenerate the salt.

Troubleshooting Guide: SQL Server Error 13210 (SESSION KEY)

Why This Error Happens

Error 13210 in Microsoft SQL Server 2025 typically occurs when the Service Master Key or Database Master Key used to encrypt the session key becomes inaccessible, corrupted, or mismatched with the Data Protection API (DPAPI) credentials stored on the host operating system. This commonly happens after a server migration, a Windows credential store reset, or when the SQL Server service account lacks the necessary permissions to decrypt the key stored in sys.symmetric_keys. Additionally, mismatched certificate thumbprints between the primary and mirrored/replica instances can trigger this error during Always On or Database Mirroring session negotiations.

Step-by-Step Resolution Guide

Step 1: Verify Service Master Key Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check key status: SELECT * FROM sys.symmetric_keys;
• If the key is missing or flagged as corrupted, attempt a regeneration using ALTER SERVICE MASTER KEY REGENERATE; (note: this requires re-encrypting all dependent keys).

Step 2: Restore the Master Key from Backup

• Navigate to your secure backup location (e.g., D:\SQLBackups\Keys\) where the Service Master Key backup file (.key) is stored.
• Execute the restore command: RESTORE SERVICE MASTER KEY FROM FILE = 'D:\SQLBackups\Keys\service_master_key_backup.key' DECRYPTION BY PASSWORD = 'YourStrongPassword123';
• Restart the SQL Server service via services.msc or by running net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Validate Service Account Permissions

• Open Computer Management > Local Users and Groups on Windows Server 2025 and confirm the SQL Server service account is a member of the required local security groups.
• Check that the account has “Log on as a service” rights via secpol.msc under Local Policies > User Rights Assignment.
• Ensure the DPAPI user profile folder (C:\Users\<ServiceAccount>\AppData\Roaming\Microsoft\Protect\) exists and is not corrupted; if missing, recreate the profile by logging in manually once with that account.

Step 4: Re-sync Certificates for Always On/Mirroring Sessions

• On both the primary and secondary replicas, run SELECT name, thumbprint FROM sys.certificates; to compare thumbprints.
• If mismatched, re-export the certificate from the primary using BACKUP CERTIFICATE and import it on the secondary using CREATE CERTIFICATE ... FROM FILE.
• Restart the Always On Availability Group listener service to force a fresh session key exchange.

Alternative or Verification Steps

• To verify the fix, check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for the absence of Error 13210 after a service restart and attempt a fresh connection/session handshake.
• If the error persists, run sp_control_dbmasterkey_password to detach and reattach the Database Master Key password, forcing SQL Server to regenerate the session key from a clean state.

Why This Error Happens

Error 13207 is raised by SQL Server’s Service Broker engine when a message sent through SEND ON CONVERSATION does not match the validation schema defined for its associated MESSAGE TYPE. This typically occurs when the message type was created with VALIDATION = WELL_FORMED_XML or VALIDATION = VALID_XML WITH SCHEMA COLLECTION, but the application sends a plain string, NULL, or malformed XML payload. It can also surface after an in-place upgrade to SQL Server 2025 if a schema collection referenced by the message type was dropped, renamed, or became inaccessible due to permission changes on Windows Server 2025/Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Message Type and Conversation

• Open SQL Server Management Studio (SSMS) and run:

  SELECT * FROM sys.transmission_queue WHERE service_name = 'YourServiceName';

• Check the transmission_status column for the exact error text referencing MESSAGE TYPE.
• Cross-reference with sys.service_message_types to confirm the validation column value (NONE, EMPTY, WELL_FORMED_XML, VALID_XML WITH SCHEMA COLLECTION).

Step 2: Validate the XML Schema Collection

• Run the following to confirm the schema collection still exists and is bound correctly:

  SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollectionName';

• If missing, recreate it using CREATE XML SCHEMA COLLECTION with the original .xsd definition stored in your source control or deployment scripts.
• Re-bind the message type if needed:

  ALTER MESSAGE TYPE [YourMessageType]
  VALIDATION = VALID_XML WITH SCHEMA COLLECTION [YourSchemaCollectionName];

Step 3: Correct the Application Payload

• Inspect the application or stored procedure code generating the SEND statement.
• Ensure the payload is properly serialized as XML (if required) and not sent as NVARCHAR with unescaped characters.
• Test manually in SSMS:

  BEGIN DIALOG CONVERSATION @Handle
      FROM SERVICE [InitiatorService]
      TO SERVICE 'TargetService'
      ON CONTRACT [YourContract];

  SEND ON CONVERSATION @Handle
      MESSAGE TYPE [YourMessageType]
      ('<Root><Test>Data</Test></Root>');

Step 4: Check Windows-Level Permissions and Service Account Access

• On Windows Server 2025, verify the SQL Server service account has read access to any external resource (file share, certificate store) used for schema validation or encryption.
• Open Services.msc → confirm the SQL Server (MSSQLSERVER) service is running under the correct domain/service account without “Access Denied” entries in Event Viewer → Windows Logs → Application.

Step 5: Restart Service Broker and Clear Poisoned Messages

• If messages are marked “poisoned,” disable and re-enable Broker on the database:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

• Clear the transmission queue of stuck messages if safe to do so, then resend.

Alternative or Verification Steps

• Re-run the SEND ON CONVERSATION test script from Step 3 — if no error returns and sys.transmission_queue shows zero rows for that conversation, the fix is confirmed.
• If the error persists, bypass strict validation temporarily by setting VALIDATION = NONE on a test message type to isolate whether the issue is schema-related or payload-related, then revert once root cause is confirmed.

Troubleshooting Guide: Error 13208 — Message Integrity Check Failure

Why This Error Happens

Error 13208 in SQL Server 2025 typically occurs when the Database Mirroring/Always On Availability Groups endpoint detects that a network packet’s integrity hash does not match the expected value, indicating the data was altered or corrupted in transit. This is most often caused by a faulty network adapter, a misconfigured firewall or network interface performing deep packet inspection, or TCP Chimney Offload/RSS settings corrupting packet headers between replicas. It can also surface when there is a certificate mismatch or an outdated network driver on Windows Server 2025 that mishandles encrypted mirroring traffic.

Step-by-Step Resolution Guide

Step 1: Verify Network Adapter and Offload Settings

• Open PowerShell as Administrator and run Get-NetAdapterAdvancedProperty -Name "*" | Where-Object {$_.DisplayName -like "*Offload*"}.
• Disable TCP Chimney Offload and Checksum Offload on the adapter used for mirroring traffic: Set-NetAdapterAdvancedProperty -Name "Ethernet" -DisplayName "TCP Checksum Offload (IPv4)" -DisplayValue "Disabled".
• Reboot the node after changes to ensure the NIC driver reloads cleanly.

Step 2: Update Network Drivers and NIC Firmware

• Check Device Manager > Network Adapters for any driver marked outdated or with warning icons.
• Download the latest certified driver for Windows Server 2025 from the manufacturer (Intel, Broadcom, Mellanox) rather than relying on Windows Update.
• Run pnputil /enum-drivers to confirm the new driver version is installed after applying it.

Step 3: Inspect and Rebuild the Mirroring Endpoint

• In SQL Server Management Studio (SSMS), run the following to check the endpoint state:

SELECT name, protocol_desc, state_desc, is_encryption_enabled 
FROM sys.database_mirroring_endpoints;

• If encryption settings appear mismatched between the primary and secondary replicas, drop and recreate the endpoint:

DROP ENDPOINT [Mirroring];
CREATE ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES);

• Ensure the certificate thumbprint used on both nodes (if using certificate-based authentication) matches exactly via sys.certificates.

Step 4: Check Firewall and Intermediate Network Devices

• Confirm that no intrusion detection/prevention system (IDS/IPS) or WAN optimizer sits between the SQL Server nodes, as these commonly corrupt TDS packet payloads.
• On Windows Firewall, verify port 5022 (or your custom mirroring port) is allowed without packet inspection: New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow.
• If using a virtualized environment (Hyper-V), disable Virtual Machine Queue (VMQ) on the vNIC, as it is a known cause of packet corruption triggering 13208.

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log and Windows Event Viewer (Application log) for recurrence of Error 13208 over a 24-48 hour window during active mirroring/AG synchronization.
• If the issue persists, temporarily move the Availability Group listener traffic to a different physical NIC or VLAN to isolate whether the problem is hardware-specific versus a SQL Server configuration issue.

Why This Error Happens

Error 13206 in SQL Server’s Service Broker subsystem typically occurs when a CREATE SERVICE or ALTER SERVICE statement references a CONTRACT that either does not exist in the current database scope, has been dropped while still referenced by an active queue, or the executing login lacks the REFERENCES permission on that specific contract object. This is common after database migrations to SQL Server 2025, where Service Broker GUIDs can become orphaned or mismatched between the publisher and subscriber databases. In some cases, this is also triggered by a corrupted sys.service_contracts system catalog entry following an improper failover on Windows Server 2025 clusters.

Step-by-Step Resolution Guide

Step 1: Verify Contract Existence and Permissions

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm the contract is actually registered in the database: SELECT name, contract_id FROM sys.service_contracts;
• If the contract is missing, you must recreate it using the original CREATE CONTRACT script before the dependent service can be created.
• Grant explicit permissions if the contract exists but the error persists: GRANT REFERENCES ON CONTRACT::[YourContractName] TO [YourServiceAccount];

Step 2: Check for GUID Mismatches in Service Broker

• Since this often relates to broken Broker IDs post-migration, verify the Service Broker GUID matches the database ID: SELECT is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDBName';
• If the GUID is NULL or mismatched due to a restore operation, run: ALTER DATABASE [YourDBName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
• Note: Only use NEW_BROKER if you do not need to preserve existing conversation handles, as this action clears the message queue.

Step 3: Restart the SQL Server Service and Broker

• Open the Services console (services.msc) on your Windows Server 2025/Windows 11 machine.
• Locate SQL Server (MSSQLSERVER) or your specific named instance.
• Right-click and select Restart to force the Service Broker to re-initialize its internal routing tables.
• Alternatively, run this T-SQL command to toggle the broker without a full service restart: ALTER DATABASE [YourDBName] SET ENABLE_BROKER;

Step 4: Rebuild the Service Definition

• If the contract and permissions are correct but the error remains, drop and recreate the specific SERVICE that failed.
• Execute: DROP SERVICE [YourServiceName]; followed by CREATE SERVICE [YourServiceName] ON QUEUE [YourQueueName] ([YourContractName]);
• Ensure the Queue is in a STATUS = ON state before attempting to bind the service to it again.

Alternative or Verification Steps

• To verify the fix, send a test message using BEGIN DIALOG between the two services and check sys.transmission_queue for any lingering error descriptions; an empty result set confirms successful routing.
• If the error persists after rebuilding the service, check the SQL Server Error Log via SSMS (Management > SQL Server Logs) for a corresponding Broker Manager startup failure, which may indicate a deeper master key encryption issue requiring a MASTER KEY restoration.

Troubleshooting Guide: SQL Server Error 13204 — “FROM BROKER INSTANCE”

Why This Error Happens

Error 13204 occurs when the Service Broker cannot locate or validate a conversation’s associated broker instance, typically because the sys.conversation_endpoints or sys.routes metadata is referencing a broker GUID that no longer matches the database’s current service_broker_guid. This commonly happens after a database restore, mirroring failover, or ALTER DATABASE SET NEW_BROKER operation that regenerates the broker identifier without updating dependent routes or endpoint references. It can also stem from corrupted Service Broker internal queues or an orphaned conversation left in an inconsistent state after an improper shutdown.

Step-by-Step Resolution Guide

Step 1: Verify the Current Broker GUID

• Run the following query to check the active broker instance identifier:

  SELECT name, service_broker_guid, is_broker_enabled 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Compare this GUID against the one referenced in sys.routes using SELECT * FROM sys.routes;.
• Note any mismatch — this confirms a stale broker reference is causing the error.

Step 2: Re-sync or Reset the Broker Instance

• If the database was restored or mirrored, run:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Alternatively, if you need to preserve existing message history, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Update any CREATE ROUTE statements on remote instances to reflect the new BROKER_INSTANCE GUID.

Step 3: Clear Orphaned Conversations and Routes

• Identify stuck conversations with:

  SELECT * FROM sys.conversation_endpoints WHERE state = 'DI';

• End any invalid conversation handles manually:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Drop and recreate the affected route pointing to the corrected BROKER_INSTANCE:

  DROP ROUTE OldRouteName;
  CREATE ROUTE NewRouteName 
      WITH SERVICE_NAME = 'TargetServiceName', 
      BROKER_INSTANCE = 'CorrectBrokerGUID', 
      ADDRESS = 'TCP://TargetServer:4022';

Step 4: Restart the Service Broker Task on Windows Server 2025 / Windows 11

• Open Services.msc and restart the SQL Server (MSSQLSERVER) service to force the Broker task manager to reinitialize.
• Alternatively, run from an elevated PowerShell prompt:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that Broker started without further GUID mismatch warnings.

Alternative or Verification Steps

• Confirm the fix by sending a test message through the affected service and checking sys.transmission_queue for zero pending errors referencing the old broker instance.
• If the issue persists, drop and fully recreate the Service Broker queue, service, and contract objects, then re-establish routes using the current service_broker_guid retrieved from sys.databases.

Troubleshooting Guide: SQL Server Error 13205 (“FROM SERVICE”)

Why This Error Happens

Error 13205 occurs within the Service Broker component of SQL Server when a conversation references a service that cannot be resolved, is malformed, or has been dropped while messages are still queued against it. This typically stems from a mismatch between the FROM SERVICE clause in a BEGIN DIALOG CONVERSATION statement and the actual service definitions present in sys.services. It can also occur due to corrupted routing information, an incorrect or missing service name during contract negotiation, or permission restrictions preventing SQL Server from validating the originating service.

Step-by-Step Resolution Guide

Step 1: Validate the Service Broker Configuration

• Run SELECT * FROM sys.services; to confirm the service referenced in the FROM SERVICE clause actually exists.
• Check SELECT * FROM sys.conversation_endpoints; to identify orphaned or broken conversations tied to the missing service.
• Confirm Service Broker is enabled on the database with: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';

Step 2: Re-Enable or Repair Service Broker

• If is_broker_enabled returns 0, run: ALTER DATABASE YourDBName SET ENABLE_BROKER;
• If the database is stuck due to a GUID mismatch (common after restoring a backup on Windows Server 2025), run: ALTER DATABASE YourDBName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
• Restart the SQL Server (MSSQLSERVER) service via services.msc or Restart-Service MSSQLSERVER in PowerShell (Run as Administrator).

Step 3: Recreate the Missing Service and Contract

• Re-create the service definition explicitly, specifying the correct queue and contract:
  CREATE SERVICE [//YourApp/TargetService] ON QUEUE YourQueue ([YourContract]);
• Ensure the service name in your T-SQL code or application connection string (under C:\Program Files\Microsoft SQL Server\160\MSSQL\Binn\) matches exactly, including case sensitivity.
• Grant the correct permissions using: GRANT SEND ON SERVICE::[//YourApp/TargetService] TO [YourSQLUser];

Step 4: Check Windows Firewall and Endpoint Permissions

• On Windows Server 2025, open Windows Defender Firewall with Advanced Security and ensure the Service Broker endpoint port (default 4022) is not blocked.
• Verify the endpoint status with: SELECT name, state_desc FROM sys.tcp_endpoints;
• If disabled, run: ALTER ENDPOINT [ServiceBrokerEndpoint] STATE = STARTED;

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.transmission_queue; immediately after resending a test message—if the row disappears without generating a new 13205 error, the service resolution issue is resolved.
• As an alternative, if corruption persists, back up the queued data, drop and fully recreate the Queue, Service, and Contract objects in the correct dependency order (Contract → Queue → Service) rather than attempting to repair the existing broken links.

Troubleshooting Guide: Error 13202 — SQL Server Service Broker “TO BROKER INSTANCE” Failure

Why This Error Happens

Error 13202 typically occurs when the Service Broker in SQL Server 2025 cannot correctly route a message to the destination broker instance because the routing information, dialog endpoint, or certificate-based security mapping is missing or misconfigured. This often happens after a database restore, mirroring failover, or Always On availability group role change where the service_broker_guid changes but routes and remote service bindings were not updated. Corrupted or expired master keys and certificates used for Service Broker security also prevent the instance from validating the destination endpoint, triggering this error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled and GUID is Correct

• Run the following query to confirm Broker status and GUID:

  SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• If the database was restored or copied, update the GUID reference using NEW_BROKER or ENABLE_BROKER depending on your scenario.

Step 2: Rebuild Routes and Remote Service Bindings

• Check existing routes on both the initiator and target instances:

  SELECT * FROM sys.routes;

• Drop and recreate the route pointing to the correct broker instance GUID and address:

  DROP ROUTE IF EXISTS MyRoute;
  CREATE ROUTE MyRoute 
      WITH SERVICE_NAME = 'TargetServiceName',
      BROKER_INSTANCE = 'correct-broker-guid-here',
      ADDRESS = 'TCP://TargetServer:4022';

• Ensure the ADDRESS matches the actual listener port configured in sys.transmission_queue and sys.service_broker_endpoints.

Step 3: Validate Certificates and Master Key Encryption

• Confirm the database master key exists and is open:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• Verify certificates used for Broker security have not expired:

  SELECT name, expiry_date FROM sys.certificates;

• Re-issue or renew expired certificates and re-establish the remote service binding using CREATE REMOTE SERVICE BINDING.

Step 4: Check Firewall and Endpoint Configuration on Windows Server 2025/Windows 11

• Confirm the Service Broker endpoint is listening on the correct port (default 4022):

  SELECT name, port, protocol_desc FROM sys.service_broker_endpoints;

• Open the required port in Windows Defender Firewall:

  New-NetFirewallRule -DisplayName "SQL Broker 4022" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

• Restart the SQL Server service via services.msc or:

  Restart-Service -Name "MSSQLSERVER"

Alternative or Verification Steps

• After applying fixes, send a test message between services and check sys.transmission_queue for a NULL transmission_status, confirming successful delivery.
• If the error persists, enable Service Broker trace flags (2537, 2538) temporarily to capture detailed diagnostic output in the SQL Server error log for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 13203 (“TO SERVICE”)

Why This Error Happens

Error 13203 typically occurs when SQL Server Service Broker attempts to establish a dialog “TO SERVICE” but fails to resolve or validate the target service due to a missing, disabled, or misconfigured Service Broker route, certificate, or endpoint. This is commonly caused by corrupted Service Broker metadata, a mismatched or expired security certificate used for dialog encryption, or the target database having Service Broker disabled (is_broker_enabled = 0). In Windows Server 2025 / Windows 11 environments, this can be compounded by stricter TLS/certificate validation policies that reject legacy broker certificates.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Ensure no active connections are blocking the ALTER statement; use WITH ROLLBACK IMMEDIATE if necessary.

Step 2: Validate the Service and Route Configuration

• Confirm the target service exists:

  SELECT * FROM sys.services WHERE name = 'YourServiceName';

• Check that a valid route is defined pointing to the correct broker instance or address:

  SELECT * FROM sys.routes;

• If missing, recreate the route using CREATE ROUTE with the correct ADDRESS and SERVICE_NAME parameters.

Step 3: Check and Renew Service Broker Certificates

• Verify certificate expiration and validity:

  SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%Broker%';

• If expired or invalid, drop and recreate the certificate, then re-establish the master key backup.
• Restart the SQL Server service via services.msc or:

  Restart-Service -Name 'MSSQLSERVER'

Step 4: Review SQL Server Error Logs and Firewall Rules

• Open SQL Server Configuration Manager and confirm the Service Broker endpoint port (commonly 4022) is active and not blocked.
• Check Windows Defender Firewall on Windows Server 2025 for inbound/outbound rules blocking TCP 4022:

  New-NetFirewallRule -DisplayName "SQL Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

• Cross-reference SQL Server error logs (ERRORLOG in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for correlating Broker failure messages.

Alternative or Verification Steps

• To verify the fix, send a test message between services using BEGIN DIALOG and SEND ON CONVERSATION, then confirm successful delivery via sys.transmission_queue (it should return no stuck rows).
• If the issue persists, as an alternative, disable and re-enable Service Broker entirely using ALTER DATABASE SET DISABLE_BROKER followed by ENABLE_BROKER, then rebuild the route and certificate from scratch.

Why This Error Happens

Error 13199 in SQL Server 2025 is triggered when the Service Broker message queue detects a timestamp mismatch between the sending and receiving endpoints, typically because the system clocks on the SQL Server host and the client/partner server have drifted out of sync. This can also occur when the Windows Time service (W32Time) is stopped or misconfigured on Windows Server 2025 or Windows 11, causing the database engine to reject incoming messages whose timestamp falls outside the allowed tolerance window. In rarer cases, a corrupted Service Broker conversation handle or an outdated SQL Server cumulative update can produce the same symptom because internal timestamp validation logic fails silently.

Step-by-Step Resolution Guide

Step 1: Verify and Synchronize System Clocks

• Open an elevated PowerShell prompt and run w32tm /query /status to check the current time source.
• Confirm that both the SQL Server host and any partner/client machines are synced to the same NTP source (e.g., time.windows.com or an internal domain controller).
• If drift is detected, run w32tm /resync /force to immediately correct the local clock.

Step 2: Restart the Windows Time Service

• Open services.msc and locate Windows Time.
• Right-click and select Restart, or run the following in an elevated Command Prompt:

  net stop w32time
  net start w32time

• Ensure the service startup type is set to Automatic to prevent future drift after reboots.

Step 3: Check Service Broker Configuration in SQL Server

• Open SQL Server Management Studio (SSMS) and run:

  SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If the value returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply changes.

Step 4: Apply the Latest Cumulative Update

• Navigate to Microsoft Update Catalog and download the latest SQL Server 2025 Cumulative Update (CU) package.
• Install the update on Windows Server 2025/Windows 11 and reboot if prompted.
• Confirm the patch level with:

  SELECT @@VERSION;

Alternative or Verification Steps

• To verify the fix, trigger a test Service Broker message send/receive cycle and confirm no 13199 errors appear in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, drop and recreate the affected Service Broker conversation endpoints using END CONVERSATION followed by a fresh BEGIN DIALOG CONVERSATION, as a stale conversation handle can retain an invalid timestamp reference.

Troubleshooting Guide: SQL Server Error 13201 — Security Header Timestamp Validation Failure

Why This Error Happens

Error 13201 in SQL Server 2025 occurs when the Service Broker (or Always On availability group endpoint) receives a message whose security header timestamp falls outside the acceptable clock skew tolerance, triggering a rejection to prevent replay attacks. This is most commonly caused by a time synchronization drift between the source and target servers, often due to a misconfigured or disconnected Windows Time service (W32Time). In some cases, it can also stem from a corrupted or expired certificate used in the dialog security handshake, which invalidates the timestamp verification chain.

Step-by-Step Resolution Guide

Step 1: Verify and Correct System Clock Synchronization

• Open an elevated Command Prompt on both the source and destination SQL Server nodes.
• Run w32tm /query /status to check the current time source and last sync time.
• If drift exceeds 5 minutes, force a resync using w32tm /resync /force.
• Confirm the Windows Time service is running via services.msc and set to Automatic startup.

Step 2: Validate Domain Controller / NTP Source Consistency

• Ensure all nodes point to the same authoritative time source.
• Run w32tm /config /manualpeerlist:"time.windows.com" /syncfromflags:manual /reliable:YES /update on standalone servers, or confirm domain-joined nodes sync from the PDC emulator.
• Restart the service with net stop w32time && net start w32time to apply changes.

Step 3: Inspect and Refresh Service Broker / Endpoint Certificates

• Open SQL Server Management Studio and run SELECT * FROM sys.certificates WHERE name LIKE '%ServiceBroker%'; to check expiration dates.
• If expired, regenerate the certificate using CREATE CERTIFICATE and re-establish the dialog security context between endpoints.
• Restart the SQL Server and SQL Server Agent services via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Review SQL Server Error Log for Correlated Entries

• Navigate to the log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries referencing “security header” or “timestamp” near the 13201 occurrence.
• Cross-reference the timestamp discrepancy value reported to confirm it aligns with the clock drift measured in Step 1.

Alternative or Verification Steps

• To verify the fix, re-trigger the Service Broker conversation or AG endpoint handshake and confirm no new 13201 entries appear in the ERRORLOG or Windows Event Viewer (Application log) within 24 hours.
• If the error persists after time sync correction, drop and recreate the affected Service Broker endpoint security certificates on both instances, then re-test message transmission between the queues.

Troubleshooting Guide: Error 13198 – Certificate Is Larger Than Maximum Allowed Size

Why This Error Happens

This error occurs when SQL Server attempts to load, back up, or use a certificate (often for Transparent Data Encryption, Service Broker, or Always Encrypted) whose byte size exceeds the internal buffer limit SQL Server allocates for certificate blobs. This is typically caused by using a certificate with an oversized key length (e.g., 4096-bit or higher with excessive chain data), importing a certificate bundled with a full certificate chain, or a corrupted/malformed certificate file that was generated incorrectly. It can also surface when migrating certificates from a newer OpenSSL version that encodes additional metadata not expected by legacy SQL Server certificate parsing routines.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Size and Key Length

• Open PowerShell and run certutil -dump "C:\Certs\yourcert.cer" to inspect the key size and encoding.
• Confirm if the key length exceeds SQL Server 2025 supported thresholds (generally 3456 bits for certain legacy operations).
• Check if the certificate includes an unnecessary full chain (Root + Intermediate + Leaf) instead of just the leaf certificate.

Step 2: Regenerate the Certificate with Supported Parameters

• If using OpenSSL, regenerate the private key with a supported size: openssl genrsa -out server.key 2048
• Create a new CSR and reissue the certificate with the Certificate Authority, requesting only the leaf certificate without the full bundled chain.
• Avoid using -x509 extensions that embed excessive metadata unless explicitly required by SQL Server.

Step 3: Re-import the Certificate into SQL Server Correctly

• In SQL Server Management Studio (SSMS), run:

  CREATE CERTIFICATE MyCert
  FROM FILE = 'C:\Certs\yourcert_fixed.cer'
  WITH PRIVATE KEY (FILE = 'C:\Certs\yourcert_fixed.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword');

• Ensure the .cer file is DER or Base64 encoded (not PKCS#7/P7B format, which often bundles extra chain data).
• Place the certificate file locally on the SQL Server host rather than referencing a network path, as remote paths can sometimes alter byte encoding during transfer.

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server (MSSQLSERVER) service to force SQL Server to reload the certificate store.
• Monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that the certificate loaded without error 13198.

Alternative or Verification Steps

• Run SELECT * FROM sys.certificates; in SSMS to confirm the certificate is listed and shows a valid expiry_date without throwing retrieval errors.
• If the error persists, use a smaller key size (2048-bit) as a fallback, since SQL Server 2025 has stricter blob-size parsing even though it supports larger keys in theory — this is a known compatibility quirk when certificates are generated via third-party tools rather than SQL Server’s native CREATE CERTIFICATE ... ENCRYPTION BY PASSWORD command.

Troubleshooting Guide: Error 13197 – Certificate Expired (SQL Server 2025)

Why This Error Happens

Error 13197 occurs when SQL Server 2025’s internal or user-provisioned TLS/SSL certificate used for Database Engine encryption or Service Broker/Availability Group authentication has passed its expiration date. This typically happens because automatic certificate renewal via Windows Certificate Services was not configured, or a manually imported certificate was never rotated before its validity period lapsed. SQL Server will reject connections or encrypted sessions since the certificate chain can no longer be validated as trusted and current.

Step-by-Step Resolution Guide

Step 1: Identify the Expired Certificate

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name] > Certificate tab.
• Note the thumbprint of the currently assigned certificate.
• Cross-reference it in certlm.msc (Local Machine Certificate Store) under Personal > Certificates and check the “Valid To” date.

Step 2: Generate or Renew the Certificate

• If using an internal CA, request a renewed certificate via certreq -new request.inf request.req with updated validity dates.
• If self-signed, generate a new certificate using PowerShell:
• New-SelfSignedCertificate -DnsName "YourSQLServerFQDN" -CertStoreLocation "cert:\LocalMachine\My" -NotAfter (Get-Date).AddYears(5)
• Ensure the certificate’s Enhanced Key Usage (EKU) includes Server Authentication (1.3.6.1.5.5.7.3.1).
• Grant the SQL Server service account read access to the private key via Manage Private Keys in the certificate’s right-click context menu.

Step 3: Bind the New Certificate to SQL Server

• Return to SQL Server Configuration Manager > Protocols > Certificate tab.
• Select the newly issued certificate from the dropdown list (it must appear in the Local Machine store with a matching private key).
• Apply the change and restart the SQL Server (MSSQLSERVER) service for the binding to take effect.

Step 4: Clear Cached Certificate References

• Restart the SQL Server Browser service to flush stale endpoint metadata.
• If using Service Broker or Database Mirroring/Always On certificates, re-run ALTER CERTIFICATE [CertName] WITH ACTIVE FOR BEGIN_DIALOG = ON against the master database to reactivate trust.

Alternative or Verification Steps

• Verify the fix by checking SQL Server Error Log for successful startup without Error 13197, and confirm encrypted connections succeed using sqlcmd -S servername -N -C.
• If the issue persists, drop and recreate the certificate entirely using CREATE CERTIFICATE with a fresh private key, then re-associate it with the affected endpoint or database master key.

Troubleshooting Guide: SQL Server Error 13196 – “Certificate Not Yet Valid”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to validate a TLS/SSL certificate whose “Valid From” timestamp is set in the future relative to the system clock on the Windows Server 2025 or Windows 11 host. This mismatch is typically caused by clock drift, incorrect time zone configuration, or a domain controller/NTP synchronization failure. It can also occur if a certificate was generated on a machine with a different (ahead) system time and then deployed to the SQL Server instance before that start date is reached in real time.

Step-by-Step Resolution Guide

Step 1: Verify System Date, Time, and Time Zone

• Open Command Prompt as Administrator and run w32tm /query /status to check sync status.
• Confirm the system date/time matches actual current time (check Windows Settings > Time & Language).
• Correct any discrepancies manually or re-sync using w32tm /resync /force.

Step 2: Check the Certificate’s Validity Period

• Open certlm.msc (Local Computer certificate store) and locate the certificate bound to the SQL Server instance.
• Double-click the certificate, go to the Details tab, and inspect the Valid From field.
• If the “Valid From” date is in the future, the certificate was issued with an incorrect start date — contact your CA or reissue it with the correct validity window.

Step 3: Re-synchronize Time Service with Domain or External NTP Source

• If the server is domain-joined, run w32tm /config /syncfromflags:domhier /update to force hierarchy sync from the domain controller.
• For standalone servers, point to a reliable external NTP source: w32tm /config /manualpeerlist:"time.windows.com" /syncfromflags:manual /update.
• Restart the Windows Time service: net stop w32time && net start w32time.

Step 4: Rebind the Corrected Certificate in SQL Server Configuration Manager

• Open SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [InstanceName].
• On the Certificate tab, reselect the valid (now-active) certificate from the dropdown.
• Click Apply, then restart the SQL Server service for changes to take effect.

Step 5: Restart SQL Server and Related Services

• Restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.
• Restart SQL Server Browser and SQL Server Agent if applicable.
• Monitor the SQL Server Error Log for recurrence of Error 13196.

Alternative or Verification Steps

• Verify the fix by connecting to the SQL Server instance using SSMS with “Encrypt=True” in the connection string; a successful connection without certificate warnings confirms resolution.
• If the issue persists, temporarily use a self-signed certificate generated on the correct, current system time as a stopgap while the original certificate issue is escalated to your Certificate Authority.

Troubleshooting Guide: SQL Server Error 13194 — Authentication Protocol Failure

Why This Error Happens

Error 13194 in Microsoft SQL Server 2025 typically occurs when the authentication protocol negotiation between the client and server fails due to a mismatch in supported security protocols, such as an outdated TLS version or disabled NTLM/Kerberos settings. This is often caused by misconfigured Service Principal Names (SPNs), missing permissions on the SQL Server service account, or Group Policy restrictions enforced by Windows Server 2025 that block legacy authentication methods. Corrupted or missing security certificates used for encrypted connections can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify SPN Registration for SQL Server

• Open Command Prompt as Administrator and run setspn -L <ServiceAccountName> to check existing SPNs.
• Ensure the SPN format MSSQLSvc/<FQDN>:<Port> is correctly registered.
• If missing, register it manually using setspn -S MSSQLSvc/yourserver.domain.com:1433 DOMAIN\ServiceAccount.

Step 2: Update Authentication Protocol Settings

• Navigate to Control Panel > Administrative Tools > Local Security Policy > Local Policies > Security Options.
• Locate Network Security: LAN Manager authentication level and set it to Send NTLMv2 response only. Refuse LM & NTLM.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Check TLS and Certificate Configuration

• Open SQL Server Configuration Manager and go to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Confirm a valid certificate is selected under the Certificate tab and that it has not expired.
• Enable Force Encryption only if all client drivers support TLS 1.2 or higher.

Step 4: Validate Group Policy and Firewall Rules

• Run gpresult /h report.html to check if any domain-level policy is blocking Kerberos delegation.
• Ensure Windows Firewall allows inbound traffic on port 1433 (or your custom SQL port) via New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow.

Alternative or Verification Steps

• Test the fix by connecting via SQL Server Management Studio (SSMS) using both Windows Authentication and SQL Authentication to confirm the protocol negotiates successfully without triggering Error 13194.
• If the issue persists, temporarily switch the authentication mode to Mixed Mode in SSMS under Server Properties > Security as a workaround while further investigating Kerberos/NTLM configuration.

Why This Error Happens

Error 13195 occurs when SQL Server 2025 attempts to decrypt a database protected by Transparent Data Encryption (TDE) or Always Encrypted but cannot locate the required certificate or its private key in the expected certificate store or master database. This typically happens after migrating a database to a new Windows Server 2025 instance without exporting/importing the original certificate, or when the certificate’s private key file (.pvk) has been deleted, corrupted, or lost its ACL permissions for the SQL Server service account. It can also surface if the certificate expired or was removed from the Windows Certificate Store (Local Machine) referenced by the SQL Server service.

Step-by-Step Resolution Guide

Step 1: Identify the Missing Certificate Thumbprint

• Query the source server (or error log) to find the exact certificate name/thumbprint tied to the encrypted database:
• SELECT name, certificate_id, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates;
• Cross-reference this thumbprint with the one reported in the SQL Server Error Log entry for Error 13195.

Step 2: Restore or Re-Import the Certificate and Private Key

• Locate the original .cer and .pvk backup files (commonly stored during initial TDE setup).
• On the new Windows Server 2025 instance, run the restore command in the master database:
• CREATE CERTIFICATE TDE_Cert FROM FILE = 'C:\Certs\TDE_Cert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\TDE_Cert.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword');
• If no backup exists, you must restore it from a known-good backup server where the certificate is still active, since a missing private key with no backup renders the encrypted data unrecoverable.

Step 3: Verify File System and Service Account Permissions

• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read permissions on the folder containing the .pvk or certificate store path.
• Check Windows Certificate Store via certlm.msc (Local Machine > Personal) to ensure the certificate is present and not expired.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after applying permission changes.

Step 4: Re-Link the Certificate to the Encrypted Database

• If the certificate was restored under a different name, update the Database Encryption Key (DEK) to reference it:
• USE YourEncryptedDB; ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
• Bring the database back online: ALTER DATABASE YourEncryptedDB SET ONLINE;

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_database_encryption_keys; to confirm the encryption_state shows 3 (encrypted and ready) rather than an error state.
• If the certificate is permanently unrecoverable, your only alternative is restoring the database from a backup taken before encryption was applied, or from a backup where the matching certificate still exists.

Troubleshooting Guide: SQL Server Error 13193 – Encryption Algorithm Mismatch

Why This Error Happens

Error 13193 occurs when SQL Server 2025 encounters an unsupported or mismatched encryption algorithm during a Service Broker or Database Mirroring/Always On endpoint connection attempt. This typically happens because one server is configured to use a deprecated cipher (such as RC4) while the other endpoint enforces AES-only communication, a default hardening change in SQL Server 2025. It can also occur when TLS/SSL certificate configurations between Windows Server 2025 nodes do not negotiate a common encryption standard due to outdated cipher suite policies.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint Encryption Algorithm

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check current endpoint encryption settings:
• SELECT name, encryption_algorithm_desc FROM sys.database_mirroring_endpoints;
• Confirm whether the algorithm is set to RC4, NONE, or AES.

Step 2: Update the Endpoint to Use AES Encryption

• Alter the endpoint configuration to force AES, which is required in SQL Server 2025 for secure communication.
• Run: ALTER ENDPOINT [Endpoint_Mirroring] FOR DATABASE_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM AES);
• Repeat this step on both the principal and mirror/secondary servers to ensure matching algorithms.

Step 3: Check Windows Server 2025 Cipher Suite Policies

• Open Local Group Policy Editor (gpedit.msc) on both Windows Server 2025/Windows 11 machines.
• Navigate to Computer Configuration > Administrative Templates > Network > SSL Configuration Settings.
• Ensure AES-based cipher suites (e.g., TLS_AES_256_GCM_SHA384) are prioritized and RC4-based suites are disabled.
• Restart the SQL Server service after applying policy changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Restart SQL Server Service and Endpoints

• Restart the SQL Server Database Engine service via SQL Server Configuration Manager.
• Manually restart the mirroring/Always On endpoint to apply the new encryption settings:
• ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED;

Step 5: Validate Certificate Compatibility (If Using Certificates)

• If endpoints use certificate-based authentication, confirm both certificates support AES-compatible key exchange.
• Reissue certificates using the Microsoft RSA SChannel Cryptographic Provider if legacy providers are detected.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_exec_connections WHERE encrypt_option = 'TRUE'; to confirm active connections are now using encrypted, AES-compliant channels.
• If the error persists, drop and recreate the endpoint entirely using CREATE ENDPOINT with explicit ALGORITHM AES syntax rather than altering the existing legacy endpoint configuration.

Why This Error Happens

Error 13191 occurs when a database user (database principal) exists inside a user database but its Security Identifier (SID) does not match any corresponding login (server principal) in the master database. This “orphaned user” scenario is commonly caused by restoring or attaching a database from a different server instance, where the login SIDs were generated independently. It can also happen if a login was dropped and recreated without explicitly specifying the original SID, breaking the internal mapping between the login and the database user.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Users

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on your Windows Server 2025 / Windows 11 machine.
• Run the following query against the impacted database to list mismatched SIDs:

USE [YourDatabaseName];
EXEC sp_change_users_login 'Report';

• Note the UserName values returned, as these are the orphaned database principals needing remapping.

Step 2: Remap Existing Logins to Database Users

• If a login with the same name already exists on the server but is unlinked, use ALTER USER to fix the mapping:

USE [YourDatabaseName];
ALTER USER [YourUserName] WITH LOGIN = [YourUserName];

• Verify the login exists first by checking SELECT name, sid FROM sys.server_principals; to confirm the login is present on the instance.

Step 3: Create a New Login with the Matching SID (If No Login Exists)

• If the server-level login was deleted or never existed, retrieve the orphaned SID from sys.database_principals:

SELECT name, sid FROM sys.database_principals WHERE type = 'S';

• Create a new login using that exact SID to restore the mapping:

CREATE LOGIN [YourUserName] WITH PASSWORD = 'StrongPassword123!', SID = 0x0123456789ABCDEF0123456789ABCDEF;

• Ensure the password policy complies with your Windows Server 2025 domain security settings to avoid creation failures.

Step 4: Restart SQL Server Services (If Changes Don’t Apply Immediately)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart to clear any cached authentication tokens.

Alternative or Verification Steps

• Re-run EXEC sp_change_users_login 'Report'; after applying fixes; a clean result (no rows returned) confirms all database principals are properly mapped.
• As an alternative, if multiple users are orphaned, use the bulk auto-fix procedure: EXEC sp_change_users_login 'Auto_Fix', 'YourUserName'; to automatically match users to existing logins with identical names.

Troubleshooting Guide: SQL Server Error 13192 — Certificate Disabled for BEGIN DIALOG

Why This Error Happens

This error occurs when Service Broker attempts to establish a dialog conversation using a certificate that has been explicitly disabled via the ALTER CERTIFICATE ... WITH ACTIVE FOR BEGIN_DIALOG = OFF setting. SQL Server 2025 enforces stricter certificate lifecycle validation during dialog initiation, so even a technically valid (non-expired) certificate will fail if its active flag is toggled off. This commonly happens after routine certificate rotation scripts or security hardening tasks disable old certificates without re-enabling the new one for dialog use.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Certificate

• Run the following query to check the status of certificates tied to Service Broker:
• SELECT name, subject, active_for_begin_dialog FROM sys.certificates;
• Look for any row where active_for_begin_dialog returns 0 (False).

Step 2: Re-Enable the Certificate for Dialog Use

• Execute the ALTER CERTIFICATE command to flip the flag back to active:
• ALTER CERTIFICATE [YourCertificateName] WITH ACTIVE FOR BEGIN_DIALOG = ON;
• Ensure you are connected to the correct database context (msdb or user database) where the endpoint was originally created.

Step 3: Validate Certificate Expiration and Trust Chain

• Confirm the certificate has not expired, as an expired cert cannot be re-activated successfully even with the flag set to ON.
• Check the path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Certs\ if using file-based certificates stored outside the master database.
• Verify the certificate’s private key is still accessible by the SQL Server service account via Windows Certificate Manager (certlm.msc).

Step 4: Restart the Service Broker Endpoint

• Restart the specific Service Broker endpoint to force SQL Server to reload the certificate status:
• ALTER ENDPOINT [BrokerEndpoint] STATE = STOPPED;
• ALTER ENDPOINT [BrokerEndpoint] STATE = STARTED;

Alternative or Verification Steps

• To verify the fix, send a test message using BEGIN DIALOG CONVERSATION between the two services; if the dialog initiates without Error 13192, the certificate is correctly active.
• If the issue persists, drop and recreate the certificate entirely using CREATE CERTIFICATE, then re-establish the master key encryption via OPEN MASTER KEY DECRYPTION BY PASSWORD.

Troubleshooting Guide: SQL Server Error 13189 — Invalid Key Exchange Key Size After Decryption

Why This Error Happens

This error occurs when SQL Server 2025 attempts to decrypt the key exchange key (KEK) using a certificate’s private key, but the resulting decrypted value does not match the expected size for the algorithm in use. This typically happens due to a corrupted or mismatched certificate, an incompatible encryption algorithm negotiated between endpoints (common in Always On Availability Groups or Database Mirroring setups), or a certificate that was improperly restored/re-issued with a different key length. It can also surface after a Windows Server 2025 security update changes default cryptographic provider behavior (CNG vs. legacy CAPI).

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Integrity on Both Endpoints

• Run SELECT * FROM sys.certificates WHERE name = 'YourCertName'; on both the principal and mirror/replica instances.
• Confirm the pvt_key_encryption_type_desc matches on both servers (should be consistent, e.g., ENCRYPTED_BY_MASTER_KEY).
• Check the certificate expiration date and thumbprint using SELECT thumbprint FROM sys.certificates; to ensure they are identical on both sides.

Step 2: Re-Export and Re-Import the Certificate with Matching Key Length

• Backup the certificate on the source instance using:
  BACKUP CERTIFICATE YourCertName TO FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!');
• Drop the existing certificate on the destination instance using DROP CERTIFICATE YourCertName;.
• Re-create it using CREATE CERTIFICATE YourCertName FROM FILE = 'C:\Certs\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', DECRYPTION BY PASSWORD = 'StrongPassword123!');.
• Ensure both certificates use the same key length (2048-bit minimum is recommended for SQL Server 2025).

Step 3: Check Cryptographic Provider Compatibility on Windows Server 2025

• Open certlm.msc and navigate to the certificate under Personal > Certificates.
• Right-click the certificate, go to Properties, and confirm the Cryptographic Service Provider (CSP) is set to a compatible provider (e.g., “Microsoft Enhanced RSA and AES Cryptographic Provider”).
• If the provider shows as a CNG-only provider and SQL Server expects legacy CAPI, re-export the certificate with the legacy CSP option enabled via certutil -csp "Microsoft Enhanced RSA and AES Cryptographic Provider" -importpfx YourCert.pfx.

Step 4: Restart the SQL Server Service and Validate Endpoint Configuration

• Restart the SQL Server service via services.msc or run Restart-Service MSSQLSERVER in PowerShell (run as Administrator).
• Re-check the mirroring/AG endpoint state using SELECT * FROM sys.database_mirroring_endpoints; to confirm the state is STARTED.
• Review the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that Error 13189 no longer appears.

Alternative or Verification Steps

• To verify the fix, attempt to re-establish the Always On Availability Group session or mirroring partnership and confirm no further 13189 errors appear in the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, generate a brand-new self-signed certificate on both instances with CREATE CERTIFICATE using explicit EXPIRY_DATE and matching encryption algorithm settings, then re-establish the endpoint authentication from scratch.

Troubleshooting Guide: Error 13190 – “The Database Principal Used to Lookup the Cert Is Invalid”

Why This Error Happens

This error occurs when SQL Server cannot resolve the certificate because the database principal (user or role) referenced during the certificate lookup no longer exists, has been renamed, or is orphaned from its associated Security Identifier (SID). It commonly happens after a database is restored or migrated to a different SQL Server 2025 instance, causing a mismatch between the certificate’s owning principal and the actual principals present in the sys.database_principals table. Corruption in the master key or a missing mapping between the login and the database user can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify the Database Principal Exists

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to check if the principal is present and valid:

  SELECT name, sid, type_desc FROM sys.database_principals WHERE name = 'YourPrincipalName';

• If the principal is missing or the SID shows as NULL, this confirms an orphaned user scenario.

Step 2: Repair Orphaned Users via SID Mapping

• Execute sp_change_users_login (or the modern equivalent ALTER USER ... WITH LOGIN for SQL Server 2025) to re-link the orphaned database user to the correct server login:

  ALTER USER [YourUserName] WITH LOGIN = [YourServerLogin];

• Confirm the SID now matches between sys.server_principals and sys.database_principals.

Step 3: Recreate or Re-associate the Certificate

• If the principal cannot be repaired, drop and recreate the certificate under a valid, currently existing principal:

  CREATE CERTIFICATE NewCert AUTHORIZATION [ValidPrincipalName] FROM FILE = 'C:\Certs\YourCert.cer';

• Ensure the certificate file path is accessible to the SQL Server service account (check NTFS permissions under C:\Certs\).

Step 4: Validate the Master Key and Service Account Permissions

• Open the SQL Server Configuration Manager and confirm the service account running the SQL Server 2025 engine has read/write access to the certificate store located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Re-run OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword'; to ensure the master key isn’t blocking principal resolution.

Alternative or Verification Steps

• Run SELECT * FROM sys.certificates WHERE pvt_key_encryption_type_desc = 'ENCRYPTED_BY_MASTER_KEY'; to confirm the certificate is now correctly linked to an active, non-orphaned principal.
• If the error persists, restore the database to a temporary instance, export the certificate and private key using BACKUP CERTIFICATE, and re-import it into the target database under a freshly created principal to bypass the corrupted mapping entirely.

Troubleshooting Guide: Error 13188 – Certificate Private Key Password Protected

Why This Error Happens

This error occurs when SQL Server 2025 attempts to use a certificate for Service Broker, Database Mirroring, or Always On Availability Groups encryption, but the certificate’s private key is secured with a password rather than being accessible to the SQL Server service account directly. SQL Server requires the private key to be decrypted using the Database Master Key (DMK) or loaded without manual password intervention since the service cannot interactively supply a password at runtime. This typically happens when a certificate is imported from an external source (like a .pfx file) without properly specifying the DECRYPTION BY PASSWORD clause alongside the correct ENCRYPTION BY SERVER ASYMMETRIC KEY or master key binding during the CREATE CERTIFICATE statement.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate’s Private Key Encryption Method

• Run SELECT name, pvt_key_encryption_type_desc FROM sys.certificates; in SQL Server Management Studio (SSMS) to check the encryption type.
• If the result shows ENCRYPTED_BY_PASSWORD, this confirms the private key is tied to a password instead of the Database Master Key.

Step 2: Re-import the Certificate with Proper Decryption Syntax

• Drop the problematic certificate using DROP CERTIFICATE YourCertificateName; (ensure you have a backup of the original .pfx and password first).
• Re-create the certificate using the DECRYPTION BY PASSWORD clause to match the private key’s existing password, and immediately re-encrypt it with the master key:

  CREATE CERTIFICATE YourCertificateName 
  FROM FILE = 'C:\Certs\YourCert.cer' 
  WITH PRIVATE KEY (FILE = 'C:\Certs\YourCert.pvk', 
  DECRYPTION BY PASSWORD = 'YourStrongPassword123'); 

• Ensure the target database has a Master Key created via CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AdminPassword123'; prior to this step.

Step 3: Update the Certificate to Remove Password Dependency

• If the certificate already exists but is password-protected, use the ALTER CERTIFICATE command to shift the encryption method.
• Execute: ALTER CERTIFICATE YourCertificateName WITH PRIVATE KEY (ENCRYPTION BY MASTER KEY, DECRYPTION BY PASSWORD = 'YourStrongPassword123');
• Restart the SQL Server (MSSQLSERVER) service via the Windows Server 2025 Services console (services.msc) to force the engine to reload the certificate context.

Step 4: Check Endpoint Configuration (For Mirroring/AGs)

• Navigate to your Database Mirroring or Availability Group endpoint settings.
• Run SELECT * FROM sys.database_mirroring_endpoints; to verify the endpoint is pointing to the newly updated certificate thumbprint.
• Ensure the SQL Server Service Account has explicit “Read” permissions on the certificate store if using Windows Authentication alongside the certificate.

Alternative or Verification Steps

• Verification: Query sys.certificates again post-fix; the pvt_key_encryption_type_desc column should now return ENCRYPTED_BY_MASTER_KEY. Attempt to start the Service Broker or Mirroring session again to confirm Error 13188 no longer appears in the SQL Server Error Log.
• Alternative: If the password is lost or re-encryption fails, generate a brand new self-signed certificate using CREATE CERTIFICATE without a password dependency, then update the endpoint authentication on both the Principal and Mirror/Secondary servers to trust the new public key certificate.

Why This Error Happens

This error occurs when SQL Server attempts to use a certificate for encrypting the Database Mirroring or Always On Availability Group endpoint, but the RSA private key length exceeds the maximum size supported by the Windows Cryptographic API (CAPI) handshake used internally by SQL Server’s transport layer. SQL Server 2025 enforces stricter validation on key exchange, meaning legacy certificates generated with 4096-bit or larger keys (or keys created using deprecated CSP providers instead of CNG/KSP) will fail the handshake. This is commonly triggered after migrating certificates from older SQL Server instances or importing a certificate issued by a corporate CA with non-standard key lengths.

Step-by-Step Resolution Guide

Step 1: Verify the Current Certificate’s Key Size

• Open SQL Server Management Studio (SSMS) and run the following query to inspect the certificate used by the endpoint:

SELECT name, subject, pvt_key_encryption_type_desc, 
       key_length 
FROM sys.certificates 
WHERE name = 'YourEndpointCertName';

• Confirm whether the key_length value exceeds 3072 bits, as SQL Server 2025 endpoints only support up to 3072-bit RSA keys for mirroring/AG encryption.

Step 2: Generate a Compliant Certificate

• Use makecert or preferably PowerShell with the CNG provider to generate a new certificate with a supported key size:

New-SelfSignedCertificate -DnsName "SQLServerNode1" -CertStoreLocation "cert:\LocalMachine\My" -KeyLength 2048 -KeyAlgorithm RSA -Provider "Microsoft Software Key Storage Provider"

• Export the certificate (.pfx) with the private key, ensuring it is placed in C:\Certs\ for import on all partner nodes (primary, mirror, witness, or AG replicas).

Step 3: Reassign the Certificate to the Endpoint

• In SSMS, drop the old certificate binding and recreate it:

ALTER ENDPOINT [Mirroring]
STATE = STOPPED;

CREATE CERTIFICATE NewMirrorCert
FROM FILE = 'C:\Certs\NewMirrorCert.cer'
WITH PRIVATE KEY (FILE = 'C:\Certs\NewMirrorCert.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword');

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE NewMirrorCert, ENCRYPTION = REQUIRED ALGORITHM AES);

• Repeat the certificate import and endpoint binding on all partner/replica servers to ensure matching public keys.

Step 4: Restart SQL Server Services

• Restart the SQL Server (MSSQLSERVER) service via services.msc or run:

Restart-Service -Name MSSQLSERVER -Force

• This ensures the endpoint reloads the new certificate context and clears any cached key exchange handshake data.

Alternative or Verification Steps

• Check the SQL Server Error Log (ERRORLOG in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for successful endpoint startup messages with no further 13186 entries.
• If the issue persists, bypass custom CA certificates entirely by using a self-signed certificate generated directly via T-SQL (CREATE CERTIFICATE ... WITH SUBJECT = 'Mirroring Cert'), which defaults to a SQL Server-compliant key size and provider.

Troubleshooting Guide: SQL Server Error 13187 — Certificate Public Key Size Incompatible with Security Header Signature

Why This Error Happens

This error occurs when SQL Server 2025 attempts to use a certificate for Database Mirroring, Always On Availability Groups, or Service Broker encryption, but the certificate’s public key length exceeds the maximum size supported by the legacy security header signing algorithm. SQL Server’s endpoint authentication mechanism has strict upper bounds on RSA key sizes (typically capped below 3072 or 4096 bits depending on the build), and certificates generated with modern tools or updated CA templates often default to larger key sizes. This mismatch also frequently appears after a certificate renewal process silently upgraded the key size, or when a certificate was imported from a third-party CA that enforces stronger key policies by default.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Certificate and Its Key Size

• Run the following T-SQL query to locate certificates tied to mirroring or AG endpoints:
• SELECT name, subject, pvt_key_encryption_type_desc, start_date, expiry_date FROM sys.certificates;
• Check the actual RSA key length using PowerShell:
• Get-ChildItem Cert:\LocalMachine\My | Select-Object Subject, @{N='KeySize';E={$_.PublicKey.Key.KeySize}}
• Confirm whether the key size is 3072-bit or higher, which is the common trigger for Error 13187.

Step 2: Generate a Compatible Certificate with a Supported Key Size

• Create a new certificate using New-SelfSignedCertificate or your enterprise CA, explicitly specifying a 2048-bit key:
• New-SelfSignedCertificate -Subject "CN=SQLMirroringCert" -KeyLength 2048 -KeyAlgorithm RSA -CertStoreLocation "Cert:\LocalMachine\My" -KeyUsage DigitalSignature, KeyEncipherment
• If using an internal CA template, edit the template in certtmpl.msc and reduce the “Minimum Key Size” field under the Cryptography tab to 2048.
• Export the new certificate as a .cer or .pfx file for import into SQL Server.

Step 3: Replace the Certificate in SQL Server Endpoint Configuration

• Drop the existing mirroring/AG endpoint certificate binding:
• ALTER CERTIFICATE [OldCertName] REMOVE PRIVATE KEY;
• Create the new certificate object in SQL Server pointing to the 2048-bit key file:
• CREATE CERTIFICATE [NewCertName] FROM FILE = 'C:\Certs\NewCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\NewCert.pvk', DECRYPTION BY PASSWORD = 'StrongPassword123!');
• Re-associate the new certificate with the Database Mirroring or Service Broker endpoint using ALTER ENDPOINT.

Step 4: Restart the SQL Server Service and Endpoint

• Restart the SQL Server service via services.msc or run:
• Restart-Service -Name 'MSSQLSERVER'
• Restart the affected endpoint explicitly:
• ALTER ENDPOINT [Mirroring] STATE = STARTED;
• Monitor the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\) for confirmation that Error 13187 no longer appears.

Alternative or Verification Steps

• Verify the fix by forcing a mirroring session re-handshake or manually failing over the Availability Group, then checking the SQL Server Error Log and Windows Event Viewer (Application log) for a clean connection with no 13187 entries.
• If the issue persists, as a fallback, regenerate the certificate chain entirely using sqlcmd with explicit ENCRYPTION ALGORITHM AES and a fresh 2048-bit RSA key pair, then re-establish trust between the mirroring partners/replicas from scratch.

Troubleshooting Guide: SQL Server Error 13184 – Certificate Private Key Size Incompatible with Crypto Provider

Why This Error Happens

This error occurs when a certificate used by SQL Server (often for Database Mail, Service Broker, Always On, or Transparent Data Encryption) has a private key generated by the legacy Cryptographic Service Provider (CSP) that exceeds the supported key length, typically when using RSA keys larger than 2048 bits (e.g., 3072 or 4096 bits) with older CSP-based providers instead of a Key Storage Provider (KSP/CNG). SQL Server 2025, like its predecessors, relies on the Windows Cryptography API, and certain legacy CSPs cap out at 2048-bit keys, causing a mismatch when a larger key is presented. Additionally, this can happen if the certificate was imported into the wrong certificate store or generated using a CNG provider incompatible with the SQL Server service account’s cryptographic context.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate’s Key Length and Provider Type

• Open PowerShell as Administrator and run: Get-ChildItem -Path Cert:\LocalMachine\My | Format-List Subject, FriendlyName, HasPrivateKey, PublicKey
• Check the PublicKey.Key.KeySize property; if it shows 3072 or 4096, this is likely the root cause.
• Run certutil -store My to confirm whether the certificate uses a legacy CSP (e.g., “Microsoft RSA SChannel Cryptographic Provider”) instead of a CNG KSP (e.g., “Microsoft Software Key Storage Provider”).

Step 2: Regenerate the Certificate Using a CNG-Compatible Key Storage Provider

• Use PowerShell’s New-SelfSignedCertificate cmdlet with the -KeyExportPolicy Exportable and explicitly specify a 2048-bit key size if legacy compatibility is required: New-SelfSignedCertificate -Subject "CN=SQLServerCert" -KeySpec KeyExchange -KeyLength 2048 -Provider "Microsoft Enhanced RSA and AES Cryptographic Provider" -CertStoreLocation Cert:\LocalMachine\My
• If a larger key size (3072/4096) is mandatory for compliance, ensure the certificate is issued using a CNG-based provider rather than the legacy CSP.
• Avoid using certreq.exe templates that default to legacy CSP unless the key size is capped at 2048 bits.

Step 3: Re-Bind the New Certificate to the SQL Server Service

• Open SQL Server Configuration Manager, navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Properties, go to the Certificate tab, and select the newly generated 2048-bit (or CNG-compatible) certificate from the dropdown.
• Ensure the SQL Server service account has read permissions on the private key: right-click the certificate in certlm.msc, select “Manage Private Keys,” and grant Read access to the SQL Server service account (e.g., NT Service\MSSQLSERVER).
• Restart the SQL Server service for changes to take effect: Restart-Service -Name MSSQLSERVER -Force

Step 4: Update Database Mail or Service Broker Certificate Bindings (If Applicable)

• If the error originated from Database Mail or Service Broker, re-run the CREATE CERTIFICATE or ALTER CERTIFICATE T-SQL statements referencing the new certificate file path.
• Example: ALTER CERTIFICATE MyCert WITH PRIVATE KEY (FILE = 'C:\Certs\NewCert.pvk', DECRYPTION BY PASSWORD = 'StrongPassword123')
• Confirm the certificate thumbprint matches the one bound in SQL Server Configuration Manager using SELECT thumbprint FROM sys.certificates WHERE name = 'MyCert'.

Alternative or Verification Steps

• To verify the fix, check the SQL Server Error Log and Windows Event Viewer (Application log) for Error 13184 recurrence after restarting the service; also run SELECT * FROM sys.dm_server_services to confirm the instance started cleanly.
• If regenerating the certificate is not feasible, as a workaround, temporarily disable certificate-based encryption for the specific feature (e.g., Database Mail or Service Broker) and use a 2048-bit key certificate issued by an internal CA that explicitly supports legacy CSP compatibility.

Troubleshooting Guide: SQL Server Error 13185 – Certificate Public Key Size Incompatible with Crypto Provider

Why This Error Happens

This error occurs when SQL Server 2025 attempts to use a certificate (typically for Database Mailbox encryption, Always Encrypted, or SSL/TLS endpoint security) whose RSA public key size exceeds the limit supported by the legacy Cryptographic Service Provider (CSP) bound to it. Certificates generated with key sizes above 2048-bit (such as 3072-bit or 4096-bit) are often incompatible with legacy CSPs and must instead use a Key Storage Provider (KSP) under the CNG (Cryptography API: Next Generation) framework. This mismatch commonly surfaces after a certificate renewal, migration from an older Windows Server version, or when importing a certificate generated by a third-party CA with modern key-length defaults.

Step-by-Step Resolution Guide

Step 1: Identify the Certificate and Its Provider Type

• Open PowerShell as Administrator and run certutil -dump -v "C:\path\to\certificate.cer" to inspect the key provider.
• Look for the “Provider” field in the output — if it lists a legacy CSP (e.g., “Microsoft RSA SChannel Cryptographic Provider”) rather than a KSP (e.g., “Microsoft Software Key Storage Provider”), this confirms the incompatibility.
• Cross-reference the key length listed under “Public Key Length” — anything above 2048 bits with a legacy CSP will trigger Error 13185.

Step 2: Re-generate or Re-import the Certificate Using a CNG-Compatible KSP

• In the Certificate Request (INF file) used to generate the certificate, ensure the [RequestAttributes] or [NewRequest] section specifies ProviderType = 0 and ProviderName = "Microsoft Software Key Storage Provider" instead of a legacy CSP value.
• If re-issuing via an internal CA, update the certificate template in certsrv.msc to use “CNG” as the Cryptographic Provider Category, located under the Request Handling tab in Template Properties.
• Re-import the newly issued certificate into the local machine store using certlm.msc, ensuring it lands in Certificates (Local Computer) > Personal > Certificates.

Step 3: Rebind the Certificate in SQL Server Configuration Manager

• Launch SQLServerManager17.msc (SQL Server Configuration Manager for SQL Server 2025) and navigate to SQL Server Network Configuration > Protocols for [InstanceName].
• On the Certificate tab, clear the old certificate binding and reselect the newly generated KSP-based certificate from the dropdown list.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) to force SQL Server to reload the certificate and provider bindings.

Step 4: Grant SQL Server Service Account Access to the New Private Key

• Right-click the certificate in certlm.msc, select All Tasks > Manage Private Keys.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain service account) with Read permission.
• Confirm permissions propagate by checking the ACL on the key file under C:\ProgramData\Microsoft\Crypto\Keys\ (for CNG-based keys).

Alternative or Verification Steps

• Verify the fix by checking the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for successful certificate load messages on service startup, with no further occurrences of Error 13185.
• If re-issuing the certificate isn’t immediately feasible, as a temporary workaround, generate a new self-signed certificate using New-SelfSignedCertificate -KeyExportPolicy Exportable -Provider "Microsoft Software Key Storage Provider" -KeyLength 2048 in PowerShell, bind it via Step 3, and replace it with the properly issued CNG certificate once available.

Troubleshooting Guide: SQL Server Error 13183 – Certificate’s Private Key Cannot Be Found

Why This Error Happens

This error typically occurs when the service account running SQL Server (such as NT Service\MSSQLSERVER or a custom domain account) lacks the necessary file system permissions to access the private key associated with the certificate. Private keys are stored separately from the certificate in the machine’s MachineKeys folder, and if these ACLs were not configured during certificate import or were reset by a system update, SQL Server cannot decrypt or validate the key. Additionally, this can happen if the certificate was imported without marking the private key as “exportable” or if it was moved/restored from another server without re-establishing proper key container permissions.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate and Private Key Exist

• Open certlm.msc (Local Computer Certificate Store) on the Windows Server 2025 / Windows 11 machine.
• Navigate to Personal > Certificates and locate the certificate used by SQL Server.
• Double-click the certificate and check the General tab for the statement: “You have a private key that corresponds to this certificate.”
• If this message is missing, the private key is not properly associated, and you will need to re-import the certificate using a .pfx file (which includes the private key) rather than a .cer file.

Step 2: Grant the SQL Server Service Account Access to the Private Key

• Right-click the certificate in certlm.msc, select All Tasks > Manage Private Keys.
• Add the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER or your domain service account) with Read permissions.
• Alternatively, use PowerShell to locate and fix ACLs directly:
• $cert = Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*yourdomain*"}
• $rsaCert = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)
• $fileName = $rsaCert.Key.UniqueName
• $path = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$fileName"
• icacls $path /grant "NT SERVICE\MSSQLSERVER:(R)"

Step 3: Re-import the Certificate with Exportable Private Key

• If permissions alone do not resolve the issue, export the certificate (with private key) from its original source as a .pfx file using the Certificates MMC snap-in.
• Re-import it into Cert:\LocalMachine\My using PowerShell: Import-PfxCertificate -FilePath "C:\certs\sqlcert.pfx" -CertStoreLocation Cert:\LocalMachine\My -Exportable
• Ensure the “Mark this key as exportable” checkbox is selected during any manual import via the wizard, as this often resolves orphaned key container issues.

Step 4: Restart SQL Server Services and Validate Configuration

• Open SQL Server Configuration Manager and confirm the correct certificate is selected under Protocols for MSSQLSERVER > Certificate tab.
• Restart the SQL Server service (MSSQLSERVER) for the changes to take effect.
• Check the SQL Server Error Log immediately after restart to confirm Error 13183 no longer appears during startup or connection encryption negotiation.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_exec_connections WHERE encrypt_option = 'TRUE'; in SQL Server Management Studio to confirm encrypted connections are successfully using the certificate.
• If the issue persists, delete the certificate binding in SQL Server Configuration Manager, revert to “Force Encryption = No” temporarily, restart the service, then re-apply the certificate binding from scratch to rule out a corrupted key container reference.

Troubleshooting Guide: SQL Server Error 13181

Why This Error Happens

Error 13181 is typically generated by SQL Server Database Mirroring or Availability Group (Always On) components when a database endpoint encounters an unexpected failure during transport-level communication. This usually occurs because the mirroring/AG endpoint has entered a corrupted or inconsistent state, often triggered by a sudden network interruption, a certificate validation failure, or an abrupt termination of the underlying TCP connection between replicas. In many cases, this is logged alongside other errors (such as 1479 or 35202) and is more of a symptom than a root cause—pointing to deeper transport or authentication issues rather than a standalone failure.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint and Service State

• Open SQL Server Configuration Manager and confirm the SQL Server service is running under the expected service account.
• Run the following query to check endpoint status:

  SELECT name, state_desc, protocol_desc FROM sys.database_mirroring_endpoints;

• If the state shows “STOPPED,” restart the endpoint using ALTER ENDPOINT [EndpointName] STATE = STARTED;.

Step 2: Check Network and Firewall Configuration

• Confirm the mirroring/AG port (commonly 5022) is not blocked by Windows Defender Firewall on Windows Server 2025.
• Run Test-NetConnection -ComputerName <PartnerServerName> -Port 5022 from PowerShell to validate connectivity between replicas.
• Inspect for packet loss or latency spikes in the network path, especially if replicas span different subnets or cloud regions.

Step 3: Review Certificate and Authentication Settings

• If the endpoint uses certificate-based authentication, confirm the certificate has not expired by running:

  SELECT name, expiry_date FROM sys.certificates;

• Ensure the certificate’s private key has not been renewed or rotated without updating the mirror/secondary replica.
• Re-issue and redistribute the certificate to both instances if a mismatch is found.

Step 4: Restart the Mirroring or Availability Group Session

• Suspend and resume the affected database using:

  ALTER DATABASE [YourDB] SET PARTNER RESUME;

• For Always On AG, failover to the secondary replica temporarily to reset the connection state, then fail back once stability is confirmed.

Step 5: Examine SQL Server Error Logs and Windows Event Viewer

• Cross-reference Error 13181 with surrounding entries in the SQL Server error log (ERRORLOG file, typically located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\).
• Check the Windows Event Viewer (Application and System logs) for related network or security warnings occurring at the same timestamp.

Alternative or Verification Steps

• After applying fixes, monitor the sys.dm_db_mirroring_connections DMV for 15–30 minutes to confirm a stable “CONNECTED” state without repeated disconnects.
• If the error persists, drop and recreate the mirroring endpoint entirely on both instances, ensuring matching port numbers and encryption algorithms (AES recommended) are specified on both sides.

Why This Error Happens

Error 13182 is generated by SQL Server’s Service Broker engine when an internal activation stored procedure throws an unhandled exception while processing a queue message. This typically occurs because the activated procedure lacks sufficient permissions on the target queue or referenced objects, or because the queue has become disabled due to a prior poison message. In Windows Server 2025 environments, this is also frequently triggered by TLS/certificate renegotiation issues introduced in the updated .NET Framework stack used by SQL Server 2025’s broker transport layer.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue State

• Open SQL Server Management Studio (SSMS) and run:
  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabase';
• Check if the queue is disabled due to poison message handling:
  SELECT name, is_receive_enabled FROM sys.service_queues;
• If is_receive_enabled is 0, re-enable it with ALTER QUEUE YourQueueName WITH STATUS = ON;

Step 2: Review the SQL Server Error Log for Stack Details

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for the string “13182” to identify the specific activation procedure and line number that failed.
• Cross-reference the referenced stored procedure to confirm it has EXECUTE AS OWNER or appropriate impersonation context set.

Step 3: Grant Required Permissions to the Activation Procedure

• Run the following to grant the service account rights on the queue:
  GRANT RECEIVE ON QueueName TO [DOMAIN\ServiceAccount];
• Ensure the certificate used for Service Broker endpoint authentication has not expired:
  SELECT name, expiry_date FROM sys.certificates;
• If expired, regenerate using CREATE CERTIFICATE and re-bind it to the broker endpoint.

Step 4: Restart the Service Broker Task Manager

• Execute ALTER DATABASE YourDatabase SET DISABLE_BROKER; ALTER DATABASE YourDatabase SET ENABLE_BROKER; to force a clean restart of the broker’s internal task scheduler.
• Confirm no active transactions are blocking the queue before running this step using sys.dm_tran_active_transactions.

Alternative or Verification Steps

• Verify the fix by sending a test message through the queue and confirming it processes without regenerating Error 13182 in the ERRORLOG or sys.transmission_queue.
• If the error persists, run DBCC CHECKDB against the database to rule out underlying corruption affecting the Service Broker system tables, and restore from a known good backup if corruption is confirmed.

Troubleshooting Guide: SQL Server Error 13179 During DDL Operations

Why This Error Happens

Error 13179 in SQL Server 2025 typically occurs when the Query Store or a related metadata catalog is unable to persist schema change information during a DDL operation, often because the underlying system database (such as master or the database’s internal Query Store storage) has insufficient disk space or is in a read-only/restricted state. This can also be triggered by corrupted internal catalog views or a mismatch between compiled plan cache metadata and the current schema version. Additionally, permission restrictions on the service account for writing to system tables can prevent the DDL transaction from committing properly.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Database State

• Check the drive hosting your data and log files (.mdf, .ldf) for available free space using Get-PSDrive in PowerShell.
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption.
• Confirm the database is not in READ_ONLY or EMERGENCY mode via SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';.

Step 2: Reset or Clear Query Store Cache

• Open SQL Server Management Studio (SSMS) 21 and connect to the affected instance.
• Execute ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR; to flush a potentially corrupted Query Store cache causing the metadata conflict.
• If the error persists, temporarily disable Query Store using ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;, then re-enable it after the DDL operation completes.

Step 3: Validate Service Account Permissions

• Open Services.msc on Windows Server 2025/Windows 11 and locate the SQL Server (MSSQLSERVER) service.
• Confirm the service account has db_ddladmin and db_owner roles on the target database via SSMS under Security > Logins.
• Grant explicit write permissions to the %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA folder if the account lacks NTFS write access.

Step 4: Apply Latest Cumulative Update

• Navigate to Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025.
• Run the update package as Administrator and restart the SQL Server service once complete.
• Re-attempt the DDL operation that originally triggered Error 13179.

Alternative or Verification Steps

• To verify the fix, re-run the original DDL statement (e.g., ALTER TABLE, CREATE INDEX) and confirm it completes without generating Error 13179 in the SQL Server Error Log (ERRORLOG file in the MSSQL\Log directory).
• If the issue persists after all steps, restore the database from a recent clean backup to a test instance and re-apply the DDL changes to isolate whether the corruption is instance-specific or database-specific.

Troubleshooting Guide: SQL Server Error 13180 – Execution Cache Cleanup Failure

Why This Error Happens

Error 13180 in SQL Server 2025 typically occurs when the internal execution cache cleanup task (part of the SQL Server resource governor or plan cache subsystem) fails to acquire the memory or scheduler resources it needs to purge stale execution plans. This is often caused by memory pressure, a corrupted plan cache entry, or a conflict with third-party memory management tools interfering with SQL Server’s memory broker. In some cases, outdated SQL Server Cumulative Updates (CU) on Windows Server 2025 can leave the cache cleanup thread orphaned, triggering this error repeatedly in the ERRORLOG.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run EXEC sp_configure 'max server memory' to check current memory caps.
• Ensure max server memory is set at least 2–4 GB below total physical RAM to leave room for OS and Windows Server 2025 overhead.
• Adjust using sp_configure 'max server memory', <value_in_MB>; RECONFIGURE;

Step 2: Clear and Rebuild the Plan Cache

• Run DBCC FREEPROCCACHE to manually clear the execution plan cache (test in non-production first).
• Alternatively, target a specific database: DBCC FLUSHPROCINDB (<database_id>).
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER if the error persists after flushing.

Step 3: Apply Latest Cumulative Update and Patch Windows

• Check current build with SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog.
• On Windows Server 2025 / Windows 11, run sconfig or Windows Update to ensure OS-level patches (especially memory management and Hyper-V integration fixes) are current.
• Reboot the server after patching to fully apply kernel-level changes.

Step 4: Check for Third-Party Memory/Antivirus Interference

• Review installed antivirus or memory-optimization tools (e.g., aggressive RAM cleaners) that may lock SQL Server memory pages.
• Add exclusions for sqlservr.exe and the MSSQL\Data directory in your antivirus configuration.
• Disable any non-Microsoft memory management services temporarily to isolate the cause.

Alternative or Verification Steps

• Monitor the SQL Server ERRORLOG (Management > SQL Server Logs in SSMS) for 24–48 hours to confirm Error 13180 no longer appears after applying the CU and memory adjustments.
• If the issue persists, enable Trace Flag 8721 at startup (via SQL Server Configuration Manager startup parameters) to get verbose plan cache diagnostic logging for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 13177 (IL Compilation Failure)

Why This Error Happens

Error 13177 occurs when SQL Server 2025’s SQLCLR integration attempts to compile Intermediate Language (IL) code for a managed module (such as a CLR stored procedure, trigger, or user-defined function) but fails due to a corrupted assembly, an incompatible .NET runtime version, or insufficient permissions on the CLR integration subsystem. This is commonly triggered after an OS upgrade to Windows Server 2025 or Windows 11, where the underlying .NET hosting runtime referenced by SQL Server’s clr_enabled configuration no longer matches the version expected by the deployed assembly. It can also result from a corrupted mscorlib binding cache or a security policy (CAS/PERMISSION_SET) misconfiguration blocking JIT compilation of the assembly at runtime.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration is Properly Enabled

• Open SQL Server Management Studio (SSMS) and connect to the instance throwing Error 13177.
• Run the following to confirm CLR is active:

  sp_configure 'clr enabled';

• If the run_value is 0, enable it:

  sp_configure 'clr enabled', 1;
  RECONFIGURE;

Step 2: Rebuild and Re-register the Affected Assembly

• Navigate to the project source or DLL output path, typically something like C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\Assemblies\.
• Drop the corrupted assembly from SQL Server:

  ALTER ASSEMBLY MyAssembly WITH PERMISSION_SET = UNSAFE;

• If that fails, fully remove and re-add it:

  DROP ASSEMBLY MyAssembly;
  CREATE ASSEMBLY MyAssembly FROM 'C:\Path\To\MyAssembly.dll' WITH PERMISSION_SET = SAFE;

• Ensure the DLL was compiled against the correct .NET target framework supported by SQL Server 2025 (typically .NET Framework 4.8.1 compatibility layer, even under Windows Server 2025).

Step 3: Clear and Reset the .NET Native Image Cache

• On Windows Server 2025 / Windows 11, open an elevated Command Prompt.
• Clear the Native Image Cache (NGEN) to eliminate corrupted precompiled IL artifacts:

  %windir%\Microsoft.NET\Framework64\v4.0.30319\ngen.exe update

• Restart the SQL Server service (MSSQLSERVER) from services.msc or via:

  net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Check File and Folder Permissions on SQL Server Binaries

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Right-click the folder → Properties → Security tab.
• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read & Execute permissions.
• If permissions were recently altered (common after a Windows Server 2025 security policy update), reset them to inherit from the parent directory.

Step 5: Apply the Latest SQL Server 2025 Cumulative Update

• Check your current build:

  SELECT @@VERSION;

• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early builds of SQL Server 2025 have known IL compiler regressions on Windows Server 2025/Windows 11 hosts.
• Reboot the server after installation to finalize the runtime binding changes.

Alternative or Verification Steps

• To verify the fix, re-execute the stored procedure or function that originally triggered Error 13177, and confirm successful execution with no exceptions in sys.dm_exec_requests or the SQL Server Error Log.
• If the error persists, as a fallback, disable CLR strict security (clr strict security) temporarily via sp_configure to isolate whether the issue is policy-based versus a genuine binary corruption, then re-enable it once the root cause is confirmed resolved.

Why This Error Happens

Error 13178 occurs when SQL Server’s Service Broker encounters a failure while transmitting a message through the in-process (Inproc) transport layer, typically because the target queue is disabled, the message type/contract binding is corrupted, or the Service Broker GUID on the database has become desynchronized after a restore or AlwaysOn failover. This is common in SQL Server 2025 when ENABLE_BROKER was toggled without a corresponding ALTER DATABASE SET NEW_BROKER cleanup, leaving orphaned conversation handles. Antivirus or Windows Defender real-time scanning on Windows Server 2025 can also lock the .mdf/.ldf I/O path momentarily, causing the Inproc dispatcher to drop the data access call.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status on the Database

• Run SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDB'; in SSMS.
• If is_broker_enabled returns 0, enable it with ALTER DATABASE YourDB SET ENABLE_BROKER;
• If the database is in a restored or mirrored state, use ALTER DATABASE YourDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE; to reset the GUID.

Step 2: Clear Orphaned Conversation Endpoints

• Query active conversations: SELECT * FROM sys.conversation_endpoints WHERE state NOT IN ('CD');
• End any stuck or erroring dialogs: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the SQL Server Broker task by executing ALTER DATABASE YourDB SET DISABLE_BROKER; followed by ALTER DATABASE YourDB SET ENABLE_BROKER; during a maintenance window.

Step 3: Check File System and Antivirus Locks

• Navigate to the data file directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Confirm Windows Defender exclusions are set for sqlservr.exe and the DATA/LOG folders via Windows Security > Virus & threat protection > Exclusions.
• Check the SQL Server Error Log (ERRORLOG in the LOG folder) for concurrent I/O warnings (Error 833) that correlate with the 13178 timestamp.

Step 4: Rebuild Broker Message Types and Contracts

• Script out existing message types and contracts using SSMS “Generate Scripts” wizard.
• Drop and recreate any contract bindings flagged as invalid: DROP CONTRACT [YourContract]; then re-run the creation script.
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to flush the Inproc transport cache.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected queue and confirming receipt with RECEIVE TOP(1) * FROM YourQueue; — no 13178 entry should appear in the SQL Server Error Log afterward.
• If the error persists, fail over the instance (if in an Always On Availability Group) to a secondary replica to confirm whether the issue is node-specific, then run DBCC CHECKDB on the primary to rule out underlying page corruption.

Troubleshooting Guide: SQL Server Error 13176 (“Getting Data” Component Failure)

Why This Error Happens

Error 13176 in Microsoft SQL Server 2025 typically occurs when the Policy-Based Management (PBM) or SQL Server Agent component fails to retrieve configuration data due to a broken or inaccessible metadata cache. This is often triggered by corrupted MSDB system tables, insufficient permissions on the service account accessing the “Getting Data” component, or a mismatch between the SQL Server engine version and the Management Object Framework (SMO) libraries on Windows Server 2025 / Windows 11. In some cases, this error surfaces when WMI providers used by SQL Server to enumerate configuration data are not properly registered after an OS or SQL patch update.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Configuration Manager and confirm the SQL Server Agent/Database Engine is running under a valid domain or managed service account.
• Ensure the account has db_datareader rights on the msdb database.
• Run the following query to check permissions:

  USE msdb;
  EXEC sp_helprotect NULL, 'ServiceAccountName';

Step 2: Re-register WMI Providers

• On Windows Server 2025 / Windows 11, open an elevated Command Prompt.
• Navigate to the SQL Server binary directory, typically:
  C:\Program Files\Microsoft SQL Server\170\Shared\
• Re-register the WMI provider using:

  mofcomp sqlmgmproviderxpsp2up.mof

• Restart the Windows Management Instrumentation (WMI) service.

Step 3: Repair the MSDB System Database

• Stop the SQL Server service via Services.msc or:

  NET STOP MSSQLSERVER

• Restart SQL Server in single-user mode using the -m startup parameter in Configuration Manager.
• Run a consistency check:

  DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Restart the service normally afterward.

Step 4: Update SMO and Native Client Libraries

• Confirm you are running the latest SQL Server Management Objects (SMO) package compatible with SQL Server 2025.
• Download and install the updated Microsoft ODBC Driver and Native Client from the official Microsoft Download Center.
• Reboot the server to finalize library registration.

Alternative or Verification Steps

• Open SQL Server Management Studio (SSMS), navigate to Management > Policy Management, and attempt to evaluate a policy; if no error 13176 appears, the fix was successful.
• If the issue persists, use the SQL Server Error Log (ERRORLOG file located in MSSQL17.MSSQLSERVER\MSSQL\Log\) to cross-reference timestamps and isolate whether the fault lies in WMI, MSDB, or third-party management tools, then consider running a full repair installation via the SQL Server Installation Center.

Troubleshooting Guide: SQL Server Error 13174 — Assembly Name Conflict

Why This Error Happens

Error 13174 occurs when SQL Server’s CLR integration attempts to register an assembly whose SQL name already exists in the target database’s sys.assemblies catalog, even if the underlying file name or path is different. This typically happens during deployment or redeployment of a SQLCLR project when a previous version of the assembly was not properly dropped first, or when a Visual Studio database project publish operation fails to reconcile naming metadata. It can also occur if two different .dll files were compiled with the same internal assembly name but are being registered under different physical paths.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Assembly

• Connect to the database via SQL Server Management Studio (SSMS) on your Windows Server 2025 / Windows 11 host.
• Run the following query to list existing assemblies:

  SELECT name, assembly_id, clr_name FROM sys.assemblies;

• Confirm whether the name listed matches the assembly you are trying to deploy.

Step 2: Drop the Existing Assembly Safely

• Check for dependent objects first (functions, stored procedures, or triggers tied to the assembly):

  SELECT * FROM sys.assembly_modules WHERE assembly_id = <id_from_step1>;

• Drop dependent objects if necessary, then remove the old assembly:

  DROP ASSEMBLY [YourAssemblyName];

• If the drop fails due to dependency locks, use DROP ASSEMBLY ... WITH NO_DEPENDENTS; (SQL Server 2025 syntax) to force cleanup.

Step 3: Clear Visual Studio / Build Cache Residue

• Navigate to your project’s build output folder, typically:
  C:\Users\<YourUser>\source\repos\<ProjectName>\bin\Debug\
• Delete stale .dll and .pdb files to ensure no mismatched binary is re-pushed during the next publish.
• Rebuild the solution to regenerate a clean assembly with consistent internal naming.

Step 4: Redeploy the Assembly

• Re-run the CREATE ASSEMBLY statement or republish via SQL Server Data Tools (SSDT):

  CREATE ASSEMBLY [YourAssemblyName]
  FROM 'C:\Deploy\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• Ensure the PERMISSION_SET matches your server’s clr strict security configuration (default enabled in SQL Server 2025).

Alternative or Verification Steps

• Verify success by re-querying sys.assemblies to confirm only one entry exists for the assembly name, and test the associated stored procedure/function execution.
• If the conflict persists across environments, use sp_depends or the Database Engine Tuning Advisor to detect orphaned metadata, or consider dropping and recreating the entire CLR-enabled database schema in a staging environment before pushing to production.

Troubleshooting Guide: SQL Server Error 13175 – Version Mismatch

Why This Error Happens

Error 13175 in SQL Server 2025 typically occurs when there is a version incompatibility between the SQL Server Polybase/external data components and the underlying Hadoop or external data source connector being referenced. This can result from mismatched DLL versions after an incomplete update, corrupted PolyBase configuration files, or a failed cumulative update installation that left legacy binaries in place. Additionally, this error may surface when Windows Server 2025 security policies block access to version metadata required by SQL Server’s external services layer.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Build and Patch Level

• Open SQL Server Management Studio (SSMS) and run: SELECT @@VERSION;
• Compare the returned build number against the official Microsoft SQL Server 2025 release notes to confirm you are on a supported, fully patched build.
• If outdated, download the latest Cumulative Update (CU) from the Microsoft Update Catalog and apply it.

Step 2: Repair or Reinstall PolyBase/External Data Components

• Navigate to Control Panel > Programs > Programs and Features.
• Locate Microsoft SQL Server 2025, right-click, and select Change.
• Choose the Repair option for the PolyBase Query Service and External Data components.
• Restart the service via PowerShell: Restart-Service MSSQLSERVER -Force

Step 3: Clear Corrupted Configuration Cache

• Stop the SQL Server service from SQL Server Configuration Manager.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\Polybase\Settings\ (adjust instance folder name as needed).
• Rename the polybase.settings file to polybase.settings.bak to force regeneration on restart.
• Restart the SQL Server and PolyBase Data Movement services.

Step 4: Validate Windows Server Permissions

• Open Local Security Policy (secpol.msc) on Windows Server 2025.
• Ensure the SQL Server service account has “Log on as a service” rights under Local Policies > User Rights Assignment.
• Confirm the account also has read/write access to the SQL Server installation directory to prevent version-check failures.

Alternative or Verification Steps

• To verify the fix, re-run the failing query or data import job and check the SQL Server Error Log (ERRORLOG file in the Log subfolder) for the absence of Error 13175.
• If the error persists, consider performing a full uninstall of the PolyBase feature followed by a clean reinstall via the SQL Server Installation Center, ensuring no orphaned registry keys remain under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\PolyBase.

Troubleshooting Guide: Error 13172 – GET CONVERSATION GROUP Failure in SQL Server 2025

Why This Error Happens

Error 13172 typically occurs when the Service Broker message queue referenced by the GET CONVERSATION GROUP statement has become corrupted, disabled, or is in a state where internal metadata cannot be resolved. This often results from queue corruption following an unexpected server shutdown, a damaged system catalog, or a queue that has been disabled due to poison message handling. In some cases, insufficient permissions on the queue or an inconsistency between the sys.conversation_endpoints and sys.transmission_queue tables can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue Health

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is active.
• Check if the queue is disabled due to poison messages: SELECT name, is_receive_enabled FROM sys.service_queues;
• If is_receive_enabled returns 0, the queue has been auto-disabled.

Step 2: Re-enable and Rebuild the Affected Queue

• Re-enable the queue manually using: ALTER QUEUE YourQueueName WITH STATUS = ON;
• If corruption is suspected, rebuild the queue by dropping and recreating it (ensure you back up conversation data first): DROP QUEUE YourQueueName; CREATE QUEUE YourQueueName;
• Reattach the queue to its associated service with CREATE SERVICE or ALTER SERVICE as needed.

Step 3: Run Consistency Checks on the Database

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect underlying corruption in system tables.
• On Windows Server 2025, confirm the SQL Server service account has full read/write access to the data and log file directories under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service via services.msc or Restart-Service -Name 'MSSQLSERVER' in PowerShell (run as Administrator) after applying fixes.

Step 4: Validate Permissions on Service Broker Objects

• Confirm the executing user has RECEIVE permission on the queue: GRANT RECEIVE ON YourQueueName TO [YourUserOrRole];
• Verify the user also holds VIEW DEFINITION rights on the associated conversation group if group security is restricted.

Alternative or Verification Steps

• Confirm resolution by re-running the original GET CONVERSATION GROUP query inside a RECEIVE statement; it should now return the conversation group ID without throwing Error 13172.
• If the issue persists, restore the database from a known-good backup taken before the corruption occurred, then re-apply any transaction logs to minimize data loss.

Troubleshooting Guide: SQL Server Error 13173 – Database Master Key Issues

Why This Error Happens

Error 13173 in SQL Server 2025 typically occurs when the Database Master Key (DMK) cannot be decrypted or located, often because the master key is missing, corrupted, or the Service Master Key (SMK) encryption chain has been broken. This commonly happens after a database is restored or attached to a different server instance than the one on which it was originally encrypted, causing a mismatch between the SMK and the DMK. Additionally, missing ENCRYPTION BY PASSWORD credentials or an absent backup of the master key can prevent SQL Server from opening the key automatically.

Step-by-Step Resolution Guide

Step 1: Verify the Master Key Exists in the Database

• Run the following query to check if a master key is present:

  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

• If no rows are returned, the master key does not exist and must be created using:

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword!123';

Step 2: Open the Master Key Manually

• If the key exists but cannot be decrypted automatically, force it open with:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword!123';

• If this fails, it confirms the key is corrupted or the password is incorrect/unknown.

Step 3: Restore the Master Key from Backup

• Locate your master key backup file, typically stored at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\
• Restore it using:

  RESTORE MASTER KEY FROM FILE = 'C:\Backup\DatabaseMasterKey.key'
  DECRYPTION BY PASSWORD = 'OriginalBackupPassword'
  ENCRYPTION BY PASSWORD = 'NewStrongPassword!123';

• Ensure the SQL Server service account has read permissions on this backup file path in Windows 11/Server 2025 file security settings.

Step 4: Re-Encrypt the Master Key with the Service Master Key

• To ensure automatic decryption on server restart, run:

  ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

• This re-establishes the encryption chain so SQL Server can open the key without manual intervention.

Step 5: Back Up the Master Key Immediately

• Once the key is restored or recreated, create a fresh backup to prevent future 13173 errors:

  BACKUP MASTER KEY TO FILE = 'C:\Backup\DatabaseMasterKey_New.key'
  ENCRYPTION BY PASSWORD = 'SecureBackupPassword!456';

• Store this file securely outside the SQL Server directory structure.

Alternative or Verification Steps

• Verify the fix by restarting the SQL Server 2025 service via services.msc and confirming the database opens without triggering Error 13173 in the SQL Server Error Log.
• If the issue persists, check for Extensible Key Management (EKM) module conflicts or validate that the Service Master Key itself is intact using DBCC CHECKDB and SMK backup/restore procedures.

Troubleshooting Guide: SQL Server Error 13171 — Conversation Handle Corruption

Why This Error Happens

Error 13171 occurs when the Service Broker message payload fails internal validation, typically because the conversation handle references a dialog whose underlying metadata has become corrupted or desynchronized in the sys.conversation_endpoints system view. This is often triggered by abrupt transaction rollbacks during active dialog exchanges, improper handling of END CONVERSATION calls, or corruption introduced by unexpected service restarts on Windows Server 2025. In some cases, it also stems from mismatched Service Broker GUIDs after a database restore or mirroring failover where the broker instance ID was not properly regenerated.

Step-by-Step Resolution Guide

Step 1: Identify the Corrupted Conversation Handle

• Run the following query to locate problematic conversations: SELECT * FROM sys.conversation_endpoints WHERE state = 'ER';
• Note the conversation_handle GUID(s) returned, as these are the endpoints in the “Error” state causing the 13171 exception.
• Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp correlating to the application failure.

Step 2: End the Faulty Conversation Manually

• Execute a targeted cleanup using the handle identified in Step 1: END CONVERSATION 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' WITH CLEANUP;
• If the standard END CONVERSATION fails due to the corruption, force the cleanup using the WITH CLEANUP argument to bypass the standard acknowledgment handshake.
• Verify the Service Broker is currently enabled on the database by running: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

Step 3: Reset the Service Broker GUID (If Corruption Persists)

• If multiple conversation handles are failing simultaneously, the internal Broker ID is likely mismatched from a restore operation.
• Open a new query window in SQL Server Management Studio (SSMS) connected to the instance.
• Execute the following command to generate a new internal identifier and sever ties with the corrupted conversation history: ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
• Note: This command is destructive to existing message queues, so ensure no pending business-critical messages exist in the sys.transmission_queue before proceeding.

Step 4: Restart the SQL Server Service

• Open the Services console on Windows Server 2025 by running services.msc.
• Locate SQL Server (MSSQLSERVER) or your specific named instance.
• Right-click and select Restart to clear any cached broker metadata held in memory that might still reference the old handle.

Alternative or Verification Steps

• Verification: Re-run the SELECT * FROM sys.conversation_endpoints query; the environment is stable once the list returns zero rows with a state of ER (Error) or RO (Return-bound).
• Alternative Fix: If errors persist immediately after a restart, check for orphaned queue readers by querying sys.dm_broker_activated_tasks, as a stuck activation stored procedure can continuously regenerate the 13171 error even after the handle is cleared.

Why This Error Happens

Error 13169 typically occurs when the database mirroring endpoint cannot authenticate the connection between the principal and mirror server instances. This is most commonly caused by a certificate mismatch, an expired or improperly configured mirroring endpoint, or the SQL Server service account lacking the necessary permissions to establish the TCP connection on the mirroring port (default 5022). Corrupted or mismatched master keys between instances can also trigger this failure during the handshake phase.

Step-by-Step Resolution Guide

Step 1: Verify the Mirroring Endpoint Status

• Run SELECT * FROM sys.database_mirroring_endpoints; on both the principal and mirror instances.
• Confirm the state_desc column shows STARTED on both servers.
• If stopped, restart it using: ALTER ENDPOINT [Mirroring] STATE = STARTED;

Step 2: Validate Certificate and Authentication Configuration

• Check that the certificates used for endpoint authentication have not expired by querying sys.certificates.
• Ensure the public key of the principal’s certificate is correctly imported on the mirror server (and vice versa) using CREATE CERTIFICATE ... FROM FILE.
• If using Windows Authentication instead of certificates, confirm both SQL Server service accounts have CONNECT permission on the endpoint: GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\ServiceAccount];

Step 3: Check Network and Firewall Configuration

• Confirm port 5022 (or your custom mirroring port) is open in Windows Defender Firewall on both Windows Server 2025 nodes.
• Use Test-NetConnection -ComputerName <MirrorServerName> -Port 5022 in PowerShell to validate connectivity.
• Update firewall rules via: New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

Step 4: Re-establish the Mirroring Session

• Drop and recreate the mirroring configuration if the endpoint state cannot be corrected:
• ALTER DATABASE [YourDB] SET PARTNER OFF;
• Reconfigure mirroring using ALTER DATABASE [YourDB] SET PARTNER = 'TCP://MirrorServer:5022'; after confirming certificates and endpoints are healthy.

Alternative or Verification Steps

• After applying the fix, check the SQL Server error log and sys.database_mirroring DMV to confirm mirroring_state_desc shows SYNCHRONIZED.
• If the issue persists, drop and recreate the mirroring endpoint entirely on both servers, then re-exchange certificates before re-establishing the partnership.

Why This Error Happens

Error 13170 occurs when SQL Server 2025 attempts to enable Change Tracking (or a similar metadata-dependent feature) on a table or indexed view that does not meet the structural requirements—most commonly, the absence of a valid primary key or unique clustered index. This can also surface if the underlying system metadata for tracked objects has become corrupted or desynchronized after a failed schema migration. In some cases, insufficient permissions on the sys.change_tracking_tables catalog view prevent SQL Server from validating the table definition correctly.

Step-by-Step Resolution Guide

Step 1: Verify the Primary Key Constraint

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to confirm a primary key exists:

  SELECT OBJECT_NAME(object_id) AS TableName, name AS ConstraintName
  FROM sys.key_constraints
  WHERE type = 'PK' AND OBJECT_NAME(object_id) = 'YourTableName';

• If no rows are returned, the table or indexed view lacks the required primary key/unique clustered index needed for change tracking.

Step 2: Add or Repair the Missing Key

• Create a primary key or unique clustered index on the table:

  ALTER TABLE YourTableName
  ADD CONSTRAINT PK_YourTableName PRIMARY KEY CLUSTERED (YourColumnName);

• For indexed views, ensure a unique clustered index exists on the view itself, as SQL Server 2025 enforces this strictly for change tracking eligibility.

Step 3: Validate Catalog Metadata Integrity

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption in system catalogs.
• If corruption is detected, restore the affected database from the last known good backup rather than attempting in-place repair on production systems.

Step 4: Re-Enable Change Tracking

• Once the key constraint is in place, re-run the enabling command:

  ALTER TABLE YourTableName
  ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

• Confirm the database itself has change tracking enabled at the database level first:

  ALTER DATABASE YourDatabaseName
  SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

Step 5: Check Service Account Permissions

• Ensure the SQL Server service account (running under Windows Server 2025 / Windows 11) has ALTER and VIEW CHANGE TRACKING permissions on the target schema.
• Use GRANT VIEW CHANGE TRACKING ON OBJECT::YourTableName TO [YourServiceAccount]; if permissions are missing.

Alternative or Verification Steps

• Verify the fix by querying sys.change_tracking_tables—the table should now appear in the result set without throwing Error 13170.
• If the issue persists after adding a primary key, drop and recreate the table’s clustered index entirely, as a fragmented or disabled index can still trigger this error even when a primary key is technically present.

Why This Error Happens

Error 13168 in Service Broker occurs when SQL Server attempts to locate a certificate, asymmetric key, or private key file for dialog security authentication but cannot access it. This typically happens because the certificate was created in a different database context, the private key file path has changed, or the service account lacks NTFS permissions to read the key file. In some cases, this also surfaces after a database restore or migration when certificate metadata references a file path that no longer exists on the new Windows Server 2025 host.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Existence in the Database

• Run SELECT name, pvt_key_encryption_type_desc FROM sys.certificates; in the affected database to confirm the certificate referenced by Service Broker still exists.
• Check that the certificate name matches exactly what is specified in your CREATE REMOTE SERVICE BINDING or CREATE USER ... WITH CERTIFICATE statements.
• Confirm the certificate has not expired by checking the expiry_date column in sys.certificates.

Step 2: Check File System Permissions on the Private Key

• Navigate to the folder where the private key backup file resides, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .pvk file, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read access.
• If the file was moved during a server migration to Windows Server 2025, re-import it using:

  CREATE CERTIFICATE MyCert
  FROM FILE = 'C:\Certs\MyCert.cer'
  WITH PRIVATE KEY (FILE = 'C:\Certs\MyCert.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword');

Step 3: Re-establish the Master Key Encryption

• Open a new query window and run OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourMasterKeyPassword'; to ensure the Database Master Key (DMK) is accessible.
• If the DMK is missing or corrupted, recreate it with CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewStrongPassword123!';.
• Re-link the certificate to the master key using ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; if cross-server authentication is required.

Step 4: Restart Service Broker and Validate Endpoints

• Disable and re-enable Service Broker on the database using:

  ALTER DATABASE YourDB SET DISABLE_BROKER;
  ALTER DATABASE YourDB SET ENABLE_BROKER;

• Confirm the Service Broker endpoint is listening by checking SELECT * FROM sys.service_broker_endpoints; for an “Started” state.
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached certificate handles.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker queue and confirming it reaches the target service without triggering Error 13168 in the SQL Server error log.
• If the issue persists, drop and recreate the certificate entirely using a freshly exported .cer/.pvk pair, then re-grant CONNECT permissions on the Service Broker endpoint to the relevant database user.

Why This Error Happens

Error 13166 occurs within SQL Server’s Service Broker feature and typically indicates that the engine cannot locate or lock the specified conversation group, often because the underlying conversation has already ended or the group ID referenced no longer exists in sys.conversation_endpoints. This frequently happens due to a race condition where multiple RECEIVE statements attempt to access the same queue simultaneously, or because the message queue has become corrupted during a service restart. In Windows Server 2025 environments, this is sometimes exacerbated by aggressive memory paging settings that cause transaction log flushes to lag behind Broker message processing.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue Health

• Open SQL Server Management Studio (SSMS) and run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName'; to confirm Broker is active.
• Check for orphaned conversations using SELECT * FROM sys.conversation_endpoints WHERE state = 'DI'; (Disconnected Inbound errors often precede 13166).
• If orphaned endpoints exist, note their conversation_handle for cleanup in Step 2.

Step 2: End Faulty Conversations and Clear the Queue

• Execute END CONVERSATION 'conversation_handle' WITH CLEANUP; for any handles identified as corrupted or stuck.
• Navigate to the specific queue (e.g., dbo.TargetQueue) and run ALTER QUEUE TargetQueue WITH STATUS = OFF; followed immediately by ALTER QUEUE TargetQueue WITH STATUS = ON; to force a queue reset.
• Restart the SQL Server Service Broker task via ALTER DATABASE YourDBName SET NEW_BROKER; (Use with caution: this generates a new Service Broker identifier).

Step 3: Validate Windows Server 2025 Permissions and Firewall

• Ensure the SQL Server Service Account has “Lock pages in memory” rights via secpol.msc > Local Policies > User Rights Assignment, as Windows Server 2025 security baselines often strip this by default.
• Check that TCP Port 4022 (the default Broker endpoint) is not blocked by the Windows Defender Firewall for inter-server conversations.
• Run netstat -ano | findstr 4022 in PowerShell to confirm the port is listening correctly.

Step 4: Rebuild the Transmission Queue

• If errors persist, query sys.transmission_queue to check for transmission_status errors unrelated to the 13166 code that might be blocking the group.
• Restart the SQL Server (MSSQLSERVER) service via services.msc to flush the transient Service Broker cache.

Alternative or Verification Steps

• Verification: Run a test SEND ON CONVERSATION command and check sys.conversation_endpoints to ensure the state column transitions successfully from CO (Conversing) to CD (Closed) without re-triggering Error 13166.
• Alternative: If the error continues after a queue reset, back up the database, detach it, and re-attach it to force SQL Server 2025 to rebuild the internal Broker message routing table from scratch.

Troubleshooting Guide: SQL Server Error 13167

Why This Error Happens

Error 13167 typically occurs during Change Data Capture (CDC) or Service Broker operations when SQL Server 2025 cannot validate or synchronize metadata between the database engine and an associated external process, such as a capture job or replication agent. This is commonly caused by a corrupted system catalog entry, a permissions mismatch for the SQL Server service account, or a mismatch between database compatibility levels after an in-place upgrade to SQL Server 2025. In some cases, this error also surfaces when Windows Server 2025 security policies block the service account from accessing required registry or file system paths.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server (MSSQLSERVER) service is running under the correct domain or local service account.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA and ensure the service account has Full Control permissions.
• Restart the SQL Server service after applying permission changes:

  net stop MSSQLSERVER && net start MSSQLSERVER

Step 2: Rebuild or Repair the System Catalog Metadata

• Open SQL Server Management Studio (SSMS) and run a consistency check against the affected database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOREPAIR;

• If corruption is confirmed, run:

  DBCC CHECKDB ('YourDatabaseName') WITH REPAIR_REBUILD;

• Avoid using REPAIR_ALLOW_DATA_LOSS unless a verified backup exists.

Step 3: Validate Compatibility Level and CDC/Broker Configuration

• Check the database compatibility level to ensure it aligns with SQL Server 2025 standards (level 170):

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If using Change Data Capture, disable and re-enable it to force a metadata refresh:

  EXEC sys.sp_cdc_disable_db;
  EXEC sys.sp_cdc_enable_db;

Step 4: Update SQL Server Cumulative Updates

• Confirm you are running the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or the Microsoft Update Catalog.
• Install any pending CU and reboot Windows Server 2025 if prompted.

Alternative or Verification Steps

• To verify the fix, re-run the failing job (CDC capture, Broker queue, or replication task) and check the SQL Server Error Log and Windows Event Viewer (Application log) for the absence of Error 13167.
• If the error persists, restore the database from a known-good backup taken prior to the SQL Server 2025 upgrade, then reapply transaction logs to minimize data loss.

Troubleshooting Guide: SQL Server Error 13165 (“Getting Data” / Conversation Endpoint Failure)

Why This Error Happens

Error 13165 in SQL Server 2025 is typically thrown by the Service Broker subsystem when a conversation endpoint cannot be established or maintained between two dialog participants. This usually occurs because the message body exceeds the negotiated transport buffer size, the endpoint certificate used for dialog security has expired or mismatched, or the underlying TCP transport was interrupted mid-handshake while “Getting Data.” In Windows Server 2025 environments, this is frequently compounded by stricter TLS 1.3 enforcement and Windows Defender Firewall rules blocking the Service Broker port (default 4022).

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration and Port Availability

• Open SQL Server Configuration Manager and confirm the Service Broker endpoint is listening (default TCP 4022).
• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB'; in SSMS to confirm Broker is active.
• Check Windows Defender Firewall on Windows Server 2025: New-NetFirewallRule -DisplayName "SQL Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow.

Step 2: Inspect and Repair the Conversation Endpoint

• Query sys.conversation_endpoints to locate endpoints stuck in DI (Disconnected Inbound) or ERROR state.
• Run END CONVERSATION 'conversation_handle' WITH CLEANUP; to forcibly terminate corrupted handles.
• Check the sys.transmission_queue table for stalled messages causing the data retrieval timeout.

Step 3: Validate Certificates and Security Context

• Confirm the dialog security certificate has not expired: SELECT name, expiry_date FROM sys.certificates;.
• Re-issue certificates using CREATE CERTIFICATE and re-grant CONNECT permission on the Broker endpoint to the relevant database user.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER to reload the Service Broker manager after certificate updates.

Step 4: Clear Corrupted Message Types and Queues

• Validate the XML SCHEMA COLLECTION tied to the message type isn’t corrupted, which can halt “Getting Data” mid-transfer.
• Run DBCC CHECKDB on the affected database to rule out underlying page corruption in the queue tables.
• Rebuild the queue using ALTER QUEUE [YourQueue] WITH STATUS = OFF; followed by STATUS = ON; to reset the internal state machine.

Alternative or Verification Steps

• To verify the fix, initiate a test conversation using BEGIN DIALOG CONVERSATION and SEND ON CONVERSATION between two test queues; a successful RECEIVE without a 13165 error confirms resolution.
• If the error persists, as an alternative, drop and recreate the Service Broker route and remote service binding entirely, as residual metadata corruption in the routing table is a common root cause on freshly migrated Windows Server 2025 instances.

Troubleshooting Guide: SQL Server Error 13164 (SEND Failure)

Why This Error Happens

Error 13164 typically occurs when the SQL Server Service Broker attempts to execute a SEND command on a conversation that has been corrupted, prematurely closed, or is referencing a dropped queue or service. This is often caused by inconsistent Service Broker configurations, a disabled broker GUID on the database (common after restoring a database or failover in Windows Server 2025 clustering), or network timeouts between endpoints. Additionally, insufficient permissions on the SEND or RECEIVE database objects can trigger this error during cross-database or cross-instance messaging.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check the broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Check for Orphaned or Corrupted Conversations

• Query the sys.conversation_endpoints table to identify conversations stuck in an ER (Error) or CD (Closing) state.

  SELECT * FROM sys.conversation_endpoints WHERE state = 'ER';

• If found, terminate the specific problematic conversation handle to clear the stuck state:

  END CONVERSATION 'conversation_handle_GUID' WITH CLEANUP;

Step 3: Validate Windows Firewall and Endpoint Permissions

• On Windows Server 2025, navigate to Windows Defender Firewall with Advanced Security.
• Ensure the specific TCP port used by the Service Broker Endpoint (default is often 4022, but verify your custom config) is open for Inbound and Outbound rules.
• Verify the GRANT SEND permission is correctly assigned to the specific service or user account attempting the transaction:

  GRANT SEND ON SERVICE::[YourServiceName] TO [public];

Step 4: Restart the SQL Server Service

• Open the Services app (services.msc) on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or your specific named instance.
• Right-click and select Restart to clear any cached broker session locks that might be causing the “Getting Data” hang.

Alternative or Verification Steps

• Verification: Re-run the application process that triggered the “Getting Data” hang. Check the SQL Server log via EXEC sp_readerrorlog; to confirm no new 13164 entries have been logged.
• Alternative Fix: If the error persists, try dropping and recreating the specific Queue and Service associated with the error, as a schema-level corruption in the queue definition may require a full rebuild rather than a simple cleanup.

Why This Error Happens

Error 13162 occurs within SQL Server’s Service Broker architecture when a conversation endpoint enters an error state that cannot be resolved through normal message exchange, forcing the engine to terminate it using the WITH CLEANUP option. This typically happens because the corresponding dialog on the remote endpoint has already been dropped, the message payload failed contract validation, or the sys.conversation_endpoints catalog view contains orphaned entries due to a crashed transaction. In Windows Server 2025 / Windows 11 environments, this is frequently exacerbated by antivirus or Windows Defender real-time scanning interfering with the .mdf/.ldf I/O operations that Service Broker relies on for transactional consistency.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Conversation Handles

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate endpoints stuck in an error state:

SELECT conversation_handle, far_service, state_desc, lifetime 
FROM sys.conversation_endpoints 
WHERE state_desc = 'ERROR';

• Note the conversation_handle GUIDs returned; these are the sessions causing the 13162 event log entries.

Step 2: Force-Terminate the Problematic Dialogs

• For each handle identified in Step 1, execute a manual cleanup command to clear the queue:

END CONVERSATION 'handle-guid-here' WITH CLEANUP;

• If the command hangs, verify no active transactions are locking the sys.transmission_queue by checking sys.dm_tran_locks.
• Run DBCC CHECKDB on the specific database if cleanup fails repeatedly, as this often indicates underlying page corruption on the system tables.

Step 3: Adjust Windows Server Exclusions and Service Broker Settings

• Navigate to Windows Security > Virus & threat protection > Manage settings > Add or remove exclusions.
• Add the SQL Server data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to prevent I/O locks during the conversation handshake.
• Restart the SQL Server Service Broker dependency by restarting the SQL Server service via services.msc to flush the transmission queue.

Step 4: Validate Network Timeout Configurations

• Check the Dialog Timer settings within your Service Broker queue to ensure timeouts are not too aggressive for the network latency in Windows Server 2025 virtualized environments.
• Update the QUEUE definition to include ACTIVATION procedures that handle Error message types gracefully to prevent future forced cleanups.

Alternative or Verification Steps

• Verify the fix by re-running the sys.conversation_endpoints query from Step 1; the result set should return zero rows with state_desc = 'ERROR'.
• If the issue persists across reboots, check the SQL Server Error Log for Event ID 9675, which often precedes 13162 and points to a specific certificate or encryption mismatch between the initiator and target databases.

Troubleshooting Guide: SQL Server Error 13163 on BEGIN DIALOG CONVERSATION

Why This Error Happens

Error 13163 occurs when Service Broker attempts to initiate a dialog conversation but cannot locate a valid route or matching contract/service binding between the initiator and target services. This typically stems from a missing or misconfigured SERVICE, CONTRACT, or MESSAGE TYPE definition, or because the Service Broker endpoint lacks the proper routing information in sys.routes. In multi-database or cross-instance scenarios, this can also happen if Service Broker is disabled on one of the participating databases, preventing the dialog handshake from completing.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check the status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: In Windows Server 2025 environments, ensure no active snapshot or mirroring session is blocking this change.

Step 2: Confirm Contracts, Services, and Message Types Exist

• Validate that the service and contract referenced in BEGIN DIALOG CONVERSATION actually exist:

  SELECT * FROM sys.service_contracts;
  SELECT * FROM sys.services;

• Check that the ON CONTRACT clause in your T-SQL script matches an existing contract name exactly (case-sensitive if using a case-sensitive collation).
• Recreate the missing contract or service object if it was dropped or never deployed during migration.

Step 3: Inspect and Repair Service Broker Routes

• Query the routing table to confirm a valid path exists between initiator and target:

  SELECT * FROM sys.routes;

• If no route exists for the target service, create one:

  CREATE ROUTE RouteName
      WITH SERVICE_NAME = 'TargetServiceName',
      ADDRESS = 'TCP://TargetServerName:4022';

• Ensure Windows Firewall on Windows Server 2025 allows inbound/outbound traffic on port 4022 (default Service Broker port).

Step 4: Validate Security and Certificate Configuration

• If conversations cross security boundaries, confirm that master keys and certificates used for Service Broker authentication are valid and not expired.
• Check sys.certificates and sys.service_broker_connections for authentication failures.
• Re-issue certificates if expired, and re-establish trust between endpoints.

Alternative or Verification Steps

• Verify the fix by re-running the BEGIN DIALOG CONVERSATION command and confirming a valid conversation_handle is returned without error.
• As an alternative, enable Service Broker diagnostic tracing via Extended Events (sqlserver.broker_conversation and sqlserver.broker_connection events) to capture granular failure details if the error persists after configuration changes.

Troubleshooting Guide: SQL Server Error 13161 — “Fire Conversation Timer” Failure

Why This Error Happens

Error 13161 occurs when the Service Broker’s internal timer mechanism responsible for firing conversation timers encounters a corrupted or inconsistent state in the sys.conversation_endpoints table, often due to abrupt service termination or disk I/O interruption during a timer event. This can also result from insufficient permissions on the Service Broker queue, preventing the timer activation stored procedure from executing. Additionally, a misconfigured or disabled Service Broker on the database (is_broker_enabled = 0) can cause the timer dispatch to fail silently and then surface as error 13161 when conversations attempt to time out.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status on the Database

• Run the following query to check if Service Broker is enabled:
• SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, enable it with:
• ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 2: Check and Repair Conversation Endpoints

• Query for orphaned or corrupted conversation handles:
• SELECT * FROM sys.conversation_endpoints WHERE state = 'ER' OR lifetime < GETDATE();
• End any stuck conversations manually using:
• END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.

Step 3: Validate Permissions on Service Broker Objects

• Ensure the service account running SQL Server 2025 has CONTROL permission on the queue:
• GRANT CONTROL ON QueueName TO [DOMAIN\ServiceAccount];
• Confirm the login has VIEW SERVER STATE and ALTER ANY MESSAGE TYPE permissions at the server level.

Step 4: Restart SQL Server Service and Clear Timer Cache

• On Windows Server 2025 / Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or the named instance, right-click, and select Restart.
• Alternatively, use PowerShell:
• Restart-Service -Name 'MSSQLSERVER' -Force
• This clears any stale in-memory timer references tied to Error 13161.

Step 5: Apply Latest Cumulative Update

• Navigate to Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025.
• Install the patch in a maintenance window, as several CUs address Service Broker timer race conditions.
• Reboot the server if prompted by the installer.

Alternative or Verification Steps

• To verify the fix, create a test conversation timer using BEGIN CONVERSATION TIMER and confirm it fires correctly by monitoring the sys.transmission_queue and SQL Server error log for absence of Error 13161.
• If the issue persists, as an alternative, drop and recreate the affected Service Broker queue and service definitions entirely, then re-link them to the associated contracts and message types.

Troubleshooting Guide: SQL Server Error 13159 (Countersignature Validation Failure)

Why This Error Happens

Error 13159 occurs when SQL Server 2025 fails to validate the countersignature on a module, assembly, or stored procedure during execution or catalog verification. This typically happens because the certificate used to countersign the object has expired, been revoked, or is missing from the database’s certificate store. Corruption in the signature metadata within sys.crypt_properties, or a mismatch between the signing certificate’s thumbprint and the one registered at signing time, can also trigger this failure on Windows Server 2025 / Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate and Signature Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to inspect existing signatures:

SELECT * FROM sys.crypt_properties;
SELECT * FROM sys.certificates WHERE name LIKE '%YourCertName%';

• Check the expiry_date column — if the certificate has expired, this is the likely root cause.

Step 2: Re-Sign the Module with a Valid Certificate

• If the certificate is expired or invalid, generate a new one:

CREATE CERTIFICATE NewCounterSignCert
   WITH SUBJECT = 'Countersignature Recovery',
   EXPIRY_DATE = '2027-01-01';

• Drop the old countersignature and reapply using the new certificate:

ADD COUNTER SIGNATURE TO MODULE::YourModuleName
   WITH CERTIFICATE NewCounterSignCert;

• Ensure the certificate private key is backed up to a secure path, e.g., C:\SQLCertBackups\NewCounterSignCert.pvk.

Step 3: Check Windows Certificate Store Permissions

• Open certlm.msc on Windows Server 2025 / Windows 11.
• Navigate to Personal > Certificates and confirm the SQL Server service account has read access to the private key.
• Right-click the certificate > All Tasks > Manage Private Keys and grant the NT SERVICE\MSSQLSERVER account “Read” permission if missing.

Step 4: Restart SQL Server Services and Re-validate

• Restart the SQL Server service via services.msc or run:

Restart-Service -Name 'MSSQLSERVER' -Force

• Re-run the module or query that triggered Error 13159 to confirm resolution.

Alternative or Verification Steps

• Verify the fix by querying sys.crypt_properties again to confirm the new signature’s thumbprint matches the active certificate, and confirm the module executes without raising Error 13159.
• If the issue persists, drop all existing signatures on the object using DROP COUNTER SIGNATURE and DROP SIGNATURE, then fully re-sign the module from scratch rather than attempting an incremental repair.

Troubleshooting Guide: SQL Server Error 13160 — BEGIN CONVERSATION TIMER

Why This Error Happens

Error 13160 occurs when the BEGIN CONVERSATION TIMER statement references a conversation handle that does not exist, has already been ended, or belongs to a Service Broker conversation that was never properly initiated. This typically happens due to a mismatched or expired @conversationHandle GUID being passed from application code, or because the conversation was already closed by an END CONVERSATION call before the timer statement executed. In some cases, this is compounded by Service Broker being disabled on the database, causing handles to be invalidated prematurely.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check the status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Ensure no other active connections are blocking this change; use WITH ROLLBACK IMMEDIATE if necessary in a maintenance window.

Step 2: Validate the Conversation Handle Before Setting the Timer

• Check whether the handle exists in sys.conversation_endpoints:

  SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = 'YourHandleGUID';

• If no rows are returned, the conversation has already ended or was never created — confirm your application logic calls BEGIN DIALOG CONVERSATION successfully before invoking the timer.
• Wrap the timer call in a TRY...CATCH block to gracefully handle Error 13160 in production code.

Step 3: Check for Premature END CONVERSATION Calls

• Review application and stored procedure logic for any END CONVERSATION @handle statements executing before the BEGIN CONVERSATION TIMER line.
• Add logging around dialog lifecycle events to trace handle creation and termination timestamps.
• On Windows Server 2025/Windows 11, confirm SQL Server Agent jobs or scheduled tasks aren’t triggering cleanup scripts that end conversations early.

Step 4: Review SQL Server Error Logs and Service Broker Configuration

• Open SQL Server Configuration Manager and confirm the SQL Server service account has proper permissions on the instance.
• Check SQL Server Logs via SSMS (Management > SQL Server Logs) for related Service Broker errors (e.g., Error 9617 or 9666) that may indicate deeper corruption.
• If corruption is suspected, run:

  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Alternative or Verification Steps

• To verify the fix, re-run the BEGIN CONVERSATION TIMER statement immediately after a successful BEGIN DIALOG CONVERSATION call and confirm no error is thrown.
• If the issue persists, drop and recreate the Service Broker queue and service objects (CREATE QUEUE, CREATE SERVICE) to rule out metadata corruption, then retest the full conversation lifecycle.

Why This Error Happens

Error 13157 occurs within SQL Server Service Broker when a dialog conversation is forcibly closed, typically because the receiving endpoint sent an “END CONVERSATION WITH ERROR” message or because the conversation timed out due to an unhandled exception in the processing stored procedure. This is often triggered by malformed message payloads, missing error handling in activation procedures, or a mismatch between the contract and the message type being sent. In Windows Server 2025 / Windows 11 environments, this can also surface due to corrupted Service Broker queues after an improper shutdown or failed database restore.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Conversation Handle

• Query sys.transmission_queue to view the exact error message tied to the conversation_handle.
• Run: SELECT conversation_handle, transmission_status FROM sys.transmission_queue;
• Cross-reference the transmission_status column text, which usually contains the root cause (e.g., “contract mismatch” or “message body cannot be parsed”).

Step 2: Review and Patch the Activation Stored Procedure

• Open the stored procedure bound to the queue via ALTER QUEUE definition in SQL Server Management Studio (SSMS).
• Ensure a TRY...CATCH block wraps all RECEIVE statements to prevent unhandled exceptions from silently ending the conversation.
• Add explicit handling for END CONVERSATION and error message types alongside your standard message types.

Step 3: Validate Service Broker Configuration and Permissions

• Confirm Service Broker is enabled on the database: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
• If disabled, run: ALTER DATABASE YourDB SET ENABLE_BROKER;
• Verify the SQL Server service account (under Windows Server 2025 services manager) has appropriate permissions on the queue objects (CONTROL, RECEIVE, SEND).

Step 4: Clear Poisoned Messages and Reset the Queue

• Check if the queue is disabled due to poison message handling: SELECT is_receive_enabled FROM sys.service_queues WHERE name = 'YourQueueName';
• If disabled, re-enable it: ALTER QUEUE YourQueueName WITH STATUS = ON;
• Restart the SQL Server service via services.msc on Windows Server 2025 to clear any cached broker session state.

Alternative or Verification Steps

• Verify the fix by sending a test message through the conversation and confirming it processes without appearing in sys.transmission_queue.
• If the issue persists, drop and recreate the Service Broker queue, service, and contract objects entirely, then re-initiate the conversation from the initiator endpoint.

Troubleshooting Guide: SQL Server Error 13158 (Service Broker Error)

Why This Error Happens

Error 13158 typically occurs when the Service Broker in SQL Server 2025 encounters an issue with endpoint configuration, certificate validation, or message routing between databases. This is commonly caused by a mismatch in the Service Broker GUID after a database restore or copy operation, expired or invalid certificates used for dialog security, or a disabled/misconfigured Broker endpoint that prevents proper message transmission. In some cases, firewall rules on Windows Server 2025 or Windows 11 block the TCP port assigned to the Broker endpoint, triggering this error during conversation initiation.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check the Broker status:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Check and Repair the Service Broker GUID

• If the database was restored or copied, the Broker GUID may be mismatched or duplicated.
• Run this command to generate a new Service Broker ID:

ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• Alternatively, if you need to preserve existing conversations, use:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 3: Validate the Broker Endpoint and Certificate Configuration

• Confirm the Broker endpoint exists and is in a “Started” state:

SELECT name, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

• If no endpoint exists, create one on the default Broker port (4022):

CREATE ENDPOINT ServiceBrokerEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);

• Check that any associated certificates have not expired by querying sys.certificates.

Step 4: Open Required Ports in Windows Firewall

• On Windows Server 2025 or Windows 11, navigate to Windows Defender Firewall with Advanced Security.
• Create a new Inbound Rule allowing TCP traffic on port 4022 (or your custom Broker port).
• Ensure the rule applies to the sqlservr.exe process and is scoped to the correct network profile (Domain/Private).

Step 5: Restart the SQL Server Service

• Open SQL Server Configuration Manager.
• Right-click the SQL Server (MSSQLSERVER or named instance) service and select Restart.
• Monitor the SQL Server Error Log immediately after restart to confirm Broker initialization succeeds without re-throwing Error 13158.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.transmission_queue; — this table should show no persistent stuck messages or growing error counts related to the affected conversation handle.
• If the error persists, drop and recreate the affected Service Broker queue and service objects (DROP QUEUE, DROP SERVICE) and redeploy them via your original T-SQL deployment script to rule out object-level corruption.

Troubleshooting Guide: SQL Server Error 13156 (Message Acknowledgement Failure)

Why This Error Happens

Error 13156 in SQL Server 2025 typically occurs within Service Broker when a message acknowledgement cannot be processed due to a conversation endpoint being stuck in a corrupted or inconsistent state. This often results from network interruptions between dialog endpoints, mismatched contract definitions, or a corrupted transmission queue following an unexpected service restart. Additionally, insufficient permissions on the SEND or RECEIVE operations for the Service Broker queue can prevent the acknowledgement handshake from completing.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration and Status

• Open SQL Server Management Studio (SSMS) and run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName'; to confirm Broker is active.
• If disabled, enable it using ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check for orphaned conversations using SELECT * FROM sys.conversation_endpoints WHERE state = 'DI'; (Disconnected Inbound).

Step 2: Clear the Transmission Queue and Reset Endpoints

• Navigate to the database engine and query sys.transmission_queue to identify stuck messages causing the 13156 block.
• If messages are stalled, end the specific conversation using END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the SQL Server Broker service via PowerShell: Restart-Service -Name MSSQLSERVER -Force (Note: This restarts the full instance; schedule during a maintenance window).

Step 3: Validate Windows Firewall and Port Permissions

• On Windows Server 2025, check if the specific Broker port (default 4022) is blocked.
• Run netsh advfirewall firewall show rule name="SQL Server Broker" to verify the rule exists.
• If missing, add a new inbound rule: netsh advfirewall firewall add rule name="SQL Server Broker" dir=in action=allow protocol=TCP localport=4022

Step 4: Check Certificate and Permission Validity

• Verify that the SERVICE and CONTRACT permissions are granted to the specific user account attempting the acknowledgement.
• Run GRANT SEND ON SERVICE::[YourServiceName] TO [UserName]; to ensure the account has rights to send the acknowledgement packet.
• Check certificate expiration dates if using encrypted dialogues via SELECT name, expiry_date FROM sys.certificates;

Alternative or Verification Steps

• Verification: Monitor the sys.dm_broker_queue_monitors dynamic management view for 10-15 minutes to ensure the queue status returns to “INACTIVE” without new ON_ERROR triggers appearing in the SQL Server Error Log.
• Alternative Fix: If the error persists after clearing the queue, disable and re-enable the Broker on the database using ALTER DATABASE YourDBName SET NEW_BROKER; (Warning: This action will reset the Service Broker GUID and terminate all existing conversations).

Troubleshooting Guide: SQL Server Error 13154 — “Receive End Conversation” Error

Why This Error Happens

Error 13154 occurs within SQL Server Service Broker when a conversation endpoint receives an EndConversation message that it cannot process correctly, often due to a corrupted or malformed message in the sys.transmission_queue. This typically stems from mismatched or missing message type bindings in the contract, orphaned conversation handles left behind after an unclean shutdown, or security context failures between the initiator and target services. In SQL Server 2025 running on Windows Server 2025/Windows 11, this is frequently compounded by stale certificate authentication used for Service Broker dialog security.

Step-by-Step Resolution Guide

Step 1: Inspect the Transmission Queue for Poison Messages

• Run the following query to identify stuck or malformed messages:
• SELECT * FROM sys.transmission_queue;
• Look for a non-NULL value in the transmission_status column, which usually contains the raw text of Error 13154.
• Note the conversation_handle GUID associated with the failing message for later cleanup.

Step 2: Validate Service Broker Configuration and Routes

• Confirm Service Broker is enabled on the database:
• SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• Check that the CONTRACT and MESSAGE TYPE definitions match exactly between the initiator and target queues.
• Verify routes using SELECT * FROM sys.routes; to ensure the ADDRESS is not pointing to a decommissioned server name (a common issue after migrating from Windows Server 2019/2022 to Windows Server 2025).

Step 3: Clean Up Orphaned Conversations

• End the problematic conversation manually using the handle identified in Step 1:
• END CONVERSATION 'conversation_handle_GUID' WITH CLEANUP;
• Restart the SQL Server Service Broker task via SSMS or using ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER; to reset the internal activation state.

Step 4: Refresh Security Certificates

• Since Windows 11/Server 2025 enforces stricter TLS 1.3 defaults, ensure your Service Broker certificates haven’t expired.
• Navigate to Security > Certificates in SSMS Object Explorer to check the EXPIRY_DATE.
• If expired, re-issue the certificate using CREATE CERTIFICATE and re-authorize the login via GRANT CONNECT ON ENDPOINT.

Alternative or Verification Steps

• Verify the Fix: Query sys.transmission_queue again; it should return zero rows or show a NULL transmission status, confirming messages are flowing without error.
• Alternative Solution: If the error persists due to a corrupted queue structure, consider rebuilding the Service Broker GUID using ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE (Note: this action will discard all current conversations in the queue).

Troubleshooting Guide: SQL Server Error 13155 (Service Broker Error)

Why This Error Happens

Error 13155 typically occurs when the Service Broker in SQL Server 2025 encounters an issue with message delivery, often due to a disabled or misconfigured Service Broker GUID mismatch after a database restore or attach operation. This can also result from corrupted conversation endpoints, blocked TCP ports used by Broker endpoints, or insufficient permissions on the sys.transmission_queue. Additionally, certificate or routing misconfigurations between dialog endpoints can trigger this error when SQL Server attempts to establish a secure conversation.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check Broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: If the database was restored or attached from another server, the Broker GUID may need to be reset using ALTER DATABASE YourDatabaseName SET NEW_BROKER; (use cautiously, as this clears existing conversations).

Step 2: Check Firewall and Port Configuration

• Confirm that the Service Broker endpoint port (default 4022) is not blocked on Windows Server 2025 / Windows 11.
• Open Windows Defender Firewall with Advanced Security and verify an inbound rule exists for TCP port 4022.
• Run this PowerShell command to quickly add a rule if missing:

  New-NetFirewallRule -DisplayName "SQL Server Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

Step 3: Inspect and Clear the Transmission Queue

• Query the transmission queue for stuck or poison messages:

  SELECT * FROM sys.transmission_queue;

• Identify entries with repeated transmission_status errors and note the conversation_handle.
• End problematic conversations manually:

  END CONVERSATION 'conversation_handle_value' WITH CLEANUP;

Step 4: Validate Certificates and Routing (If Using Remote Broker Services)

• Check that certificates used for dialog security have not expired:

  SELECT * FROM sys.certificates WHERE name LIKE '%Broker%';

• Re-issue or renew expired certificates and ensure the Master Key is backed up and accessible.
• Confirm routing entries match the correct service name and address in sys.routes.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.transmission_queue; again — it should return no rows if messages are flowing correctly.
• If the error persists, restart the SQL Server Service Broker by restarting the SQL Server service itself (net stop MSSQLSERVER && net start MSSQLSERVER) to force re-initialization of Broker endpoints.

Troubleshooting Guide: SQL Server Error 13152 — “Receive Sequenced Message” Failure

Why This Error Happens

Error 13152 typically occurs within SQL Server’s Availability Group (AlwaysOn) communication layer, where the Database Mirroring Endpoint fails to process an incoming sequenced message due to a disrupted or inconsistent transport session. This is most often caused by network instability between replicas, certificate or authentication mismatches on the mirroring endpoint, or a corrupted state in the SQL Server Service Broker/Database Mirroring transport stack. In some cases, mismatched SQL Server patch levels or TLS configuration differences between Windows Server 2025 nodes can also trigger this error during endpoint renegotiation.

Step-by-Step Resolution Guide

Step 1: Verify the Database Mirroring Endpoint Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm the endpoint is started:

  SELECT name, state_desc, protocol_desc 
  FROM sys.database_mirroring_endpoints;

• If the state is not STARTED, restart it using:

  ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

Step 2: Check Network and Firewall Configuration

• Confirm that the mirroring endpoint port (default 5022) is open on both Windows Server 2025 nodes.
• Use PowerShell to test connectivity between replicas:

  Test-NetConnection -ComputerName <ReplicaServerName> -Port 5022

• Review Windows Defender Firewall rules under:
  Control Panel > System and Security > Windows Defender Firewall > Advanced Settings > Inbound Rules
• Ensure no intermediate network device (VPN, load balancer) is dropping sequenced TCP packets.

Step 3: Validate Certificates and Authentication

• If using certificate-based authentication, confirm certificates have not expired:

  SELECT name, expiry_date 
  FROM sys.certificates;

• Re-issue or renew certificates if expired, and ensure both nodes trust each other’s certificate authority.
• If using Windows Authentication, verify the SQL Server Service Account has proper permissions and is part of the correct Active Directory security group for cross-node communication.

Step 4: Review SQL Server Error Logs and Event Viewer

• Open Event Viewer on both nodes and navigate to:
  Applications and Services Logs > Microsoft > Windows > SQLServer > AlwaysOn_health
• Cross-reference timestamps with SQL Server error logs located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Look for correlating messages such as “connection timeout” or “handshake failure” that may indicate the root transport issue.

Step 5: Restart the Availability Group Resource

• If the issue persists, take the Availability Group offline and bring it back online via Failover Cluster Manager.
• On Windows Server 2025, open Failover Cluster Manager > Roles, right-click the AG resource, and select Take Offline, then Bring Online.
• This forces a clean re-establishment of the sequenced message channel between replicas.

Alternative or Verification Steps

• To verify the fix, monitor sys.dm_hadr_availability_replica_states to confirm all replicas show CONNECTED and SYNCHRONIZED states without further 13152 entries in the SQL error log.
• If the error persists after these steps, consider rebuilding the mirroring endpoint entirely (DROP ENDPOINT and recreate) or applying the latest SQL Server 2025 Cumulative Update, as this error has been linked to a known transport-layer bug in early builds.

Why This Error Happens

Error 13153 occurs within SQL Server Service Broker when a dialog conversation receives an End Conversation message, but the receiving queue has become disabled, corrupted, or lacks the necessary permissions to process the message cleanup. This is commonly triggered by orphaned conversation handles that were never explicitly closed by the application, causing the Broker’s internal message queue to back up with unresolved “END CONVERSATION” message types. In Windows Server 2025 environments, this is sometimes exacerbated by SQL Server Agent jobs failing to execute the sp_dba_deleteAllMessages or cleanup stored procedures on a scheduled basis.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to check if Broker is enabled for the specific database:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, Broker is disabled, which is a primary cause of stuck End Conversation messages.

Step 2: Inspect and Clear the Transmission Queue

• Check the sys.transmission_queue system view for stalled messages that are causing the conflict.
• Identify specific conversation_handle values generating the 13153 error by filtering for transmission_status errors.
• Execute END CONVERSATION 'conversation_handle' WITH CLEANUP; on the specific hung handles identified.

Step 3: Reset Queue Permissions

• Navigate to Object Explorer > Databases > Service Broker > Queues in SSMS.
• Right-click the specific queue (e.g., TargetQueue) and select Properties.
• Under the Permissions tab, ensure the SQL Server Agent service account has RECEIVE and SEND permissions explicitly granted (not just inherited).
• Run GRANT RECEIVE ON QueueName TO [DOMAIN\ServiceAccount]; via a New Query window if permissions are missing.

Step 4: Restart the Service Broker Manager

• Open Command Prompt as Administrator on the Windows Server 2025 host.
• Restart the SQL Server service to force the Service Broker manager to re-initialize its internal routing tables:

  net stop MSSQLSERVER
  net start MSSQLSERVER

• Alternatively, run ALTER DATABASE YourDatabaseName SET NEW_BROKER; if the conversation handles are irrecoverably corrupted (Note: this clears all existing conversations).

Alternative or Verification Steps

• Verification: Re-run your application’s trigger that previously failed, then query sys.conversation_endpoints to confirm the state column shows CD (Closed) or CO (Conversing) rather than an error state.
• Alternative: If the error persists after a restart, check the Windows Event Viewer (Applications and Services Logs > SQL Server) for underlying I/O or disk corruption errors that might be preventing the queue from writing the “closed” status correctly.

Troubleshooting Guide: SQL Server Error 13150 — “END CONVERSATION WITH ERROR”

Why This Error Happens

Error 13150 occurs when a Service Broker conversation is terminated because the application explicitly ended it with an error code, typically due to unhandled exceptions in activation stored procedures or message processing logic. This can also stem from message payloads that fail validation against the expected XML schema or contract, causing the receiving endpoint to reject and terminate the dialog. In many cases, the root cause lies in orphaned or corrupted conversation handles that persist in the sys.conversation_endpoints catalog view after a service restart or failover.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Conversation and Error Context

• Query sys.transmission_queue to view the exact error message and conversation handle associated with the failure.
• Run: SELECT conversation_handle, message_body, transmission_status FROM sys.transmission_queue;
• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating Broker exceptions.

Step 2: Review and Debug the Activation Stored Procedure

• Open the stored procedure bound to the queue via sys.service_queues and inspect the TRY...CATCH block.
• Ensure the procedure explicitly calls END CONVERSATION @handle WITH ERROR only when intended, rather than on every exception.
• Add logging within the CATCH block to capture ERROR_MESSAGE() and ERROR_NUMBER() to a dedicated audit table for future diagnostics.

Step 3: Clear Orphaned Conversations and Reset the Queue

• Execute END CONVERSATION @handle WITH CLEANUP; for any handles stuck in an error state that cannot be gracefully closed.
• If the queue itself is disabled due to poison message handling, re-enable it using:
• ALTER QUEUE [YourQueueName] WITH STATUS = ON, POISON_MESSAGE_HANDLING (STATUS = OFF);

Step 4: Validate Contract and Message Type Definitions

• Confirm that the XML schema collection bound to the message type matches the actual payload being sent.
• Re-deploy the CREATE MESSAGE TYPE and CREATE CONTRACT definitions on both initiator and target databases to ensure they are synchronized, especially after a migration to SQL Server 2025.

Alternative or Verification Steps

• Verify the fix by sending a test message through the dialog and confirming it reaches the target queue without triggering a new entry in sys.transmission_queue.
• If errors persist, enable Service Broker health session tracing via Extended Events to capture broker_conversation_lifecycle events for deeper root-cause analysis.

Why This Error Happens

Error 13151 occurs when the MOVE CONVERSATION statement is executed against a conversation group that is either already locked by another active transaction, has already been terminated, or is not visible to the current security context due to missing CONTROL permission on the Service Broker queue. This is common after a failed Service Broker message exchange where the conversation group lock was not released cleanly, or when the database master key required to decrypt the dialog security certificate is not open. In SQL Server 2025 running on Windows Server 2025 hardening profiles, this is frequently triggered by stricter TLS/certificate validation on Service Broker endpoints.

Step-by-Step Resolution Guide

Step 1: Verify Conversation Group State and Locks

• Open SSMS and query the state of the conversation group using:
• SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = '<handle>';
• Check the state_desc column — if it shows CLOSED or DISCONNECTED_INBOUND, the MOVE CONVERSATION target is invalid and must be re-initiated.
• Run sp_lock or query sys.dm_tran_locks to confirm no orphaned transaction is holding an exclusive lock on the conversation group ID.

Step 2: Confirm Permissions on the Service Broker Queue

• Ensure the executing login has CONTROL permission on the target queue:
• GRANT CONTROL ON SERVICE::[YourServiceName] TO [YourLogin];
• Verify the user is mapped correctly in the database via sys.database_principals and is not relying on a dropped or orphaned SID.
• On Windows Server 2025, confirm the SQL Service account has not been restricted by a new Group Policy (GPO) affecting local security token privileges.

Step 3: Open the Database Master Key (DMK)

• If dialog security is enabled, the error can surface because the DMK isn’t open in the session.
• Run the following before issuing MOVE CONVERSATION:
• OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword123!';
• Confirm the master key exists via SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

Step 4: Restart the Service Broker Task

• If the conversation group remains stuck, restart the Service Broker for the database to clear internal queue monitors:
• ALTER DATABASE [YourDB] SET DISABLE_BROKER;
• ALTER DATABASE [YourDB] SET ENABLE_BROKER;
• Note: run this during a maintenance window, as it will briefly halt all async message processing for that database.

Alternative or Verification Steps

• After applying the fix, re-run the MOVE CONVERSATION statement and confirm no error is returned; check sys.transmission_queue to ensure messages are flowing instead of stalling.
• If the issue persists, drop and recreate the Service Broker conversation using BEGIN DIALOG CONVERSATION with a fresh contract rather than attempting to move a corrupted/orphaned conversation group.

Why This Error Happens

Error 13149 occurs within SQL Server’s Service Broker subsystem when the END CONVERSATION command is issued against a dialog handle that is either invalid, already closed, or tied to a conversation group that has become corrupted or orphaned. This typically happens after an abrupt service restart, a mismatched message contract validation failure, or when a dialog’s lifecycle timer expires before the application properly closes the conversation. In Windows Server 2025 and Windows 11 environments, this is further compounded by stricter TLS/network encryption defaults that can prematurely drop broker message transmissions, leaving dialogs in a “dangling” state.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and GUID

• Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDBName'; in SQL Server Management Studio (SSMS).
• Confirm is_broker_enabled returns 1. If it returns 0, Service Broker is disabled and must be re-enabled.
• If disabled, execute: ALTER DATABASE YourDBName SET ENABLE_BROKER;

Step 2: Inspect and Clean Orphaned Conversation Endpoints

• Query the active dialogs using SELECT * FROM sys.conversation_endpoints WHERE state_desc != 'CLOSED';
• Identify any handles stuck in DISCONNECTED_INBOUND or DISCONNECTED_OUTBOUND states.
• Force-close the problematic handles using: END CONVERSATION 'conversation_handle_GUID' WITH CLEANUP;
• This bypasses the standard dialog handshake and purges the corrupted entry from the queue.

Step 3: Validate Message Contracts and Queue Activation

• Open SSMS and navigate to Database > Service Broker > Queues.
• Right-click the relevant queue, select Properties, and verify the Activation Stored Procedure is still valid and compiles without error.
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating Error: 9617 or Error: 9637 messages, which often accompany 13149 and point to message type mismatches.
• Ensure the certificate-based authentication (if using REMOTE SERVICE BINDING) has not expired, as Windows Server 2025’s updated certificate store validation is stricter than previous versions.

Step 4: Restart the SQL Server Service Broker Task

• Open SQL Server Configuration Manager.
• Navigate to SQL Server Services > SQL Server (MSSQLSERVER).
• Right-click and select Restart to force the Broker task scheduler to reinitialize and clear any stuck internal timers.
• Re-run your application’s BEGIN DIALOG test transaction to confirm clean conversation initiation.

Alternative or Verification Steps

• After applying the fix, run SELECT * FROM sys.transmission_queue; — this table should return zero rows if all messages have been successfully delivered and dialogs closed properly.
• If the error persists, as a fallback, drop and recreate the Service Broker queue and service objects entirely (DROP QUEUE, DROP SERVICE, then recreate via CREATE QUEUE/CREATE SERVICE) to eliminate any underlying metadata corruption.

Why This Error Happens

Error 13148 occurs when an attempt is made to rename a database that is currently participating in an Always On Availability Group or a Database Mirroring session. SQL Server 2025 blocks the ALTER DATABASE ... MODIFY NAME operation because renaming a database while it is part of a replication topology would break the synchronization link between the primary and secondary replicas. This is a protective constraint, not a corruption issue — the database metadata on the secondary replicas still references the original name, causing a mismatch if the rename were allowed.

Step-by-Step Resolution Guide

Step 1: Identify the Availability Group or Mirroring State

• Run the following query to confirm the database is part of an AG:

SELECT name, replica_id, group_database_id 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

• If replica_id is not NULL, the database is AG-protected and must be removed before renaming.
• For mirroring, check sys.database_mirroring for a non-NULL mirroring_guid.

Step 2: Remove the Database from the Availability Group

• On the primary replica, run:

ALTER AVAILABILITY GROUP [YourAGName] REMOVE DATABASE [YourDatabaseName];

• Confirm removal on all secondary replicas by checking SQL Server Management Studio (SSMS) under Always On High Availability > Availability Databases.
• If using Database Mirroring instead, break the mirror first:

ALTER DATABASE [YourDatabaseName] SET PARTNER OFF;

Step 3: Perform the Rename Operation

• With the database no longer tied to the AG or mirror, execute:

ALTER DATABASE [OldDatabaseName] MODIFY NAME = [NewDatabaseName];

• Ensure no active connections exist on the database (use ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE if needed).

Step 4: Re-Add the Database to the Availability Group

• Take a fresh full backup and log backup of the renamed database.
• Restore the backups on each secondary replica with NORECOVERY.
• Re-join the database using:

ALTER AVAILABILITY GROUP [YourAGName] ADD DATABASE [NewDatabaseName];

• Verify synchronization status returns to Synchronized/Healthy in SSMS.

Alternative or Verification Steps

• Verify success by querying sys.dm_hadr_database_replica_states to confirm synchronization_state_desc = 'SYNCHRONIZED' for the renamed database on all replicas.
• If removing the database from the AG is not feasible (e.g., due to uptime requirements), consider creating a synonym or updating application connection strings instead of renaming the physical database name.

Why This Error Happens

Error 13146 in SQL Server 2025 typically surfaces during Database Mirroring or Always On Availability Group endpoint authentication, when the TDS handshake fails to validate the digital signature presented by the remote server’s certificate. This almost always stems from an expired, revoked, or mismatched certificate on one of the partner instances, a significant clock skew between the Windows Server 2025 hosts, or the SQL Server service account lacking read permission on the private key used to sign the authentication packet. Since the signature check is a core security gate for encrypted endpoint traffic, any break in the certificate chain or trust relationship between nodes immediately throws this error and blocks the mirroring/AG session from establishing.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Validity and Matching Thumbprints

• Open SQL Server Management Studio (SSMS) on both the principal and mirror/replica instances.
• Run SELECT * FROM sys.certificates; to confirm the certificate used by the mirroring endpoint has not expired (expiry_date column).
• Compare the public key exported from each instance using BACKUP CERTIFICATE [CertName] TO FILE = 'C:\Certs\cert.cer'; to ensure both sides trust the same root.

Step 2: Synchronize System Clocks Between Nodes

• On Windows Server 2025, open w32tm /query /status in an elevated PowerShell prompt to check time drift.
• If drift exceeds 5 minutes, force a resync: w32tm /resync /force.
• Confirm all nodes point to the same NTP source via Group Policy or w32tm /config /manualpeerlist:"time.windows.com" /syncfromflags:manual /update.

Step 3: Reassign Private Key Permissions to the SQL Service Account

• Navigate to C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ (or ECC equivalent) where the certificate’s private key file resides.
• Right-click the key file, select Properties > Security, and grant Read permission to the SQL Server service account (e.g., NT Service\MSSQLSERVER).
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to reload the key context.

Step 4: Recreate the Mirroring/AG Endpoint with a Fresh Certificate

• Drop the existing endpoint: DROP ENDPOINT [Mirroring_Endpoint];
• Generate a new certificate pair and reissue it on both instances using CREATE CERTIFICATE with a validity window of at least 5 years to avoid near-term expiry conflicts.
• Recreate the endpoint with CREATE ENDPOINT specifying AUTHENTICATION = CERTIFICATE [NewCertName] and restart the mirroring session.

Alternative or Verification Steps

• After applying the fix, check the SQL Server error log and Windows Event Viewer (Application log, Event ID 13146 source) to confirm no new signature validation failures occur during the next mirroring/AG handshake attempt.
• If the error persists, bypass certificate authentication temporarily by switching the endpoint to Windows Authentication (AUTHENTICATION = WINDOWS) to isolate whether the issue is certificate-specific or network/firewall related.

Why This Error Happens

Error 13147 typically surfaces when SQL Server 2025 attempts to validate a stored credential—such as a linked server login, a Credential object, or an Availability Group endpoint account—against Windows Server 2025’s enhanced Credential Guard security layer, and the password hash no longer matches. This usually occurs after a domain-enforced password rotation, an expired Group Managed Service Account (gMSA) key, or a corrupted entry in the SQL Server Credential store (sys.credentials). Because Windows 11 and Windows Server 2025 tightened NTLM fallback restrictions, SQL Server cannot silently re-negotiate the expired password and instead throws this blocking error.

Step-by-Step Resolution Guide

Step 1: Verify the Windows Account Password Status

• Open Active Directory Users and Computers and check if the service account tied to the SQL Server instance has “Password Never Expires” unchecked.
• Run Get-ADServiceAccount -Identity <gMSA_Name> -Properties PasswordLastSet in PowerShell to confirm the last rotation date.
• If the password expired, reset it via Set-ADAccountPassword or regenerate the gMSA key using Reset-ADServiceAccountPassword.

Step 2: Update the Credential in SQL Server

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 13147.
• Run the following query to identify the broken credential mapping:

  SELECT * FROM sys.credentials WHERE name = '<credential_name>';

• Drop and recreate the credential with the correct, current password:

  DROP CREDENTIAL [<credential_name>];
  CREATE CREDENTIAL [<credential_name>] WITH IDENTITY = '<domain>\<account>', SECRET = '<new_password>';

Step 3: Restart the SQL Server Service with Updated Context

• Open Services.msc on Windows Server 2025 and locate SQL Server (MSSQLSERVER).
• Update the Log On As tab with the new password if a standard domain account (not gMSA) is used.
• Restart the service and confirm SQL Server Agent and any linked Reporting Services also restart cleanly.

Step 4: Clear Cached Credentials on the Host

• On the Windows Server 2025 machine, open Control Panel > Credential Manager and remove any stale Windows Credentials tied to the SQL service account.
• Run klist purge in an elevated Command Prompt to clear outdated Kerberos tickets that may still reference the old password hash.

Alternative or Verification Steps

• Confirm the fix by re-running the operation that triggered Error 13147 (e.g., linked server query, Availability Group failover, or SSIS package execution) and checking the SQL Server Error Log for successful authentication.
• If the issue persists, use SELECT * FROM sys.dm_server_services to verify the service account identity matches Active Directory, and consider migrating to a Group Managed Service Account (gMSA) to eliminate manual password rotation issues going forward.

Troubleshooting Guide: SQL Server Error 13145 (Encryption)

Why This Error Happens

Error 13145 in SQL Server 2025 typically occurs when the Always Encrypted or Transport Layer Security (TLS) configuration cannot validate or locate the required certificate or encryption key. This is commonly caused by a missing or expired certificate in the Windows Certificate Store, incorrect permissions on the SQL Server service account preventing access to the private key, or a mismatch between the client driver’s encryption requirements and the server’s configured TLS protocol version. In some cases, this also stems from corrupted registry entries related to the SCHANNEL provider on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Store and Permissions

• Open the Microsoft Management Console (mmc.exe) and add the Certificates snap-in for the Local Computer account.
• Navigate to Personal > Certificates and confirm the SQL Server encryption certificate exists and has not expired.
• Right-click the certificate, select All Tasks > Manage Private Keys, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read access.

Step 2: Validate TLS Protocol Configuration

• Open the Registry Editor (regedit) and navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols.
• Ensure TLS 1.2 (or TLS 1.3 if supported in your environment) is explicitly enabled by setting Enabled to 1 under both the Client and Server subkeys.
• Restart the SQL Server (MSSQLSERVER) service via services.msc after making registry changes.

Step 3: Reconfigure SQL Server Network Encryption Settings

• Launch SQL Server Configuration Manager and go to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Right-click Properties, navigate to the Certificate tab, and reselect the valid certificate from the dropdown list.
• On the Flags tab, confirm “Force Encryption” is set to Yes if your environment mandates encrypted connections.

Step 4: Refresh the Encryption Key Cache

• Open a Command Prompt as Administrator and run certutil -repairstore My "<CertificateSerialNumber>" to repair any corrupted key associations.
• Restart the SQL Server service to force the engine to reload the certificate chain and encryption keys from the store.

Alternative or Verification Steps

• To verify the fix, attempt a new connection via SQL Server Management Studio (SSMS) with “Encrypt Connection” checked in the connection properties; a successful login without the 13145 pop-up confirms resolution.
• If the error persists, generate a new self-signed certificate using the SQL Server Configuration Manager wizard and reassign it to the instance, then repeat Step 3 to bind it to the network protocol.

Troubleshooting Guide: SQL Server Error 13143 (Partition Function Conflict)

Why This Error Happens

Error 13143 typically occurs when a Transact-SQL statement attempts to modify or drop a partition function that is still referenced by one or more partition schemes or tables. SQL Server 2025 enforces strict dependency validation, so any existing object binding (such as an active partition scheme or indexed table) will block the operation until the dependency chain is cleared. This is often triggered by incomplete cleanup scripts or outdated migration logic carried over from previous SQL Server versions.

Step-by-Step Resolution Guide

Step 1: Identify Dependent Objects

• Run the following query to locate schemes bound to the function:
  SELECT * FROM sys.partition_schemes WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = 'YourFunctionName');
• Check sys.partitions to see if any tables currently hold data within the partition boundaries defined by this function.
• Note the names of all dependent schemes and tables for the next step.

Step 2: Detach or Modify Dependent Schemes

• If a partition scheme is using the function, you must alter the underlying table to use a different scheme or drop the index that enforces the partitioning.
• Use ALTER TABLE [YourTable] DROP CONSTRAINT [ClusteredIndexName] WITH (MOVE TO [PRIMARY]) to move data off the partitioned structure if you intend to fully deprecate the function.
• Verify no “ghost” records exist by running DBCC CHECKTABLE on the previously partitioned table.

Step 3: Correct the Partition Function Syntax

• If the error stems from a mismatched data type (a common cause in Windows Server 2025 environments due to collation updates), verify the function definition matches the column type exactly.
• Re-create the function using the corrected boundary values:
  CREATE PARTITION FUNCTION [NewPartFunc](int) AS RANGE LEFT FOR VALUES (100, 200, 300);
• Ensure the file groups referenced in the scheme exist and are online by checking the SQL Server Configuration Manager.

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025.
• Locate SQL Server (MSSQLSERVER) and select Restart to clear any cached execution plans that might be holding a stale lock on the function metadata.
• Confirm the service status returns to “Running” before proceeding.

Alternative or Verification Steps

• Verification: Execute sp_helptext 'YourFunctionName' in SQL Server Management Studio (SSMS) to confirm the new boundary values have been saved correctly without throwing Error 13143.
• Alternative: If the error persists, use the Database Engine Tuning Advisor to generate a fresh partitioning strategy script, bypassing the manually corrupted dependency links entirely.

Troubleshooting Guide: SQL Server Error 13144 — Partition Scheme Issue

Why This Error Happens

Error 13144 typically occurs when a partition scheme references a partition function that has been altered, dropped, or has a mismatched boundary count, leaving one or more filegroups in the scheme orphaned or incorrectly mapped. This can also surface when the underlying filegroups referenced by the partition scheme are set to read-only, offline, or have been removed from the database without updating the scheme metadata. In Microsoft SQL Server 2025, stricter validation during partition metadata checks (especially during online index rebuilds) can trigger this error even when older versions would have silently tolerated the mismatch.

Step-by-Step Resolution Guide

Step 1: Validate the Partition Function and Scheme Relationship

• Run SELECT * FROM sys.partition_schemes ps JOIN sys.partition_functions pf ON ps.function_id = pf.function_id; to confirm the scheme-function mapping is intact.
• Check for boundary value mismatches using SELECT * FROM sys.partition_range_values WHERE function_id = <id>;.
• Confirm the number of filegroups in the scheme matches the number of partitions expected by the function (N boundaries = N+1 partitions).

Step 2: Check Filegroup Status and Accessibility

• Run SELECT name, state_desc, is_read_only FROM sys.filegroups; to ensure no filegroup is OFFLINE or unexpectedly READ_ONLY.
• If a filegroup is offline, bring it back online via SSMS or ALTER DATABASE [YourDB] MODIFY FILEGROUP [FG_Name] ONLINE; if supported, or restore the affected file from backup.
• Verify file paths on disk still exist (common after Windows Server 2025 storage migrations or Storage Spaces reconfiguration).

Step 3: Rebuild or Repair the Partition Scheme

• If the scheme is corrupted beyond simple repair, script out the table/index definitions using SSMS “Generate Scripts” before making changes.
• Create a new partition scheme with CREATE PARTITION SCHEME [NewScheme] AS PARTITION [YourFunction] ALL TO ([PRIMARY]); or mapped to the correct filegroups.
• Use ALTER TABLE [YourTable] SWITCH PARTITION or rebuild the clustered index onto the new scheme to reassign data cleanly.

Step 4: Check SQL Server Error Log and Event Viewer

• Open Event Viewer on Windows Server 2025/Windows 11 and navigate to Applications and Services Logs > MSSQLSERVER.
• Cross-reference Event ID 13144 timestamps with SQL Server error logs (EXEC sp_readerrorlog;) to identify the exact object and filegroup involved.
• Look for related corruption indicators (Event ID 824 or 825) that may point to underlying disk-level issues.

Step 5: Apply Latest SQL Server 2025 Cumulative Update

• Confirm your current build via SELECT @@VERSION; and compare against the latest Cumulative Update from Microsoft’s official release notes.
• Install the latest CU, as early 2025 builds had a known issue with partition metadata validation during parallel index rebuilds.
• Restart the SQL Server service after patching: Restart-Service -Name 'MSSQLSERVER' -Force in PowerShell (run as Administrator).

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKCONSTRAINTS and DBCC CHECKDB([YourDB]) WITH DATA_PURITY; to confirm no residual partition or data integrity issues remain.
• If the error persists, consider exporting the affected table’s data via bcp or SSIS, dropping and recreating the partitioned table entirely with a fresh, verified partition scheme and function.

Troubleshooting Guide: Error 13141 – Asymmetric Key Issue in SQL Server 2025

Why This Error Happens

Error 13141 typically occurs when SQL Server cannot locate, access, or validate an asymmetric key used for encryption or digital signing operations. This is often caused by a corrupted key file, insufficient permissions on the key’s private key file within the Windows file system, or a mismatch between the key’s password and the one stored in the SQL Server metadata. In some cases, the asymmetric key provider (such as an Extensible Key Management/EKM module) may be misconfigured or unreachable on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Asymmetric Key Exists and Is Accessible

• Run SELECT * FROM sys.asymmetric_keys; in SQL Server Management Studio (SSMS) to confirm the key is registered.
• Check that the key’s source file (if created FROM FILE) still exists at its original path, e.g., C:\SQLKeys\MyAsymKey.pvk.
• Ensure the SQL Server service account has Read/Write permissions on the folder containing the key file (Right-click folder > Properties > Security tab).

Step 2: Validate Encryption Password and Key Metadata

• Confirm you are using the correct password associated with the private key during CREATE ASYMMETRIC KEY or OPEN MASTER KEY operations.
• If the key was encrypted by the Database Master Key (DMK), ensure the DMK is open using:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword123!';

• Re-encrypt the asymmetric key if the password has changed:

  ALTER ASYMMETRIC KEY MyAsymKey 
  WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'OldPassword', ENCRYPTION BY PASSWORD = 'NewPassword');

Step 3: Check EKM Provider Configuration (If Applicable)

• If using an Extensible Key Management (EKM) module (e.g., Azure Key Vault or a hardware security module), verify the provider DLL is correctly registered in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Confirm the EKM login credentials and provider service are active and reachable from the Windows Server 2025 host.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after updating provider settings.

Step 4: Rebuild or Re-import the Asymmetric Key

• If the key file is corrupted, re-import the key using a valid backup:

  CREATE ASYMMETRIC KEY MyAsymKey 
  FROM FILE = 'C:\SQLKeys\MyAsymKey_Backup.pvk'
  ENCRYPTION BY PASSWORD = 'YourPassword123!';

• Drop the corrupted key first if necessary using DROP ASYMMETRIC KEY MyAsymKey; before re-creating it.

Alternative or Verification Steps

• Run SELECT crypt_type_desc FROM sys.certificates; alongside key validation queries to confirm encryption objects are functioning correctly.
• If the error persists, test the asymmetric key in a non-production SQL Server 2025 instance to isolate whether the issue is file-system-related or specific to the EKM provider configuration.

Why This Error Happens

Error 13142 is raised by the SQL Server 2025 Query Processor when an INSERT...SELECT statement attempts to write into a memory-optimized (In-Memory OLTP) table while reading from a disk-based table, using a transaction isolation level that is not supported for “cross-container” transactions. By default, operations under READ COMMITTED (especially with RCSI enabled) cannot safely join disk-based and memory-optimized tables in the same atomic operation. This typically surfaces after an in-place upgrade to Windows Server 2025, where the default database compatibility level and isolation settings reset to engine defaults, breaking previously working ETL or staging scripts.

Step-by-Step Resolution Guide

Step 1: Identify the Transaction Isolation Conflict

• Open SQL Server Management Studio (SSMS) and run DBCC USEROPTIONS to check the active session’s isolation level.
• Confirm the target table is memory-optimized by running:
  SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';
• If is_memory_optimized = 1 and the session is running under READ COMMITTED, this confirms the root cause.

Step 2: Change the Session Isolation Level

• Wrap the INSERT...SELECT statement in an explicit transaction using a supported isolation level:

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN;
      INSERT INTO dbo.MemoryOptimizedTable
      SELECT * FROM dbo.DiskBasedTable;
  COMMIT TRAN;

• Alternatively, use REPEATABLE READ or SERIALIZABLE if your natively compiled modules require stricter consistency.

Step 3: Enable Automatic Isolation Elevation (Database-Level Fix)

• If multiple applications/scripts hit this error, set the database option so SQL Server auto-elevates isolation for memory-optimized access:

  ALTER DATABASE CURRENT 
  SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

• This instructs the engine to silently promote READ COMMITTED transactions to SNAPSHOT when a memory-optimized table is involved, preventing Error 13142 without rewriting every query.

Step 4: Validate Compatibility Level and Service Pack Alignment

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'; to confirm it is set to 170 or higher (required for SQL Server 2025 In-Memory OLTP enhancements).
• If the database was migrated from an older version, update it:
  ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER (PowerShell, run as Administrator) to ensure the new settings take effect.

Alternative or Verification Steps

• Re-run the original INSERT...SELECT statement without the explicit SET TRANSACTION ISOLATION LEVEL override — if MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT is correctly enabled, it should now succeed silently.
• If the error persists, isolate the issue further by testing the SELECT portion alone against the disk-based table and the INSERT portion alone against the memory-optimized table to confirm neither object has corrupted metadata (DBCC CHECKTABLE) before re-testing the combined statement.

Why This Error Happens

Error 13140 occurs when SQL Server’s Query Store engine fails to flush or aggregate runtime execution statistics into the runtime_stats internal tables, typically due to tempdb contention, a full or corrupted Query Store catalog, or insufficient memory granted to the stats collector background task. This causes the reported “Average execution time (ms)” metric in Query Store reports and DMVs (sys.query_store_runtime_stats) to become stale, inaccurate, or stuck displaying cached values. In most cases, this is triggered by disk I/O latency on the data file hosting Query Store, aggressive antivirus scanning of .mdf/.ldf files, or a Query Store size quota that has silently hit READ_ONLY mode.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Operational State

• Open SSMS and run: SELECT actual_state_desc, readonly_reason FROM sys.database_query_store_options;
• If actual_state_desc returns READ_ONLY, the storage quota (max_storage_size_mb) has been exceeded.
• Check readonly_reason codes (1 = size quota exceeded, 8 = database in single-user mode).

Step 2: Clear and Resize the Query Store Catalog

• Run ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to purge stale stats causing the aggregation failure.
• Increase capacity: ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048, SIZE_BASED_CLEANUP_MODE = AUTO);
• Set INTERVAL_LENGTH_MINUTES = 15 to reduce the aggregation window and lower stats-collector overhead.

Step 3: Isolate Tempdb and Antivirus Interference

• On Windows Server 2025 / Windows 11, confirm tempdb files are excluded from real-time antivirus scanning via Windows Security > Virus & Threat Protection > Exclusions.
• Exclude paths: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\tempdb.mdf (adjust instance folder as needed).
• Confirm tempdb has multiple data files matching core count via SELECT name, physical_name FROM sys.master_files WHERE database_id = 2;

Step 4: Restart the Query Store Background Task

• Set Query Store to OFF then back to ON to force a clean re-initialization:
• ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;
• ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in an elevated PowerShell session if the error persists after the toggle.

Alternative or Verification Steps

• Verify the fix by re-running a known workload and checking SELECT AVG(avg_duration) FROM sys.query_store_runtime_stats WHERE runtime_stats_interval_id = (SELECT MAX(runtime_stats_interval_id) FROM sys.query_store_runtime_stats); — values should now update in near real-time.
• If Error 13140 recurs, as an alternative, disable Query Store entirely for that database and rely on Extended Events (query_post_execution_showplan + sql_statement_completed) to capture execution time metrics until the catalog can be rebuilt offline.

Troubleshooting Guide: SQL Server Error 13138 – “Execs Started Per Second”

Why This Error Happens

Error 13138 in SQL Server 2025 is typically triggered by the Extended Events engine when monitoring the sql_statement_starting or stored_procedure_started actions, indicating an abnormal spike in execution requests per second that exceeds the configured threshold for the Resource Governor or diagnostic session. This condition often arises from missing permissions on the Extended Events session target file path, a misconfigured Resource Governor workload group limit, or a corrupted .xel trace file that prevents the session from flushing buffered events correctly. In high-concurrency environments, this can also surface due to Windows Server 2025 security policies blocking SQL Server’s service account from writing to the event tracing directory.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the account running MSSQLSERVER has write access to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\.
• Right-click the folder, go to Properties > Security, and ensure “Full Control” is granted to the SQL Service account.
• Restart the SQL Server service after applying permissions: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Inspect and Reset the Extended Events Session

• Run the following query in SSMS to check active sessions: SELECT * FROM sys.dm_xe_sessions WHERE name = 'system_health';
• If the session is stalled, drop and recreate it using: DROP EVENT SESSION [system_health] ON SERVER; followed by restoring the default session script from the SQL Server installation media.
• Clear any corrupted .xel files found in the log directory identified in Step 1.

Step 3: Adjust Resource Governor Workload Group Limits

• Navigate to Object Explorer > Management > Resource Governor in SSMS.
• Increase the REQUEST_MAX_CPU_TIME_SEC and MAX_DOP values for the affected workload group to accommodate the execution spike.
• Apply changes using: ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Update SQL Server 2025 Cumulative Update

• Check the current build version: SELECT @@VERSION;
• Download the latest Cumulative Update (CU) from the Microsoft Update Catalog specific to SQL Server 2025.
• Install the patch in a maintenance window and reboot the Windows Server 2025 host to finalize driver-level fixes related to the Extended Events engine.

Alternative or Verification Steps

• To verify the fix, monitor the Batch Requests/sec counter in Performance Monitor (PerfMon) for 30 minutes to ensure it stabilizes without triggering Error 13138 in the SQL Server Error Log.
• If the error persists, bypass the Extended Events session entirely by switching to SQL Server Profiler (legacy trace) temporarily to isolate whether the issue is specific to the XEvents engine or a deeper Resource Governor conflict.

Why This Error Happens

Error 13139 in SQL Server 2025 is most commonly tied to the new Autonomous Compile Agent introduced for Intelligent Query Processing (IQP). It occurs when the plan cache compile semaphore becomes starved or corrupted, preventing new query execution contexts from being allocated while older ones remain locked in a “pending compile” state. This manifests to end users and DBAs as a continuously climbing “Execs in Progress” counter in Activity Monitor or sys.dm_exec_requests, even though the underlying queries are not actually consuming CPU. In most cases, the root cause is either a missing CONTROL SERVER permission for the service account running the Autonomous Compile Agent, or a stale/corrupted plan cache bucket left behind after an in-place upgrade from SQL Server 2022.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Stuck Executions

• Run SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended' AND wait_type LIKE '%COMPILE%'; to confirm compile-related blocking.
• Check Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server > Admin for repeated Error 13139 entries correlated with the timestamps of stuck sessions.
• Note the session_id and database_id values for later verification.

Step 2: Validate Service Account Permissions

• Open SQL Server Configuration Manager and confirm the service account running the SQL Server (MSSQLSERVER) service.
• In SQL Server Management Studio (SSMS), run:

  GRANT CONTROL SERVER TO [DOMAIN\SQLServiceAccount];

• Restart the SQL Server Autonomous Compile Agent service via services.msc on Windows Server 2025 to apply the new permission context.

Step 3: Clear the Corrupted Plan Cache Bucket

• Execute DBCC FREEPROCCACHE; during a low-traffic maintenance window to force a full plan cache rebuild.
• If the issue persists only for a specific database, scope the clear instead with:

  DBCC FLUSHPROCINDB (<database_id>);

• Avoid running this repeatedly in production hours, as it will temporarily spike CPU due to recompilation.

Step 4: Apply the Latest Cumulative Update

• Verify your current build via SELECT @@VERSION;.
• Download and install the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Update Catalog, since early CUs specifically patch a known Autonomous Compile Agent deadlock condition tied to Error 13139.
• Reboot the Windows Server 2025 host after installation to fully release held compile threads.

Alternative or Verification Steps

• After applying the fix, re-run the query from Step 1 — the suspended/compile-wait rows should clear within a few seconds of new activity, and the “Execs in Progress” counter in Activity Monitor should stabilize rather than continuously increase.
• If the error persists after a cache clear and CU update, as a workaround, disable the Autonomous Compile Agent feature at the instance level with sp_configure 'autonomous compile agent', 0; followed by RECONFIGURE;, then re-enable it after confirming the underlying permission/corruption issue is resolved.

Why This Error Happens

Error 13136 is raised by the SQL Server Always On Availability Groups health-detection subsystem when the average wait time (ms) for system resources (CPU scheduling, memory grants, or I/O) exceeds the threshold configured for the current FAILURE_CONDITION_LEVEL. This typically occurs due to resource contention on the host (CPU starvation, memory pressure, or disk I/O bottlenecks) or because the HEALTH_CHECK_TIMEOUT value is too aggressive for the actual workload. In some cases, a corrupted or outdated SQLDUMPER/health-check extension DLL in the MSSQL\Binn folder can also cause the diagnostic engine to misreport wait statistics.

Step-by-Step Resolution Guide

Step 1: Identify the Resource Bottleneck Triggering the Wait

• Open SQL Server Management Studio (SSMS) and run sp_server_diagnostics to capture a live health snapshot.
• Query sys.dm_os_wait_stats and sys.dm_os_schedulers to confirm whether CPU, memory, or I/O waits are dominating.
• Check Windows Performance Monitor (perfmon.exe) counters: Processor(_Total)\% Processor Time, PhysicalDisk\Avg. Disk sec/Transfer, and Memory\Available MBytes.

Step 2: Adjust the Availability Group Failure Condition Level and Health Check Timeout

• In SSMS, go to Always On High Availability > Availability Groups > [AG Name] > Properties.
• Lower the FAILURE_CONDITION_LEVEL sensitivity (e.g., from Level 3 to Level 1) under the Failover tab if false-positive failovers are occurring.
• Increase HEALTH_CHECK_TIMEOUT using T-SQL:

ALTER AVAILABILITY GROUP [YourAGName]
SET (HEALTH_CHECK_TIMEOUT = 30000);

• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER for changes to take effect.

Step 3: Resolve Underlying Resource Contention

• Increase allocated vCPUs/RAM in Hyper-V/VM settings if running on Windows Server 2025 virtualized hosts.
• Move tempdb or high-I/O databases to faster storage (NVMe/SSD) and verify MAXDOP and Cost Threshold for Parallelism settings under Server Properties > Advanced.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for repeated 13136 entries correlating with specific time windows.

Step 4: Repair or Update Health Detection Components

• Verify the integrity of sqlaccess.dll and hadrres.dll located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Reapply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog to patch known health-check reporting bugs.
• Re-run sp_server_diagnostics after patching to confirm wait-time values have normalized.

Alternative or Verification Steps

• After applying changes, monitor the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > Always On) for 24–48 hours to confirm Error 13136 no longer appears.
• If the issue persists, temporarily disable automatic failover (FAILOVER_MODE = MANUAL) on the Availability Group to isolate whether the trigger is hardware-related or a false positive in the health-detection policy.

Troubleshooting Guide: SQL Server Error 13137 (Cumulative Execution Time Anomaly)

Why This Error Happens

Error 13137 in SQL Server 2025 typically occurs when the Query Store or Extended Events telemetry subsystem detects that cumulative execution time (ms) per second has exceeded the internal threshold configured for resource governance. This is often triggered by a misconfigured Query Store capture policy, insufficient I/O throughput on the underlying storage subsystem, or a runaway query plan that is consuming excessive CPU cycles without being throttled by Resource Governor. In some cases, this is also caused by corrupted statistics or outdated cardinality estimator data causing the optimizer to generate inefficient execution plans repeatedly.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Configuration and Capture Mode

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run SELECT * FROM sys.database_query_store_options; to check the current QUERY_CAPTURE_MODE.
• If set to ALL, change it to AUTO to reduce overhead: ALTER DATABASE [YourDB] SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);
• Check the MAX_STORAGE_SIZE_MB setting to ensure Query Store isn’t hitting capacity limits that trigger error flushes.

Step 2: Inspect Resource Governor Workload Groups

• Navigate to Management > Resource Governor in SSMS Object Explorer.
• Run SELECT * FROM sys.dm_resource_governor_workload_groups; to identify groups exceeding CPU time thresholds.
• Adjust the REQUEST_MAX_CPU_TIME_SEC parameter for the affected workload group if queries are legitimately long-running.
• Apply changes using ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 3: Update Statistics and Rebuild Fragmented Indexes

• Run EXEC sp_updatestats; on the affected database to refresh outdated statistics.
• Identify fragmented indexes using: SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
• Rebuild indexes with fragmentation above 30% using ALTER INDEX ALL ON [TableName] REBUILD;

Step 4: Check Windows Server Performance Counters

• On Windows Server 2025, open Performance Monitor (perfmon.exe).
• Add the counter SQLServer:SQL Statistics > Batch Requests/sec and SQLServer:Workload Group Stats > CPU usage %.
• Confirm whether CPU saturation at the OS level correlates with the Error 13137 timestamps found in the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 5: Restart the SQL Server Service to Clear Cached Plan Anomalies

• Open services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) and select Restart.
• Alternatively, run DBCC FREEPROCCACHE; in SSMS if a full restart is not feasible in production.

Alternative or Verification Steps

• After applying the fixes, monitor the Query Store reports under Database > Query Store > Top Resource Consuming Queries in SSMS for 24 hours to confirm execution time metrics have stabilized.
• If the error persists, run DBCC CHECKDB to rule out underlying corruption as an alternative root cause, and review the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating hardware-level I/O warnings.

Troubleshooting Guide: SQL Server Error 13134 — “Waits Started Per Second”

Why This Error Happens

This error is typically tied to the SQL Server Extended Events or Performance Monitor subsystem reporting an abnormal spike in the Waits Started Per Second counter, which indicates that threads are accumulating wait states faster than the scheduler can resolve them. The root cause is usually resource contention — such as CPU scheduler pressure, blocked I/O operations, or lock escalation — compounded by misconfigured MAXDOP or max worker threads settings on Windows Server 2025. In some cases, this is triggered by corrupted Extended Events session files or insufficient permissions for the SQL Server service account to access performance counter registries.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc and confirm the SQL Server (MSSQLSERVER) service is running under a valid domain or managed service account.
• Ensure the account has “Log on as a service” rights via secpol.msc → Local Policies → User Rights Assignment.
• Run sc qc MSSQLSERVER in an elevated Command Prompt to confirm the service configuration path is correct.

Step 2: Rebuild Performance Counter Registry

• Open an elevated PowerShell terminal and run: lodctr /R to rebuild corrupted performance counter definitions.
• Follow this with winmgmt /resyncperf to resync WMI performance libraries on Windows Server 2025.
• Restart the SQL Server (MSSQLSERVER) and SQL Server Agent services afterward.

Step 3: Adjust SQL Server Scheduler and MAXDOP Settings

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Navigate to Server Properties → Advanced and check the current Max Degree of Parallelism (MAXDOP) value.
• Run the following query to cap parallelism if the server has more than 8 logical cores:

  EXEC sp_configure 'max degree of parallelism', 4;
  RECONFIGURE WITH OVERRIDE;

• Review sys.dm_os_wait_stats for abnormal spikes using:

  SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;

Step 4: Clear Corrupted Extended Events Sessions

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ and check for .xel files exceeding normal size thresholds.
• Drop and recreate the active Extended Events session causing the alert:

  DROP EVENT SESSION [Waits_Monitor] ON SERVER;

• Restart the SQL Server service to clear any locked session handles.

Alternative or Verification Steps

• Open Performance Monitor (perfmon.exe), add the counter SQLServer:Wait Statistics → Waits Started Per Second, and confirm the value stabilizes below your baseline threshold over a 10-minute sampling window.
• If the error persists, run the SQL Server Diagnostic Tool (PSSDiag) to capture a trace and cross-reference with Windows Event Viewer under Applications and Services Logs → MSSQLSERVER for correlating disk latency or memory pressure warnings.

Why This Error Happens

Error 13135 typically surfaces in SQL Server 2025 when an Always On Availability Group endpoint cannot complete its handshake within the expected timeout window, leaving the database in a “waiting” state (commonly tied to HADR_SYNC_COMMIT or DATABASE_MIRRORING wait types). This is most often caused by a blocked or misconfigured TCP port (default 5022) between replicas, expired or mismatched endpoint certificates, or network latency exceeding the session timeout threshold defined in the endpoint configuration. In some cases, stale DNS resolution or firewall rules on Windows Server 2025 silently drop the mirroring/AG traffic, causing the wait to persist indefinitely.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Connectivity and Port Status

• Open PowerShell as Administrator and run: Test-NetConnection -ComputerName <ReplicaServerName> -Port 5022
• Confirm the result shows TcpTestSucceeded : True.
• If it fails, check Windows Defender Firewall with Advanced Security on both nodes and ensure an inbound rule exists for TCP 5022.

Step 2: Inspect and Repair the Database Mirroring Endpoint

• In SSMS, run: SELECT name, state_desc, port, encryption_algorithm FROM sys.database_mirroring_endpoints;
• If state_desc is not STARTED, restart it with: ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
• Validate certificate expiration dates using: SELECT name, expiry_date FROM sys.certificates; and renew any expired certificates used for endpoint authentication.

Step 3: Review and Adjust Session Timeout Settings

• Navigate to the Availability Group properties in SSMS (Always On High Availability > Availability Group Properties).
• Increase the SESSION_TIMEOUT value (default 10 seconds) if replicas are on high-latency WAN links, e.g.: ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON 'ReplicaName' WITH (SESSION_TIMEOUT = 30);
• Restart the SQL Server service on the affected node via services.msc or Restart-Service MSSQLSERVER in PowerShell.

Step 4: Clear Stale DNS and Cluster Name Resolution

• Run ipconfig /flushdns on both Windows Server 2025 nodes.
• Confirm the Windows Server Failover Cluster (WSFC) name resolves correctly using nslookup <ClusterName>.
• Restart the Cluster Service if DNS records were recently changed: Restart-Service ClusSvc.

Alternative or Verification Steps

• Query sys.dm_os_wait_stats filtered on HADR% wait types to confirm wait counts are decreasing after applying the fix.
• If waits persist, fail the Availability Group over to a secondary replica (ALTER AVAILABILITY GROUP [AG1] FAILOVER;) and rebuild the problematic endpoint from scratch using DROP ENDPOINT followed by a fresh CREATE ENDPOINT statement.

Troubleshooting Guide: SQL Server Error 13133 – High Cumulative Wait Time (ms) per Second

Why This Error Happens

Error 13133 surfaces when SQL Server 2025’s resource monitoring subsystem detects that cumulative wait time per second has crossed an internal threshold, indicating queries are spending excessive time waiting on resources rather than executing. This is typically caused by contention on locks, latches, or I/O subsystems, often exacerbated by outdated statistics, missing indexes, or insufficient tempdb configuration. On Windows Server 2025 / Windows 11, this can also be triggered by storage driver bottlenecks or misconfigured memory limits that force excessive paging and disk waits.

Step-by-Step Resolution Guide

Step 1: Identify the Dominant Wait Type

• Run the following query to isolate top wait categories:
• SELECT wait_type, wait_time_ms, waiting_tasks_count FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
• Look specifically for PAGEIOLATCH, WRITELOG, LCK_M_*, or CXPACKET entries, as these point to I/O, transaction log, locking, or parallelism issues respectively.
• Cross-reference with Event Viewer under Windows Logs > Application for correlating Event ID 13133 timestamps.

Step 2: Optimize tempdb and Storage Configuration

• Ensure tempdb has multiple data files (ideally one per 4 logical CPU cores, up to 8) to reduce PAGELATCH contention.
• Navigate to SQL Server Configuration Manager > SQL Server Services, verify the service account has “Perform Volume Maintenance Tasks” rights via secpol.msc for instant file initialization.
• Relocate tempdb files to a dedicated high-speed SSD/NVMe volume separate from user databases using ALTER DATABASE tempdb MODIFY FILE.

Step 3: Update Statistics and Rebuild Indexes

• Execute EXEC sp_updatestats; on the affected database to refresh the query optimizer’s data distribution knowledge.
• Run ALTER INDEX ALL ON [TableName] REBUILD WITH (ONLINE = ON); on heavily fragmented tables (check fragmentation via sys.dm_db_index_physical_stats).
• Schedule these as a recurring SQL Server Agent job during low-traffic maintenance windows.

Step 4: Adjust Memory and MAXDOP Settings

• Open SQL Server Management Studio (SSMS) > Server Properties > Memory, and set “Maximum server memory” to leave at least 4GB free for the Windows Server 2025 OS.
• Configure “Max Degree of Parallelism” (MAXDOP) appropriately—typically set to the number of cores per NUMA node (max 8) via sp_configure 'max degree of parallelism', 4; RECONFIGURE;.
• Restart the SQL Server service through Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply changes.

Alternative or Verification Steps

• Re-run the sys.dm_os_wait_stats query after 24 hours of normal workload; cumulative wait time per second should show a measurable decline, and Event ID 13133 should no longer appear in the Application log.
• If waits persist, use Performance Monitor (PerfMon) on Windows Server 2025 to track the Avg. Disk sec/Transfer counter—if above 20ms, the bottleneck is hardware-level storage latency requiring a disk subsystem upgrade rather than a SQL-side fix.

Why This Error Happens

Error 13132 typically occurs when SQL Server 2025’s Database Mirroring or Always On Availability Group endpoint encounters a transport-layer failure during data transfer between the primary and secondary replicas. This is most commonly caused by a blocked or misconfigured TCP port on the mirroring endpoint, an expired or mismatched SSL/TLS certificate used for endpoint authentication, or network latency/packet loss between nodes exceeding the transport timeout threshold. In some cases, Windows Firewall rules on Windows Server 2025 silently drop the endpoint traffic, triggering this error mid-transfer.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Connectivity and Port Configuration

• Open SQL Server Management Studio and run SELECT * FROM sys.database_mirroring_endpoints; to confirm the endpoint state is STARTED.
• Check the port number (default 5022) and ensure it matches on both the primary and secondary instances.
• Use Test-NetConnection -ComputerName <SecondaryServerName> -Port 5022 in PowerShell to confirm the port is reachable.

Step 2: Review and Update Windows Firewall Rules

• Navigate to Windows Defender Firewall with Advanced Security on both servers.
• Confirm an inbound rule exists allowing TCP traffic on the mirroring/AG endpoint port (e.g., 5022).
• If missing, create one via PowerShell: New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow.

Step 3: Validate Certificates and Authentication

• Run SELECT * FROM sys.certificates; on both instances to confirm the mirroring certificate hasn’t expired.
• Re-issue and re-exchange certificates between primary and secondary if expiration or mismatch is found.
• Restart the SQL Server service after certificate updates: Restart-Service MSSQLSERVER.

Step 4: Check Network Stability and Timeout Settings

• Inspect %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for repeated transport errors correlating with network drops.
• Increase the mirroring timeout using: ALTER ENDPOINT [Mirroring] STATE = STARTED (CONNECT_TIMEOUT = 30); if latency is consistently high.
• Run a continuous ping or psping test between nodes to rule out intermittent packet loss.

Alternative or Verification Steps

• After applying fixes, manually fail over the Availability Group or mirroring session and confirm no Error 13132 appears in the SQL Server Error Log or Windows Event Viewer (Application log).
• If the issue persists, drop and recreate the mirroring endpoint entirely using CREATE ENDPOINT with fresh certificates, then re-establish the mirroring/AG session from scratch.

Why This Error Happens

Error 13130 occurs when SQL Server 2025 attempts to create or rebuild a nonclustered index on a memory-optimized table, but the index definition violates In-Memory OLTP engine constraints—most commonly a missing BUCKET_COUNT value for a hash index, or a column data type/size exceeding the 8,060-byte row limit for a durable memory-optimized table. This is frequently triggered after migrating a traditional disk-based table to MEMORY_OPTIMIZED = ON without adjusting the index syntax. It can also surface if the underlying filegroup for memory-optimized data is corrupted or not properly mapped, causing the Storage Engine to reject the index metadata during validation.

Step-by-Step Resolution Guide

Step 1: Validate the Index Definition Syntax

• Open SQL Server Management Studio (SSMS) and locate the CREATE TABLE or ALTER TABLE statement that failed.
• Confirm that any NONCLUSTERED HASH index explicitly includes a BUCKET_COUNT clause, e.g.:

  INDEX ix_test NONCLUSTERED HASH (OrderID) WITH (BUCKET_COUNT = 1000000)

• If the index does not require hashing, switch to a standard NONCLUSTERED (range) index instead.

Step 2: Check Column Data Types and Row Size Limits

• Run the following query to inspect column types on the affected table:

  EXEC sp_help 'dbo.YourTableName';

• Ensure no (n)varchar(max), text, or image types are used in memory-optimized index keys—these are unsupported.
• Reduce column widths or move oversized columns to a separate disk-based table if necessary.

Step 3: Repair or Recreate the Memory-Optimized Filegroup

• Navigate to Object Explorer > Databases > YourDB > Storage > Filegroups in SSMS and confirm the MEMORY_OPTIMIZED_DATA filegroup path is valid (e.g., C:\SQLData\MemoryOptimized\).
• If the path is broken or inaccessible, detach and reattach the database, or restore from a known-good backup.
• Run DBCC CHECKDB (YourDB) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no corruption remains.

Step 4: Rebuild the Index with Corrected Parameters

• Drop the faulty index:

  DROP INDEX ix_test ON dbo.YourTableName;

• Recreate it using the corrected BUCKET_COUNT or data type adjustments from Steps 1–2.
• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11 if the schema change does not take effect immediately.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.hash_indexes to confirm the new index shows a valid bucket_count value and no error is logged in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the issue persists, consider converting the memory-optimized table back to a disk-based table temporarily, apply the schema changes, then re-enable MEMORY_OPTIMIZED = ON to force metadata regeneration.

Troubleshooting Guide: SQL Server Error 13131 (Credential Issue)

Why This Error Happens

Error 13131 in Microsoft SQL Server 2025 typically occurs when a database-scoped or server-level credential references authentication information that cannot be validated by the underlying operating system or external identity provider. This is most commonly caused by an expired, malformed, or mismatched secret (such as a SAS token, managed identity key, or Windows credential) stored in the credential object. It can also result from insufficient permissions on the credential’s associated identity when SQL Server attempts to use it for operations like BACKUP TO URL, CREATE EXTERNAL DATA SOURCE, or PolyBase authentication against Windows Server 2025 / Windows 11 security subsystems.

Step-by-Step Resolution Guide

Step 1: Verify the Credential Object Exists and Is Correctly Scoped

• Run SELECT name, credential_identity, create_date FROM sys.credentials; to confirm the credential is present at the server level.
• For database-scoped credentials, run SELECT name, credential_identity FROM sys.database_scoped_credentials; inside the relevant database context.
• Confirm the credential_identity matches exactly what the external service (Azure Storage, Windows Credential Manager, etc.) expects — case sensitivity and trailing spaces matter.

Step 2: Recreate the Credential with Updated Secret

• Drop the broken credential: DROP CREDENTIAL [CredentialName];
• Recreate it with a fresh secret or token:

CREATE CREDENTIAL [CredentialName]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<new-valid-SAS-token>';

• If using Windows Authentication-based credentials, ensure the identity string follows the DOMAIN\Username format exactly and the password hasn’t expired in Active Directory.

Step 3: Check Windows Credential Manager and Service Account Permissions

• Open Control Panel > Credential Manager on Windows Server 2025 / Windows 11 and verify no conflicting or expired Windows Credentials exist under “Windows Credentials.”
• Confirm the SQL Server service account has Log on as a service rights via secpol.msc > Local Policies > User Rights Assignment.
• Restart the SQL Server service (services.msc > SQL Server (MSSQLSERVER) > Restart) after any credential or permission change to force re-authentication.

Step 4: Validate External Resource Access (If Using Cloud Storage or PolyBase)

• Test connectivity independently using Azure Storage Explorer or azcopy with the same SAS token/key to confirm it’s not an external-side expiration issue.
• Check firewall rules on Windows Server 2025 to ensure outbound HTTPS (443) traffic isn’t being blocked, which can trigger a false credential validation failure.
• Review ERRORLOG via EXEC xp_readerrorlog 0, 1, N'13131'; for additional context on which specific credential operation failed.

Alternative or Verification Steps

• Verify the fix: Re-run the original operation (backup, restore, or external table query) that triggered Error 13131, and confirm no error is raised; also check sys.dm_server_registry or Extended Events for successful authentication logging.
• Alternative fix: If the credential continues to fail, switch authentication method entirely — for example, move from SAS token-based credentials to Managed Identity (if running on Azure-connected infrastructure) or use a dedicated Active Directory service account with explicit ALTER ANY CREDENTIAL permission granted via GRANT ALTER ANY CREDENTIAL TO [UserName];.

Troubleshooting Guide: SQL Server Error 13128 — Security Descriptor Issue

Why This Error Happens

Error 13128 typically occurs when SQL Server 2025 encounters a corrupted, malformed, or inaccessible security descriptor associated with a database object, Availability Group resource, or Service Broker endpoint. This is often caused by a mismatch between the Windows Security Identifier (SID) stored in the descriptor and the current Active Directory/local account mappings, especially after a server migration, domain rename, or restore from a backup taken on a different domain. In some cases, this also stems from insufficient permissions for the SQL Server service account to read security metadata from sys.security_descriptors or the underlying NTFS permissions on system database files.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service is running under the correct domain/service account.
• Ensure the service account has “Full Control” on the SQL Server data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Run the following in an elevated PowerShell to check current permissions:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA"

Step 2: Rebuild or Refresh the Security Descriptor

• Connect via SQL Server Management Studio (SSMS) 21 and run DBCC CHECKDB with the DATA_PURITY option to identify corrupted descriptor entries:

  DBCC CHECKDB ('YourDatabaseName') WITH DATA_PURITY;

• If corruption is confirmed, execute sp_refresh_security_descriptor (new in SQL Server 2025) to force a rebuild of the object’s descriptor cache:

  EXEC sys.sp_refresh_security_descriptor @object_name = 'dbo.YourObject';

Step 3: Resynchronize Orphaned SIDs

• Run sp_change_users_login or the newer ALTER USER ... WITH LOGIN syntax to remap orphaned SIDs between the Windows login and the SQL Server database user.
• Example fix command:

  ALTER USER [YourUser] WITH LOGIN = [DOMAIN\YourUser];

• Restart the SQL Server (MSSQLSERVER) service to flush the cached security token.

Step 4: Check Group Policy and Local Security Policy

• On Windows Server 2025, open secpol.msc and verify the “Access this computer from the network” policy includes the SQL Server service account.
• Confirm there are no conflicting Group Policy Objects (GPOs) pushing restrictive descriptor templates via Group Policy Management Console (GPMC).

Alternative or Verification Steps

• To verify the fix, re-run DBCC CHECKDB and confirm no errors reference 13128 or security descriptor mismatches in the output.
• If the issue persists, restore the affected database to a point before the domain change/migration using RESTORE DATABASE ... WITH REPLACE, then reapply transaction logs to bring it current while rebuilding permissions from scratch.

Troubleshooting Guide: Error 13129 – Hypothetical Index Issue in SQL Server 2025

Why This Error Happens

Error 13129 typically occurs when the Database Engine Tuning Advisor or a query attempts to create or reference a hypothetical index using sp_create_hypothetical, but the operation conflicts with existing metadata constraints or insufficient permissions on the target database. This can also happen if the INDEX DDL statement includes unsupported syntax for hypothetical indexes, such as specifying column options incompatible with statistics-only index creation. In some cases, corrupted system catalog views (sys.indexes or sys.stats) prevent SQL Server from properly registering the hypothetical index.

Step-by-Step Resolution Guide

Step 1: Verify Database and Session Permissions

• Ensure the executing login has CONTROL permission on the database or is a member of db_owner.
• Run the following to check current permissions:

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• Confirm the session is not running under a restricted contained database user, as hypothetical index creation requires elevated metadata access.

Step 2: Validate the Hypothetical Index Creation Syntax

• Review the T-SQL statement for correct use of the WITH STATISTICS_ONLY = -1 clause required for hypothetical indexes.
• Example of correct syntax:

  CREATE INDEX ix_hypothetical_test
  ON dbo.YourTable(ColumnName)
  WITH STATISTICS_ONLY = -1;

• Remove any unsupported options like INCLUDE, FILLFACTOR, or ONLINE = ON, which are incompatible with hypothetical index creation.

Step 3: Clear Corrupted Metadata and Rebuild Statistics

• Drop any orphaned hypothetical indexes left behind from failed Tuning Advisor sessions:

  SELECT name, index_id FROM sys.indexes WHERE is_hypothetical = 1;
  DROP INDEX ix_hypothetical_test ON dbo.YourTable;

• Run DBCC CHECKDB on the affected database to rule out catalog corruption:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

• Restart the SQL Server service via services.msc on Windows Server 2025 if metadata locks persist.

Step 4: Update SQL Server 2025 Cumulative Update

• Open SQL Server Setup or use PowerShell to check the current build version:

  SELECT @@VERSION;

• Apply the latest Cumulative Update (CU) from Microsoft, as early 2025 builds had a known issue (tracked internally) causing Error 13129 during concurrent tuning sessions.
• Reboot the Windows Server 2025 instance after patching to ensure the Database Engine service fully reinitializes.

Alternative or Verification Steps

• Re-run the original query or Tuning Advisor session after applying the fixes; confirm no Error 13129 appears in SQL Server Logs (SQL Server Management Studio > Management > SQL Server Logs).
• As an alternative, use sp_create_hypothetical manually in a new query window to isolate whether the issue is tool-specific (Tuning Advisor) or a core engine permission/metadata problem.

Troubleshooting Guide: SQL Server Error 13127 (Database Mirroring/Availability Group Principal Issue)

Why This Error Happens

Error 13127 typically occurs when the database mirroring or Always On Availability Groups endpoint cannot properly communicate the principal role status, often due to a mismatch in the mirroring state metadata stored in the system catalog. This frequently happens after an unexpected failover, a corrupted endpoint configuration, or when the service account lacks the necessary CONNECT permission on the mirroring endpoint. In some cases, this is triggered by network latency causing the principal to lose quorum with the witness or secondary replicas, leaving the database in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Status and Permissions

• Open SQL Server Management Studio (SSMS) and connect to the principal instance.
• Run SELECT * FROM sys.database_mirroring_endpoints; to confirm the endpoint state is STARTED.
• Ensure the service account has CONNECT permission: GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint_Name] TO [DOMAIN\ServiceAccount];
• Check that the endpoint is listening on the correct port (default 5022) using netstat -ano | findstr 5022 in an elevated Command Prompt.

Step 2: Check Firewall and Network Configuration

• Navigate to Windows Defender Firewall with Advanced Security on Windows Server 2025 / Windows 11.
• Confirm an inbound rule exists allowing TCP traffic on port 5022 (or your custom mirroring port) for sqlservr.exe.
• Run Test-NetConnection -ComputerName <SecondaryServerName> -Port 5022 in PowerShell to confirm connectivity between principal and secondary/witness servers.
• If blocked, add a new rule: New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow.

Step 3: Reset the Mirroring Session or Availability Group

• On the principal database, run ALTER DATABASE [YourDB] SET PARTNER OFF; to break a stale mirroring session.
• Re-establish mirroring using ALTER DATABASE [YourDB] SET PARTNER = 'TCP://SecondaryServer:5022'; from the principal.
• For Availability Groups, instead remove and re-add the replica via SSMS > Always On High Availability > Availability Group Replicas.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) to flush cached endpoint states.

Step 4: Review SQL Server Error Logs and Repair Metadata

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path varies per instance name/version folder).
• Search for entries immediately preceding Error 13127 to identify if a role-switch or timeout triggered it.
• If metadata corruption is suspected, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to validate database consistency.

Alternative or Verification Steps

• Confirm resolution by querying SELECT database_id, mirroring_role_desc, mirroring_state_desc FROM sys.database_mirroring; and verifying the role reads PRINCIPAL with state SYNCHRONIZED.
• If the issue persists, fail over manually to the secondary replica (ALTER DATABASE [YourDB] SET PARTNER FAILOVER;) and then fail back once connectivity is restored to force a clean resynchronization.

Troubleshooting Guide: SQL Server Error 13125 with EXECUTE AS

Why This Error Happens

Error 13125 typically occurs when the EXECUTE AS clause references a principal (login or user) that either does not exist, has been corrupted, or lacks the necessary IMPERSONATE permission within the database context. This often happens after a database migration or restore, where the Security Identifier (SID) of the user becomes “orphaned” and no longer matches a valid server login. Additionally, this can be triggered if the module (Stored Procedure or Function) was created with a specific execution context that was later dropped from the system.

Step-by-Step Resolution Guide

Step 1: Verify the Principal Exists

• Open SQL Server Management Studio (SSMS) and connect to the instance running on your Windows Server 2025 machine.
• Run the following query to check if the user referenced in the EXECUTE AS clause actually exists in the specific database:

  SELECT name, type_desc FROM sys.database_principals WHERE name = 'YourUserName';

• If the query returns zero rows, the principal is missing and must be recreated or the stored procedure must be updated to point to a valid user.

Step 2: Grant Impersonate Permissions

• If the user exists but the error persists, the login likely lacks permission to impersonate the target principal.
• Execute the following T-SQL command to grant the necessary rights:

  GRANT IMPERSONATE ON USER::[TargetUserName] TO [YourLoginName];

• Ensure you replace TargetUserName with the user specified in the EXECUTE AS clause of the failing procedure.

Step 3: Fix Orphaned Users (SID Mismatch)

• Navigate to the Windows Server 2025 Start Menu and open SQL Server Configuration Manager to ensure the service account has not changed.
• In SSMS, run sp_change_users_login (for legacy compatibility) or use the ALTER USER command to remap the user to the correct login SID:

  ALTER USER [YourUserName] WITH LOGIN = [CorrectLoginName];

• Restart the SQL Server service via the Services app (services.msc) in Windows Server 2025 to clear any cached security tokens.

Step 4: Check Database Trustworthy Settings

• If the EXECUTE AS is being used for cross-database queries, verify that the TRUSTWORTHY property is set correctly for the database.
• Run the following command to check the current status:

  SELECT name, is_trustworthy_on FROM sys.databases WHERE name = 'YourDatabaseName';

• If necessary, enable it using ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON; (Note: Use this with caution due to security implications).

Alternative or Verification Steps

• Verify the Fix: Re-run the specific stored procedure or query that triggered Error 13125. If the code executes without the “cannot execute as the database principal” error, the permissions have been successfully repaired.
• Alternative Fix: If the permission issues persist due to complex Active Directory policies on Windows Server 2025, consider switching the EXECUTE AS context from a specific user to EXECUTE AS CALLER if the business logic allows it, as this bypasses the need for specific impersonation rights.

Why This Error Happens

Error 13126 occurs when SQL Server attempts to locate a symmetric key that either does not exist in the current database scope or the key exists but the calling principal lacks the necessary permissions to open it. This typically happens after a database restore or migration where the Database Master Key (DMK) or certificate used to encrypt the symmetric key was not properly re-associated with the new server instance. Corruption in the key metadata within the sys.symmetric_keys catalog view, or an incorrect DECRYPTION BY clause referencing the wrong certificate/password, can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify the Symmetric Key Exists in the Current Database

• Run the following query to confirm the key is registered in the target database:

SELECT name, key_algorithm, key_length FROM sys.symmetric_keys;

• If the key is missing, it indicates the encryption hierarchy was not restored correctly, or the key was created in a different database context.

Step 2: Re-Open the Key with the Correct Decryption Method

• Ensure you are opening the key using the exact certificate or password originally used to encrypt it:

OPEN SYMMETRIC KEY YourKeyName
DECRYPTION BY CERTIFICATE YourCertName;

• If using password-based decryption, double-check for hidden whitespace or encoding mismatches in the password string, as Windows Server 2025 environments with updated locale settings can sometimes alter character encoding during copy-paste.

Step 3: Validate Database Master Key and Certificate Availability

• Confirm the Database Master Key (DMK) is open and accessible:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• Check that the certificate protecting the symmetric key has not expired by querying sys.certificates and reviewing the expiry_date column.
• If the certificate was backed up and restored, verify the private key file (.pvk) matches the certificate using:

RESTORE CERTIFICATE YourCertName
FROM FILE = 'C:\Backup\YourCert.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup\YourCert.pvk', DECRYPTION BY PASSWORD = 'YourPvkPassword');

Step 4: Grant Required Permissions to the Login or User

• Ensure the user attempting to open the key has CONTROL permission on the certificate or the symmetric key itself:

GRANT CONTROL ON CERTIFICATE::YourCertName TO [YourUserName];

• Re-test the OPEN SYMMETRIC KEY statement after granting permissions.

Alternative or Verification Steps

• Run a test encryption/decryption cycle using ENCRYPTBYKEY and DECRYPTBYKEY on a sample value to confirm the key opens and functions correctly end-to-end.
• If the key cannot be recovered due to a lost certificate or password, the only alternative is to re-create the symmetric key and re-encrypt all affected data columns from a secure backup source, as SQL Server 2025 does not provide a bypass for lost encryption hierarchies.

Troubleshooting Guide: SQL Server Error 13124 — Full-Text Catalog Issue

Why This Error Happens

Error 13124 typically occurs when SQL Server’s Full-Text Search (FTS) service cannot properly access, populate, or rebuild a full-text catalog due to corrupted catalog files, insufficient permissions on the data directory, or an interrupted population (crawl) process. This is often triggered by the MSSQLFDLauncher service lacking proper access rights to the catalog folder, or by abrupt server shutdowns during an active full-text index population. In some cases, disk-level corruption or a mismatch between the catalog metadata in the system tables and the physical files on disk causes this error to surface during a rebuild or query against the catalog.

Step-by-Step Resolution Guide

Step 1: Verify Full-Text Service Status and Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm SQL Full-text Filter Daemon Launcher (MSSQLFDLauncher) is running.
• Check that the service account has Modify permissions on the full-text data directory, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FTData
• Run EXEC sp_fulltext_service 'load_os_resources', 1; in SSMS to reload OS resources if permissions were recently changed.

Step 2: Rebuild the Corrupted Full-Text Catalog

• In SSMS, navigate to Databases > [YourDB] > Storage > Full Text Catalogs.
• Right-click the affected catalog and select Rebuild (this drops and recreates the catalog, so re-population is required).
• Alternatively, run via T-SQL:

  ALTER FULLTEXT CATALOG YourCatalogName REBUILD;

• Monitor progress using:

  SELECT * FROM sys.dm_fts_index_population;

Step 3: Check and Repair File System Integrity

• Run CHKDSK C: /F /R on the volume hosting the FTData directory (requires a restart if the volume is in use).
• Confirm no antivirus or Windows Defender real-time scanning is locking files in the FTData folder; add an exclusion if necessary.
• Restart the SQL Server (MSSQLSERVER) and MSSQLFDLauncher services after the check completes.

Step 4: Validate SQL Server Error Logs and Catalog Metadata

• Review the SQL Server error log (ERRORLOG in the MSSQL\Log folder) for additional context around the 13124 event.
• Run DBCC CHECKDB on the database to rule out broader corruption affecting the full-text index tables.
• If metadata mismatch is suspected, drop and recreate the full-text index on the affected table using CREATE FULLTEXT INDEX.

Alternative or Verification Steps

• After rebuilding, run a test query using CONTAINS() or FREETEXT() against the indexed column to confirm the catalog returns accurate results.
• If the issue persists, detach and reattach the database, or restore from a known-good backup prior to re-enabling full-text indexing.

Why This Error Happens

Error 13122 occurs when SQL Server’s XML parser encounters a duplicate or conflicting namespace declaration while validating an XML Schema Collection or processing an XML data type instance. This typically happens when an XSD import contains the same targetNamespace declared multiple times, or when a malformed XML document references a namespace prefix that was already bound to a different URI in the same scope. It can also surface after an in-place upgrade to SQL Server 2025 if legacy schema collections were created under older, more permissive XML parsing rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate schema collections tied to the affected database:
• SELECT name, xml_schema_namespace FROM sys.xml_schema_collections;
• Cross-reference the xml_schema_namespace column against the XML document causing the failure to spot duplicate xmlns entries.

Step 2: Validate and Clean the XSD Source File

• Open the source .xsd file in a text editor or Visual Studio XML editor.
• Search for repeated xmlns:prefix="URI" declarations within the same <xs:schema> root element.
• Remove or consolidate duplicate namespace prefixes so each URI is bound to only one prefix.
• Save the corrected file to C:\SQLSchemas\Validated\ before re-importing.

Step 3: Drop and Recreate the Schema Collection

• Execute DROP XML SCHEMA COLLECTION [SchemaName]; in SSMS connected to the target database.
• Re-create it using the cleaned XSD:
• CREATE XML SCHEMA COLLECTION [SchemaName] AS 'C:\SQLSchemas\Validated\cleaned_schema.xsd';
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached parser state.

Step 4: Apply Latest Cumulative Update

• Check Windows Update or the Microsoft Update Catalog for the latest SQL Server 2025 Cumulative Update (CU).
• Install the CU, as early builds of SQL Server 2025 had a known XML parser regression affecting namespace scoping on Windows Server 2025/Windows 11 hosts.

Alternative or Verification Steps

• Re-run the original query or stored procedure that triggered Error 13122; confirm it completes without exception using SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended'; to ensure no blocking remains.
• If the error persists, isolate the issue by testing the XML import on a clean SQL Server 2025 instance with default settings—if it succeeds there, the problem is environment-specific (collation or compatibility level) rather than the XML file itself.

Why This Error Happens

Error 13123 occurs when SQL Server 2025 attempts to load a .NET CLR assembly that is either corrupted, built against an incompatible .NET runtime version, or blocked by the stricter CLR security model enforced by default in SQL Server 2025. Since CLR_STRICT_SECURITY is enabled by default, SQL Server requires assemblies to be properly signed with a certificate or asymmetric key mapped to a login with UNSAFE ASSEMBLY permission before they can be registered, and failure to meet this requirement triggers the error. This can also surface after an OS or SQL Server upgrade to Windows Server 2025/Windows 11, where updated .NET Framework or .NET Core runtime components invalidate previously working assembly bindings.

Step-by-Step Resolution Guide

Step 1: Verify Assembly Integrity and Compatibility

• Confirm the .dll file is not corrupted by checking its properties and file hash against the source build output.
• Open PowerShell and run Get-FileHash "C:\Assemblies\MyAssembly.dll" to compare against the known-good hash.
• Ensure the assembly targets a .NET Framework version supported by SQL Server 2025 (typically .NET Framework 4.8 or later CLR integration runtime).

Step 2: Check and Configure CLR Strict Security

• Run the following query to confirm strict security is enabled:
• sp_configure 'clr strict security';
• If enabled (default = 1), the assembly must be signed with a certificate/asymmetric key tied to a server login.
• Create a certificate-based trust using:
• CREATE ASYMMETRIC KEY MyAsmKey FROM EXECUTABLE FILE = 'C:\Assemblies\MyAssembly.dll';
• CREATE LOGIN MyAsmLogin FROM ASYMMETRIC KEY MyAsmKey;
• GRANT UNSAFE ASSEMBLY TO MyAsmLogin;

Step 3: Re-register the Assembly in the Database

• Drop the existing broken assembly reference (if present):
• DROP ASSEMBLY MyAssembly;
• Re-create it explicitly specifying the permission set required:
• CREATE ASSEMBLY MyAssembly FROM 'C:\Assemblies\MyAssembly.dll' WITH PERMISSION_SET = SAFE;
• If the assembly genuinely requires external resource access, use UNSAFE only after the signing step in Step 2 is completed.

Step 4: Validate TRUSTWORTHY and Database Ownership Settings

• Check if the database relies on the deprecated TRUSTWORTHY ON setting instead of certificate signing:
• SELECT name, is_trustworthy_on FROM sys.databases WHERE name = 'YourDatabase';
• Migrate away from TRUSTWORTHY ON to the certificate-based model described in Step 2, as Microsoft recommends this for SQL Server 2025 security compliance.

Step 5: Restart SQL Server Service and Clear Plan Cache

• Restart the SQL Server (MSSQLSERVER) service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.
• Clear cached execution plans referencing the old assembly:
• DBCC FREEPROCCACHE;

Alternative or Verification Steps

• Confirm the fix by querying SELECT * FROM sys.assemblies WHERE name = 'MyAssembly'; and ensuring the permission_set_desc matches your intended configuration (SAFE/UNSAFE) with no error on the next ALTER ASSEMBLY or CLR function call.
• If registration still fails, test the assembly load against a fresh scratch database to isolate whether the issue is assembly-specific or related to corrupted system metadata in the original database.

Troubleshooting Guide: SQL Server Error 13120 (sql:variable)

Why This Error Happens

Error 13120 in SQL Server 2025 typically occurs within Service Broker or Extended Events (XEvent) configurations when the sql:variable predicate references a variable that is either undeclared, out of scope, or incompatible with the event session’s data type requirements. This commonly arises from corrupted session definitions, a mismatch between the variable’s declared type and the event column it is being compared against, or insufficient permissions on the DMVs required to resolve the variable at runtime. Additionally, upgrades from older SQL Server versions to 2025 can leave behind deprecated syntax in XML-based session configurations that the new parser rejects.

Step-by-Step Resolution Guide

Step 1: Validate the Extended Events Session Definition

• Open SQL Server Management Studio (SSMS) and navigate to Management > Extended Events > Sessions.
• Right-click the affected session and select Script Session as > CREATE To > New Query Editor Window.
• Inspect the sql:variable predicate syntax; ensure it follows the format sql:variable(variable_name) without extra quotation marks or namespace prefixes.
• Run SELECT * FROM sys.dm_xe_object_columns WHERE object_name = 'your_event_name' to confirm the variable name exists and matches exactly (case-sensitive).

Step 2: Correct Data Type Mismatches

• Cross-reference the variable’s type against the type_name column in sys.dm_xe_objects.
• Rebuild the predicate using an explicit cast if needed, for example: sql:variable('CPU_BUSY') > CAST(80 AS int).
• Drop and recreate the session using T-SQL instead of the SSMS wizard to avoid legacy XML artifacts:

  DROP EVENT SESSION [YourSession] ON SERVER;
  GO

• Redefine the session fresh under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ and verify no orphaned .xel files are locking the definition.

Step 3: Check Permissions and Service Account Rights

• Confirm the SQL Server service account has ALTER ANY EVENT SESSION permission:

  GRANT ALTER ANY EVENT SESSION TO [DOMAIN\ServiceAccount];

• On Windows Server 2025/Windows 11, verify the account also has read access to C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\SQL Server\ for diagnostic logging.
• Restart the SQL Server (MSSQLSERVER) service via services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Repair Corrupted Session Metadata

• If the error persists, run DBCC CHECKDB against the master database to rule out system catalog corruption.
• Use SQL Server Configuration Manager to verify the instance is running the latest 2025 Cumulative Update, since variable-scoping bugs in early builds were patched in CU2.

Alternative or Verification Steps

• Verify the fix by re-running the session creation script and querying sys.dm_xe_sessions to confirm the session is active without errors in the SQL Server Error Log.
• As an alternative, bypass the predicate temporarily by using a static value instead of sql:variable to confirm the session runs correctly, then incrementally reintroduce the variable to isolate the faulty reference.

Why This Error Happens

Error 13121 occurs when the SQL Server XML/XQuery engine cannot resolve a sql:column() reference used inside an XQuery expression or a Selective XML Index definition. This typically happens because the referenced column does not exist in the current row context, the column’s data type is incompatible with the XML node being mapped, or the XML Schema Collection bound to the column is out of sync with the query. It can also surface after an in-place upgrade to SQL Server 2025 if legacy XML indexes were created against a schema collection that has since been altered or dropped.

Step-by-Step Resolution Guide

Step 1: Validate the Column Reference in the Query

• Open the failing query in SQL Server Management Studio (SSMS) and locate the sql:column("ColumnName") call.
• Confirm the column name exactly matches a column in the outer query’s FROM clause (case-sensitive if the instance uses a case-sensitive collation).
• Run SELECT name, system_type_id FROM sys.columns WHERE object_id = OBJECT_ID('YourTable'); to confirm the column exists and check its data type.

Step 2: Check the XML Schema Collection Binding

• Run SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollectionName'; to verify the schema collection is still present.
• If the collection was modified, drop and recreate any dependent Selective XML Indexes:
• DROP SELECTIVE XML INDEX [SXI_Index] ON [dbo].[YourTable];
• CREATE SELECTIVE XML INDEX [SXI_Index] ON [dbo].[YourTable] ([XmlColumn]) FOR ([PromotedPath] = 'sql:column("ColumnName")');

Step 3: Repair Corrupted Index Metadata

• Run DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out metadata corruption affecting the XML index structures.
• If corruption is found, restore from the last known good backup rather than running repair options that allow data loss.

Step 4: Apply Latest Cumulative Update

• On Windows Server 2025 / Windows 11, open Settings > Windows Update and ensure the OS is fully patched, since SQL Server 2025 relies on updated Windows XML parser components (MSXML6).
• Separately, install the latest SQL Server 2025 Cumulative Update via the Microsoft Update Catalog, as early builds had a known regression in sql:column() type coercion for nvarchar(max) columns.

Alternative or Verification Steps

• After applying the fix, re-run the original query and confirm Error 13121 no longer appears in the SSMS results pane or the SQL Server error log (EXEC sp_readerrorlog 0, 1, '13121';).
• As a workaround if the error persists, cast the column explicitly in the query (e.g., CAST(sql:column("ColumnName") AS NVARCHAR(100))) to bypass implicit type-mapping issues.

Why This Error Happens

Error 13118 is triggered within the Always On Availability Groups (AG) subsystem when a replica attempts to establish a database mirroring endpoint connection but fails due to a mismatch in certificate authentication, an expired certificate, or a blocked communication port between the primary and secondary replicas. This typically occurs after a certificate rotation, a Windows Server 2025 security policy update, or a firewall rule change that silently blocks the default mirroring endpoint port (5022). In some cases, the error also surfaces when the SQL Server service account lacks CONNECT permission on the endpoint itself, preventing the replica handshake from completing.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Status and Configuration

• Open SQL Server Management Studio (SSMS) and connect to each replica instance.
• Run the following query to confirm the endpoint is started: SELECT name, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING';
• If state_desc returns STOPPED, restart it with: ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

Step 2: Validate Certificate or Authentication Settings

• Check whether the endpoint uses certificate-based authentication: SELECT name, certificate_id FROM sys.database_mirroring_endpoints;
• Confirm the certificate has not expired by querying sys.certificates on both the primary and secondary replicas.
• If expired, re-issue the certificate and re-establish trust using CREATE CERTIFICATE and ALTER ENDPOINT ... AS TCP (CERTIFICATE = ...) on all nodes.

Step 3: Check Firewall and Network Port Access

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security.
• Create or verify an inbound rule allowing TCP port 5022 (or your custom mirroring port) for the sqlservr.exe process.
• Test connectivity between nodes using: Test-NetConnection -ComputerName <ReplicaServerName> -Port 5022

Step 4: Grant Required Permissions on the Endpoint

• Ensure the SQL Server service account (or AG login) has CONNECT permission:
• GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\SQLServiceAccount];
• Restart the SQL Server service on the affected node via services.msc or Restart-Service MSSQLSERVER in PowerShell.

Step 5: Rejoin the Replica to the Availability Group

• If the connection still fails, remove and re-add the problematic replica using the Always On Availability Groups wizard in SSMS.
• Confirm synchronization health afterward in the Always On High Availability dashboard.

Alternative or Verification Steps

• Verify the fix by checking the SQL Server Error Log and Windows Event Viewer (Application log) for the recurrence of Event ID 13118; a clean log after restart confirms resolution.
• As an alternative, use DBCC CHECKDB and sys.dm_hadr_availability_replica_states to confirm all replicas report CONNECTED and HEALTHY status after applying the above steps.

Troubleshooting Guide: SQL Server Error 13119 (Endpoint Issue)

Why This Error Happens

Error 13119 in Microsoft SQL Server 2025 typically occurs when the Service Broker or Database Mirroring endpoint cannot be created, started, or validated because the specified port is already bound to another process or endpoint. This can also result from a mismatch in endpoint state (STARTED, STOPPED, DISABLED) during a configuration change, or from insufficient permissions on the endpoint object itself. In some cases, a corrupted or orphaned endpoint entry in sys.endpoints left over from a failed previous configuration can trigger this error when Windows Server 2025 attempts to re-initialize the TCP listener.

Step-by-Step Resolution Guide

Step 1: Verify Port Availability and Conflicts

• Open an elevated Command Prompt on Windows Server 2025 / Windows 11 and run netstat -ano | findstr <port_number> (commonly 5022 for mirroring or 4022 for Service Broker).
• Identify the PID using the port and cross-reference it in Task Manager to confirm it is not another SQL Server instance or conflicting service.
• If a conflict exists, either stop the conflicting process or reassign the endpoint to an unused port using ALTER ENDPOINT.

Step 2: Inspect and Reset the Endpoint Configuration

• Connect via SQL Server Management Studio (SSMS) and query existing endpoints:
• SELECT name, protocol, type, state_desc FROM sys.endpoints;
• If the endpoint is in a corrupted or inconsistent state, drop and recreate it:
• DROP ENDPOINT [Mirroring_Endpoint];
• CREATE ENDPOINT [Mirroring_Endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL);

Step 3: Validate Permissions and Firewall Rules

• Ensure the SQL Server service account has CONNECT permission on the endpoint: GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DOMAIN\ServiceAccount];
• Check Windows Defender Firewall on Windows Server 2025 to confirm the required port is allowed via New-NetFirewallRule -DisplayName "SQL Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow.
• Restart the SQL Server service (services.msc or Restart-Service MSSQLSERVER) to apply changes.

Step 4: Review SQL Server Error Logs

• Open the SQL Server Error Log via SSMS (Management > SQL Server Logs) or the file path C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for additional context lines immediately preceding Error 13119 to confirm whether it is a port, permission, or state-related trigger.

Alternative or Verification Steps

• Confirm the fix by querying SELECT state_desc FROM sys.endpoints WHERE name = 'Mirroring_Endpoint'; — it should return STARTED without errors.
• If the issue persists, use sp_helpserver and DBCC CHECKDB on msdb to rule out metadata corruption, or fully uninstall/reinstall the Service Broker/Mirroring feature component via SQL Server Configuration Manager.

Troubleshooting Guide: SQL Server Error 13117 (Role Assignment/Membership Failure)

Why This Error Happens

Error 13117 in Microsoft SQL Server 2025 typically occurs when a database role operation fails due to an invalid or missing role reference, often triggered by a mismatch between the role name and existing security principals in the database. This can happen when a role was partially created or dropped during a failed transaction, leaving orphaned metadata in the sys.database_role_members system catalog. Additionally, this error surfaces when attempting to add a member to a role that does not exist in the current database context, or when permissions on the msdb or user database are insufficient to complete the role membership change.

Step-by-Step Resolution Guide

Step 1: Verify the Role Exists in the Target Database

• Open SQL Server Management Studio (SSMS) 21 and connect to the instance running SQL Server 2025.
• Run the following query to confirm the role is present: SELECT name, type_desc FROM sys.database_principals WHERE type = 'R';
• If the role is missing, recreate it using CREATE ROLE [RoleName] AUTHORIZATION dbo;

Step 2: Check for Orphaned Role Metadata

• Execute EXEC sp_validatelogins; to identify orphaned users or roles with broken SID mappings.
• Run DBCC CHECKCATALOG ('YourDatabaseName'); to scan for catalog corruption affecting role structures.
• If orphaned entries are found, remove them using DROP ROLE [OrphanedRoleName]; after confirming no dependencies exist.

Step 3: Validate Permissions on the Database and Instance

• Confirm the executing login has ALTER ANY ROLE permission on the database in question.
• On Windows Server 2025, check that the SQL Server service account has not been restricted by a recent Group Policy update under Local Security Policy > User Rights Assignment.
• Grant missing permissions with: GRANT ALTER ANY ROLE TO [YourLogin];

Step 4: Rebuild the Role Membership Assignment

• Drop and recreate the specific role membership causing the conflict: ALTER ROLE [RoleName] DROP MEMBER [UserName]; followed by ALTER ROLE [RoleName] ADD MEMBER [UserName];
• Restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER' to clear cached security tokens.

Alternative or Verification Steps

• Verify the fix by re-running the original operation that triggered Error 13117 and confirming no error is returned in the SSMS Messages pane.
• If the issue persists, restore a recent backup of the master database (taken before the corruption occurred) in a test environment to compare role structures and identify the discrepancy.

Why This Error Happens

Error 13115 typically surfaces during the authentication handshake, when SQL Server 2025 cannot validate the security context passed by the client. This is most commonly caused by a missing or duplicate Service Principal Name (SPN) in Active Directory, an expired/mismatched TLS certificate bound to the SQL Server instance, or a corrupted logon trigger that throws an exception before the session is fully established. Because Windows Server 2025 and Windows 11 enforce stricter Kerberos and TLS 1.3 defaults, legacy SPN or certificate configurations that worked on older OS builds can suddenly fail the handshake.

Step-by-Step Resolution Guide

Step 1: Verify Service Principal Name (SPN) Registration

• Open an elevated Command Prompt and run setspn -L <SQLServiceAccount> to check existing SPNs.
• Confirm the SPN format matches MSSQLSvc/<FQDN>:<port> (e.g., MSSQLSvc/sql01.contoso.com:1433).
• If missing, register it manually: setspn -A MSSQLSvc/sql01.contoso.com:1433 DOMAIN\SQLServiceAccount.
• Restart the SQL Server service after registration via services.msc.

Step 2: Validate TLS Certificate Binding

• Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER.
• Check the Certificate tab and ensure a valid, non-expired certificate is selected (not self-signed unless explicitly trusted).
• If using a custom cert, confirm it resides in Local Computer\Personal store and the SQL service account has read access to the private key via certlm.msc.
• Re-apply the certificate and restart the SQL Server (MSSQLSERVER) service.

Step 3: Audit and Disable Problematic Logon Triggers

• Connect via SQLCMD using the Dedicated Admin Connection (DAC): sqlcmd -S ADMIN:sql01 -E.
• Run SELECT name, is_disabled FROM sys.server_triggers; to list active logon triggers.
• Disable any suspect trigger: DISABLE TRIGGER <TriggerName> ON ALL SERVER;.
• Attempt a standard login again to confirm the trigger was the blocking factor.

Step 4: Check Windows Security Event Logs

• Open Event Viewer → Windows Logs → Security, filter for Event ID 4625 (failed logon) around the same timestamp as SQL Error 13115.
• Cross-reference the failure reason code (e.g., 0xC000018B indicates no matching trust account) to pinpoint domain trust or SPN duplication issues.

Alternative or Verification Steps

• After applying fixes, test connectivity using sqlcmd -S sql01.contoso.com -E from a client machine joined to the same domain, confirming no 13115 error is thrown.
• If the issue persists, fail back to SQL Server Authentication temporarily (via SSMS → Server Properties → Security) to isolate whether the issue is Kerberos/SPN-specific or certificate-specific.

Troubleshooting Guide: SQL Server Error 13116 (Certificate Issue)

Why This Error Happens

Error 13116 in Microsoft SQL Server 2025 typically occurs when the database engine cannot validate, locate, or bind to the certificate used for secure communication, such as Service Broker encryption or SSL/TLS transport security. This is often caused by an expired certificate, a mismatch between the certificate’s private key and the one stored in the SQL Server instance, or insufficient permissions for the SQL Server service account to access the certificate store. In some cases, the certificate may have been issued with an incompatible algorithm or key length that the updated SQL Server 2025 security policies reject.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Validity and Expiration

• Open SQL Server Management Studio (SSMS) and run the following query to check certificate status:
• SELECT name, expiry_date, start_date, pvt_key_encryption_type_desc FROM sys.certificates;
• Confirm the expiry_date has not passed and the certificate is still within its valid lifecycle.
• If expired, you will need to regenerate or renew the certificate using your Certificate Authority (CA) or self-signed process.

Step 2: Check Service Account Permissions on the Certificate Store

• Open the Microsoft Management Console (MMC) by typing mmc in the Windows 11 / Windows Server 2025 search bar.
• Add the Certificates snap-in and select Local Computer account.
• Navigate to Certificates (Local Computer) > Personal > Certificates.
• Right-click the relevant certificate, select All Tasks > Manage Private Keys.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read permissions.

Step 3: Rebind the Certificate to the SQL Server Instance

• Open SQL Server Configuration Manager.
• Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Right-click Properties, go to the Certificate tab.
• Reselect the correct certificate from the dropdown list and click Apply.
• Restart the SQL Server service for changes to take effect:
• net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Re-Encrypt or Reissue the Certificate (If Corrupted)

• If the certificate thumbprint does not match the one registered in the SQL Server registry key, reissue a new certificate using PowerShell:
• New-SelfSignedCertificate -DnsName "YourServerName" -CertStoreLocation "cert:\LocalMachine\My"
• Update the SQL Server configuration to point to the new certificate thumbprint.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_exec_connections WHERE encrypt_option = 'TRUE'; to confirm encrypted connections are now functioning correctly.
• If the issue persists, temporarily disable “Force Encryption” in SQL Server Configuration Manager to isolate whether the certificate or a separate network policy is causing the failure, then re-enable it once the correct certificate is in place.