Microsoft SQL

General

Important

When using the customer’s own Microsoft SQL server instances, the responsibility for use and configuration lies with the respective database system administrator and not with the XPhone Connect software manufacturer C4B or the XPhone Connect partner.

Caution

Observe the following notes for smooth operation when using your own SQL server instances or databases:

  • Make sure that sufficient free disk space is available on the drive on which the SQL server instances or databases were installed. The space required depends, among other things, on the number of users, the call and chat volume (journal tables), number of images sent, statistical data for analytics.

  • Regular backups should generally be configured for the XPDATA, XPSTATS and XPSTATSRaw databases. It is not necessary to create backups for the XPVDIR database, as the index tables contained there are regenerated cyclically from the connected XPhone Connect Directory data sources anyway.

  • It is recommended to use the “Simple” recovery model for all databases used. For more information, see the online help for Microsoft SQL Server.

  • If the XPhone Connect server is operated together with the SQL server on the same system, the use of the memory (RAM) for the SQL server should be limited, you can configure this e.g. via the SQL Management Studio.

The XPhone Connect Server’s persistent data is stored in four databases, distributed across two Microsoft SQL Server Express Instances. These instances are installed by the setup and are called XPCONNECT and XPANALYTICS. These instances are independent of other SQL Server installations and can also be operated in parallel to existing SQL Server installations of other editions.

The two XPDATA and XPVDIR databases are created in the XPCONNECT instance. All configuration information and the users’ journals are stored in the former while the other database exclusively contains the indices required for Connect Directory and, if necessary, the replicated contact data.

The two XPSTATS and XPSTATSRaw databases are created in the XPANALYTICS instance. The data collected for Analytics is stored in these databases.

Note

Please also note the information and workaround in the event that the internal databases have not been installed and, consequently, it may not be possible to convert the service account in the XPhone Connect Server Manager.

The database configuration can be found in the atlas.xml file (can be retrieved via the XPhone Connect Server Manager) and has the following contents by default:

<Databases databaseMigrationSQLTimeOut="900">

<Main type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPDATA;Data Source=(local)\XPCONNECT" autoLockDown="5" organisationDN="ou=Organisation" configurationDN="ou=Configuration" />

<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPSTATS;Data Source=(local)\XPANALYTICS" autoLockDown="5" />

<VDir type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPVDIR;Data Source=(local)\XPCONNECT" autoLockDown="5" />

</Databases>

Description of the parameter:

Name

Default value

Format

Description

type

MSSQL

Text

Currently only MSSQL is supported

connection

(see above)

special

This is the ConnectionString for the connection to the database. For valid parameters, see the documentation on the Class SQLConnection for .NET in the SQL Server documentation. If you wish to connect to an existing SQL server, you only need to modify the “Data Source” parameter.

autolockDown

5 (1, if no entry exists)

Number

(see below)

organisationDN

special

Required for Active Directory integration

configurationDN

special

Required for Active Directory integration

useLogin

All

Optional setting indicating the credentials with which the login attempt is to be made.

“All” - all users

“Internal” - only internal SQL users, no service Windows account

“Windows” - only the service’s Windows account

databaseMigrationSQLTimeOut

This indicates the timeout in seconds for SQL queries which must be run in order to carry out the modifications necessary to databases after updates.

The databases can also be operated on an existing SQL Server instance. If you wish to use an existing SQL Server, you must modify the connection parameter. The ConnectionString contains the Data Source parameter, with the format <DNS name of computer>\<Name of the SQL instance>. (local) represents the local computer; the first part without \ is sufficient for the default instance.

Procedure

If you wish to use the existing SQL Server instance sqlserver\connect, for example, we recommend proceeding as follows:

  1. Check the requirements of the existing SQL Server:

    • The TCP/IP protocol must be activated under Server Network Configuration.

    • Remote access must be activated on the existing SQL Server.

    • The SQL browser service must be running on the existing SQL Server.

  2. Create a user account (aduserxphoneserver) in the Active Directory under which the XPhone Connect Server service is to run.

  3. Create a database (xphoneserverdata) in the desired SQL instance which is to be used by the XPhone Connect Server.

  4. Create a database (xphoneserverdata) in the desired SQL instance which is to be used by the XPhone Connect Directory.

  5. Create a login for the user aduserxphoneserver in the SQL instance.

  6. In the SQL instance, assign the role db_owner to the login to the xphoneserverdata database.

  7. In the SQL instance, assign the role db_owner to the login to the xphoneservervdir database

  8. End the XPhone Connect Server.

  9. Change the database settings in atlas.xml to the SQL instance.

  10. Replace the XPDATA database with xphoneserverdata and XPVDIR with xphoneservervdir in atlas.xml

  11. Restrict the login attempts for both databases to integrated Windows login in atlas.xml (uselogin="Windows") and Autolockdown to 0.

  12. Change the XPhone Connect server service account to aduserxphoneserver with the aid of the XPhone Connect Server Manager.

  13. Start the XPhone Connect Server:

    <Databases databaseMigrationSQLTimeOut="900">

<Main type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xphoneserverdata;Data Source=sqlserver\connect" autoLockDown="0" uselogin="Windows" organisationDN="ou=Organisation" configurationDN="ou=Configuration" />

<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPSTATS;Data Source=(local)\XPANALYTICS" autoLockDown="5" />

<VDir type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xphoneservervdir;Data Source=sqlserver\connect" autoLockDown="0" uselogin="Windows" />

</Databases>

Procedure for Analytics

To use the existing SQL Server instance MySqlServer\MyInstance for Analytics, for example, please proceed as follows:

  1. Check the requirements of the existing SQL Server instance MySqlServer\MyInstance:

    • The TCP/IP protocol must be activated under Server Network Configuration.

    • Remote access must be activated on the existing SQL Server.

    • The SQL browser service must be running on the existing SQL Server.

    • After changing the SQL configuration, it may be necessary to restart the SQL Server to activate the changes.

    • It may be necessary to set up a firewall rule allowing access to the SQL Server instance.

  2. Access by the XPhone Connect Server to the Analytics databases (e.g. for scheme updates).

    (recommended procedure for a web server cluster)

    1. Create a user account (aduserxphoneserver) in the Active Directory under which the XPhone Connect Server service is to run.

    2. In the desired SQL instance MySqlServer\MyInstancezwei create two databases (XPAnalytics und XPAnalyticsRaw) to be used by Analytics.

      Hint

      The name of the second database must be the same as the first plus the suffix Raw.

    3. Create a login for the user aduserxphoneserveranlegen in the SQL instance.

    4. In the SQL instance, assign the role db_owner to the login to the XPAnalytics and XPAnalyticsRaw databases.

    5. End the XPhone Connect Server.

    6. In atlas.xml change the database settings for Analytics (Connection String Name Stats) to the SQL instance:

      • In atlas.xml replace the XPSTATS database with XPAnalytics .

      • In atlas.xml restrict the login attempts to integrated Windows login (uselogin="Windows") and set autolockdown to 0.

    7. Change the XPhone Connect Server service account to aduserxphoneserver with the aid of the XPhone Connect Server Manager.

  3. Access by Analytics web application to the Analytics databases (for evaluations):

    1. In Active Directory, create a user account (aduseranalyticsapppool) under which the Analytics application pool is to run in the IIS.

    2. In the IIS, assign the XPhoneConnectAnalytics application pool the domain account you have just created.

    3. Create a login for the user aduseranalyticsapppool in the SQL instance.

    4. In the SQL instance, assign the roles db_datareader and db_datawriter to the login just created in the XPAnalytics and XPAnalyticsRaw databases.

    5. Start the XPhone Connect Server.

Example of the atlas.xml:

<Databases databaseMigrationSQLTimeOut="900">
  ...
<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPAnalytics;Data Source=MySqlServer\MyInstance" autoLockDown="0" />
  ...
</Databases>

Data base security

The XPhone Connect Server must be able to access the data base in the SQL server role db_owner. The data base should be configured to restrict access by others as far as possible in order to protect the data from unauthorised access (viruses, spyware). This customisation is done automatically for the Microsoft SQL Server Instances installed by the set-up. The autoLockDown parameter controls this automatic customisation of data base security.

Caution

If existing SQL servers are used, you must be very careful here, as access to the data base for administrative purposes or by other applications is then prevented.

The SQL server installed by the set-up runs in mixed mode, which means that authentication is done both by Windows login and by the user accounts created in the SQL server. For example, SQL login makes it possible for clients to access the central address book, even if the clients could not be authenticated by Windows (e.g. mobile computers without domain login).

Hint

If an existing SQL server does not run in mixed mode, you should deactivate the automatic customisation. You must also ensure manually that the service account of the XPhone Connect Server receives the required access rights (SQL server role db_owner) for the SQL Server.

Flag

Description

0

Security is not customised or changed

1

A data base administration account is added for emergencies. Only the server knows the password for the admin account.

2

All accounts that were not explicitly activated (SQL and Windows) are removed

4

The sa account, which always exists, is given a password that only the server knows.

8

The local administrator is set up as the data base administrator.

The flags add up, which is to say that 1+4=5 results in the aforementioned setting for the SQL Server data base that is installed by the set-up. With the exception of mode 0, the account for the XPhone Connect Server service is always set up. This is only possible if the XPhone Connect Server service manager was used to change the service account.

Have you found a mistake on this page?

Or is something not formulated well or too vague? Then we look forward to receiving an e-mail, preferably with a suggestion for improvement, to doku@c4b.de. Thank you very much!