Pro DBConnector Pro DBProvider Installation Guide

Pro DBConnector and Pro DBProvider Installation Guide

Title Pro DBConnector and DBProvider Installation Guide Revision 062413 Copyright ©2013 inContact, Inc.

Product Code 4242 About inContact inContact, Inc. provides the market leading on-demand contact center platform, inContact, to over 750 contact centers across the globe. With its roots in telecommunications and network infrastructure, inContact is unique in its offering because it combines a powerful connectivity backbone with a world-class software platform. The inContact platform has grown from a powerful ACD with skills-based routing, CTI, and IVR with speech recognition, to include an innovative online hiring solution, an eLearning and communications application, workforce management functionality, and a customer feedback and survey solution. Because the inContact platform is delivered through a Software-as-a-Service (SaaS) model, inContact customers can realize significant cost savings and flexibility compared to premises-based alternatives. To learn more about inContact, visit www.incontact.com.

Contributors inContact would like to acknowledge the many individuals who helped complete this dbConnector and dbProvider Installation Guide. We would also like to thank our customers who inspire our commitment to extreme customer satisfaction.

2

Table of Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . 5 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Installing dbProvider and Config.exe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 Creating an ADO Connection String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10 Creating an ADO Connection String for non-MS SQL Databases . . . . . . . . . . . . . . . . . . . . . .13 Creating an ODBC Data Source Name (DSN) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 Database Provider Configuration: Error Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22 Error Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23 Finalizing the dbProvider Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25

dbProvider Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27 View Recent Log Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29 Create a New Log File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30 Set Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31 Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

Special Considerations for MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

3

Introduction inContact customers who need IVR or ACD access to a corporate database for CTI integration can use dbConnector. This document provides an overview of the dbConnector tools: •

dbProvider: Creates a Windows service that enables the communications between inContact and your database

•

Config.exe: Installation and administration utility that manages the installed DB Providers

dbProvider is a Windows-based service that acts as a gateway between inContact and the database. When an inContact script performs a SQL action, the query will be re-directed to the dbProvider for processing. The dbProvider will then pass through the query using the configured ADO or ODBC account. The ADO/ODBC driver will pass the query to the database and will return the result back to the dbProvider which passes back the result to the IVR/ACD environment. In theory, any database that is ADO or ODBC compliant should be accessible by dbProvider. However, inContact has encountered some drivers that are not fully compatible. Microsoft SQL, Oracle, and MySQL have been validated by inContact, but other database systems should be fully tested before placing the solution into production. Note! In this document, the terms “dbConnector” and “dbProvider” are synonymous.

4

Requirements The following technical requirements must be met prior to installing dbProvider: •

As a Windows service, dbProvider runs on a workstation or server running Microsoft Windows 2000 (or better).

•

The workstation / server must have Internet access to a handful of IP addresses that constitute access points to the inContact platform.

Note! For a list of updated ports, refer to the Open Ports document on webManager > Documentation. •

The workstation / server must have visibility to the database. (If the database happens to run on an MS Windows server, the dbProvider can run on the same machine. This configuration is not required, however.)

•

The workstation / server must have the appropriate database driver (i.e., ADO or ODBC).

•

The database driver must have the appropriate credentials to access the database. It is a security measure to use a database user account that has sufficient access but not excessive access. For example, if the IVR only needs to read from the database, it is recommended to use an account limited to read-only permissions.

Security inContact offers three “grades” of data transmission between inContact and the server hosting the dbProvider service: • Clear Text • Application Encryption • VPN Note! There may be cost implications to the selected transmission format. Generally the three grades have different price points. •

Clear Text. Clear text permits dbProvider to communicate with inContact without encryption. This may be suitable in many environments where transmitted data is non-critical or common knowledge (i.e., a company “dealer locator” solution).

•

Application Encryption. Application encryption allows dbProvider to decrypt the data stream from inContact and encrypt the data stream to inContact. This encryption is set up using the Encode and Encoder Key options in the Config utility. Because private-key encryption is utilized, the inContact Client Support Team must be given the same key which is used to configure the dbProvider connection from inContact's side of the connection. inContact uses this key to encrypt the data stream prior to transmission across the Internet to the dbProvider.

•

VPN (virtual private network). The VPN utilizes an off-the-shelf Cisco solution to securely transmit data at a lower level than the application layer. In this configuration, the dbProvider workstation / server is installed with the Cisco software client. The necessary login credentials are supplied by the inContact Client Support Team to get the connection up and running.

5

Installing dbProvider and Config.exe Note! Prior to installing dbConnector, contact your Client Support Manager to obtain a service name necessary to complete the configuration.

To Install dbProvider and Config.exe: 1.

Create a folder to store the applications on the server that will run the dbProvider service.

2.

Open webManager (www.inlogin.com) and login.

3.

Go to Software Updates.

4.

Save dbProvider.exe and Config.exe to the folder created in step 1.

5.

Run the Config.exe application.

6.

The inContact - Database Provider Configuration screen opens:

7.

Click Add to create a new dbProvider instance (if your organization has multiple, autonomous database systems, multiple instances can operate on a single workstation/server).

6

8.

Enter the following information as appropriate: Description

Field Service Info

Service Name Enter the name supplied by your inContact Client Support Manager. Service Description This is the name that shows up in the Windows Service Manager. inContact recommends using a name that describes it as a dbProvider, such as “inContact dbProvider” or “inContact inContact dbProvider”. This description will be saved in the MS Windows Services Console. Administrator Port By default, this value is 9023. If multiple instances are installed, each instance must have a different port value. This port is only used in conjunction with the browser-based administrator screen (which can be accessed by clicking Open Administrator when the service is running).

Encoding

Encode Click the Encode check box to encrypt the data stream between the dbProvider and inC ontact. b Encoder Key In the Encoder Key field, enter an encoder e key value. The length is variable, but a length of at least 16 characters is recommended. m Note! The Encoding options apply to onlyy application-levele encryption. If you wish to encrypt traffic between your dbProvider h comparable to e and inContact, create an encoder key (somewhat t a “password” for your dataa stream) and supply it to your Client Support Manager or Implementation Team. The T encoder value must be entered on both the dbProvider and the inContact platform. If an Encoder Key is not used, neither inContact e will transmit encrypted inform ation. An a nor dbProvider optional VPN connection can be purchased which will secure, encrypt, and a transport theedata stream at the transport layer rather than the application layer.

7

Description

Field Logging

Logging is an optional feature. If enabled, the log files will be placed in the specified folder where the dbProvider service is installed. For example, if dbProvider is installed in the C:\inContact folder, by default, log files will reside in the C:\inContact\LogFiles\MyACDDB folder. The Include more detail option will include additional details not typically available. Log all activity to disk Click the Log all activity to disk check box if logging is desired. Logging will include all SQL statements submitted from inContact and all responses supplied by the database and database driver. Logs are automatically purged from the logging folder after seven days to prevent excessive disk space consumption. inContact recommends using the logging feature as a troubleshooting resource. Include more detail Clicking the Include more detail check box will add troubleshooting detail to the log file. inContact recommends that Include more detail only be used during troubleshooting sessions with inContact's Technical Support Team. Log Folder Enter the location of the folder for the log files. Note! Log files are named according to today's date. The pattern is the text “DBProv” followed by MMDDYYYY_X.LOG (where “MMDDYYYY” refers to the month, date, and year and “X” refers to the incremental log file of the day). For example, the first dbProvider log file for January 15, 2006 would be “DBProv01152006_0.LOG”. A single log file will remain is use by the dbProvider until one of the following events occurs: • The file grows larger than 100Mb • The day changes • A user forces the creation of a new log file from the dbProvider Administrator

Periodic Health Check

0 Enable 30-Second Interval Health Check inContact has found that some systems encounter TCP/IP problems if SQL statements rarely pass s n through the h dbProvider. Click the Enable 30-Second-Interval 3 v Health Chec k check box, to submit a query every 30 seconds too ensure the database connectionn remains healthy. a SQL Syntax a The syntax of the SQL statement will vary depending on t he database used. d For example, “Select t 'Ping'” is valid grammar for MS SQL but not a would use a statement such as o for Oracle. Oracle v “SELECT SYSDATE FROM DUAL”.

Connection Type

ADO Connection String • Click the ADO Connection String check box to connect to your database using an ADO Connection String via an ODBC driver. •Click Configure >>. • Select the appropriate OLE driver • In the Database Name field, enter the database location, and a User name and Password as appropriate for network authentication. •Click Test Connection. If the message, “connection failed” is displayed, correct the database name and re-test the connection. Once a successful connection has been established, click OK. MySQL DSN •Click MySQL DSN to connect to your database using a DSN and Database name. • After the DSN and Database name fields have been completed, click Test DSN. • If the test fails, correct the DSN and re-test the connection.

8

9.

Click Save to update the configuration for the dbProvider.

10.

Click Install to install the service with configured preferences.

11.

Click Start All to start the service.

9

Creating an ADO Connection String 1.

In the Data Link Properties screen, click the Provider tab:

2.

Select the data you want to connect to.

3.

Click Next.

4.

The Connection tab opens:

10

In the Connection tab: 1.

From the Select or enter a server name drop down menu, select a target database server.

Note! Some networking environments prevent the database server name from appearing in the drop-down list. You may need to contact your database administrator or IT team to determine the settings for your implementation. 2.

For the Enter information to log on to the server section, inContact recommends selecting the Use a specific user name and password option. This utilizes a database account configured on the database server. The database user account needs to have necessary rights to read and/or write the queries submitted by the inContact scripts. For example, if a script submits a query that selects customer information from a table for which the assigned user name has no access, the query will fail. inContact recommends a careful evaluation of the permissions that will be required by IVR applications. The Use Windows NT Integrated security connection method simplifies environments where users need to sign on to a database application. This connection method utilizes the user name and password of the person loading the program. Because dbProvider runs as a service, the default user is named, “Local System Account”. Generally this user will have access to launch the dbProvider service, but will NOT have database permissions to connect to the database. If Integrated Security is used, you must assign a user account to run the dbProvider service by opening the Windows “Services” console, selecting the dbProvider service, choosing the Log On tab, and entering an explicit account name and password. Then make sure this account has been assigned appropriate permissions in the database. Click Allow saving password. If you don't select this option, the password will not be saved to the generated connection string.

3.

From the Select the database on the server drop down menu, select the target database. You can also enter the target database name.

4.

If the workstation / server has visibility to the database, Click Test Connection to verify connectivity. A successful connection looks like this:

5.

Click OK.

Note! Test connection succeeded does not necessarily imply the connection is fully operational. It is possible for the connection to sh w “succeeded” using a user account that has insufficient permissions to execute SQL statements submitted by a script. There may also be other reasons why the “Test Connection” shows successful when, in fact, the configuration will not correctly support the dbProvider requirements.

11

A failure to connect might look like this:

If you see a failure message, you will need to take corrective action and re-test the connection:

6.

Once the connection is successful, Click OK. This will build the ADO connection string and will populate the window in the Config utility.

7.

The Advanced tab opens:

In most cases, the “Advanced” tab for the MS SQL Server ADO Connection String wizard is unused. Contact your database administrator to determine if any of these settings are required for your implementation.

12

Creating an ADO Connection String for non-MS SQL Databases 1.

In the Data Link Properties screen, click the Provider tab:

2.

Select the data you want to connect to.

3.

Click Next.

13

4.

The Connection tab opens:

The process for setting up non-MS SQL connection strings is identical for other database vendors. The only difference is in the fields that are used for configuration. Field 1 of the Oracle properties states, Enter a server name while field 1 of the MS Jet driver states, Select or enter a database name. Many databases can be accessed either via ADO or ODBC. The drivers that can be selected directly from the Provider tab of the Data Link Properties window use MS ADO (meaning OLEDB). One of the options listed on the Provider tab is “Microsoft OLE DB Provider of ODBC Drivers”. When this option is used, a DSN (data source name) must be created. (A description of setting up a DSN will be included in the next section.) An ODBC driver adds an additional layer to the communication process. Therefore, ODBC is typically expected to perform a little slower than its ADO counterpart. However, if you expect to handle a very large volume of queries through your dbProvider, you should use the ADO driver. Some databases may have numerous available drivers from the vendor itself and/or third parties.

14

Creating an ODBC Data Source Name (DSN) On the dbProvider workstation, the “Data Sources (ODBC)” selection is used to create new data source names.

To Create an ODBC DSN: 1.

Open the Windows Control Panel:

2.

Select Performance and Maintenance:

15

3.

Select Administrative Tools:

4.

Click the Data Sources (ODBC) icon:

16

5.

The ODBC Data Source Administrator screen opens:

6.

Click the System DSN tab.

7.

Click Add... to create a new data source name.

8.

Click OK.

9.

Select the driver that corresponds to your database:

10.

Click Finish.

Note! If a driver for your database is not listed here, you will need to install it before proceeding.

17

The available fields in the ODBC Setup window depend on the database driver. Some drivers may have only a few options to configure while other drivers may have several settings. Enter the fields with the necessary information to establish communication with your database. The ODBC properties require you to specify where the database resides (which may be a file-based database such as dBase or MS Access or it could be a server-based database such as MS SQL, Oracle, or MySQL). In addition to the location of the database, you may also need to include a user name and password. Other options may be available to access your database. 11.

When you have entered information in the required fields, click OK.

The following example shows a dBase DSN:

The following example shows an MS Access DSN:

Once created, a DSN is available to be selected from the ODBC Data Source Administrator screen. The DSN is also available for selection in the Config utility's ADO Connection String wizard.

18

The following screen shows that the DSN is now available:

The following screen shows that the new DSN can now be referenced for the ODBC option:

The “Connection” tab for the ODBC option offers two methods for the Specify the source of data field: • Use a data source name • Use a connection string Note! The Use a connection string option walks you through a wizard to build a new DSN. This process creates a DSN just like the previously outlined steps, although this approach might reduce the amount of navigation to get to the Data Sources (ODBC) configuration screen.

19

The following example shows that the Use connection string option provides another way to create a DSN:

If you select this option, go to the Select Data Source screen, and click the Machine Data Source tab:

Click New... to create a System Data Source name.

20

The Create New Data Source screen opens:

Make your selections.

21

Database Provider Configuration: Error Conditions inContact supports multiple database providers that point to a single database. In the event that the primary dbProvider workstation fails, a redundant dbProvider can be configured to handle database requests. When you setup your primary and secondary dbProvider, you will need to inform your Client Support Manager which workstation will have the higher priority. While we strongly recommend that a secondary dbProvider be used, it is not required. To configure dbProvider's error handling, you must first determine how you want to handle errors: • Do you want any returned error to cause the provider to go offline? • Do you want only specific errors to cause the provider to go offline? • Once you've decided the failure behavior, then you need to determine any specific messages that need to be handled. In the Config screen, click Example to see different possible errors from MySQL, MS SQL, and MS Access. The precise text to be entered is dictated by the database server and the database driver.

The table below describes the buttons at the bottom of the Error Conditions:

22

Error Examples If you do not have a secondary db Provider: • Use the default error condition: “This DB Provider is non-redundant or Secondary-Side”.

If you have a secondary dbProvider: • There could be a connectivity disruption between inContact and the dbProvider. For example, the workstation could crash, shut down for maintenance, or lose network connectivity. •

There could be unexpected errors returned by the database or ADO/ODBC driver.

If a primary dbProvider loses connectivity with inContact: • inContact will automatically failover to the secondary dbProvider. In some cases, the dbProvider may maintain its connection with inContact but lose connectivity (or encounter other problems) with the database itself. The solution to this problem is to configure messages considered to be error messages. The primary server can be setup to go offline for ALL errors (except for a specified list) or to go offline ONLY for specific errors. If you want your primary dbProvider to take special action: If you want your primary dbProvider to take special action based on the text of returned errors, determine which errors will receive attention. A syntax error, for example, is probably not a good candidate for offlining the provider. An incorrectly written script could submit mal-formed SQL which would force the provider offline when the provider and connection to the database are perfectly healthy. In most cases, you only want to offline the primary provider in the event errors indicate the database connection is damaged.

Click OK. For example, if you only want to offline the dbProvider for returned messages, your settings would look like this:

23

If you want the provider to go offline for all errors EXCEPT for the text “Shutdown” and “Syntax error or access violation”, your settings would look like this:

24

Finalizing the dbProvider Configuration A fully configured dbProvider may look like this from the Config utility:

1.

Once all the setting have been entered, click Save.

2.

Click Install.

3.

The MyACD DB Config screen opens:

4.

Click OK.

Once installed, the provider can be found in the Windows Service Console (found under “Services” in the “Administrative Tools” console). The name of the service will be called “MyACD DB-” plus the service name entered into Config. Following the first installation, you will find the service is installed, but not yet running.

25

To Start the Service: 1.

Click Start All in the upper-right corner of the Config utility.

2.

The MyACD DB Config screen opens, showing the name of the service:

3.

Clicking Status will open the same pop up message, indicating the status of all configured dbProviders:

Now that the dbProvider has been started and is running, you can begin testing your database connectivity. Click Open Administrator at the top of the Config utility. A browser interface opens providing a limited interface directly to the dbProvider service. Click the View Recent Log Data to review the communication stream displayed in the lower frame of the browser. If the communication indicates that the provider has successfully connected to the database, run a simple script that executes a SQL statement. After running the script, check the log file to ensure the query actually passes through dbProvider. Verify that the query being submitted from inContact is passed on to the database, and that a result is being returned.

26

dbProvider Administrator The dbProvider Administrator is used to observe logging transactions between inContact and the dbProvider, and between the dbProvider and the associated database. The dbProvider service must be running for the Administrator to function. The dbProvider Administrator allows several features including a view of recent log data, forcing the dbProvider to begin writing to a new log file, changing online/offline status, and setting logging preferences.

To Use dbProvider Administrator: 1.

Once an instance of dbProvider has been installed, click Open Administrator:

2.

The Administrator view of dbProvider opens:

27

Note! If the dbProvider Administrator is opened while the service is stopped, a failed browser session page opens, stating, “The page cannot be displayed”:

28

View Recent Log Data The “View Recent Log Data” selection will display the last 500 lines of logged events. Unless the dbProvider is handling significant volume, this view is often sufficient to confirm proper connectivity and view queries passing through the system. Upon login, you will see data exchanges to initiate the connection. You will also see ongoing “H->P” and “P->H” lines indicating heartbeats and acknowledgements between the dbProvider and inContact. If queries are submitted by a script, you can inspect the SQL and the result from the database driver:

29

Create a New Log File While the “View Recent Log Data” feature provides a snapshot of real-time traffic, sometimes a comprehensive view of the file is necessary. The dbProvider locks its log file while in use:

A single log file will remain is use by the dbProvider until one of the following events occurs: • • •

The file grows larger than 100Mb The day changes A user forces the creation of a new log file by selecting “New Log File”:

30

Set Status If you wish to toggle the dbProvider between an online and offline status, click or unselect the Online check box, and click Save Changes:

31

Options The same logging options available in the Config utility can be modified directly on the dbProvider via the Options screen:

32

Special Considerations for MySQL In most cases, the dbProvider is expected to be compliant with any ADO or ODBC database driver. inContact has found, however, that MySQL's “MyODBC” driver has a tendency to fail when used via an ADO connection string. Due to the popularity of MySQL databases, a special consideration has been built into the Config utility. Select MySQL DSN Connection Type rather than ADO Connection String. The MyODBC database driver should be installed and a DSN should be created to communicate with the MySQL server. Once created, the DSN name can be entered into the DSN field. Enter the database name in the Database Name field.

inContact has found an apparent incompatibility between the MyODBC driver and the ADO option. To get around the built-in ADO option, it is necessary to install the Borland Database Engine (BDE) on the dbProvider workstation. To Install BDE and MySQL Driver (must use this driver): 1.

Download BDE.ZIP from webManager > Software Updates.

2.

Download MyODBC-3.51.10-2-win.zip from webManager > Software Updates.

3.

Unzip and Extract BDE.zip to a temporary folder.

4.

Run Setup for BDE and follow defaults (unless otherwise directed).

5.

Unzip and Extract MyODBC-3.51.10-2-win to a temporary folder.

6.

Run MyODBC-3.51.10-2-win file (ONLY USE THIS DRIVER)

To Verify that the Driver is Installed: 1.

Open the ODBC Administration console > Control Panel > Administrative Tools > Data Sources (ODBC).

2.

Click the Drivers tab and verify that MySQL ODBC 3.51 Driver is present.

To Configure DSN: 1.

Go to Control Panel > Administrative Tools > Data Sources (ODBC) under the System DSN.

33

2.

Click Add.

3.

Select “MySQL ODBC 3.51 Driver”.

4.

Click Finish. This will open the set up for Data Source. Enter the fields with the correct information.

5.

Click Test to verify connectivity. If the test passes, you will see “Success; connection was made!”. If this test fails, check fields for correct values.

6.

Click OK to save as System DSN.

34