Connecting to Azure SQL
Anywhere365 needs to directly connect to Azure SQL database(s) in your Azure tenant A dedicated and trusted instance of Azure AD. The tenant is automatically created when your organization signs up for a Microsoft cloud service subscription, such as Microsoft 365. An Azure tenant represents a single organization. [Taken from Microsoft Docs] for your CDR data to be stored and retrieved. If Dialogue Manager is used, an additional Azure SQL database is needed for the conversational (text) message data and history.
CDR and DialogueManager each require their own database.
Scenarios
After a audio call or text message between a customer and a 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. has completely terminated, all interactions controlled by that UCC (timestamps, Agents, transfers, supervisors, recording locations, QM scores, etc.) will be stored as a new record in the SQL CDR database.
An Anywhere365 Manager can leverage the CDR database to extract Dialogue data (i.e. all historic interactions of customers with agents or an Aywhere365 UCC) using PowerBI.
UniversalTimeline
Anywhere365 Agents using the WebAgent can see historic dialogues with a customer if they call from same number (or send email from the same address) in the Universal Timeline shown in the WebAgent while they are connected to the customer. The Universal Timeline service will only read from the database and relay information to the agent through an Anywhere365 DialogueCloud UCC.
Call Classification and ScreenRecordings
Anywhere365 Agents using the WebAgent can add a Classification during, or after, a dialogue with a customer tagging the conversation with a predefined value from a dropdown list, to mark the topic or type of conversation. This will be appended to the CDR record of that specific conversation, even after the dialogue with customer has ended. The CallProperty Service will write/append this Classification to an existing record in the CDR database.
Similarly when using Snapper to enable Screen Recording and the ScreenRecodings are copied to a SharePoint library, the CallProperty Service will write/append the ScreenRecording filename and location to an existing record in the CDR database.
Anywhere365 Agents using WebAgent for text based dialogues with customers (like e-mails, WebChats, WhatsApps, or various others) will get these messages through a database specifically for the Anywhere365 Dialogue Manager.
One of various Dialogue Providers (coming from an external Dialogue Partner source) connected to Anywhere365 Dialogue Manager will relay each message to an Agent and each message back to a customer, stored in the Dialogue Manager database. All message routing to and from agents is consecutively managed by an Anywhere365 Dialogue Cloud UCC to the Agents WebAgent interface.
Prerequistes
- An Azure SQL database per region: recommended at least S1 20DTU during project initiation (and is typically sufficient for Golive to production, but exceptions may apply, see warning below)
- Azure DB is remotely accessible
- SQL user account: which is database owner
- Connection string to the database(s): Only the formats provided below are supported (i.e. only connections directly to <server>.database.windows.net and the other parameters are required).
- Firewall allowance for regional Dialogue Cloud IP address: will be provided by your Anywhere365 contact.
Note: After Golive, maintenance and regular monitoring of the database is the customer's responsibility.
Your database(s) will accrue data for every conversation and will grow with use over time, or your final UCC count and configuration may be heavy on DB processing (many simultaneous dialogues in queue, frequent queries from PowerBI or e-mails with large attachments for example), so your maintenance (like monitoring size, adding additional archiving databases, deleting old data, reclaiming unused space or maybe even upgrade to a higher tier or storage) may be required. For more information on Azure SQL limits, pricing and maintenance tips a good starting point is https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases, or consult your SQL DB Administrator.
Creating an account
You can choose between:
-
Option 1 (Preferred): Azure SQL Account (supported by all Anywhere365 applications)
Note: The Dialogue Manager requires a SQL Account in the Connection string for the DialogueManager database (see below). All other applications (Core, UTL and Classification) support more options. -
Option 2: Microsoft Entra ID Formerly known as Azure Active Directory (or Azure AD, or AAD) account (Supported by Core, UniversalTimeLine and Classification services)
Note: Supported for the Core UCC Service, Universal Timeline Service and the Call Classification Service which share the same ConnectionString. NOT supported for Dialogue Manager Service.
Below a guideline on how to create these accounts (Choose the one that is needed).
-
Connect SQL Server Management Studio (SSMS) to the azure database server with the admin account (see azure).
-
Add login for the user and create to user for the master database, by submitting the following query to the master database: (change username).
Note: When creating a login or a password make sure it does NOT contain the following characters: # $ [] () , ; ? * ! @ = . % &CopySQLCREATE LOGIN sa_username
WITH PASSWORD = 'Password'
GO
CREATE USER sa_username FROM LOGIN sa_username; -
Create a user for the database and give permissions, by submitting the following query to the CDR database
CopySQLCREATE USER sa_username FROM LOGIN sa_username;
EXEC sp_addrolemember 'db_owner', 'sa_username';
More information on:
https://azure.microsoft.com/nl-nl/blog/adding-users-to-your-sql-azure-database/
-
Set Active Directory Admin from within the Azure Portal
-
Connect to server via SQL Server Management Studio (SSMS) with account set on step 1
Use the ‘Active Directory – Universal with MFA support’ authentication method.
Choose for Connect (and optionally follow the MFA steps to complete the login)
-
Run the following queries
-
Run this query against the Master (instance):
CopySQLCREATE USER "<ME-ID user address>" FROM EXTERNAL PROVIDER;
ALTER ROLE dbmanager ADD MEMBER [ME-ID user address];For example:
CopySQLCREATE USER "user.name@anywhere365.net" FROM EXTERNAL PROVIDER;
ALTER ROLE dbmanager ADD MEMBER [user.name@anywhere365.net]; -
Run this query against the UCC database:
CopySQLCREATE USER "<ME-ID user address>" FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_owner', "<ME-ID user address>";For example:
CopySQLCREATE USER "user.name@anywhere365.net" FROM EXTERNAL PROVIDER;)
EXEC sp_addrolemember 'db_owner', “user.name@anywhere365.net";)
-
-
Navigate to the UCC Config page
-
Open the Settings list
-
Edit the setting "CDRConnectionString"
-
Enter the connection string with username and password for Azure SQL
Format when using an SQL Account:
CopyConnection stringServer=tcp:<server>.database.windows.net,1433;Initial Catalog=<cdr-database-name>;User ID=<sa_username>;Password=password
Format when using an AD Account:
CopyConnection stringServer=tcp:<server>.database.windows.net;Initial Catalog=<cdr-database-name>;Persist Security Info=False;User ID=<AD_username>;Password=password;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password";
Note: Upon startup of the service the database is checked for having the correct db schema, if not it will be created (or updated in case a schema update is required for a new version of the software). The requested access rights have the proper permissions to perform these actions.
For connecting a UCC to the DialogueManager database follow the steps on the Installing DialogueManager page. Learn More