ToC path:

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