Install CRM Service for SQL

Prerequisites

Licensing prerequisites

For licensing requirements or renewal questions, contact your AnywhereNow representative.

Introduction

The Service for SQL is used to retrieve relevant information about the caller (for both inbound and outbound) during a conversation handled by the . The CRM Service retrieves contact info regarding the caller and if applicable the related relevant cases. The CRM Service will be used in combination with the Dialogue Cloud WebAgent.

Dependencies

  • A SQL Server environment

  • A SQL database

  • A SQL user with the necessary permissions to connect to the database and select data

Follow the instructions below to install the CRM Service for SQL.

Deploy package

  1. Unzip the CRM Service package from the Package

  2. Edit the properties.ps1

    • Domain = Domain of the install account

    • UserName = Username of the install account

  3. Run InstallCrmService.ps1 in PowerShell as administrator

(Optional) More than one CRM Service - use the CRM Service variable

Used in file: properties.ps1

$crmservice = "CrmService"

The variable $crmservice allows you to change the name under which the CRM Service will be installed in IIS, as well as it’s folder name in wwwroot.

If you need to have more than one CRM Service on a server or want to make clear which CRM Service is installed you can alter this value to suit your needs. All CRM Services fall under the applicationpool CrmApplicationPool.

Configure Plugin

Deploy plugin

  1. Unzip the SQL package

  2. Paste the SQL folder inside the Plugin folder of the CRM Service:

    Default path
    C:\inetpub\wwwroot\CrmService\Plugins

Activate plugin

  1. Open the CrmConfiguration.yaml

    Default path
    C:\inetpub\wwwroot\CrmService\CrmConfiguration.yaml

  2. Change the description to:

    CrmConfiguration.yaml
    # Plugin
description: SQL

  3. Change the useSharePoint to:

    CrmConfiguration.yaml
    # Use SharePoint to get configuration
useSharePoint: true

Add Endpoint

  1. Open the UCC Config page on SharePoint

  2. Open the Plugin Settings list

  3. Add CRMService settings endpoint to ListsPlugin ListsEndpoints

Note

Add to the existing item and separate with "Enter".

Setting Value Scope
ListsEndpoints

http:///webagentservice/api/v1/settings

http://FQDN/CrmServiceSQL/api/v1/settings

Note

The CrmService name could be different if a custom name was provided during install so could be http://fqdn/CrmService or http://fqdn/CrmServiceSQL. We advice to always install this service as http://fqdn/CrmServiceSQL

Warning

Make sure to leave the fqdn to this service configured as a ".local" URL, especially when using multiple UCC services in the same region. If this is not done, the UCC cannot reach the right webserver internally leading to undesired or non-functional WebAgent behavior.

 ListsPlugin

Add WebAgentService URL

  1. Open the UCC Config page on SharePoint
  2. Open the Plugin Settings list
  3. Add the following setting:
Setting Value Scope
CrmServiceUrl

http://fqdn/CrmServiceSQL

Note

URL (Value) needs to be accessible from the WebAgentService to this CRM Service.
If on the same server this can be http and a .local address.

Note

The CrmService name may be different if a custom name was provided or if multiple CrmServices are configured.
The URL values http://fqdn/CrmService or http://fqdn/CrmServiceSQL may therefore be different. We advice to always install this service as http://fqdn/CrmServiceSQL

Warning

Make sure to leave the fqdn to this service configured as a ".local" URL, especially when using multiple UCC services in the same region. If this is not done, the UCC cannot reach the right webserver internally leading to undesired or non-functional WebAgent behavior.

 WebAgentService

Add PluginSettings

Warning

This CRM service depends on settings which need to be identical if used across multiple UCC's within a service, meaning the values should be identical across all UCC configurations using it. We call these primary settings. A primary setting is defined by the first UCC to publish its settings during startup of the service. This may not always be the same UCC if multiple UCC's share this service! If values differ across multiple UCC's this will lead to unpredictable behavior after a service restart. It is extremely important to keep the values consistent/identical and valid.

  1. Open the UCC Config page on SharePoint

  2. Open the Plugin Settings list

  3. Add the following Mandatory settings

    Setting Value Scope
    ConnectionString

    Encrypted ConnectionString to make contact with the SQL database

    Example connectionstring before encryption
    Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database-name>;User ID=<username>;Password=<password>

    Note

    Encrypt entire ConnectionString with the AnywhereNow password tool.

    		 CrmService
    BaseContactQuery

    Mandatory query used for the retrieval of contacts:

    Query
    Select Top (100) {0} From Contact Where {1}

    Note

    Each query must contain a row, rownum, limit or fetch first <num> rows.
    In other words SELECT * search is not allowed (nor advised or good practice).

    		CrmService
    BaseLeadsQuery

    Mandatory query used for the retrieval of contacts (or leads if they are separate entities):

    Query
    Select Top (100) {0} From Contact Where {1}

    Note

    Each query must contain a row, rownum, limit or fetch first <num> rows.
    In other words SELECT * search is not allowed (nor advised or good practice).

    		CrmService

For additional or optional configuration parameters see Additional configuration CRM Service for SQL Server

(Example) Create default tables in SQL Database

You can use any table you want by changing the Query settings. If you do want to use our defaults, follow these steps to create the tables:

  1. Open Microsoft SQL Server Management Studio (SSMS) (Download SQL Server Management Studio (SSMS))

    or

    Azure Data Studio (Download and install Azure Data Studio - Azure Data Studio)

  2. Connect to Server / Instance where the SQL Database is located.

  3. Navigate to the SQL Database

  4. Right Click Database

  5. Select New Query

  6. Paste the following SQL code:

    SQL
    IF OBJECT_ID(N'dbo.Case', N'U') IS NULL

CREATE TABLE [dbo].[Case](

[Id] [nvarchar](max) NOT NULL,

[ContactId] [nvarchar](max) NOT NULL,

[Description] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

IF OBJECT_ID(N'dbo.[Contact]', N'U') IS NULL

CREATE TABLE [dbo].[Contact](

[Id] [nvarchar](max) NOT NULL,

[Firstname] [nvarchar](max) NULL,

[Lastname] [nvarchar](max) NULL,

[Skill] [nvarchar](max) NULL,

[Birthdate] [datetime] NULL,

[Location] [nvarchar](max) NULL,

[PhoneNumber] [nvarchar](max) NULL,

[Email] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

  7. Execute the Query (F5 or Run)

  8. Validate the outcome. By checking the Messages tab you can see if query was executed successfully.