HaloCRM

HaloCRM Guides

Guides > Using HaloDBLookupService to set up a Database Lookup for an external SQL Server Database

Using HaloDBLookupService to set up a Database Lookup for an external SQL Server Database

  1. Setup HaloDBLookupService on your server

    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.

    Server Requirements:

    • IIS (Internet Information Services) must be enabled on the server
    • Windows Server 2012+
    • Within server manager, ensure asp.net 4.5, .net Framework 4.5 and .net Framework 3.5 are installed


    1. 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
    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:\Windows\System32\inetsrv\Config\applicationHost.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.

    Troubleshooting;

    • Restart the server – sometimes you need to do this after installing dotnet core and URL rewrite.
    • Open an elevated command prompt, run iis reset to restart IIS.
    • Install VC_redist.x64 or VC_redist.x86 from V2 Install Files.
    • WebDav Server can cause problems. If it is installed (view this in Server manager) then uninstall it.
    • 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.
    • 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.
    • DO NOT EDIT THE SITE LEVEL WEB.CONFIG FILES. CHANGES TO THESE WILL BE OVERWRITTEN WHEN UPGRADING.

    Database Connection

    The database connection properties are stored in appsettings.json (created in step 5 above).

    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.json.

    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.

    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.

    Certificate

    The Halo API and HaloDatabaseLookupService need to share a common 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 certificate needs to be installed to the Trusted Root of the LocalMachine of the server running HaloDBLookupService.

    Then you can add this certificate to Halo in Config > Advanced > Certificates.

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

    Setting up the Database Lookup in Halo

    1. In Halo go to Config > Integrations > Database Lookups.

    2. Add a new lookup

    3. Set Connection type to "External SQL DB (different network from Halo)".

    4. New options to connect to HaloDBLookupService will open up. Populate as follows;

    5. HaloDBLookupService URL = The base url you configured in step 7 of "Setup HaloDBLookupService on your server". E.g "https://dblookups.mycompany.com". Do not include a trailing slash.

    6. Connection Name = leave blank to use DefaultConnection, or populate the name of the connection you want to use if using another connection you created in step 3 of "Database Connection".

    7. Certificate = Pick the certificate you added to Halo in the "Certificate" section above.

    8. Populate the rest of the lookup record as if you're creating any other DBLookup.

    9. Save the record.

    10. Test using the Test button. An error will be returned if the test fails.

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

    Upgrading HaloDBLookupService

    Updating HaloDBLookupService is a matter of obtaining the new files, and overwriting the old ones. Please ensure that you do not delete or overwrite your appsettings.json file.

Popular Guides

Pin It on Pinterest