Dialogue Cloud

On Premises

Filter:
Submit Search

Install CRM Service for SQL

The CRM Customer Relationship Management, or CRM, is (usually) a software-based, data management method to deal with interactions with customers and potential customers. Service for SQL is used to retrieve relevant information about the caller (for both inbound and outbound) during a conversation handled by the UCC A Unified Contact Center, or UCC, is a queue of interactions (voice, email, IM, etc.) that are handled by Agents. Each UCC has its own settings, IVR menus and Agents. Agents can belong to one or several UCCs and can have multiple skills (competencies). A UCC can be visualized as a contact center “micro service”. Customers can utilize one UCC (e.g. a global helpdesk), a few UCC’s (e.g. for each department or regional office) or hundreds of UCC’s (e.g. for each bed at a hospital). They are interconnected and can all be managed from one central location.. 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 Anywhere365 WebAgent.

Note: An additional Anywhere365 WebAgent for Voice or for Omnichannel license is required.
Note: A CRM connector license specific to each data source (Database or CRM backend platform) is required.

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 Anywhere365 CRM Service package from the Bundle 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:

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

Activate plugin

  1. Open the CrmConfiguration.yaml

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

  2. Change the description to:

    Copy
    CrmConfiguration.yaml
    # Plugin
    description: SQL

  3. Change the useSharePoint to:

    Copy
    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://FQDN FDQN stands for Fully Qualified Domain Name and specifies the exact location in the tree hierarchy of the Domain Name System (DNS). An example for [hostname].[domain].[top level domain] is [www].[microsoft].[com]/webagentservice/api/v1/settings

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

Note: The CrmService name could be different if a custom name was provided so could be http://fqdn/CrmService or http://fqdn/CrmServiceSQL

Warning: Make sure to configure the setting to the ".local" URL when using multiple services in the same region. If this is not done, you may experience unexpected 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 muliple CrmServices are configured.
The URL values http://fqdn/CrmService or http://fqdn/CrmServiceSQL may therefore be different.

Warning: Make sure to configure the setting to the ".local" URL when using multiple services in the same region. If this is not done, you may experience unexpected behavior.

WebAgentService

Add PluginSettings

Warning: This CRM service depends on primary settings shared across multiple UCCs, meaning the value should remain consistent across all UCC configurations using it. A primary setting is initiated only during startup of the UCC with the first UCC to publish its settings defining the primary setting for all UCCs sharing this service. If values vary across multiple UCCs this will lead to unpredictable behavior after a restart, so it is extremely important to keep the values consistent and valid.

  1. Open the UCC Config page on SharePoint

  2. Open the Plugin Settings list

  3. Add the following settings

    Setting

    Value

    Scope

    ConnectionString

    Encrypted Connectionstring to make contact with the database

    Copy
    Example connectionstring before encryption
    Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database-name>;User ID=<username>;Password=<password>
    Note: Encrypt with the A365 password tool.

    CrmService

    BaseContactQuery

    Mandatory query used for the retrieval of contacts:

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

    CrmService

    BaseLeadsQuery

    Mandatory query used for the retrieval of contacts:

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

    CrmService

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

(Optional) Create tables in SQL Database

You can use any table you want by changing the Query settings. If you do want to keep using the default, 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:

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