==== introduction =====

Before you can use niota to edit data in your databases you have to set up connections to these databases. A connections consists of 
  * login for a technical user that is allowed to connect to the database 
  * server (host) where the database is hosted
  * additional settings for the connection

The avoid redundancy these components of a connection are also split to several configuration tables. To set up a connetion you have to configure the login, the host and finally the connection itself.


==== configure hosts =====
Edit file ''hosts.php'' in your client specific conf-folder.
You will find some predefined hosts there like for Zagreus web service calls or niota meta database. In case meta database server is not running on the same machine as the application itself you have to adapt the IP address. In case both are running on the same machine you can keep pre-configured ''localhost'' settings. Adapt the IP port to your needs in case you have configured a different port on your local install.

** Please mind that syntax for host:port might differ on different database management systems. **

<code>
/* adapt your IP addresses and ports here */ 
const ET_HOST = [
        ET_METADATA_CONNECTION =>'localhost:3366',
	ET_ZAGREUS_CONNECTION =>'localhost:7323',
        "SQLSERVER" =>'localhost,1433'
	];
</code>

Microsoft SQL Server needs a comma between host address and port instead of a colon!

** MSSQL named instances ** require the instance name instead of the IP address - in case you need native SQL connection.
<code>
const ET_HOST = [
        ET_METADATA_CONNECTION =>'127.0.0.1:3366',
	ET_ZAGREUS_CONNECTION =>'http://127.0.0.1:7323',
        "SQLSERVER" =>'C9RRELE02\MSSQL_RITCON_E'
	];
</code>

==== configure logins =====
Edit file ''hosts.php'' in your client specific conf-folder.
You will find some predefined logins there like for Zagreus web service calls or niota meta database. In case you decided to have different users/login than the predefined change here the settings accordingly.

** We strongly recommend to use dedicated niota users on your database systems and restrict these users to minimum access rights. Do not user system admin users to access your data via niota! **

** Use password encryption for your technical users! **

In the shipped configuration the names of the logins are equal to the names of the host. This is just to simplify the naming issue. In case you have the some login for different SQL servers or Oracle or ... you can of course name the hosts differently then the logins.

<code>
/* adapt your login users and passwords here */ 
const ET_LOGIN = [
        ET_METADATA_CONNECTION => array(
		LOGIN_USERNAME => 'niota_web',
		LOGIN_PASSWORD => '*****',
		LOGIN_ENCRYPTED => true
		),
        ET_ZAGREUS_CONNECTION => array(
		LOGIN_USERNAME => 'admin',
		LOGIN_PASSWORD => '*****',
		LOGIN_ENCRYPTED => true
		),		
        "SQLSERVER" => array(
		LOGIN_USERNAME => 'niota_web',
		LOGIN_PASSWORD => '*****',
		LOGIN_ENCRYPTED => false
		)
</code>


==== configure connections =====

After you have defined your database hosts and the login you can configure the connecion(s). You have to edit the file ''connections.php'' in your client specific conf-folder. 

** To assure that your login has really access rights to the database where you want to setup the connection we recommend to test the login with a standard client for your database ** 

If you use MS SQL Server you can connection via Management Studio, if you use Oracle, you can use SQL Developer, ..... 


One connection out of the array of configured connections must be set as the **metadata database connection**. This connection must be a MySQL connection.

Other database connections can be set to your local database management systems and may be of these types
  * MySQL Server
  * MS SQL Server
  * Oracle
  * Teradata

You can also configure other types of connection in this file.
  * Web service connection
  * LDAP connection (for authentication via LDAP)

=== define metadata connection ===

The metadata connection is assigned by this variable.
<code>
// the metadata connection, from the ET_CONNECTIONS keys
define('ET_METADATA_CONNECTION', 'niota_meta');
</code>

The assigned value must exist as a valid entry in the array of defined connections. The array of possible connections is defined in constant ''ET_CONNECTIONS''.

==== define database connections ====

You can define in theory as many connections as you like. In reality it's recommended to limit the number of connections. (max 128).
To get a valid connection definition you have to set a number of entries in the constant array ''ET_CONNECTIONS''

Every connection definition is a named array with this settings. Not every setting must be made in every case. If you do not set opitional parameters then some default values are used.

There are some pre-definied constants/variables that you can use in the configuration of a connection.
A database connection must be of type ''CONN_TYPE_DATABASE''.
 
<code>
PHPGRID_DB_TYPE                     => <database type of the connection>,
PHPGRID_DB_DISPLAY_NAME             => "<display name>",
PHPGRID_DB_HOSTNAME                 => "<host:port>",
PHPGRID_DB_USERNAME                 => "<user for connection>",
PHPGRID_DB_PASSWORD                 => "<password for connection>",
PHPGRID_DB_NAME                     => "<fixed datbase name for connection>",
PHPGRID_DB_CHARSET                  => "<character set of connection on db side",
PHPGRID_DISPLAY_NUMBER_DECIMALPOINT => "<decimal point character",
PHPGRID_DB_PW_ENCRYPTED             => <password encryption true/false flag>
PHPGRID_SOURCE_DATE_FORMAT          => "<date format on db side>",
PHPGRID_DISPLAY_DATE_FORMAT         => "<date format in niota",
PHPGRID_SOURCE_DATETIME_FORMAT      => "<timestamp format on db side>",
PHPGRID_DISPLAY_DATETIME_FORMAT     => "<timestamp format in niota>",
PHPGRID_CONN_TYPE                   => <connection type>,
</code> 
\\
\\
== PHPGRID_DB_TYPE == 
Type of connection in case of a database connection.
[[niota-docs:niota_constants|Here you can see allowed value for connection database type]]

In case you choose a type that is an ODBC connection type you need to configure a proper ODCB connection as well and reference to this connection in the hostname parameter.
\\
\\
== PHPGRID_DB_DISPLAY_NAME ==
Text string that is used in the UI displayname of the connection.
\\
\\
== PHPGRID_DB_HOSTNAME ==
Name or address of the host where the database engine in runnning.
The hostname may have different types of setting depending on the type of DB connection you choose.

  * name of the ODBC connection in case you use a ODBC connection type
  * hostname:port in case you use a native connection type
  * ''false'' in case you use Oracle tnsames.ora - file 
\\
\\
== PHPGRID_DB_USERNAME ==
Name / ID of the user you want to connect to the database. Use a technical user or a service user here. Asure, that the password of user changes **not** regularly.
\\
\\
== PHPGRID_DB_PASSWORD ==
Password used to connect to the database. Use **encrypted** password if possible. If you use encrypted password, set ''PHPGRD_DB_PW_ENCRYPTED'' **true**.\\ 
Use php-applicaton /app/tools.php to encrypt your password string.
\\
\\
== PHPGRID_DB_NAME ==
Name of the database the system connects to. **In case of MS SQL Server the database name has to include the schema as well.**
\\
\\
== PHPGRID_DB_CHARSET ==
Character set Id of the database. The application itself always tries to convert to utf-8. You have to specify the dataset of the DB / the source system here.
\\
\\
== PHPGRID_DISPLAY_NUMBER_DECIMALPOINT ==
Character to use as decimal point separator in niota.
\\
\\
== PHPGRID_DB_PW_ENCRYPTED ==
Flag, that indicates if your password is encrypted **(''true'')** or not **(''false'')**.
\\
\\
== PHPGRID_SOURCE_DATE_FORMAT ==
Formatstring, that describes how date variables are stored / formated on database side. Mainly relevant for Oracle connections.
\\
\\
== PHPGRID_DISPLAY_DATE_FORMAT ==
Formatstring, that describse how date variables should be formated in niota.
\\
\\
== PHPGRID_SOURCE_DATETIME_FORMAT ==
Formatstring, that describes how date-time (timestamp) variables are stored / formated on database side. Mainly relevant for Oracle connections.
\\
\\
== PHPGRID_DISPLAY_DATETIME_FORMAT ==
Formatstring, that describse how date-time (timestamp) variables should be formated in niota.
\\
\\
== PHPGRID_CONN_TYPE ==
Overall type of the connection definition.
[[niota-docs:niota_constants|Here you can see allowed value for connection type]]
\\
\\

==== set up MS SQL Server db connection ====

=== use ODBC connection === 

  * create the MSSQL ODBC data source. 
Take care wether you create a 64 bit or 32 bit data source. 64 bit PHP does not work with 32 bit ODBC data source and vice versa.

  * edit file ''niota/conf/connections.php'' according to the connection details. 
The ''PHPGRID_DB_HOSTNAME'' key has got the value of the ODBC data source name.

  * test the connection by loading a grid in niota

=== use native connection ===
  * prerequisits for PHP 7: appropritate php driver dll file: c:\Program Files\php-7.3.8_x64\ext\**php_sqlsrv_73_ts_x64.dll**. MSSQL native driver requires ODBC driver to be installed. For php_sqlsrv_73_ts_x64.dll native driver install **ODBC Driver 17 for SQL Server** ODBC driver.
  * edit file ''niota/app/conf/connections.php'' according to the connection details. 
See the sample connection to modify in file “niota/app/conf/connections_sample.php”.

  * test the connection by loading a grid in niota
  * 

^"SQL_Server_ODBC"=> array ( ^"SQL_Server_native"=> array ( ^"SQL_Server_native_named_instance"=> array ( ^
|PHPGRID_DB_TYPE => DB_TYPE_ODBC_MSSQL|PHPGRID_DB_TYPE => DB_TYPE_MSSQL|PHPGRID_DB_TYPE => DB_TYPE_MSSQL|
|PHPGRID_DB_HOSTNAME => "odbc_connection_resource_name"|PHPGRID_DB_HOSTNAME => “myhostname.com,1433”|PHPGRID_DB_HOSTNAME => “MYSQL\MYINSTANCE”|
|PHPGRID_DB_CHARSET => "iso-8859-2"|PHPGRID_DB_CHARSET => "UTF-8" or PHPGRID_DB_CHARSET => SQLSRV_ENC_CHAR|...|

((Table 1 SQL Server connection definitions (ODBC vs. native).)) Only differences are listed.

==== use Windows Authentication ====
It is possible to use Windows Authentication in the case of native also in the case of ODBC connection.

**requirements**

  * Windows Server OS
  * Microsoft Active Directory (AD) installed on Windows Server OS
  * SQL Server database server
      * Windows authentication must be enabled under menu item: server properties
      * AD user must exists as a „login” on the database server
      * AD user must have access right to query table meta info – needed for proper working of niota
{{ :niota-docs:windows_auth_01.png?400 |}}
((Figure 1 AD user is defined as a "login" on the db server))

**administrative tasks**\\ 

**IMPORTANT!** AD user used for Windows Authentication (database authentication) must be the same user who runs Apache service! (can be set in service properties)

When Apache runs under a specified user account, consider that this account may be removed by your group policies configured by your central IT department.
It may occur that the user is removed / deactivated frequently. Then authentication will fail. You need a permanent right to run services with an AD account / local account.

AD user must have rights on system to copy and move files during niota activities
  * Excel import
  * File upload – special care to access network drive if needed
  * Write log files

{{ :niota-docs:windows_auth_02.png?400 |}}
((Figure 2 Specify AD user to run Apache . (The same user is used for Windows Authentication against SQL Server database server – see Figure #1)))

Take care on specifying the AD user as „domain\user-name”. E.g.: in the picture domain is „win19” and the user name is „test”.

**connection definitions**\\ 

^"SQL_Server_ODBC"=> array ( ^"SQL_Server_native"=> array (^
|PHPGRID_DB_TYPE => DB_TYPE_ODBC_MSSQL|PHPGRID_DB_TYPE => DB_TYPE_MSSQL|
|PHPGRID_DB_HOSTNAME => "odbc_connection_resource_name"|PHPGRID_DB_HOSTNAME => "SQL-Server-instance-name”|
|...|...|

((Table 2 SQL Server connection definitions (ODBC vs. native) with Windows Authentication )) Only differences and user authentication settings are listed.

ODBC connection:
PHPGRID_DB_HOSTNAME has the value of the ODBC datasource name (while you have to use server name/IP in the case of native connection - see next section).

native connection:
PHPGRID_DB_HOSTNAME => "SQL-Server-instance-name" is the instance name of the db server. In this example the AD user and the db server are defined in the same domain. Host name can also be defined by the IP address of the database server. For further details see PHP documentation:
https://www.php.net/manual/en/function.sqlsrv-connect.php

User name and password fields has to be set as empty-string in the case of both connections.

{{ :niota-docs:windows_auth_03.png?400 |}}
((Figure 3 name of database server in SQL Server Management Studio))
{{ :niota-docs:windows_auth_04.png?400 |}}
((Figure 4 get server name by using sql select statement))
==== set up Oracle db connection ====

niota uses native Oracle connection. Nevertheless Oracle Instant Client is also required. The Oracle instant client zip can be downloaded from niota download page.

If you have got the right version of Instant Client on your machine you can start with step 3 here.

Installation steps:


  * create a new environment variable in Windows: 
    * Go to Control Panel -> System -> System Properties -> Environment Variables
    * In the “System variables” frame click “New” 
    * Define the new variable as: ORACLE_HOME = C:\path\to\oracle-instant-client
  * Add ”Oracle instant client \bin” folder path to the beginning of the environment variable: Path.
Path = %ORACLE_HOME%\bin;...
Note: if your ”Oracle instant client” installation does not have a bin folder then add the “instant client” folder to the Path. Like: 
Path = C:\path\to\oracle-instant-client;...
Or you can use the following formula (the two lines are equivalent):
Path = %ORACLE_HOME%;...

  * restart the computer
  * edit file ''niota/app/conf/connections.php'' according to the connection details

You can find an example definition in file ''niota/app/conf/connections_sample.php'' at the following section:

<code>
Oracle-tnsnames.ora => array(	...
</code>

Read notes and comments in file “connections_sample.php”. Comments provide additional information about the configuration. You can check the two sort of Oracle connection definitions in ‘Table 2’.

  * log in to niota and load a grid that uses the configured connection

^"Oracle"=> array ( ^"Oracle-tnsnames.ora"=>array (^
|''PHPGRID_DB_TYPE => DB_TYPE_OCI805''|''PHPGRID_DB_TYPE => DB_TYPE_OCI805''|
|''PHPGRID_DB_DISPLAY_NAME => "Oracle database"''|''PHPGRID_DB_DISPLAY_NAME => "Oracle connection use tnsnames.ora"''|
|''PHPGRID_DB_HOSTNAME => "<myhostname.com>:<port>"''|''PHPGRID_DB_HOSTNAME => false''|

((Table 2 – Oracle native connection vs. Oracle TNS-connection definitions.)) Only differences are listed

==== set up Teradata db connection ====
  * create the Teradata ODBC data source
 
Take care wether you create a 64 bit or 32 bit data source. 64 bit PHP does not work with 32 bit ODBC data source and vice versa.

  * edit file ''niota/app/conf/connections.php'' according to the connection details

The ''PHPGRID_DB_HOSTNAME'' key has set to the value of the ODBC data source name.

  * test the connection by loading a grid in niota


==== set up MySQL db connection ====

MySQL uses native connections. You do not need any driver.

  * edit file ''niota/app/conf/connections.php'' according to the connection details
  * test the connection by loading a grid in niota



==== define webservice connection ====
<code>
PHPGRID_WS_DISPLAY_NAME             => "<display name>",
PHPGRID_WS_ENDPOINT                 => "<webservice endpoint>",
PHPGRID_WSDL_LOCATION               => "<location of wsdl file",
PHPGRID_WS_USERNAME                 => "<user for connection>",
PHPGRID_WS_PASSWORD                 => "<password for connection>",
PHPGRID_CONN_TYPE                   => <connection type>,
PHPGRID_WS_PASSWORD_ENCRYPTED       => <password encryption true/false flag>
</code>
\\
\\
== PHPGRID_WS_DISPLAY_NAME ==
Text string that is used in the UI displayname of the connection.
\\
\\
== PHPGRID_WS_ENDPOINT ==
URL of the endpoint of the webservice. 
Syntax is like this:
<code>
http://<server>:<port>/<path>
</code>
\\
== PHPGRID_WSDL_LOCATION ==
URL and name of the WSDL-file of the webservice. The filename must include the file-extension 
Syntax is like this:
<code>
http://<server>:<port>/<path>/<wsdl-file>
</code>
\\
\\
== PHPGRID_WS_USERNAME ==
Name / ID of the user you want to connect to the webservice. Use a technical user or a service user here. Asure, that the password of user changes **not** regularly.
\\
\\
== PHPGRID_WS_PASSWORD ==
Password used to connect to the webservice. Use **encrypted** password if possible. If you use encrypted password, set ''PHPGRID_WS_PASSWORD_ENCRYPTED'' **true**.
\\
\\
== PHPGRID_WS_PASSWORD_ENCRYPTED ==
Flag, that indicates if your web service password is encrypted **(''true'')** or not **(''false'')**.
\\
\\
==== define LDAP connection ====
<code>
PHPGRID_DB_HOSTNAME                 => "<ldap-server>:<port>",
PHPGRID_DB_DN                       => "<ladp-servername in canonical notation>",
</code>
\\
== PHPGRID_DB_HOSTNAME ==
URL of the LDAP server and port.
Syntax is like this:
<code>
ldaps://<server>:<port>
//
e.g. in case of secure LDAP
ldaps://my.hostname.com:636
//
e.g. in case of plain LDAP
ldap://my.hostname.com:389
</code>
\\
== PHPGRID_DB_DN ==
Name of the LDAP server in canonical notation
Syntax is like this:
<code>
dc=<first part>,dc=<main part>,dc=<top level>
e.g.
dc=my,dc=hostname,dc=com>
</code>