TFS4JIRA Self-Hosted | connection between TFS synchronizer application installed on a Windows machine and MS-SQL database present in another machine

Steps to Use MS-SQL DB on a different server and synchronizer application on a different server:

Server1: Synchronizer server

Server2: MS-SQL server

1. Initially Install Microsoft® SQL Server® Compact 4.0 SP1 - download link: https://www.microsoft.com/en-us/download/details.aspx?id=30709 in the synchronizer application server (Server1).

2. Next, try to open tfs-jira-synchronizer using http://localhost/tfs-jira-synchronizer. If the synchronizer is opened, then Install the Export SQLCe tool in the synchronizer application server (Server1).

3. Stop TFS4Jira Synchronizer application pool in IIS in the synchronizer application server (Server1).

4. Export SQL CE db into an .sql script (schema and data) in the synchronizer application server (Server1).
   Run following command in directory where you downloaded export sqlce tool(it requires to open terminal as administrator)

5. Once the command is executed

Synchronizer.sql file is created in path C:\inetpub\wwwroot\tfs-jira-synchronizer\App_Data\

6. Create a new DB in MSSQL in the MSSQL server

(Server2).
Note: Just so you know, in the below SQL queries, we have PASSWORD = 'B$3K94ynVQf7aD'; you can change as per your standards and use the changed passwords wherever you see.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
-- Create database for synchronizer
CREATE DATABASE TFS4JIRA;
GO

-- enable contained db auth on the level of a db
Use MASTER;
GO

ALTER DATABASE TFS4JIRA
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TFS4JIRA
SET containment=partial;

ALTER DATABASE TFS4JIRA
SET MULTI_USER;
GO

SELECT containment_desc FROM sys.databases
WHERE name='TFS4JIRA'

-- create user with password, contained in the db
USE [TFS4JIRA]
CREATE USER TFS4JIRA_Synchronizer_User WITH PASSWORD = 'B$3K94ynVQf7aD';
GO

-- add the user to the db_owner role - because EF will alter schema on migrations
-- (otherwise db_data_reader and _writer would be enough)
USE [TFS4JIRA]
ALTER ROLE db_owner ADD MEMBER TFS4JIRA_Synchronizer_User
GO

7. Enable “SQL Server and Windows Authentication mode” in the MSSQL server (Server2).

7.1 Open SSMS
7.2 Right click the server and choose Properties.
7.3 Open “Security” section
7.4 Change “Server Authentication” option to "SQL Server and Windows Authentication Mode" and click “Ok”
7.5 Restart SQL Express instance to apply authentication changes

Copy the Synchronizer.sql file from server1 to server2

command: in (server2) in command prompt

sqlcmd -S <your-device-name>\SQLEXPRESS,1433 -U TFS4JIRA_Synchronizer_User -P B$3K94ynVQf7aD -d TFS4JIRA -i C:\Users\Administrator\Desktop\Synchronizer.sql

8. Updated connection string in Web.config in the synchronizer application server

(Server1).

Update “forceDbType” application setting by changing its value to “SyncProfileContextExpressConnection”.

Change the value for SyncProfileContextExpressConnection in <connectionStrings>

<add name="SyncProfileContextExpressConnection" connectionString="Data Source=<your-device-name>\TFS4JIRA,1433; Database=TFS4JIRA; User Id=TFS4JIRA_Synchronizer_User; Password=B$3K94ynVQf7aD; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

9. Restart the TFS4Jira Synchronizer application pool in IIS in the synchronizer application server