The Database Properties page is used to specify the type of database management system (DBMS) installed on the server and additional connection parameters:
Set the parameters:
Override database connection settings
Deselect the checkbox to use the specified repository connection settings if they match the database connection settings. The checkbox is selected by default, database connection is set up manually:
DBMS Type. In the drop-down list select the DBMS type. In the web application one can connect to ClickHouse DBMS and create the following objects based on the DBMS: table, query, external table, view, DBMS command, and procedure. The created tables can be used as data sources only for table dictionaries.
Server. Enter IP address or DNS name that was used to register the server with the connected database or select repository global variable. The field is displayed if the DBMS type is selected: Oracle, Microsoft SQL Server, Microsoft SQL Server (ODBC), OLE DB (ODBC HIVE), OLE DB (ODBC), Greenplum/Pivotal HD Hawq, PostgreSQL, ClickHouse.
If DBMS port differs from default port, the parameter value is set in the format:
For PostgreSQL: <IP address or DNS server name>:<port number>.
For Microsoft SQL Server: <IP address or DNS server name>,<port number>.
For Oracle: <IP address or DNS server name>:<port number>/<database service name>. Server port and database service name are determined in the tnsnames.ora file.
Schema/Database. Enter database identifier or select repository global variable. Database identifier matches with repository identifier. The Schema box is displayed if the DBMS type is selected: Oracle, OLE DB (ODBC HIVE), OLE DB (ODBC). The Database box is displayed if the DBMS type is selected: Microsoft SQL Server, Microsoft SQL Server (ODBC), Greenplum/Pivotal HD Hawq, PostgreSQL, ClickHouse.
Schema. Enter identifier of a custom schema if it was created during DBMS back end preparation. The box is displayed if the DBMS type is selected: Microsoft SQL Server, Microsoft SQL Server (ODBC), Greenplum/Pivotal HD Hawq, PostgreSQL. The dbo schema is used by default for Microsoft SQL Server, Microsoft SQL Server (ODBC); the public schema is used by default for Greenplum/Pivotal HD Hawq, PostgreSQL.
DSN or Connection String. Enter data source name or database connection parameters as a string. The box is displayed if the ClickHouse (ODBC) or Generic ODBC DBMS type is selected. One should install and set up appropriate ODBC driver for work. If custom schemas are used on DBMS server, add the Schema keyword to the connection string and name of the required schema as a value. When connecting to ClickHouse, it is recommended to specify the STRINGMAXLENGTH parameter in connection string, which limits the maximum size of string values, for example, STRINGMAXLENGTH=8000. To execute authentication in database, user credentials can be sent in open or closed format:
If user credentials are to be sent in open format, specify user name and password in the connection string.
If user credentials are to be sent in closed format, execute the following operations:
Specify the %USR% and %PWD% masks as values for user name and password in the connection string.
Select the Substitute Credentials by Mask checkbox.
Fill in the User Name and Password boxes.
To set up additional parameters of connection to the database, use the Advanced Settings drop-down list.
Select in the drop-down list one of the methods for user authentication on database server. The parameter is available for all DBMS types, except for ClickHouse (ODBC), Generic ODBC:
Password. The user is authenticated by means of specifying user name and password explicitly. In Linux OS authentication type is available if the DBMS type is selected: Oracle, Microsoft SQL Server (ODBC), PostgreSQL.
Integrated Domain. The user is authenticated by means of specifying domain user name and password explicitly (of the current operating system account). In Linux OS authentication type is available if the PostgreSQL DBMS type is selected.
Domain. The user is authenticated by means of specifying domain, user name and password explicitly. In Linux OS authentication type is available if the DBMS type is selected: Oracle, PostgreSQL. In Windows OS, authentication type is available if the DBMS type is selected: Oracle, Microsoft SQL Server, Microsoft SQL Server (ODBC), Greenplum/Pivotal HD Hawq, PostgreSQL.
Select the checkbox to grant access permissions for database objects both at the repository and DBMS levels. The checkbox is deselected by default, permissions are changed only at the repository level. The checkbox is selected automatically if the Use Login Credentials checkbox is selected. The parameter is available for all DBMS types, except for ClickHouse (ODBC), Generic ODBC.
Use Unicode encoding for data representation
Select the checkbox to create text fields with the NVARCHAR type in tables. This type is used to work with Unicode characters. The checkbox is deselected by default, and the VARCHAR type is used.
Select the checkbox to get access to creating tables on the DBMS level only from Foresight Analytics Platform. An application role is a role on the Microsoft SQL Server DBMS level, which has permissions to create tables in the specified schema and grant permissions for these tables. For example, if the user creates new tables but he does not have permissions to create tables in the selected database and grant privileges for these tables, the Database Authorization dialog box opens. In this case application role enables the user to connect to the database using role credentials without opening the authorization dialog box. Then one can execute operations with tables within this connection. The checkbox is deselected by default. The parameter is available if the Microsoft SQL Server DBMS type is selected. The Automatic Connection and Use Login Credentials checkboxes do not affect using application role.
The parameter is available for all DBMS types, except for ClickHouse (ODBC), Generic ODBC.
Select the checkbox to automatically connect to database during repository connection and determine one of the connection options:
Use Login Credentials. Select the checkbox to connect to the DBMS server with the same user name and password that were specified during repository connection setup. At the same time, the user gets necessary permissions for all available tables registered in the metadata schema on the DBMS server. The checkbox is deselected by default, connection is established when addressing the database or objects that use the database. The Manage Rights on DBMS Objects checkbox is also selected together with this checkbox. This enables the user to automatically grant permissions for relational objects of repository (tables, queries, and so on) at DBMS level.
NOTE. If the Use Login Credentials checkbox is selected for the database object, take into account the length of the database schema name when creating DBMS objects because using the database name for DBMS objects as a prefix results in exceeding the limit of 30 characters per name.
User Name. Enter user name to connect to database server. If the Domain authentication type is selected, enter domain and user name in the format: <Domain\User Name>.
Password. Enter user password.
NOTE. The User Name and Password boxes are available if the Use Login Credentials checkbox is deselected.
When setting up connection to ClickHouse (ODBC) and Generic ODBC DBMS, only the User Name and Password boxes are available to use the %USR% and %PWD% masks in the connection string. In the web application availability of the boxes depends on the state of the Substitute Credentials by Mask checkbox.
The Database Authorization dialog box opens on connecting to database:
If the Automatic Connection checkbox is deselected.
If errors are encountered during connection.
On creating, deleting or changing objects' structure, if user credentials specified at the system login are used to connect to the server and the user logged into the system is not the schema owner.
If the account data used for connection does not belong to the schema owner.
If the Automatic Connection checkbox is selected, the connection is established automatically when objects open. If automatic connection is used, and the Use Login Credentials checkbox is selected, the user requires the Read and Write Permission for All Objects privilege to create relational objects, otherwise the attempt to create a table will return an access error. If the Use Login Credentials checkbox is deselected, one should enter credentials of the ADMIN schema owner in the User Name and Password boxes, otherwise on an attempt to create a table the authorization dialog box opens.
Connection errors occur when:
Invalid user name or password are specified.
The current user has insufficient permissions to connect to the database.
Invalid schema and/or server are specified.
To finish the wizard, click the Save button.
See also:
Connecting to Relational Databases