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

by

Troubleshooting Guide: SQL Server Error 545 – Identity Column Insert Violation

Why This Error Happens

This error occurs when an INSERT statement attempts to explicitly supply (or omit) a value for an identity column without the session first enabling IDENTITY_INSERT for that specific table. SQL Server enforces this restriction because identity columns are designed to auto-generate sequential values, and explicit value insertion is only permitted in a controlled, temporary session state. Additionally, this error can surface in replication topologies when a replication agent inserts into a NOT FOR REPLICATION identity column without the proper internal login context, often due to misconfigured publication/subscription settings.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Query

  • Capture the exact table name referenced in the error message (%.*ls placeholder).
  • Review the application or ETL script issuing the INSERT statement to confirm whether an explicit ID value is being passed.
  • Check if the insert is coming from a manual script, an ORM (like Entity Framework), or a replication job.

Step 2: Enable IDENTITY_INSERT for Manual Inserts

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following before your INSERT statement:
  SET IDENTITY_INSERT dbo.YourTableName ON;
  INSERT INTO dbo.YourTableName (ID, ColumnA, ColumnB) 
  VALUES (105, 'Value1', 'Value2');
  SET IDENTITY_INSERT dbo.YourTableName OFF;
  • Remember that IDENTITY_INSERT can only be active for one table per session at a time.
  • Ensure you turn it OFF immediately after the insert to avoid conflicts with other transactions.

Step 3: Fix Application Code to Avoid Explicit IDs

  • Modify the application logic (C#, Java, Python, etc.) to exclude the identity column from the INSERT column list entirely.
  • For Entity Framework Core on .NET 9/10, verify the model configuration uses .ValueGeneratedOnAdd() correctly rather than manually setting the Key property.
  • Rebuild and redeploy the application to ensure it no longer passes a value for the auto-incrementing field.

Step 4: Resolve Replication-Specific Identity Conflicts

  • If the error stems from a replication agent, open SQL Server Management Studio and navigate to Replication > Local Publications.
  • Right-click the specific publication, select Properties, and go to the Articles page.
  • Verify the “Identity Range Management” settings under Article Properties are correctly configured to allow the Distribution Agent to manage NOT FOR REPLICATION values automatically.
  • Restart the SQL Server Agent service via services.msc on Windows Server 2025 to apply the updated replication settings.

Alternative or Verification Steps

  • Verify the Fix: Run DBCC CHECKIDENT ('dbo.YourTableName', NOCHECK) to confirm the current identity value seed matches the expected sequence after your manual insert.
  • Alternative Approach: If replication continues to throw this error intermittently, consider dropping and re-initializing the specific article’s identity range via the Replication Monitor to force a fresh allocation of NOT FOR REPLICATION ranges.