Modifying Database for user mappings

Owned by Paweł Mazur
Dec 21, 2020
2 min read

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, the 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 the FieldMappings table by looking at the right sync profile 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 the "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.