Issue description

Some Service Desk functionalities are not working for some customers using Service Desk 4.3.0 or higher with a MS SQL (Microsoft SQL Server) database.

The functionalities that are known to be impacted are listed below:

• Customers cannot access their requests on the customer portal
• Customers can't be added as request participants
• the Service desk mail handler won't process any incoming email

Note that this list is not exhaustive, as more functionalities might be impacted
Note that this bug is known to only impact customers who had Service Desk 1.x or 2.x installed at some point. This bug does not impact customers who have been using Service Desk from the version 3.x or higher.

Diagnosis Steps

To verify if you are impacted by this bug, check if:

• you are using Service Desk 4.3.x (or Jira 8.3.x)
• Jira is connected to a Microsoft SQL Database
• you can find any of the 2 following errors in the Jira Logs (if you can see it, then you are likely impacted):
  • error 1

    2019-08-12 12:18:17,027 JIRA-Bootstrap ERROR [bootstrap.lifecycle.server.ServerPluginLifeCycle] ActiveObjects could not be initialised
    com.atlassian.pocketknife.api.ao.ActiveObjectInitialisationException: ActiveObjects could not be initialised
    at com.atlassian.pocketknife.internal.ao.ActiveObjectsControllerImpl.initialise(ActiveObjectsControllerImpl.java:42)
    
    Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
    Database:
    - name:Microsoft SQL Server
    - version:13.00.5366
    - minor version:0
    - major version:13
    Driver:
    - name:Microsoft JDBC Driver 7.2 for SQL Server
    - version:7.2.1.0
    
    com.microsoft.sqlserver.jdbc.SQLServerException: The object 'df_AO_54307E_SERVICEDESK_PROJECT_KEY' is dependent on column 'PROJECT_KEY'.
    

  • error 2:

    Exception executing SQL update <ALTER TABLE jiraschema.AO_54307E_SERVICEDESK ALTER COLUMN PROJECT_KEY NVARCHAR(255)>
    com.microsoft.sqlserver.jdbc.SQLServerException: The object 'df_AO_54307E_SERVICEDESK_PROJECT_KEY' is dependent on column 'PROJECT_KEY'.
    

Workaround

Workaround 1

This workaround consists in updating the column type of PROJECT_KEY from VARCHAR to NVARCHAR for the AO_54307E_SERVICEDESK table

1. Remove the 'df_AO_54307E_SERVICEDESK_PROJECT_KEY' constraint:

   ALTER TABLE [dbo].[AO_54307E_SERVICEDESK] DROP CONSTRAINT [df_AO_54307E_SERVICEDESK_PROJECT_KEY];

2. Manually modify the PROJECT_KEY column as shown in the error message:

   ALTER TABLE dbo.AO_54307E_SERVICEDESK ALTER COLUMN PROJECT_KEY NVARCHAR(255);

3. Re-add the constraint using the output you provided in your previous update:

   ALTER TABLE [dbo].[AO_54307E_SERVICEDESK] ADD CONSTRAINT [df_AO_54307E_SERVICEDESK_PROJECT_KEY] DEFAULT ('N/A') FOR [PROJECT_KEY]
   GO;

Workaround 2

1. Generate a new XML backup in Jira by using Jira's XML Backup utility
2. Shutdown Jira
3. Create a new empty database for Jira in the MS SQL database, per the instructions in Connecting Jira applications to a database
4. Delete the dbconfig.xml file in your JIRA home directory.
5. Restart JIRA and you should see the first step of the JIRA setup wizard for configuring your database connection.
6. Configure JIRA's connection to your new database (created in step 2 above) and click the 'Next' button.
7. On the 'Application Properties' setup page, click the 'import your existing data' link and restore your data from the XML backup created in step 1 above.