Configuration of 4D SQL Server

The SQL server of 4D 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 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 TLS option on the target side of the connection (4D Server) on the "SQL" page of the Database Settings. 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.

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 Database Settings. To do this, display the SQL 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.

Hier legen Sie für den integrierten SQL Server die Standardeinstellungen beim Starten und die Zugriffsrechte fest, sowie die SQL Engine Optionen:

• Über die Option Automatisch starten können Sie den SQL Server beim Starten des Programms mitstarten.
• TCP Port: Der 4D SQL Server antwortet standardmäßig auf dem TCP Port 1919. Wird er bereits von einem anderen Dienst genutzt oder benötigen Ihre Verbindungsparameter eine andere Einstellung, können Sie den von 4D SQL Server verwendeten TCP Port ändern.
  Hinweis: Übergeben Sie 0, verwendet 4D die standardmäßige TCP Port Nummer, also 1919.
• IP Adresse: Sie können die IP-Adresse des Rechners setzen, auf dem der SQL Server SQL Anfragen bearbeiten soll. Der Server antwortet standardmäßig für alle IP Adressen (Option Alle).
  Die DropDown-Liste “IP Adresse” enthält automatisch alle auf dem Rechner vorhandenen IP Adressen. Wählen Sie eine bestimmte Adresse, beantwortet der Server nur Anfragen, die an diese Adresse gesendet werden.
  Das ist für 4D Anwendungen sinnvoll, die auf Rechnern mit mehreren TCP/IP Adressen gehostet werden.
  Hinweis: Auf dem Client müssen IP Adresse und TCP Port des SQL Server, an den sich das Programm anmeldet, in der Definition der ODBC Datenquelle korrekt definiert werden.
• SSL aktivieren: Diese Option gibt an, ob der SQL Server das SSL Protokoll zum Bearbeiten von SQL Verbindungen aktivieren soll. Ist dieses Protokoll aktiviert, müssen Sie an das Ende der IP Adresse des SQL Server die Kombination ":ssl" anhängen, wenn Sie eine Verbindung mit dem Befehl SQL LOGIN öffnen. Standardmäßig verwendet der SL Server interne Dateien für SSL Schlüssel und Zertifikat. Sie können aber auch eigene Elemente verwenden. Dazu kopieren Sie ihre eigenen Dateien key.pem und cert.pem  an folgende Stelle: MeineDatenbank/Preferences/SQL („Meine Datenbank“ ist der Ordner bzw. das Paket der Datenbank).
• Flash Player Anfragen erlauben: Damit können Sie festlegen, dass der 4D SQL Server Flash Player Anfragen unterstützt. Diese Operation basiert auf der Datei "socketpolicy.xml," die im Ordner Preferences der Datenbank vorhanden sein muss (Preferences/SQL/Flash/). Flash Player benötigt diese Datei, um cross-domain Verbindungen bzw. Verbindungen über sockets von Flex (Web 2.0) Anwendungen zuzulassen.
  In bisherigen 4D Versionen musste diese Datei manuell hinzugefügt werden. Ab jetzt wird die Aktivierung über die Option Anfragen erlauben automatisch ausgeführt. Aktivieren Sie diese Option, werden Flash Player Anfragen angenommen und - falls erforderlich - wird eine generische Datei "socketpolicy.xml" für die Datenbank angelegt.
  Deaktivieren Sie diese Option, wird die Datei "socketpolicy.xml" deaktiviert (umbenannt). Alle Flash Player Anfragen, die der SQL Server dann empfängt, werden abgewiesen. Beim Öffnen der Datenbank ist die Option aktiv oder inaktiv, je nachdem, ob im Ordner Preferences der Datenbank eine aktive Datei "socketpolicy.xml" liegt.
  Hinweis: Über den 4D Befehl SQL SET OPTION können Sie die vom SQL Server verwendete Verschlüsselung zum Bearbeiten externer Anfragen setzen.

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 page of the Database Settings.

Note: You can also use the Datenbankmethode On SQL Authentication to control in a custom way any external access to the 4D internal SQL engine.

The parameters set in this dialog box are applied to the default schema. The control of external access to the database is based on the concept of SQL schemas (see the 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: Each type of access is set independently from the others. More specifically, if you only assign Read Only type access to one group this will not have any effect since this group as well as all the others will continue to benefit from Read/Write access (assigned to <Everybody> by default). In order to set a Read Only type access, you also need to configure the Read/Write access.

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 section.