Configuration of 4D SQL Server

version 11.3 (Modified)

4D v11 includes a powerful SQL server that allows external access to data stored in the 4D database. For third-party applications and 4D applications, this access is carried out using a 4D ODBC driver. It is also possible to make direct connections between a 4D client and 4D Server application. All connections are made using the TCP/IP protocol.

The SQL server of a 4D application can be stopped or started at any time. Moreover, for performance and security reasons, you can specify the TCP port as well as the listening IP address, and restrict access possibilities to the 4D database.

External access to the 4D SQL server can be made either via ODBC (all configurations), or directly (4D client application connected to 4D Server). This is summarized in the following diagram:

In blue: connections via ODBC

In red: direct connections

At the query level, opening a direct external connection or a connection via ODBC is carried out using the SQL LOGIN command. For more information, please refer to the description of this command.

• Connections via ODBC: 4D provides an ODBC driver that allows any third-party application (Excel® type spreadsheet, other DBMS, and so on) or another 4D application to connection to the SQL server of 4D. The 4D ODBC driver must be installed on the machine of the SQL Client part. The installation and configuration of the 4D ODBC driver is detailed in a separate manual.

• Direct connections: Only a 4D Server v11 SQL application can reply to direct SQL queries coming from other 4D applications. Similarly, only 4D applications of the "Professional" product line can open a direct connection to another 4D application. During a direct connection, data exchange is automatically carried out in synchronous mode, which eliminates questions related to synchronization and data integrity. Only one connection is authorized per process. If you want to establish several simultaneous connections, you must create as many processes as needed. Direct connections can be secured by selecting the Enable SSL option on the target side of the connection (4D Server) on the "SQL" page of the Preferences. Direct connections are only authorized by 4D Server if the SQL server is started. The main advantage of direct connections is that data exchanges are accelerated.

In 4D v11, the SQL server can be started and stopped in three ways:

• Manually, using the Start SQL Server/Stop SQL Server commands in the Run menu of the 4D application:

Note: With 4D Server, this command can be accessed as a button on the SQL Server Page.

When the server is launched, this menu item changes to Stop SQL Server.

• Automatically on startup of the application, via the Preferences. To do this, display the SQL/Configuration page and check the Launch SQL Server at Startup option:

• By programming, using the START SQL SERVER and STOP SQL SERVER commands ("SQL" theme).

When the SQL server is stopped (or when it has not been started), 4D will not respond to any external SQL queries.

Note: Stopping the SQL server does not affect the internal functioning of the 4D SQL engine. The SQL engine is always available for internal queries.

It is possible to configure the publishing parameters for the SQL server integrated into 4D. These parameters are found on the SQL/Configuration page of the database Preferences:

• The Launch SQL Server at Startup option can be used to start the SQL server on application startup.

• TCP Port: By default, the 4D SQL server responds on the TCP port 1919. If this port is already being used by another service, or if your connection parameters require another configuration, you can change the TCP port used by the 4D SQL server.

Note: If you pass 0, 4D will use the default TCP port number, i.e. 1919.

• IP Address: You can set the IP address of the machine on which the SQL server must process SQL queries. By default, the server will respond to all the IP addresses (All option).

The "IP Address" drop-down list automatically contains all the IP addresses present on the machine. When you select a particular address, the server will only respond to queries sent to this address.

This is intended for 4D applications hosted on machines having sev-eral TCP/IP addresses.

Note: On the client side, the IP address and the TCP port of the SQL server to which the application connects must be correctly configured in the ODBC data source definition.

• Enable SSL: This option indicates whether the SQL server must enable the SSL protocol for processing SQL connections. When you check this option, you must copy the 4D key.pem and cert.pem files to the following location: MyDatabase/Preferences/SQL (where "MyDatabase" represents the database folder/package).

• Allow Flash Player requests: This option can be used to enable the mechanism for supporting Flash Player requests by the 4D SQL server. This mechanism is based on the presence of a file, named "socketpolicy.xml," in the preferences folder of the database (Preferences/SQL/Flash/). This file is required by Flash Player in order to allow cross-domain connections or connections by sockets of Flex (Web 2.0) applications.

In the previous version of 4D, this file had to be added manually. From now on, the activation is carried out using the Allow Flash Player requests option: When you check this option, Flash Player requests are accepted and a generic "socketpolicy.xml" file is created for the database if necessary.

When you deselect this option, the "socketpolicy.xml" file is disabled (renamed). Any Flash Player queries received subsequently by the SQL server are then rejected.

On opening of the database, the option is checked or not checked depending on the presence of an active "socketpolicy.xml" file in the preferences folder of the database.

Note: It is possible to set the encoding used by the SQL server for processing external requests using the 4D SQL SET OPTION command.

For security reasons, it is possible to limit actions that external queries sent to the SQL server can perform in the 4D database.

This can be done at two levels:

• At the level of the type of action allowed,

• At the level of the user carrying out the query.

These settings can be made on the SQL/Configuration page of the database Preferences:

The parameters set in this dialog box are applied to the default schema. Starting with version 11.3 of 4D, control of external access to the database is based on the concept of SQL schemas (see the Principles for integrating 4D and the 4D SQL engine section). If you do not create custom schemas, the default schema will include all the tables of the database. If you create other schemas with specific access rights and associate them with tables, the default schema will only include the tables that are not included in custom schemas.

You can configure three separate types of access to the default schema via the SQL server:

• "Read Only (Data)": Unlimited access to read all the data of the database tables but no adding, modifying or removing of records, nor any modification to the structure of the database is allowed.

• "Read/Write (Data)": Read and write (add, modify and delete) access to all the data of the database tables, but no modification of the database structure is allowed.

• "Full (Data and Design)": Read and write (add, modify and delete) access to all the data of the database tables, as well as modification of the database structure (tables, fields, relations, etc.) is allowed.

You can designate a set of users for each type of access. There are three options available for this purpose:

• <Nobody>: If you select this option, the type of access concerned will be refused for any queries, regardless of their origin. This parameter can be used even when the 4D password access manage-ment system is not activated.

• <Everybody>: If you select this option, the type of access concerned will be allowed for all queries (no limit is applied).

• Group of users: This option lets you designate a group of users as exclusively authorized to carry out the type of access concerned. This option requires that 4D passwords be activated. The user at the origin of the queries provides their name and password when con-necting to the SQL server.

WARNING: This mechanism is based on 4D passwords. In order for the SQL server access control to come into effect, the 4D password system must be acti-vated (a password must be assigned to the Designer).

Note: An additional security option can be set at the level of each 4D project method. For more information, please refer to the "Available through SQL option" paragraph in the Principles for integrating 4D and the 4D SQL engine section.