Difference between revisions of "How to connect a SQL database to Open Directory Service (ODS)"
The Wiki of Unify contains information on clients and devices, communications systems and unified communications. - Unify GmbH & Co. KG is a Trademark Licensee of Siemens AG.
|Line 183:||Line 183:|
Revision as of 15:51, 5 December 2011
- 1 Preface
- 2 Prerequisites
- 3 ODS Assistant
- 3.1 Settings
- 3.2 Data Sources
- 3.3 Add new SQL Data Source to ODS
- 3.4 Maintenance
This How To describes the connection of a SQL database to OpenScape Office (OSO) by using the OpenDirectory Service (ODS). The How To is based on OSO version V3R2 deviations may occur, if higher versions are used.
All data shown here are examples only which are used to demonstrate the functions. In real installations they have to be replaced by data of the installation environment.
Before you start, you have to check some prerequisites otherwise the connection fails.
HW depends on used OpenScape Office plattform, no additional PC HW is required.
OpenScape Office LX / MX / HX from V3R2 on
- Open Directory base license
- Open Directory connector license
Note: Check that you use the correct licensing line for ODS.
In general two lines exist on the Central Licensing Server (CLS)
- OpenScape Office LX/MX licensing
- OpenScape Office HX licensing
ODS base and ODS connector are availabe in both lines.
Information about Database
Several information about the database, which has to be connected to Open Directory Service must be available as prerequisite for properly configuration. At least information about:
- IP Adress
- Database name
- Login parameters
- Database structure (tables attributes)
Depending on the database type and configuration several additional information may be required. It has to be ensured that an external server (ODS Server machine) is allowed to access the database.
To get such information it is highly recommended to involve the database administrator before starting the configuration. In some cases it could be necessary to create a specific user within the database server for access of Open Directory Service. This can only be done by the database adminstrator.
Administration of ODS is done by the OpenScape Office Administration Portal. For access type the following URL into the Internet browser
After authentification Open Directory Service can be configured within the expert modus.
A "left mouse click" to "Directory Service" opens the Assitant.
Some general items for the Open Directory Service have to be configured here:
Activation of Open Directory Service
Per default Open Directory Service is deactivated. You can activate it by a tic within the checkbox. Afterwards you will get access to the configuration.
LDAP Server access data
Open Directory Service data can be accessed by OSO or third party LDAP capable clients. For authentication purpose the internal LDAP Server of Open Directory Service requires login name and a password.
In V3R2 the login is set fix to
It cannot be modified. The password should comprise 6 charactes at least.
Make shure that you remember the login data. You will need it later, when a LDAP client needs access to the LDAP Server of ODS. An anonymous LDAP login is not granted by ODS.
Open Directory Service allows the connection of several databases. The connection is done by so called “Database Connectors”.
For every database type a specific connector with appropriate configuration parameters exists. A database connector type can be used to connect multiple databases of the same type. Database connectors have to be configured individually to access a specific database. The specific configuration of a Database connector within the Open Directory Service is labelled as Data Source.
Predefined Data Sources
Within Open Directory Service two Data Sources are preconfigured
- OpenScape Office directories - Allows access to OpenScape Office directories.
- OpenScape Office Speed Dials - Allows access to the OpenScape Office speed dials.
The pre-configured datasources cannot be deleted or modified except the configuration of “number conversion”, which could be necessary for some LDAP clients.
User defined Data Sources
You can create new Data Sources in a simple dialog guided way by pressing the “Add” button within the “Data sources” site.
Afterwards the Database Connector specific dialog starts, which allows to enter the appropriate data. You can modify already configured datasources at any time by pressing the edit button. Datasources, which you do not need need any longer can be deleted accordingly. Multiple datasources can be defined and operated simultaneously.
Status of connected datasources
The Settings Windows gives an overview about all configured datasources to you. In the last row the status of the data source is indicated by a coloured dot.
- Green: Operational
- Red: Status “ODBC not ok, LDAP not ok” configuration not correct or data source not available.
- Yellow: Status “ODBC ok but LADP not ok” Service should be restarted
- Grey: Status “configuration incomplete”
If you move the mouse pointer over the dot an additional text is shown in case of malfunction.
Add new SQL Data Source to ODS
If you click to the "Add" button within the Data Source window, a new dialog opens, which allows the configuration of a new connection to a SQL datbase. Five tabs leads you, dependent on the type of SQL datasource through the whole configuation process.
At first you define the name of the datasource. This name is used to identify the datasource within the Open Directory Service. Max 20 alphanumeric characters can be used here
Database specific information such as localtion can be enteres here Max 100 alphanumeric characters can be used here
ODBC driver Depending on the type of the target database the suitable driver has to be selected. A drop down menu offers the available types of ODBC driver:
The following configuration parameters may differ from driver to driver.
Database Server address
The IPv4 address of the database server has to be entered here in the format:
Entry of FQDN and URL is not recommended here
Database Server port
The port of the Database Server within the Server machine has to be entered here
Database name This field supports alphanumeric input . The database name is case sensitive as it is used by the database server internally to identiy the database.
This field supports alphanumeric input . The database login name is case sensitive. Login name ist used by the database server to identify the user and to grant access to data according to its user configuration..
This field supports alphanumeric input . The database login name is case sensitive. Password is used by the database server to identify the user.
TDS Protocol version
This parameter applies only to Microsoft SQL / Sybase SQL databases. Serveral protocol types are supported. More information about protocol usage is shown by clicking on the yellow info icon.
After you have enterd all parameters the connection to the database can be tested. A click on the button labeled “Test Database access” checks if access to the database is granted by the database server. A dialog box informs about success or non success of the test.
220.127.116.11.1.3 Test Sever an database connection
At the end of the database configuration the settings should be saved. Afterwards the Datasource name is available within the left column under the “Data Sources” headline . Further information about the data source is available within the Data Sources Main Window. A datasource can be modified or deleted by pressing the appropriate button after datasource selection within the Main Window.
18.104.22.168.2 Database Table Within ODBC databases (relational databases) the data are stored within tables and table specific attributes. Once the connection to the external database is established the available tables can be displayed by pressing the button “Query database tables” (This feature is not available for Microsoft SQL and Sybase SQL databases; Please enter the table name manually instead).
Note: Currently only one table can be selected here
In case it is not clear which attributes are available in a table the “Preview database table” button can be used to read the attributes from the selected table.
A browser function allows to step through the table and to display the content of the attributes. 22.214.171.124.3 Field Mappings Within the penultimate step the mapping of the ODBC database table attributes to the Open Directory fields, which are finally presented by the LDAP server has to be done.
The mapping is done in the way, that the available attributes of the selected table are presented at the left side and the Open Directory Sevices (LDAP) field at the right side. The table attribute can be chosen by a click to the attribute within the drop down menu. An already used and assigned attribute is presented in blue color. Multiple assignments of an attributes to different LDAP fields are allowed. On the Open Directory Service side the available fields are presented also within a drop down menu. Selection is done by mouse click. Within the drop down menue only the “free” (not assigned) fields are presented for mapping. A preview function is available which allows to check the output of the assigned Open Directory fields by browsing through the database entries. Already mapped fields are diplayed on the screen. Add and remove operations are available for editing the mapping. Note: Mapping of multiple attributes to one Open Directory field is not possible. 126.96.36.199.4 Number conversion Number conversion allows to normalize the phone numbers which are stored within an external database into full qalified number format (e.g. +12 (345) 678 90). This is needed for correct caller identification and desktop dialling. Number conversion can be done individually for every data source. The stored phone numbers within the source database are not modified.
The function is activated by checking the “Active” checkbox. If the function is activated the additional dial parameters, which characterize the location of the database / data source are presented and need to be populated with the correct values. In case the data source is at the same location than the OpenScape Office telephone system the telephone system settings can be imported by pressing the button (Query System Settings) . The user has to configure if the phone numbers in the database starts with a dialout prefix or not. This setting is needed because in some counties a ‘0’ as prefix cannot differentiated from the national access prefix (e.g. in Germany). Number conversion steps 1. The phone number will be cleaned by removing space, brackets, hyphens, commas 2. The internal algorithm will convert all numbers into full qualified format or leaves them as they are in case of internal or fully qualified numbers.
Limitations IIf the customer database might contain numbers where prefixes and area codes are missing it will automatically add the actual area code. Even when it could be identified by human – for example if the length of the phone number is unique for a district – the algorithm will fail. 188.8.131.52.5 LDAP Test Administration menue of Data Sources offers also the test of the configured parameters. Selection of the LDAP Test Tab allows user to check, if the the settings and configuration made before, deliver the requested data.
Pressing the button “Run test” will seach within the configured datasource and displays all entries of the datasource and the content of the configured Open Directory Service data fields (LDAP fields).
In case that the output within some fields does not meet the expectation (e.g. Phone number conversion is wrong), configuration can be adjusted and the test can be repeated as often until the results are satisfying.