SAMLIdentifiers - Duplicate Key Value

I am occasionally receiving an error from SQL Server when the SAML component is inserting into the SAMLIdentifiers table. When setting the IDCache property in the Application_Start method in Global.asax, I am setting the DeleteExpiredPriorToAdd property of DatabaseIDCache to true.

The error is:

Violation of PRIMARYKEY constraint ‘PK_SAMLIdentifiers’. Cannot insert duplicate key in object’dbo.SAMLIdentifiers’. The duplicate key value is(avMqNLrmP_MrPST1ESY1jHpPxIR). The statement has been terminated.

Stack trace:
atSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection,Action wrapCloseInAction)
atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommandcmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async,Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,TaskCompletionSource completion, Int32 timeout, Task& task, BooleanasyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSourcecompletion, String methodName, Boolean sendToPipe, Int32 timeout, BooleanasyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ComponentSpace.SAML2.Data.DatabaseIDCache.Add(String id, DateTimeexpirationDateTime)

How can I solve this?


Hi Brian
This indicates the SAML assertion ID is already present in the table and this is considered to be a potential assertion replay attack. The most likely cause is resubmission (eg browser refresh) of the page containing the SAML response/assertion Post data by the user.
SAML assertion IDs should be unique across time.
The DeletePriorToAdd is not to avoid potential duplicates but rather just housekeeping to delete expired entries from the table.
Let me know if you have any other concerns.

Thank you for the additional background. By your statement that SAML Assertion IDs should be unique across time, does that mean that each SSO request, even from the same user, should have a new SAML Assertion ID?

I am seeing records disappear from the SAMLIdentifiers table long before their expiration dates. We have not implemented SSO logout. What could be causing these records to disappear?

I am trying to determine why our users are getting errors from our SAML service provider site. So far, the duplicate key errors are the only errors I am seeing reported from the code. Is there a way for me to log the SAML token that is being sent to my service provider site?


That’s correct. Every assertion ID, regardless of whether it’s for the same user or not, should be unique.
The DeleteExpiredPriorToAdd property will cause expired entries to be deleted from the table.
The SQL associated with setting this property to true is simply to delete rows from the table where the ExpirationDateTime column is less than or equal to the UTC now time.
Could you please provide more specific information regarding records disappearing? What is the ExpirationDateTime for these records and when are they deleted?
You can enable SAML trace to see the authn request being sent to the identity provider. This will also log any errors.
Also, I suggest checking the identity provider log to see if any errors are being logged there.
Can the issue be replicated? It would be interesting to know whether the user hits the browser refresh button or not.

Now that I know that the date and time being used for the expiration is in UTC, it makes sense. I was assuming the date and time were in the local timezone.

I have added the trace section to the web.config of the service provider site. I don’t have access to the identity provider logs. While I can replicate the PK issue by hitting refresh or using the browser history, I haven’t determined whether that is the cause for our users yet. I am hoping that the trace will turn up some clues.


If you’d like us to take a look at the log file please send it as an email attachment to mentioning this topic.