The Database Properties page is used to specify the type of the 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 with database connection settings. The checkbox is selected by default, database connection is set up manually:
DBMS Type. select in the drop-down list DBMS type.
Server. Enter server name, which stores the connected database, or select repository global variable. The field is displayed if the DBMS type is selected: Oracle, Microsoft SQL Server 2008, 2012\2014\2016, ODBC, OLE DB (ODBC HIVE), OLE DB (ODBC), DB2, Teradata, Greenplum/Pivotal HD Hawq, PostgreSQL, Vertica.
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), DB2, Teradata. The Database box is displayed if the DBMS type is selected: Microsoft SQL Server 2008, 2012\2014\2016, ODBC, Greenplum/Pivotal HD Hawq, PostgreSQL, Vertica.
Schema. Enter identifier of a custom database schema if is was used during DBMS back end preparation. The box is displayed if the DBMS type is selected: Microsoft SQL Server 2008, 2012\2014\2016, ODBC, Greenplum/Pivotal HD Hawq, PostgreSQL, Vertica. The dbo schema is used by default for Microsoft SQL Server 2008, 2012\2014\2016, ODBC; the public schema is used by default for Greenplum/Pivotal HD Hawq, PostgreSQL, Vertica.
File Name. Select the file with database connection settings using the Select File if the SQLite DBMS type is selected. A standard file selection dialog box opens.
NOTE. The parameter is available only in the desktop application.
DNS 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. 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 in the web application. If database connection is set up in the desktop application, skip this step.
Fill in the User Name and Password boxes.
To set up advanced database connection parameters:
In the desktop application click the Advanced Settings button. The Advanced Settings dialog box opens.
In the web application use the Advanced Settings drop-down set of parameters.
Authentication Type. 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 domain user name and password (the current OS 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 2008, 2012\2014\2016, ODBC, Greenplum/Pivotal HD Hawq, PostgreSQL.
Manage Rights on DBMS Objects. 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.
Use Application Role. 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.
Automatic Connection. Select the checkbox to automatically connect to database and determine one of the connection options:
Use Login Credentials. Select the checkbox to connect to the DBMS server connection with the same user name and password 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. 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) on 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 on 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.
The parameter is available for all DBMS types, except for ClickHouse (ODBC), Generic ODBC.
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 connectionstring. 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 boxed, otherwise the attempt to create a table will open an authorization dialog box.
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:
In the web application click the Save button.
In the desktop application:
Click the Ready button.
Click the Finish button on the Final Step page:
See also: