KB : Modifying Database for user mappings

Owned by Avinash Bhagawati
Nov 21, 2023

TF4JIRA Synchronizer database

  1. Open Internet Information Services (IIS) Manager and stop the TFS4JIRA Sync App Pool.
  2. Backup your current TFS4JIRA Synchronizer. It should be enough to zip/compress the following directory:
    • C:\inetpub\wwwroot\tfs-jira-synchronizer
  3. Download and install CompactView. It's a tool that allows you to view and modify Microsoft SQL Server CE database files (sdf). Download it using the following link:
  4. Copy the Synchronizer's database file (config.sdf) to your Desktop:
    • C:\inetpub\wwwroot\tfs-jira-synchronizer\App_Data\config.sdf
  5. Open CompactView.
    • Select File > Open database.
  6. Open the config.sdf file you copied to the Desktop.
    • If you get the following error message while trying to open the file:
      Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

      It means Microsoft SQL CE 4.0 is not installed, but a 4.0 CE database is being used. Follow this KB article to install Microsoft SQL CE 4.0.

  7. On the top-left corner of the application window change the drop-down button from Read only to Allow editing.
  8. Find the FieldMappings & "ValueMappings" & "SyncProfiles" tables.
  9. You will need to find the corresponding field mappings in FieldMappings table by looking at the right syncProfile ID.
  10. Remember the "Id" in FieldMappings table because you will need it at ValueMappings table later
  11. Insert the records at ValueMappings table, "Id" is incremental, "JiraValue" is the username attribute in Jira, "TfsValue" is the username attribute in TFS, and "FieldMapping_Id" is the ID you checked above in "FieldMappings" table. (you may automatize this by script later or do it fully manually at CompactView)
  12. Change the drop-down button back to Read only.
  13. Close CompactView. You don't need to save anything. Changes are made on the fly.
  14. Replace the original config.sdf file with the one from your Desktop and make sure that the IIS user has access (permissions) to that file:
    • C:\inetpub\wwwroot\tfs-jira-synchronizer\App_Data\config.sdf
  15. Open Internet Information Services (IIS) Manager and start the TFS4JIRA Sync App Pool.