User Anonymization Fails - SQL Server 2019

Owned by Ciaran Bryden (Deactivated)
Last updated: Feb 23, 2022
2 min read

Introduction

User Anonymization is a feature in Jira (from Jira 8.7.0) which removes/changes personal user data in fields, issues, schemes and more. For more information on this feature see below:

JEMH extends this Jira feature as part of ‘Transferring ownership’, where users specified in JEMH Profiles, audit records etc are changed to the new given user or anonymized.

Problem

Anonymization task fails with error along the lines of:

com.microsoft.sqlserver.jdbc.SQLServerException: The data types ntext and nvarchar are incompatible in the equal to operator.

This error will show in the logs or on the UI, like below:

Solution

Fixing this issue requires access to the database and running a query to change the entity type to a valid one. As there are many entities used in JEMH as part of anonymization, we have put together a query which includes all of them. Using Microsoft SQL Server Management Studio (SMSS) you can create a new query on your database and execute the following query:

Change ‘jiraschema’ to the name of your schema.

ALTER TABLE [jiraschema].[AO_78C957_CONFIG] ALTER COLUMN REPORTER_USER_NAME NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_CONFIG] ALTER COLUMN FORWARD_USER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_CONFIG] ALTER COLUMN PRIVILEGED_USER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_CONFIG] ALTER COLUMN CUSTOM_EMAIL_FROM_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_CONFIG] ALTER COLUMN CUSTOM_EMAIL_REPLY_TO_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_AUDITEVENTS] ALTER COLUMN EMAIL_SOURCE_USER_KEY NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_AUDITEVENTS] ALTER COLUMN FROM_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_NOTIF_SOURCE] ALTER COLUMN EVENT_USER_KEY NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_LSNR_PROJ] ALTER COLUMN JIRA_FROM_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_LSNR_PROJ] ALTER COLUMN JIRA_REPLY_TO_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_LSNR_PROJ] ALTER COLUMN NON_JIRA_REPLY_TO_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_LSNR_PROJ] ALTER COLUMN NON_JIRA_FROM_ADDRESS NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_LSNR_CONF] ALTER COLUMN PRIVILEGED_USER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_NAGCONFIG] ALTER COLUMN NAGIOS_REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_REGEXPPROCCNF] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PROJCONFIG] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PROJCONFIG] ALTER COLUMN ASSIGNEE NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJSCRIPTMAP] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJSCRIPTMAP] ALTER COLUMN ASSIGNEE NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJDOMMAP] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJDOMMAP] ALTER COLUMN ASSIGNEE NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJGRPMAP] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJGRPMAP] ALTER COLUMN ASSIGNEE NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJKYWRDMAP] ALTER COLUMN REPORTER NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_PRJKYWRDMAP] ALTER COLUMN ASSIGNEE NVARCHAR(255) ALTER TABLE [jiraschema].[AO_78C957_WFRULE] ALTER COLUMN RUN_AS NVARCHAR(255)

Now stop Jira, restart the database and start Jira again.

The above query may not be enough for anonymizing admin users, as more JEMH lifecycle entities have admin user records for created and updated columns.

If you find more ntext types in other tables, you can modify these queries. You will need:

  • The table name (e.g. AO_78C957_WFRULE)

  • The column name (e.g. RUN_AS)