TDM Portal publish fails with "SQL state [S0001]; error code [544]; Cannot insert explicit value for identity column in table 'when IDENTITY_INSERT is set to OFF"

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Created Data Masking Solution for SQL Server Database using the TDM Portal Generators to perform "Inflight Masking".  The masking is getting completed with a number of records in log file. However, the target database does not have any records inserted into it. Looking at the status of the job, we see the following error reported:

Insert into <TABLE> failed: UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO [dbo].[<TABLE>] ([COLUMN1],[COLUMN2],[COLUMN3],[COLUMN4]} VALUES (?,?,?,?);
SQL state [S0001]; error code [544];
Cannot insert explicit value for identity column in table '<TABLE>' when IDENTITY_INSERT is set to OFF.

Environment

TDM Portal 4.11+

Cause

This is caused by a lack of user permissions. The user must have db owner permissions (own the table), or at the very least, the user must have ALTER permission on the table. If the user's is not allowed to ALTER the table, then we will see the following warning in the TDMPublish.log (debug mode enabled):

ExecuteDLLStmt failed for SET IDENTITY_INSERT [dbo].[<TABLE>] OFF, exception: StatementCallback; uncategorized SQLException for SQL [SET IDENTITY_INSERT [dbo].[<TABLE>] OFF]; SQL state [S0011]; error code [1088]; Cannot find the object "dbo.Env_Event_Date" because it does not exist or you do not have permissions.

Which results in the error that the IDENTITY_INSERT failed:

Failed to execute query due to 'PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO [dbo].[<TABLE>] ([<COLUMN1>],[<COLUMN2>],[<COLUMN3>],[<COLUMN4>]) VALUES (?,?,?,?)]; SQL state [S0001]; error code [544]; Cannot insert explicit value for identity column in table '<TABLE>' when IDENTITY_INSERT is set to OFF.'

When performing an IDENTITY_INSERT, only one single table per connection can have IDENTITY_INSERT set to ON. As a safeguard, TDM Portal will set IDENTITY_INSERT to OFF for all tables first, and then sets it to ON for the target table during the publishing. Once the publishing completes, we should set IDENTITY_INSERT OFF again for each table. 

In this use case, we found that the user making the connection was granted "db_datareader" and "db_datawriter" access, which only grants the ability to read, and perform INSERT, UPDATE, and DELETE to the table. When the user was granted "ddladmin" access, which allows the user to ALTER the table, the issue was resolved.

Resolution

Please work with your SQL Server DBA to verify the user account in the Publish connection profile is the db owner, or at the very least has the appropriate permissions to ALTER the required tables.

Additional Information

NOTE: the IDENTITY_INERT command is part of DML (data manipulation language - managing the data itself) while set insert identity is part of DDL (data definition language - managing the metadata, modifying the table schemas and so on). Normal users have usually only access to DML (for inserting and updating data) and for creating and modifying tables they need "stronger/additional" permissions.