Revision as of 13:17, April 17, 2018 by Pmcknigh (talk | contribs) (→‎Management Framework Components on Linux)
Jump to: navigation, search

Microsoft SQL Server Databases

You must install client software to access the version of Microsoft SQL Server you are using. Refer to Microsoft documentation for details. You can use any edition of Microsoft SQL Server, including Express.

Genesys uses ODBC client software to access all supported versions of MSSQL. On Windows, ODBC software is provided as part of the operating system setup. On the Linux operating system, a Genesys installation must use MS SQL ODBC 13.0 client software to access all supported versions of MS SQL. For the Windows platform, you can use the alternative version described in Using an Alternative Version of ODBC Client Software, below.

Using Microsoft Client Software

Genesys uses TCP/IP as a way to access Microsoft SQL Server. When installing Microsoft SQL Server and/ or Microsoft client software, make sure that Server and Client are using TCP/IP. Dynamic ports are not supported; you must configure the server to listen on a fixed port (1433).

You can access default instances or named instance (including Express) of Microsoft SQL Server. To use a default instance, set the following parameters of the Database Access Point: 

dbengine = mssql
dbserver = <sql server host>
dbname = <database name>
username = <user>
password = <password>

If a named (non-default) instance is used, the dbserver parameter must be specified in the format:
dbserver = <sql server host>\<named instance>

Or for the Microsoft SQL Express edition:
dbserver = <sql server host>\sqlexpress

Management Framework Components on Windows

  • The MSSQL connection is made using ODBC, by default. In legacy environments, the connection can be made using the MSSQL 2005 Server Native Client driver, if it is installed.
  • To work with MS SQL databases, Configuration Server and Message Server require Microsoft Data Access Components (MDAC) version 2.8 or later.
  • For 64-bit platforms, Genesys provides an alternative version of the dbclient that can be used on Windows 2012 . See Using an Alternative Version of ODBC Client Software.

Management Framework Components on Linux

On the Linux operating system, you must install and configure access to ODBC driver software provided by Microsoft, and ensure that dbclinet_msql_64 is available, as follows: On the hosts that will be using this component, install the database client software and make it available for the component. To support MS SQL 2017 Server on Linux, /etc/odbcinst.ini and /etc/odbc.ini must contain the following, respectively:

odbcinst.ini 

[SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.1.0
UsageCount=1
Important
Genesys recommends that you use ODBC driver version 13.x. If you are not using version 13.1, replace 13.1.so.1.0 with the version you are using.

odbc.ini 

[MS SQL Server]
Driver=SQL Server
Description=<optional>My MS SQL Server
Trace=No

Using an Alternative Version of ODBC Client Software

Genesys provides newer versions of some dbclients in the dbclient_next folder of the component’s Installation Package (IP). Use these processes to enable support of new databases and features running on the Linux platform. Only 64-bit versions of alternate clients are provided. To use the alternative versions, copy the provided executables into the root installation folder.

Secure Connections Using TLS v1.2

Important
This functionality is supported only on the Windows platform.

TLS version 1.2 is supported for MS SQL Server versions 2016, 2014, 2012, 2008, and 2008 R2. TLS v1.1 and TLS v1.2 are not supported for versions prior to Windows Server 2008 R2 and Windows 7.

To use TLS v1.2 for secure connections between MS SQL Server and clients, Microsoft provides updates that must be installed on both SQL server and client machines. Minimum requirements and required updates for installation are provided by Microsoft here.

TLS v1.2 is not enabled by default in Windows versions prior to Windows 8.1 and Windows Server 2012. Consult the table in the More Information section here to determine if TLS v1.2 is enabled by default in your system:

If TLS v.1.2 is not enabled by default in your Windows system, follow the instructions here to enable TLS v1.2 using registry entries.

After the the SQL server and client machines are ready to support TLS v1.2, enable DB Server (if installed) to use TLS v1.2 as described in the following sections:

Using Windows Authentication

Genesys recommends that you upgrade to DB Server 8.1.301.13, which supports the Domain Source Name (DSN) for MSSQL connection using Windows Authentication. When creating DSN, select the installed driver that supports TLSv1.2.

For more information about creating and using DSN with Genesys components, see Configuring Applications to use Windows Authentication when Accessing MS SQL Server below.

Using SQL Authentication

As stated above, registry entries must be set, so that the drivers with TLS v1.2 support are used to connect with the MSSQL Server. DB Server supports only two drivers, SQL Native Client and SQL Server.

Using SQL Native Client Driver

SQL Native Client driver can be used only with legacy systems using MS SQL Server 2005 with the driver installed on it. Set the registry entries as follows:

  1. Open the Windows command-line window (Run...) and enter regedit.
  2. Go to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Native Client.
  3. Change the Key Driver path, which identifies the driver file used to connect with the MS SQL Server, to the Native Client driver installed for TLSv1.2 support. For example, Driver=C:\Windows\system32\sqlncli11.dll.

Using SQL Server Driver

Set the registry entries for the SQL Server Driver for connection with MS SQL Server, as follows:

  1. Open the Windows command-line window (Run...) and enter regedit.
  2. Go to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server.
  3. Change the Key Driver path, which identifies the driver file used to connect with the MS SQL Server, to the ODBC SQL Server driver installed to provide TLS v1.2 support. For example, Driver=C:\Windows\system32\msodbcsql11.dll.

Windows Authentication with MS SQL Server

Important
This functionality is available only on the Windows platform.

Windows Authentication provides a more secure way for an Application to access an MS SQL database without storing the database password in the Genesys configuration. Windows Authentication uses the Kerberos security protocol, enforces password policies to ensure strong passwords, and supports account lockout and password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

This section describes how to enable and configure Windows Authentication with MS SQL Server for Genesys applications that support it.

Enabling a Windows Process to Utilize Windows Authentication on the MS SQL Server

For an application to use Windows Authentication to access an MS SQL database, the Windows account under which the application runs must have both of the following:

  • Login access to the MS SQL Server.
  • Appropriate access to the MS SQL database that the application will use.

To verify that both exist:

  1. Start MS SQL Server Management Studio.
  2. In the Connect to Server dialog box, specify an MS SQL Server name and administrator credentials to connect to the MS SQL server.
  3. In Object Explorer, expand the entry for the MS SQL Server identified in the previous step, then Security, then Logins. The Logins folder should contain an entry for either the Windows account itself, or the group to which that account belongs; for example, <Domain name>\Administrators.
  4. To determine if the Windows account is either directly mapped to the database, or has administrative access to all databases, right-click the user's Login to open the Properties dialog box and select Server Roles. Then do one of the following:
    • If sysadmin is checked in the Server Roles list, this Windows account has access to all databases.
    • If sysadmin is not checked, click User Mapping to see if this Windows account is mapped to the appropriate database as db_owner.

If an appropriate Login does not exist, and/or the Login does not have access to the database, do the following steps, as appropriate:

  1. Start MS SQL Server Management Studio.
  2. In the Connect to Server dialog box, specify an MS SQL Server name and administrator credentials to connect to the MS SQL server.
  3. In Object Explorer, expand the entry for the MS SQL Server identified in the previous step, then Security, then Logins.
  4. Click New Login. The Login-New dialog box opens.
  5. In the Login name field, enter the user name of the Windows account in the format <domain>\<username>.This creates the new Login.
  6. In Object Explorer, configure access to the appropriate database, as follows:
    1. Select User Mapping in the left panel.
    2. In the upper half of the right side, select the appropriate database. The name of the Login you just created appears in the User field.
    3. In the Database role membership for: list, select db_owner.
  7. Click OK.

After a Windows account has a Login and is associated with a database, anyone using that account can log in to the MS SQL Server without specifying a username or password.

If the application that connects with the database has been installed as a Windows Service, by default it is started under a Local System account with a user name of NT AUTHORITY\SYSTEM, in the group BUILTIN\Administrators. But this user account has no access permissions to the database, so the user account from which the service gets started needs to be changed.

Do one of the following to change the user account of the service:

  • In the Computer Management/Services console, right-click the service and navigate to Properties > Log On tab > This Account, and enter a Windows username and password that has permission to connect to the MS SQL Server and access the database.
  • Run the following command to change the user account: 
    sc.exe config <service name> obj= <.\user account name> password= <user account password>

You must also change the user account of the Local Control Agent service, so that it is able to start the application under a non-default Windows account.

Configuring Applications to use Windows Authentication when Accessing MS SQL Server

If an Application is using DB Server to access a database, it must be using DB Client 8.5.1 or higher. In addition, a Windows process for DB Server must be set up as described above. DB Server must then be set in the DAP.

If an Application is accessing the database directly (without DB Server), a DAP is required without access to DB Server. A Windows process for the Application itself must also be set up as described above.

After a Windows process and MS SQL Server have both been enabled to use Windows Authentication, you can force Genesys applications to connect to the database using Windows Authentication by using either a Trusted User or a Data Source Name (DSN).

For an application to use Windows Authentication, it must be provisioned with username=trusted in its configuration, where trusted is a keyword. The password field is not used in this case, and can be left empty.
Refer to the configuration options of the particular application to determine if it supports Windows Authentication and where, in its configuration, to enter the database user name.
Example: Message Server with Direct Connection to Database To configure Message Server that connects directly to the Log Database (the default configuration), configure the options that describe the Log Database in the Database Access Point of the MS SQL Log Database, as follows:

  • In the DB Info section of the Configuration tab, enter trusted in the User Name field.

To set up Windows Authentication using a DSN, you must first open and configure an ODBC Data Source using Microsoft Windows NT Authentication, as follows:

  1. Open a Data Source Administrator by following the steps here for your particular version of Windows.
    Tip
    There might be two Data Sources (ODBC) available, one for 32-bit and another for 64-bit. Select one according to the type of Genesys application.
  2. On the System DSN tab, click Add to add a system data source.
  3. Select one of the following drivers, as appropriate:
    • MS SQL Server (recommended)
    • MS SQL Native Client
    • MS SQL Server Native Client
  4. Click Finish.
  5. Follow the instructions here to configure the DSN to be used to connect to the database.
    Important
    Use the System DSN to configure only Windows Authentication.
  6. Click Finish. The application displays a summary page.
  7. Click Test Data Source to run a test connection and ensure that your configuration is valid. If the test is successful, the Test Results are displayed.

After the Data Source is set up, you can then enable the MS SQL DB Client to support it.

For an application to use Windows Authentication using DSN, it must be provisioned with DBMS Name=dsn and Database Name=<dsn name> in its configuration, where dsn is a keyword and <dsn name> is the name of DSN you configured in the previous step. The username option is not required, and can be set to any name or password, or can be left empty.

Refer to the configuration options of the particular application to determine if it supports Windows Authentication and where, in its configuration, to enter the name of the database and DBMS.

Example: Message Server with Direct Connection to Database

To configure Message Server that connects directly to the Log Database (the default configuration), configure the options that describe the Log Database in the Database Access Point of the MS SQL Log Database, as follows:

  • In the DB Info section of the Configuration tab, enter dsn and the name of the DSN in the DBMS Name and Database Name fields, respectively.

Example: Message Server using DB Server 8.1.3 to Connect to Database

To configure Message Server that connects to the Log Database using DB Server 8.1.3, configure the option that disables the direct connection, as follows: 

...
[messages]
...
dbthread=false
...

Configure the options that describe the DB Server connection, and the Log Database, in the Database Access Point of the MS SQL Log Database, as follows:

  • In the DB Info section of the Configuration tab, enter the following information:
    • DBMS Name—dsn
    • Database Name—DSN Name

Configure DB Server 8.1.3 using dbclient_851. If DB Server is started as a service, the user account of the service must be modified so it has access to the Configuration Database.

Encrypting Communications with Microsoft SQL DBMS

Important
This functionality is supported only on the Windows platform.

In addition to using Windows Authentication with an MS SQL Server you can also force Genesys components to use a secure connection to MSSQL by configuring MS SQL server to accept only encrypted connections, based on the certificate added to the server.

To configure the MS SQL Server to accept encrypted connections, you must add a certificate with a fully qualified computer domain name to MS Certificate Storage on the server side. Add the certificate to the Personal folder and the Trusted CA to the Trusted Root Certification Authorities folder, both in the Local Computer account. Use Microsoft Management Console (mmc) to manage certificates.

To configure the server:

  1. In MS SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and select Properties from the drop-down menu.
  2. On the Certificate tab, select the desired certificate from the Certificate drop-down menu, and click OK.
  3. On the Flags tab, select Yes in the ForceEncryption box, and click OK to close the dialog box.
  4. Restart the SQL Server service.

After you add the certificate, all client connections with this server will be encrypted.

Using Microsoft SQL Server Databases with National Languages

Single Language Deployment

No special configuration or other preparations are needed to use Genesys applications in single language mode with Microsoft SQL Server databases. The databases themselves must be created with target language and default encoding, as given in the following table:

[+] Show table

Multiple Languages Deployment

To use Microsoft SQL to store data in multiple languages, the database tables must be able to store UNICODE characters (UCS-2 encoding).

The following SQL Server collation name must be set for Multi-language support:

  • Latin1_General_100_CI_AS_KS_WS_SC

When configuring a Database Access Point to access a multi-language database, you must specify utf8-ucs2=true in the [dbclient] section of the annex of the DAP.

Using MSSQL 2012 Always On Failover Cluster Instances (SQL Server)

Genesys supports MSSQL 2012 Always On Failover Cluster Instances (FCI), that uses Windows Server Failover Clustering (WSFC) to provide local high availability (HA) of redundant MSSQL databases at the server-instance level. Resources (databases) are grouped into a WSFC resource group, which is owned by a single WSFC node. Each FCI is an instance of an SQL Server and contains a set of WSFC nodes. When a failure occurs, the ownership of that resource group is switched to another WSFC node within the FCI. The switchover is done automatically and without any impact to the user.

For more information about FCI and WSFC, refer to Microsoft documentation at https://msdn.microsoft.com/en-us/library/ms189134(v=sql.110).aspx.

Failure of an MSSQL 2012 Cluster Database

There is no automatic resubmission for MSSQL. If the database fails, you must manually resubmit all failed write operations.

Comments or questions about this documentation? Contact us for support!