# eXtremeSQL Remote SQL and Security Measures

> Remote SQL (RSQL) applications can run on different processors even with different architectures. For example validation tests have been run using database shards provided by servers running on the following systems:

> - Itanium processor (big-endian) x64
> - x86 processor (little-endian) x64
> - x86 processor (little-endian) x32

> Client applications can be run on x86, x32 or x64 systems. So neither the architecture nor the endianness affects the eXtremeSQL RSQL interface.

> ## Security measures

> eXtremeSQL Remote SQL applications can implement security of two types: password authentication or Secure Sockets Layer as explained in the following sections.

> ### Password authentication

> RSQL applications can password-protect a remote SQL connection by setting the `authenticationRequired` parameter in the SqlServer class. If the parameter is set to true, in order to connect to the server, a client application must pass the authentication credentials to the RemoteSqlEngine or DistributedSqlEngine through the `authenticate()` API:

>         RemoteSqlEngine::authenticate(char const* login, char const* password);

>         DistributedSqlEngine::authenticate(char const* login, char const* password);

> The authentication requires a table Users with two required string fields, `login` and `password` to keep user names and passwords:

>         create table Users (login string primary key, password string);
>         insert into Users values ('user1', 'mypass');
>         insert into Users values ('user2', 'hYfjdKK2');

> Remote client applications must provide the credentials to match those found in the table Users in order to connect to the database. Note the following important characteristics of this password-protection feature:

> > 1. The Users table can include fields in addition to `login` and `password`.

> > 2. The credentials are not encrypted by the database runtime. If desired, it is the application's responsibility to either encrypt or somehow protect the content of the `Users` table.

> > 3. The transmission of the credentials through the Remote Engine protocol is not encrypted either. However it is possible to use the Remote server authentication in conjunction with SSL encryption.

> > 4. If SSL encryption (without authentication) is enabled when using xSQL, the password transmission would be protected.

> ### RemoteSQL SSL Client Applications

> To start RemoteSQL client and server applications with SSL security it is necessary to set SSL parameters that are used to establish the network connection. Please refer to the Network Communications page for details on implementing OpenSSL security.

> ## Setting Remote SQL timeouts

> For RSQL applications built with eXtremeSQL versions prior to release 7.0, an RSQL client may appear to hang while waiting to establish a server connection or while waiting for the results of a query. This is because the RemoteSqlEngine implementation uses TCP `connect()` and `read()` functions which rely on the TCP timeouts to notify the client of a network error. (Note that a connection timeout error does little to tell you what went wrong or why the error happened; it just identifies that the error occurred. Timeout errors can happen for a number of reasons. The server, the requesting device, the network hardware or an internet connection can be at fault.)

> The problem is the default TCP timeout values are quite large. Moreover changing these values requires administrative privileges and effect the entire operating environment. On Linux the TCP timeout values are kernel parameters and on Windows the timeouts are configured through the registry.

> To avoid the hassle of modifying system-wide parameters, the implementation of the RemoteSQL engine has been extended to allow setting the connection and read timeouts through the `RemoteSqlEngine::open()` and the `DistributedSQlEngine::open()` methods through two additional parameters: `connect_timeout` and `read_timeout`. Internally the timeout values are passed to the socket API. However, an application should not execute a query of unpredictable size using RSQL, even via xSQL. One should know the approximate query length in advance and make any necessary adjustment to the timeouts or modify the query, as the server will remain busy performing the query.

> The old-style API can still be used. If not explicitly specified, the timeout parameters default values are 2000 (i.e. 2000 milliseconds or 2 seconds) for the `connect()` and 1200000 ( 20 minutes) for `read()` operations.

> The following code snippet demonstrates a call to the new `open()` API from a client application that connects to the above server:

>         RemoteSqlEngine engine;

>         engine.open(SERVER_HOST, SERVER_PORT, connect_attempts, local_domain,
>                 0, connect_timeout, read_timeout);

> or

>         DistributedSqlEngine engine;

>         engine.open(nodes, n_nodes, n_replicas, replication_type, connect_attempts,
>                 &bad_node, local_domain, 0, connect_timeout, read_timeout);

> From Java applications, the new `open()` API call would be as follows:

>         SqlConnection con = new SqlRemoteConnection(host, port, maxAttempts, bufSize,
>                                 connectTimeout, readTimeout);

> or

>             SqlConnection con = new SqlRemoteConnection(nodes, nReplicas,
>                         SqlRemoteConnection.ReplicationType.SQLReplication,
>                         maxAttempts, bufSize, connectTimeout, readTimeout);

> From Python applications, the new `open()` API call would be as follows:

>         Remote engine: conn = exdb.connect('localhost', 5001,
>                         connectTimeout=3000L, readTimeout=1500L)

> or

>         Distributed engine: conn = exdb.connect(nodes=('localhost:5001', 'localhost:5002'),
>                             connectTimeout=3000L, readTimeout=1500L)

> When using xSQL to connect to a remote database, the timeout values are set in the configuration file:

>         sql_connect_timeout : 2000,
>         sql_read_timeout : 120000,

> Also, the eXtremeSQL JDBC driver allows an application to adjust the default timeout value of 90 seconds by specifying a timeout value (in seconds) in the connection string. For example, the code snippet below creates a connection to the `extremedb` JDBC driver using TCP address `localhost`, port 5001 and the timeout for read operations of 10 seconds:

>         String url = "jdbc:extremedb:localhost:5001:10";
>         String user = null;
>         String password = null;
>         Connection con = DriverManager.getConnection(url, user, password);

> ##

> ## Transmission buffer reallocation

> If the buffer size (the default size is 64 * 1024 bytes) passed into the SqlServer instance is not large enough for the record (data) being transmitted, the runtime will reallocate (increase) the size of the buffer in order to process the data transmission without raising an out-of-memory exception.

> ## Shutting down Remote SQL servers

> In xSQL, the `shutdown` command can be executed to shutdown (stop) a remote server. The command is implemented via the SQL UDF `xsql_shutdown()`. When the shutdown is issued by a distributed SQL client (xSQL acting as client with multiple servers on different network nodes), all servers (shards) that the client is connected to are shut down.

> Note the following limitations:

> > 1.The UDF doesn't work for servers running on Windows platforms (yet the client can run on Windows and shut down servers running on *nix systems).

> > 2.An xSQL server can be stopped by the `shutdown` command only if it is running in a non-interactive mode.

> For xSQL there is an 'alias' on the server: the two commands `exit` and `shutdown` are equivalent. Any eXtremeSQL client application connected to a RemoteSqlEngine or DistributedSqlEngine can shut down the remote server(s) by executing the UDF. For example:

>         engine.executeStatement("select xsql_shutdown();");

> (Note however that if this statement is executed by DistributedSqlEngine and `nReplicas > 1`, then only one (random) replica of each shard receives the statement and shuts down.)