HaloCRM Guides

Setup HaloDBLookupService on your server

Requirements:

• Windows Server 2012+
• Within server manager, ensure asp.net 4.5, .net Framework 4.5 and .net Framework 3.5 are installed

This is a web api that should be set up on your network on a server which can access the Sql Server and that can be accessed from the Halo API.

1. Download the latest HaloDBLookupService from https://s3.haloitsm.com/ftp/HaloDBLookupService.zip.
2. Extract HaloDBLookupService.zip and place the contents of the "HaloDBLookupService_Version" directory in a directory called "HaloDBLookupService" in the root of the C drive.
3. Open IIS, create a new site with the Physical Path as the path to the WebApp directory you created (C:HaloDBLookupService).
4. Open C:WindowsSystem32inetsrvConfigapplicationHost.config. Check the nodes for “modules” and “handlers” have overrideModeDefault="Allow". If not, change “Deny” to “Allow” and save.
5. Rename appsettings.default.json in C:HaloDBLookupService to appsettings.json.
6. Start the site and browse to http://your-new-sites-url/health. You should get a JSON response with some information and application health. If this does not work, follow the troubleshooting steps below. 
7. Get a public URL for your site with an SSL certificate. And do the test in step 6 again.
8. IIS (Internet Information Services) must be enabled on the server.
   Consider it to be similar to setting up Halo, on-prem, but just for another API. You can choose the Name of your Lookup service URL (just like you would for your Halo API and Auth). You need to set up a site for this in IIS using the downloaded files and change your app settings to point at the correct DB. You should already have a DB that you want to access, so you should be able to generate a connection string for this DB and put this in your app settings.
   

Database Connection

The database connection properties are stored in appsettings.json.

HaloDBLookupService can connect to a single database or you can specify multiple connection strings and have the connection differ based on the Database lookup that is being run.

You should use a SQL user account with limited access to the database. Read-only and only access to the tables/stored procedures you want to query from HaloDBLookupService. Ensure the application can only query what it needs to.

1. Open appsettings.default this is a file which is downloaded as part of the HaloDBLookupService zip

2. There you see a property called "ConnectionStrings" with a value "DefaultConnection". Change the connection details of DefaultConnection to connect to your database. If you wish to use Windows authentication, delete the User Id and Password properties and change Trusted_Connection to True. However, if you wish to use SQL authentication then "Trusted_Connection" must be set to false.
   

3. If you want to add more connections, add another value underneath "DefaultConnection" and name it whatever you'd like. Ensure you input valid JSON (add a comma to the previous line when adding a new line).

4. When setting up the Database Lookup in Halo you can specify the name of the connection to run the lookup on.

5. Save.

6. Restart the server – sometimes you need to do this after installing dotnet core and URL rewrite.
7. Open an elevated command prompt, run iis reset to restart IIS.
8. Install VC_redist.x64 or VC_redist.x86 from V2 Install Files.
9. WebDav Server can cause problems. If it is installed (view this in Server manager) then uninstall it.
10. Change the user running the Application Pool. In IIS go to Application Pools, select Halo WebApp, Advanced Settings from the right hand side menu. Change the “Identity” to the user currently logged into windows.
11. In IIS, check that there is a MIME Type for “.json”. Check this by selecting MIME Types from the menu, and checking if there is an extension for “.json” that reads “application/json”. If there is not one, add it now (Ensure that you add this to the server level and not the site level otherwise it will be overwritten when upgrading). Some instances of IIS will already have this MIME type.
12. DO NOT EDIT THE SITE LEVEL WEB.CONFIG FILES. CHANGES TO THESE WILL BE OVERWRITTEN WHEN UPGRADING.
13. Install dependencies:
    DotNet Hosting 6: https://download.visualstudio.microsoft.com/download/pr/b50f2f63-23ed-4c96-9b38-71d319107d1b/26f8c79415eccaef1f2e0614e10cd701/dotnet-hosting-6.0.21-win.exe
    URL rewrite:
    https://download.microsoft.com/download/1/2/8/128E2E22-C1B9-44A4-BE2A-5859ED1D4592/rewrite_amd64_en-US.msi
    C++ redistributables: https://aka.ms/vs/17/release/vc_redist.x64.exe

Certificate

The Halo API and HaloDatabaseLookupService need to share a common self signed certificate. The Halo API signs the request with a certificate, and HaloDatabaseLookupService validates the signature using the same certificate before processing the request.

It's recommended you create a self-signed certificate. You'll be uploading this certificate to your Halo database so don't use a certificate that you use for something else already, or one that you use for SSL. This can be done in configuration > advanced settings > certificates.

This certificate needs to be installed to the Trusted Root of the LocalMachine of the server running HaloDBLookupService.

For additional security you can specify the Issuer in appsettings.json (e.g "CN=MyDomain") and HaloDBLookupService will also validate the Issuer.

If you need to activate extra logging for HaloDBLookupService you can change LogLevel to "Warning" in appsettings.json.

Setting up the Database Lookup in Halo