Quick Start

In order to run, the SQL Relay server requires a configuration file. However, When SQL Relay is first installed, no such configuration file exists. You must create one in the appropriate location. This location depends on the platform and on how you installed SQL Relay.

• Unix and Linux
• Built from source - /usr/local/firstworks/etc/sqlrelay.conf (unless you specified a non-standard --prefix or --sysconfdir during the build)
• RPM package - /etc/sqlrelay.conf
• FreeBSD package - /usr/local/etc/sqlrelay.conf
• NetBSD package - /usr/pkg/etc/sqlrelay.conf
• OpenBSD package - /usr/local/etc/sqlrelay.conf
• Windows
• Built from source - C:\Program Files\Firstworks\etc\sqlrelay.conf
• Windows Installer package - C:\Program Files\Firstworks\etc\sqlrelay.conf (unless you specified a non-standard installation folder)

The most minimal sqlrelay.conf would be something like:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

This configuration defines an instance of SQL Relay named example that opens and maintains a pool of 5 persistent connections to the ora1 instance of an Oracle database using scott/tiger credentials.

The instance can be started using:

sqlr-start -id example

( NOTE: When installed from packages, SQL Relay may have to be started and stopped as root.)

and can be accessed from the local machine using:

sqlrsh -host localhost -user scott -password tiger

( NOTE: By default, the user and password used to access SQL Relay are the same as the user and password that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag. )

By default, SQL Relay listens on all available network interfaces, and can be accessed remotely by hostname. For example, if the server running SQL Relay is named sqlrserver then it can be accessed from another system using:

sqlrsh -host sqlrserver -user scott -password tiger

The instance can be stopped using:

sqlr-stop -id example

All running instances of SQL Relay can be stopped using:

sqlr-stop

(without the -id argument)

Basic Configuration

The example above may be sufficient for many use cases, but SQL Relay has many configuration options. For a production deployment, you will alomst certainly want to configure it further.

Modules

The SQL Relay server is highly modular. Configuring it largely consists of telling it which modules to load, and which options to use for each module. There are database connection modules, protocol modules, authentication modules, modules related to security, data translation modules, logging and notification modules, and others.

The module that you'll most likely need to configure first is a database connection module.

Database Configuration

By default, SQL Relay assumes that it's connecting to an Oracle database, but database connection modules for many other databases are provided.

The dbase attribute of the instance tag specifies which database connection module to load and the connect string options (options in the string attribute of the connection tag) specify which parameters to use when connecting to the database. Connect string options are different for each database.

Examples follow.

Oracle

In this example, SQL Relay is configured to connect to an Oracle database. The dbase attribute defaults to "oracle", so the dbase attribute may be omitted when connecting to an Oracle database. It is just set here for illustrative purposes.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_home=/u01/app/oracle/product/12.1.0;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

The oracle_home option refers to the base directory of an Oracle instance. On Windows platforms, it should be specified as a Windows-style path with doubled backslashes. For example:

C:\\Oracle\\ora12.1

The oracle_home option is often unnecessary though, as the $ORACLE_HOME environment variable is usually set system-wide.

The oracle_sid option refers to an entry in the tnsnames.ora file (usually $ORACLE_HOME/network/admin/tnsnames.ora) similar to:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = examplehost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora1)
    )
  )

( NOTE: The tnsnames.ora file must be readable by the user that the instance of SQL Relay runs as.)

If you are using Oracle Instant Client, then it's likely that you don't have an $ORACLE_HOME or a tnsnames.ora file. In that case, the oracle_sid can be set directly to a tnsnames-style expression and the oracle_home option can be omitted.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = examplehost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora1)))"/>
		</connections>
	</instance>
</instances>

Microsoft SQL Server (via ODBC)

Microsoft SQL Server (via FreeTDS)

SAP/Sybase (via the native SAP/Sybase library)

In this example, SQL Relay is configured to connect to a SAP/Sybase database via the native SAP/Sybase library.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="sap">
		<connections>
                        <connection string="sybase=/opt/sap;lang=en_US;server=SAPSERVER;user=sapuser;password=sappassword;db=sapdb"/>
		</connections>
	</instance>
</instances>

The sybase option refers to the base directory of the SAP/Sybase software installation. On Windows platforms, it should be specified as a Windows-style path with doubled backslashes. For example:

C:\\SAP

The sybase option is often unnecessary though, as the $SYBASE environment variable is usually set system-wide.

On Linux/Unix platforms, the server option refers to an entry in the interfaces (usually $SYBASE/interfaces) file which identifies the database server, similar to:

SAPSERVER
	master tcp ether examplehost 5000
	query tcp ether examplehost 5000

( NOTE: The interfaces file must be readable by the user that the instance of SQL Relay runs as.)

On Windows platforms, the server option refers to a similar entry created in an opaque location with the Open Client Directory Services Editor (dsedit).

SAP/Sybase (via FreeTDS)

The native SAP/Sybase library is available on Windows and on some Linux/Unix platforms.

IBM DB2

In this example, SQL Relay is configured to connect to an IBM DB2 database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="db2">
		<connections>
                        <connection string="db=exampledb;user=db2inst1;password=db2inst1pass;connecttimeout=0"/>
		</connections>
	</instance>
</instances>

Informix

In this example, SQL Relay is configured to connect to an Informix database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="informix">
		<connections>
                        <connection string="informixdir=/opt/informix;servername=ol_informix1210;db=informixdb;user=informixuser;password=informixpassword"/>
		</connections>
	</instance>
</instances>

The informixdir option refers to the base directory of the Informix software installation. On Windows platforms, it should be specified as a Windows-style pathwith doubled backslashes. For example:

C:\\Program Files\\IBM Informix Software Bundle

The informixdir option is often unnecessary, as the $INFORMIXDIR environment variable is usually set system-wide.

On Linux and Unix platforms, the servername option refers to an entry in the sqlhosts file (usually $INFORMIXDIR/etc/sqlhosts) which identifies the database server, similar to:

ol_informix1210 onsoctcp 192.168.123.59 ol_informix1210

The second ol_informix1210 in the sqlhosts file refers to an entry in /etc/services which identifies the port that the server is listening on, similar to:

ol_informix1210 29756/tcp

( NOTE: The sqlhosts and /etc/services files must be readable by the user that the instance of SQL Relay runs as.)

On Windows platforms, the servername option refers to a similar entry created in an opaque location with the Setnet32 program.

MySQL/MariaDB

In this example, SQL Relay is configured to connect to a MySQL/MariaDB database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

The host and db options indicate that SQL Relay should connect to the database exampledb on the host examplehost.

PostgreSQL

In this example, SQL Relay is configured to connect to a PostgreSQL database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<connections>
                        <connection string="user=postgresqluser;password=postgresqlpassword;host=postgresqlhost;db=postgresqldb"/>
		</connections>
	</instance>
</instances>

The host and db options indicate that SQL Relay should connect to the database exampledb on the host examplehost.

Firebird

In this example, SQL Relay is configured to connect to a Firebird database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="firebird">
		<connections>
                        <connection string="user=firebirduser;password=firebirdpassword;db=firebirdhost:/opt/firebird/firebirddb.gdb"/>
		</connections>
	</instance>
</instances>

The db option indicates that SQL Relay should connect to the database located at /opt/firebird/exampledb.gdb on the host examplehost.

Note that if the database is located on a Windows host, then the path segment of the db option should be specified as a Windows-style path with doubled backslashes. For example:

examplehost:C:\\Program Files\\Firebird\\Firebird_3_0\\exampledb.gdb

SQLite

In this example, SQL Relay is configured to connect to a SQLite database.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="sqlite">
		<connections>
                        <connection string="db=/var/sqlite/sqlitedb;user=sqliteuser;password=sqlitepassword"/>
		</connections>
	</instance>
</instances>

The db option indicates that SQL Relay should connect to the database /var/sqlite/exampledb.

Note that the database file (exampledb in this case) and the directory that its located in (/var/sqlite in this case) must both be readable and writable by the user that the instance of SQL Relay runs as.

Also note the user and password parameters in the connection string. SQLite doesn't require these for SQL Relay to access the database, but they are included to define a user and password for accessing SQL Relay itself.

SQLite also supports a high-performance in-memory mode where tables are maintained in memory and nothing is written to permanent storage. To use this mode with SQL Relay, set the db option to :memory:.

As each running instance of sqlr-connection will have its own separate in-memory database, you almost certainly want to limit the number of connections to 1.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="sqlite" connections="1">
		<connections>
                        <connection string="db=:memory:;user=sqliteuser;password=sqlitepassword"/>
		</connections>
	</instance>
</instances>

Note that the entire in-memory database will be lost when SQL Relay is stopped. There is no way to preserve it. Such is the nature of pure in-memory databases.

Teradata (via ODBC)

Teradata ODBC drivers are available for many platforms, including Windows and Linux.

In this example, SQL Relay is configured to connect to a Teradata database via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc">
		<connections>
			<connection string="dsn=teradata;user=teradatauser;password=teradatapassword"/>
		</connections>
	</instance>
<instances>

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[teradata]
Description=Teradata Database ODBC Driver 16.20
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
DBCName=teradatahost
UID=testuser
PWD=testpassword
AccountString=
CharacterSet=ASCII
DatasourceDNSEntries=
DateTimeFormat=IAA
DefaultDatabase=
DontUseHelpDatabase=0
DontUseTitles=1
EnableExtendedStmtInfo=1
EnableReadAhead=1
IgnoreODBCSearchPattern=0
LogErrorEvents=0
LoginTimeout=20
MaxRespSize=65536
MaxSingleLOBBytes=0
MaxTotalLOBBytesPerRow=0
MechanismName=
NoScan=0
PrintOption=N
retryOnEINTR=1
ReturnGeneratedKeys=N
SessionMode=System Default
SplOption=Y
TABLEQUALIFIER=0
TCPNoDelay=1
TdmstPortNumber=1025
UPTMode=Not set
USE2XAPPCUSTOMCATALOGMODE=0
UseDataEncryption=0
UseDateDataForTimeStampParams=0

( NOTE: The odbc.ini file must be readable by the user that the instance of SQL Relay runs as.)

Exasol (via ODBC)

Exasol ODBC drivers are available for Windows and Linux.

In this example, SQL Relay is configured to connect to an Exasol database via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc">
		<connections>
			<connection string="dsn=exasolution-uo2214lv2_64;user=sys;password=exasol"/>
		</connections>
	</instance>
<instances>

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[exasolution-uo2214lv2_64]
DRIVER=/home/dmuse/software/EXASOL_ODBC-6.0.11/lib/linux/x86_64/libexaodbc-uo2214lv2.so
EXAHOST=192.168.123.12:8563
EXAUID=sys
EXAPWD=exasol

( NOTE: The odbc.ini file must be readable by the user that the instance of SQL Relay runs as.)

Apache Hive (via ODBC)

Apache Hive is a data warehouse built on top of Apache Hadoop. Cloudera provides a Hive ODBC driver for Windows and Linux platforms which implements a SQL interface to Hive. This driver has some quirks.

The most significant quirk is that version 2.6.4.1004 (and probably other versions) for Linux ship with their own copy of libssl/libcrypto, version 1.1. These tend to conflict with the versions of libssl/libcrypto that SQL Relay itself is linked against, causing the driver to malfunction. The only known solution is to build a copy of Rudiments and SQL Relay from source, configuring rudiments with the --disable-ssl --disable-gss --disable-libcurl options, and use this copy to access Hive. Unfortunately, this has a side effect of disabling all SSL/TLS and Kerberos support in SQL Relay, as well as disabling the ability to load config files over https and ssh. Perhaps a future version of the Cloudera Hive ODBC driver for Linux will link against the system versions of libssl/libcrypto and eliminate this quirk.

In this example, SQL Relay is configured to connect to a Apache Hive data warehouse via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc">
		<session>
			<start>
				<runquery>
				use hivedb
				</runquery>
				<runquery>
				select 1
				</runquery>
			</start>
		</session>
		<connections>
			<connection string="dsn=hive;user=hiveuser;password=hivepassword;db=hivedb;overrideschema=hivedb"/>
		</connections>
	</instance>
<instances>

The contents of the session tag work around another quirk. "hivedb" is specified in the db option of the string attribute of the connection tag, but this doesn't appear to be adequate to actually put you in that schema. Running the "use hivedb" query at the beginning of the session helps. But, it appears that the schema isn't actually selected until the first query is run. So, the "select 1" query immediately following the use query solves this.

The overrideschema option in the string attribute of the connection tag works around yet another quirk. The database tends to report the current schema as something other than "hivedb", but when running "show tables" or "describe" queries, the database wants "hivedb" to be passed in as the schema for the table names.

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[hive]
Driver=/opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so
HiveServerType=2
Host=hiveserver
Port=10000

( NOTE: The odbc.ini file must be readable by the user that the instance of SQL Relay runs as.)

Apache Impala (via ODBC)

Apache Impala is a query engine for Apache Hadoop. Cloudera provides an Impala ODBC driver for Windows and Linux platforms. This driver has some quirks.

The most significant quirk is that version 2.5.20 (and probably other versions) for Linux ship linked against libsasl2.so.2. This library can be found on RedHat Enterprise 6 (or CentOS 6) but modern Linux systems have moved on to newer versions. To use the driver, it is necessary to get a copy of libsasl2.so.2.0.23 (or a similar version) from an old enough system, install it in an appropriate lib directory, and create libsasl2.so.2 as a symlink to it in that same directory.

In this example, SQL Relay is configured to connect to a Impala database via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc">
		<session>
			<start>
				<runquery>
				use default
				</runquery>
				<runquery>
				select 1
				</runquery>
			</start>
		</session>
		<connections>
			<connection string="dsn=impala;overrideschema=%;unicode=no"/>
		</connections>
	</instance>
<instances>

The contents of the session tag work around a quirk. Normally the schema would be selected by setting the db option of the string attribute of the connection tag, but this doesn't work at all. Running the "use default" query at the beginning of the session helps. But, it appears that the schema isn't actually selected until the first query is run. So, the "select 1" query immediately following the use query solves this.

The overrideschema option in the string attribute of the connection tag works around yet another quirk. When running "show tables" or "describe" queries, the database needs "%" (the SQL wildcard) to be passed in as the schema for the table names.

The driver also doesn't support unicode, so the unicode=no option in the string attribute of the connection tag tells SQL Relay not to try.

Note that there are no user/password options in the string attribute of the connection tag. Impala supports several different authentication mechanisms, but by default, it allows unauthenticated access to the database (AuthMech=0). This can be configured in the DSN though, and if a user/password authentication mechansim is selected, then the standard user/password options can be included.

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[impala]
Description=Cloudera ODBC Driver for Impala (64-bit) DSN
Driver=Cloudera ODBC Driver for Impala 64-bit
HOST=impalaserver
PORT=21050
Database=impaladb
AuthMech=0
UID=default
PWD=
TSaslTransportBufSize=1000
RowsFetchedPerBlock=10000
SocketTimeout=0
StringColumnLength=32767
UseNativeQuery=0

The Driver parameter refers to an entry in the odbcinst.ini file (usually /etc/odbcinst.ini) which identifies driver files:

[Cloudera ODBC Driver for Impala 64-bit]
Description=Cloudera ODBC Driver for Impala (64-bit)
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

( NOTE: The odbc.ini and odbcinst.ini files must be readable by the user that the instance of SQL Relay runs as.)

Amazon Redshift (via ODBC)

Amazon Redshift is a cloud-hosted data warehouse service. Amazon provides a Redshift ODBC driver for Windows and Linux platforms. This driver (or perhaps Redshift itself) has some quirks.

In this example, SQL Relay is configured to connect to a Redshift database via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc" ignoreselectdatabase="yes">
		<connections>
			<connection string="dsn=redshift;user=redshiftuser;password=redshiftpassword;overrideschema=public"/>
		</connections>
	</instance>
<instances>

The ignoreselectdatabase="yes" attribute of the instance tag works around a quirk. It instructs SQL Relay to ignore "use database" queries, or other attempts to select the current database/schema. These options tend to put the connection outside of any database/schema, and unable to return to the desired database/schema.

The overrideschema option in the string attribute of the connection tag works around another quirk. When running "show tables" or "describe" queries, the database needs "public" to be passed in as the schema for the table names.

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[redshift]
Driver=Amazon Redshift (x64)
Host=redshifthost
Database=redshiftdb
Username=redshiftuser
Password=redshiftpassword

The Driver parameter refers to an entry in the odbcinst.ini file (usually /etc/odbcinst.ini) which identifies driver files:

[Amazon Redshift (x64)]
Description=Amazon Redshift ODBC Driver (64-bit)
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

( NOTE: The odbc.ini and odbcinst.ini files must be readable by the user that the instance of SQL Relay runs as.)

Amazon Redshift (via PostgreSQL)

Amazon Redshift is a cloud-hosted data warehouse service. Since it is based on PostgreSQL 8, it is possible to connect to a Redshift instance using the PostgreSQL client library.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql" ignoreselectdatabase="yes">
		<connections>
			<connection string="host=redshifthost;port=5439;user=redshiftuser;password=redshiftpassword;db=redshiftdb;typemangling=lookup;tablemangling=lookup"/>
		</connections>
	</instance>
<instances>

The ignoreselectdatabase="yes" attribute of the instance tag works around a quirk. It instructs SQL Relay to ignore "use database" queries, or other attempts to select the current database/schema. These options tend to put the connection outside of any database/schema, and unable to return to the desired database/schema.

The port option in the string attribute of the connection tag instructs SQL Relay to connect to the Redshift default port of 5439 rather than the PostgreSQL default port of 5432.

The typemangling and tablemangling options in the string attribute of the connection tag instruct SQL Relay to return data type and table names rather than data type and table object ID's (the default for PostgreSQL)

Amazon Athena (via ODBC)

Amazon Athena is an SQL interface to data stored in Amazon S3. Simba provides an Athena ODBC driver for Windows and Linux platforms. This driver (or perhaps Athena itself) has some quirks.

In this example, SQL Relay is configured to connect to a Athena database via ODBC.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="odbc">
		<connections>
			<connection string="dsn=athena;user=RDXDE23X56D9822FFGE3;password=uEEmF+RDexoXpqTD5MiP3421emYP2M+4Rqo6GHio;overrideschema=athenadb"/>
		</connections>
	</instance>
<instances>

Note the odd user and password options in the string attribute of the connection tag.

Note also that the db option is missing from the string attribute of the connection tag. The schema must be set in the DSN, and setting it in the connection string doesn't override the one set in the DSN.

The overrideschema option in the string attribute of the connection tag works around a quirk. The database tends to report the current schema as something other than "athenadb", but when running "show tables" or "describe" queries, the database wants "athenadb" to be passed in as the schema for the table names.

The dsn option in the string attribute of the connection tag refers to an ODBC DSN (Data Source Name).

On Windows platforms, the DSN is an entry in the Windows Registry, created by the ODBC Data Source Administrator (look for "Set up ODBC data sources" in the Control Panel or just run odbcad32.exe).

On Linux and Unix platforms, the DSN is an entry in the odbc.ini file (usually /etc/odbc.ini).

[athena]
Description=Simba Athena ODBC Driver (64-bit) DSN
Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so
AwsRegion=us-east-1
Schema=athenadb
S3OutputLocation=s3://athenadata/
UID=RDXDE23X56D9822FFGE3
PWD=uEEmF+RDexoXpqTD5MiP3421emYP2M+4Rqo6GHio

The Driver parameter refers to an entry in the odbcinst.ini file (usually /etc/odbcinst.ini) which identifies driver files:

[Simba Athena ODBC Driver 64-bit]
Description=Simba Athena ODBC Driver (64-bit)
Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so

( NOTE: The odbc.ini and odbcinst.ini files must be readable by the user that the instance of SQL Relay runs as.)

Generic ODBC

ODBC can be used to access many databases for which SQL Relay has no native support.

Accessing a database via an ODBC driver usually involves:

• Installing the driver
• Creating a DSN
• On Windows this is done via a control panel
• On Linux/Unix it is done by adding entries to the odbc.ini and sometimes odbcinst.ini files
• Creating an instance of SQL Relay with dbase="odbc" which refers to the DSN, optionally specifying the user and password

See the examples above for more details. It should be possible to adapt one of them to the ODBC driver that you would like to use.

Note that many ODBC drivers have quirks. Many of the examples above demonstrate ways of working around some of these quirks.

Database Connection Count

For Performance

In a performance-oriented configuration, a good rule of thumb is to open as many connections as you can. That number is usually environment-specific, and dictated by database, system and network resources.

For Throttling

Database Cursor Count

Database cursors are used to execute queries and step through result sets. Most applications only need to use one cursor at a time. Some apps require more though, either because they run nested queries, or sometimes because they just don't properly free them.

SQL Relay maintains persistent cursors as well as connections. By default, each connection opens one cursor, but the number of cursors opened by each connection can be configured using the cursors attribute.

<?xml version="1.0"?>
<instances>
	<instance id="example" connections="10" cursors="2">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

Any number of cursors can be opened. A good rule of thumb is to open as few as possible, but as many as you know that your application will need.

( NOTE: The documentation above says that by default, each connection opens one cursor, and this is true, but it would be more accurate to say that by default each connection opens one cursor, but will open additional cursors on-demand, up to 5. This is because it is common for an app to run at least one level of nested queries. For example, it is common to run a select and then run an insert, update, or delete for each row in the result set. Unfortunately, it is also not uncommon for apps to just manage cursors poorly. So, SQL Relay's default configuration offers a bit of flexibility to accommodate these circumstances. See the next section on Dynamic Scaling for more information about configuring connections and cursors to scale up and down on-demand.)

Dynamic Scaling

Both connections and cursors can be configured to scale dynamically - open on demand and then die off when no longer needed. This feature is useful if you have spikes in traffic during certain times of day or if your application has a few modules that occasionally need more cursors than usual.

The maxconnections and maxcursors attribute define the upper bounds.

<?xml version="1.0"?>
<instances>
	<instance id="example" connections="10" maxconnections="20" cursors="2" maxcursors="10">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this example, 10 connections will be started initially but more will be be started on-demand, up to 20. Each of the newly spawned connections will die off if they are inactive for longer than 1 minute.

In this example, each connection will initially open 2 cursors but more will be opened on-demand, up to 10. Each newly opened cursor will be closed as soon as it is no longer needed.

Other attributes that control dynamic scaling behavior include:

• maxqueuelength
• growby
• ttl
• cursors_growby

Listener Configuration

By default, SQL Relay listens for client connections on port 9000, on all available network interfaces.

It can be configured to listen on a different port though...

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" port="9001"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

...and accessed using:

sqlrsh -host sqlrserver -port 9001 -user scott -password tiger

It can also be configured to listen on a unix socket...

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" socket="/tmp/example.socket">
		</listener>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

( NOTE: The user that SQL Relay runs as must be able to read and write to the path of the socket.)

...and accessed from the local server using:

sqlrsh -socket /tmp/example.socket -user scott -password tiger

If the server has multiple network interfaces, SQL Relay can also be configured to listen on specific IP addresses.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" addresses="192.168.1.50,192.168.1.51"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

When configured this way, it can be accessed on 192.168.1.50 and 192.168.1.51 but not on 127.0.0.1 (localhost).

If the socket option is specified but port and addresses options are not, then SQL Relay will only listen on the socket. If addresses/port and socket options are both specified then it listens on both.

Multiple Instances

Any number of SQL Relay instances can be defined in the configuration file.

In following example, instances that connect to Oracle, SAP/Sybase and DB2 are defined in the same file.

<?xml version="1.0"?>
<instances>

	<instance id="oracleexample">
		<listeners>
			<listener port="9000"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>

	<instance id="sapexample" dbase="sap">
		<listeners>
			<listener port="9001"/>
		</listeners>
		<connections>
                        <connection string="sybase=/opt/sap;lang=en_US;server=SAPSERVER;user=sapuser;password=sappassword;db=sapdb"/>
		</connections>
	</instance>

	<instance id="db2example" dbase="db2">
		<listeners>
			<listener port="9002"/>
		</listeners>
		<connections>
                        <connection string="db=db2db;user=db2inst1;password=db2inst1pass;lang=C;connecttimeout=0"/>
		</connections>
	</instance>

</instances>

These instances can be started using:

sqlr-start -id oracleexample
sqlr-start -id sapexample
sqlr-start -id db2example

( NOTE: When installed from packages, SQL Relay may have to be started and stopped as root.)

...and accessed using:

sqlrsh -host sqlrserver -port 9000 -user scott -password tiger
sqlrsh -host sqlrserver -port 9001 -user sapuser -password sappassword
sqlrsh -host sqlrserver -port 9002 -user db2inst1 -password db2inst1pass

Starting Instances Automatically

In all previous examples sqlr-start has been called with the -id option, specifying which instance to start. If sqlr-start is called without the -id option then it will start all instances configured with the enabled attribute set to yes.

For example, if the following instances are defined...

<?xml version="1.0"?>
<instances>

	<instance id="oracleexample" enabled="yes">
		<listeners>
			<listener port="9000"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>

	<instance id="sapexample" enabled="yes" dbase="sap">
		<listeners>
			<listener port="9001"/>
		</listeners>
		<connections>
                        <connection string="sybase=/opt/sap;lang=en_US;server=SAPSERVER;user=sapuser;password=sappassword;db=sapdb"/>
		</connections>
	</instance>

	<instance id="db2example" dbase="db2">
		<listeners>
			<listener port="9002"/>
		</listeners>
		<connections>
                        <connection string="db=db2db;user=db2inst1;password=db2inst1pass;lang=C;connecttimeout=0"/>
		</connections>
	</instance>

</instances>

...then calling sqlr-start without the -id parameter will start oracleexample and sapexample because enabled="yes" is configured for those instances. db2example will not be started because enabled="yes" is not configured for that instance.

When installed on most platforms, SQL Relay creates a systemd service file (usually in /usr/lib/systemd/system or /lib/systemd/system) or an init script in the appropriate place under /etc. These call sqlr-start with no -id option. If configured to run at boot, they will start all instances for which enabled="yes" is configured.

How to enable the service file or init script depends on what platform you are using.

On systemd-enabled Linux, this usually involves running:

systemctl enable sqlrelay.service

On non-systemd-enabled Linux, Solaris, SCO and other non-BSD Unixes, this usually involves creating a symlink from /etc/init.d/sqlrelay to /etc/rc3.d/S85sqlrelay. This can be done manually, but most platforms provide utilities to do it for you.

Redhat-derived Linux distributions have a chkconfig command that can do this for you:

chkconfig --add sqlrelay

Debian-derived Linux distributions provide the update-rc.d command:

update-rc.d sqlrelay defaults

Solaris provides svcadm:

svcadm enable sqlrelay

On FreeBSD you must add a line to /etc/rc.conf like:

sqlrelay_enabled=YES

On NetBSD you must add a line to /etc/rc.conf like:

sqlrelay=YES

On OpenBSD you must add a line to /etc/rc.conf like:

sqlrelay_flags=""

Client Protocol Options

Whether written using the native SQL Relay API, or a connector of some sort, SQL Relay apps generally communicate with SQL Relay using SQLRClient, the native SQL Relay client-server protocol.

However, SQL Relay also supports the MySQL and PostgreSQL client-server protocols. When SQL Relay is configured to use one of these, it allows MySQL/MariaDB and PostgreSQL apps to communicate directly with SQL Relay without modification, without having to install any software on the client.

In these configurations, SQL Relay becomes a transparent proxy. MySQL/MariaDB or PostgreSQL apps aimed at SQL Relay still think that they're talking to a MySQL/MariaDB or PostgreSQL database, but in fact, are talking to SQL Relay.

SQLRClient Protocol

SQLRClient is the native SQL Relay protocol, enabled by default. The example configurations above and throughout most of the rest of the guide configure SQL Relay to speak this protocol.

If an instance speaks the SQLRClient client-server protocol, then any client that wishes to use it must also speak the SQLRClient client-server protocol. This means that most software written using the SQL Relay native API, or written using a database abstraction layer which loads a driver for SQL Relay can access this instance. However, it also means that client programs for other databases (eg. the mysql, psql, and sqlplus command line programs, MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Basic Configuration

SQLRClient is the native SQL Relay protocol, no special tags or attributes are required to enable it.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this configuration:

• The connection tag instructs the instance to log in to the ora1 database as user scott with password tiger.
• By default, SQL Relay loads the sqlrclient protocol module, and listens on port 9000.

The instance can be started using:

sqlr-start -id example

and accessed from the local machine using:

sqlrsh -host localhost -user scott -password tiger

( NOTE: By default, the user and password used to access SQL Relay are the same as the user and password that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag. )

By default, SQL Relay listens on all available network interfaces, on port 9000, and can be accessed remotely by hostname. For example, if the server running SQL Relay is named sqlrserver then it can be accessed from another system using:

sqlrsh -host sqlrserver -user scott -password tiger

The instance can be stopped using:

sqlr-stop -id example

Though it is not necessary, it is possible to explicitly configure SQL Relay to load the sqlrclient protocol module as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient"/>
		</listener>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

Since this instance speaks the SQLRClient client-server protocol, any client that wishes to use it must also speak the SQLRClient client-server protocol. This means that software written using the SQL Relay native API, or written using a database abstraction layer which loads a driver for SQL Relay can access this instance. However, it also means that client programs for other databases (eg. the mysql, psql, and sqlplus command line programs, MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Listener Options

By default, SQL Relay listens on port 9000.

However, it can be configured to listen on a different port.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" port="9001"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

If the server has multiple network interfaces, SQL Relay can also be configured to listen on specific IP addresses.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" addresses="192.168.1.50,192.168.1.51"/>
		</listeners>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In the example above, it can be accessed on 192.168.1.50 and 192.168.1.51 but not on 127.0.0.1 (localhost).

It can also be configured to listen on a unix socket by adding a socket attribute to the listener tag.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener protocol="sqlrclient" socket="/tmp/example.socket">
		</listener>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

( NOTE: The user that SQL Relay runs as must be able to read and write to the path of the socket.)

If the socket option is specified but the port option is not, then SQL Relay will only listen on the socket. If port and socket options are both specified then it listens on both.

Authentication/Encryption Options

When using the SQLRClient protocol, there are several options for controlling which users are allowed to access an instance of SQL Relay.

Connect Strings Auth

The default authentication option is connect strings auth.

When connect strings authentication is used, a user is authenticated against the set of user/password combinations that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag.

In the following example, SQL Relay is configured to access the database using scott/tiger credentials.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this case, clients would also use scott/tiger credentials to access SQL Relay.

Though it is not necessary, it is possible to explicitly configure SQL Relay to load the connect strings auth module as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<auths>
			<auth module="sqlrelay_connectstrings"/>
		</auths>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

User List Auth

Another popular authentication option is user list auth.

When user list authentication is used, a user is authenticated against a static list of user/password combinations, which may be different than the user/password used that SQL Relay uses to access the database.

To enable user list auth, you must provide a list of user/password combinations, as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="oneuser" password="onepassword"/>
				<user user="anotheruser" password="anotherpassword"/>
				<user user="yetanotheruser" password="yetanotherpassword"/>
			</auth>
		</auths>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

Database Auth

Another popular authentication option is database auth.

The sqlrclient_database module can be used to authentication users directly against the database itself. This is useful when you want the credentials used to access SQL Relay to be the same as the credentials used to access the database, but it's inconvenient to maintain a duplicate list of users in the configuration file.

SQL Relay authenticates users against the database by checking the provided credentials against the credentials that are currently in use, and then, if they differ, logging out of the database and back in using the provided credentials.

To enable database auth, configure SQL Relay to load the sqlrclient_database auth module as follows.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<auths>
			<auth module="sqlrclient_database"/>
		</auths>
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

( NOTE: Database authentication should not be used in an instance where dbase="router". It's OK for the instances that the router routes to to use it, but the router instance itself should not. If database authentication is used for that instance, then authentication will fail.)

In this configuration, SQL Relay initially logs in to the database as scott/tiger as specified in the connection tag. But each time a client connects, SQL Relay logs out and attempts to log back in as the user specified by the client, unless the user/password are the same as the current user.

For example, if a client initially connects using:

sqlrsh -host localhost -user scott -password tiger

...then SQL Relay doesn't need to log out and log back in.

But if the client then tries to connect using:

sqlrsh -host localhost -user anotheruser -password anotherpassword

..then SQL Relay does need to log out and log back in.

A subsequent connection using anotheruser/anotherpassword won't require a re-login, but a subsequent connection using any other user/password will.

Though this module is convenient, it has the disadvantage that logging in and out of the database over and over partially defeats SQL Relay's persistent connection pooling.

Proxied Auth

Another authentication option is proxied auth.

When proxied authentication is used, a user is authenticated against the database itself, though in a different manner than database authentication described above. SQL Relay logs into the database as a user with permissions to proxy other users. For each client session, SQL Relay checks the provided credentials against the credentials that are currently in use. If they differ, then it asks the proxy user to switch the user it's proxying to the provided user.

Kerberos and Active Directory Encryption and Authentication

SQL Relay supports Kerberos encryption and authentication between the SQL Relay client and SQL Relay Server.

When Kerberos encryption and authentication is used:

• All communications between the SQL Relay client and SQL Relay server are encrypted.
• A user who has authenticated against a Kerberos KDC or Active Directory Domain Controller can access SQL Relay without having to provide additional credentials.

On Linux and Unix systems, both server and client environments must be "Kerberized". On Windows systems, both server and client must join an Active Directory Domain. Note that this is only available on Professional or Server versions of Windows. Home versions cannot join Active Directory Domains.

The following configuration configures an instance of SQL Relay to use Kerberos authentication and encryption:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener krb="yes" krbservice="sqlrelay" krbkeytab="/usr/local/firstworks/etc/sqlrelay.keytab"/>
		</listener>
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="dmuse@KRB.FIRSTWORKS.COM"/>
				<user user="kmuse@KRB.FIRSTWORKS.COM"/>
				<user user="imuse@KRB.FIRSTWORKS.COM"/>
				<user user="smuse@KRB.FIRSTWORKS.COM"/>
				<user user="FIRSTWORKS.COM\dmuse"/>
				<user user="FIRSTWORKS.COM\kmuse"/>
				<user user="FIRSTWORKS.COM\imuse"/>
				<user user="FIRSTWORKS.COM\smuse"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

• The krb attribute enables (or disables) Kerberos authentication and encryption.
• The krbservice attribute specifies which Kerberos service to use. This attribute is optional and defaults to "sqlrelay". It is only shown here for illustrative purposes.
• The krbkeytab attribute specifies the location of the keytab file containing the key for the specified Kerberos service. This attribute is not required on Windows. On Linux or Unix systems if this paramter is omitted, then it defaults to the system keytab, usually /etc/krb5.keytab
• User list authentication is also used. Other authentication methods are not currently supported with Kerberos.

Note that no passwords are required in the user list. Note also that users are specified in both user@REALM (Kerberos) format and REALM\user (Active Directory) format to support users authenticated against both systems.

To start the instance on a Linux or Unix system, you must be logged in as a user that can read the file specified by the krbkeytab attribute.

To start the instance on a Windows system, you must be logged in as a user that can proxy the service specified by the krbservice attribute (or it's default value of "sqlrelay" if omitted).

If those criteria are met, starting the Kerberized instance of SQL Relay is the same as starting any other instance:

sqlr-start -id example

( NOTE: When installed from packages, SQL Relay may have to be started and stopped as root.)

To access the instance, you must acquire a Kerberos ticket-granting ticket. On a Linux or Unix system, this typically involves running kinit, though a fully Kerberized environment may acquire this during login. On a Windows system, you must log in as an Active Directory domain user.

After acquiring the ticket-granting ticket, the instance of SQL Relay may be accessed from a Linux or Unix system as follows:

sqlrsh -host sqlrserver -krb

From a Windows system, it may be necessary to specify the fully qualified Kerberos service name as well:

sqlrsh -host sqlrserver -krb -krbservice sqlrelay/sqlrserver.firstworks.com@AD.FIRSTWORKS.COM

Note the absence of user and password parameters.

Kerberos authentication establishes trust between the user who acquired the ticket-granting ticket (the user running the client program) and the service (the SQL Relay server) as follows:

• The client program uses the user's ticket-granting ticket to acquire a ticket for the sqlrelay service.
• The client program then uses this service ticket to establish a security context with the SQL Relay server.
• During this process the client program sends the SQL Relay server the user name that was used to acquire the original ticket-granting ticket.
• If the security context can be successfully established, then the SQL Relay server can trust that the client program is being run by the user that it says it is.

Once the SQL Relay server trusts that the client is being run by the user that it says it is, the user is authenticated against the list of users.

While Kerberos authenticated and encrypted sessions are substantially more secure than standard SQL Relay sessions, several factors contribute to a performance penalty:

• The client program may have to acquire a service ticket from another server (the Kerberos KDC or Active Directory Domain Controller) prior to connecting to the SQL Relay server.
• When establishing the secure session, a significant amount of data must be sent back and forth between the client and server over multiple network round-trips.
• Kerberos imposes a limit on the amount of data that can be sent or received at once, so more round trips may be required when processing queries.
• Without dedicated encryption hardware and a Kerberos implementation that supports it, the computation involved in encrypting and decrypting data can also introduce delays.

Any kind of full session encryption should be used with caution in performance-sensitive applications.

TLS/SSL Encryption and Authentication

SQL Relay supports TLS/SSL encryption and authentication between the SQL Relay client and SQL Relay Server.

When TLS/SSL encryption and authentication is used:

• All communications between the SQL Relay client and SQL Relay server are encrypted.
• SQL Relay clients and servers may optionally validate each other's certificates and identities.

When using TLS/SSL encryption and authentication, at minimum, the SQL Relay server must be configured to load a certificate. For highly secure production environments, this certificate should come from a trusted certificate authority. In other environments the certificate may be self-signed, or even be borrowed from another server.

Encryption Only

The following configuration enables TLS/SSL encryption for an instance of SQL Relay:

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<listeners>
			<listener tls="yes" tlscert="/usr/local/firstworks/etc/sqlrserver.pem"/>
		</listeners>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

MySQL Protocol

SQL Relay can be configured to speak the native MySQL protocol, enabling client programs for MySQL/MariaDB (eg. the mysql command line program and MySQL Workbench) to access SQL Relay directly.

If an instance speaks the MySQL client-server protocol, then any client that wishes to use it must also speak the MySQL client-server protocol. This means that most software written using the MySQL API, or written using a database abstraction layer which loads a driver for MySQL can access this instance. However, it also means that SQL Relay's standard sqlrsh client program and client programs for other databases (eg. the psql, and sqlplus command line programs, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Basic Configuration

To enable SQL Relay to speak the MySQL protocol, add the appropriate listener tag:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3307"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

In this configuration:

• The connection tag instructs the instance to log in to the mysqldb database on host mysqlhost as user mysqluser with password mysqlpassword.
• The listener tag instructs SQL Relay to load the mysql protocol module, and listen on port 3307.

The instance can be started using:

sqlr-start -id example

and accessed from the local machine using:

mysql --host=127.0.0.1 --port=3307 --user=sqlruser --password=sqlrpassword

( NOTE: 127.0.0.1 is used instead of "localhost" because "localhost" instructs the mysql client to use a predefined unix socket.)

( NOTE: By default, the user and password used to access SQL Relay are the same as the user and password that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag. )

By default, SQL Relay listens on all available network interfaces, and can be accessed remotely by hostname. For example, if the server running SQL Relay is named sqlrserver then it can be accessed from another system using:

mysql --host=sqlrserver --port=3307 --user=sqlruser --password=sqlrpassword

The instance can be stopped using:

sqlr-stop -id example

Since this instance speaks the MySQL client-server protocol, any client that wishes to use it must also speak the MySQL client-server protocol. This means that most software written using the MySQL API, or written using a database abstraction layer which loads a driver for MySQL can access this instance. However, it also means that SQL Relay's standard sqlrsh client program and client programs for other databases (eg. the psql, and sqlplus command line programs, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Listener Options

In the example above, the instance is configured to listen on port 3307. The MySQL/MariaDB database typically listens on port 3306, so port 3307 was chosen to avoid collisions with the database itself.

However, if SQL Relay is run on a different server than the database, then it can be configured to run on port 3306. This is desirable when using SQL Relay as a transparent proxy.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

If the server has multiple network interfaces, SQL Relay can also be configured to listen on specific IP addresses.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" addresses="192.168.1.50,192.168.1.51" port="3306"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

In the example above, it can be accessed on 192.168.1.50 and 192.168.1.51 but not on 127.0.0.1 (localhost).

It can also be configured to listen on a unix socket by adding a socket attribute to the listener tag.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306" socket="/var/lib/mysql/mysql.sock"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

( NOTE: The user that SQL Relay runs as must be able to read and write to the path of the socket.)

If the socket option is specified but the port option is not, then SQL Relay will only listen on the socket. If port and socket options are both specified then it listens on both.

Authentication/Encryption Options

When using the MySQL protocol, there are several options for controlling which users are allowed to access an instance of SQL Relay.

Connect Strings Auth

The default authentication option is connect strings auth.

When connect strings authentication is used, a user is authenticated against the set of user/password combinations that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag.

In the following example, SQL Relay is configured to access the database using mysqlsuer/mysqlpassword credentials.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

In this case, clients would also use mysqluser/mysqlpassword credentials to access SQL Relay.

Though it is not necessary, it is possible to explicitly configure SQL Relay to load the connect strings auth module as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_connectstrings"/>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

User List Auth

Another popular authentication option is user list auth.

When user list authentication is used, a user is authenticated against a static list of user/password combinations, which may be different than the user/password used that SQL Relay uses to access the database.

To enable user list auth, you must provide a list of user/password combinations, as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="oneuser" password="onepassword"/>
				<user user="anotheruser" password="anotherpassword"/>
				<user user="yetanotheruser" password="yetanotherpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

When using SQL Relay as a transparent proxy, it is desirable to define the same set of users that are defined in the database itself. While this may sound inconvenient, in practice, most apps use a single user to access the database, and it's not uncommon for multiple apps to share a user. So, the list is typically short.

Database Auth

Another popular authentication option is database auth.

The mysql_database module can be used to authentication users directly against the database itself. This is useful when you want the credentials used to access SQL Relay to be the same as the credentials used to access the database, but it's inconvenient to maintain a duplicate list of users in the configuration file.

SQL Relay authenticates users against the database by checking the provided credentials against the credentials that are currently in use, and then, if they differ, logging out of the database and back in using the provided credentials.

To enable database auth, configure SQL Relay to load the mysql_database auth module as follows.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_database"/>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

In this configuration, SQL Relay initially logs in to the database as mysqluser/mysqlpassword as specified in the connection tag. But each time a client connects, SQL Relay logs out and attempts to log back in as the user specified by the client, unless the user/password are the same as the current user.

For example, if a client initially connects using:

mysql --host=127.0.0.1 --user=mysqluser --password=mysqlpassword --default-auth=mysql_clear_password

...then SQL Relay doesn't need to log out and log back in.

But if the client then tries to connect using:

mysql --host=127.0.0.1 --user=anotheruser --password=anotherpassword --default-auth=mysql_clear_password

..then SQL Relay does need to log out and log back in.

A subsequent connection using anotheruser/anotherpassword won't require a re-login, but a subsequent connection using any other user/password will.

Though this module is convenient, it has two disadvantages.

• Logging in and out of the database over and over partially defeats SQL Relay's persistent connection pooling.
• The mysql_database module requires that the MySQL/MariaDB client send it an unencrypted password. In the examples above, the --default-auth=mysql_clear_password option is used to send an unencrypted password from the mysql command line program.

The second disadvantage may or may not be a problem, depending on how secure communications on your network need to be. Note that some MySQL and MariaDB distributions omit the necessary mysql_clear_password module. For example, the distributions available in the default CentOS 6 and 7 repositories omit the module. On these platforms, an alternative distribution of MySQL/MariaDB would have to be installed or built from source.

TLS/SSL Encryption and Authentication

SQL Relay supports TLS/SSL encryption and authentication between the MySQL/MariaDB client and SQL Relay Server.

When TLS/SSL encryption and authentication is used:

• All communications between the MySQL/MariaDB client and SQL Relay server are encrypted.
• MySQL/MariaDB clients and servers may optionally validate each other's certificates and identities.

When using TLS/SSL encryption and authentication, at minimum, the SQL Relay server must be configured to load a certificate. For highly secure production environments, this certificate should come from a trusted certificate authority. In other environments the certificate may be self-signed, or even be borrowed from another server.

Encryption Only

The following configuration enables TLS/SSL encryption for an instance of SQL Relay:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3307" tls="yes" tlscert="/usr/local/firstworks/etc/sqlrserver.pem"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

Foreign Backend

Since SQL Relay supports a variety of database back-ends, the app can also be redirected to any of these databases, instead of the MySQL/MariaDB database it was originally written to use.

This is as simple as specifying a different database using the dbase attribute in the instance tag and specifying the appropriate database connection string in the string attribute of the connection tag.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this example, a MySQL front-end provides MySQL/MariaDB applications access to an Oracle back-end database.

Depending on the back-end, it might also be necessary to make some other configuration changes, as well, for example:

Special-Purpose Options

Mapping zero-scale DECIMAL to BIGINT

When targeting a foreign back-end...

Some back-end databases, like Oracle, don't have integer types. An integer type is really just a DECIMAL with a scale of 0.

By default, the MySQL protocol module maps zero-scale DECIMAL types to the MySQL DECIMAL type. Buy you might want to map them to the MySQL BIGINT type instead.

The zeroscaledecimaltobigint option instructs the module to do this mapping.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306" zeroscaledecimaltobigint="yes"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

Mapping DATE to DATETIME

When targeting a foreign back-end...

Some back-end databases, like Oracle, don't have separate DATE and DATETIME types. Instead, they just have a DATE type, which can store date and time parts, and which parts are returned depend on a parameter like NLS_DATE_FORMAT.

By default, the MySQL protocol module maps the DATE type to the MySQL DATE type. But, if you are using DATE type fields to store date/times and have the database configured to return date and time parts, then you might want the DATE type mapped to the MySQL DATETIME type.

The datetodatetime option instructs the module to do this mapping.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306" datetodatetime="yes"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

Supporting Older MariaDB JDBC Drivers

Older MariaDB JDBC drivers had a bug related to fetching the server's capability flags, and would reliably throw a NullPointerException when attempting to connect.

(I'm not sure exactly what versions this bug is present in. It's present in version 1.4.6, but fixed by 2.1.1.)

The oldmariadbjdbcservercapabilitieshack option instructs the module to return server capability flags that don't upset the old driver.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306" oldmariadbjdbcservercapabilitieshack="yes"/>
		</listeners>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>
</instances>

Limitations

The implementation of the MySQL protocol is likely sufficient for most applications, but it isn't 100% complete. Some notable limitations follow:

• Only LATIN1_SWEDISH_CI and UTF8_GENERAL_CI character sets are supported.
• Only the mysql_native_password and mysql_clear_password authentication methods are supported. Notably, mysql_old_password and authentication_windows_client are not supported.
• Multi-statement queries are not supported.
• mysql_change_user() is not supported.
• mysql_send_long_data() is not supported.
• mysql_info() will always returns NULL when used with SQL Relay.
• MYSQL_FIELD.org_name/org_name_length and MYSQL_FIELD.org_table/org_table_length are not supported.
• Kerberos authentication and encryption are not supported.

PostgreSQL Protocol

SQL Relay can be configured to speak the native PostgreSQL protocol, enabling client programs for PostgreSQL (eg. the psql command line program) to access SQL Relay directly.

If an instance speaks the PostgreSQL client-server protocol, then any client that wishes to use it must also speak the PostgreSQL client-server protocol. This means that most software written using the PostgreSQL API, or written using a database abstraction layer which loads a driver for PostgreSQL can access this instance. However, it also means that SQL Relay's standard sqlrsh client program and client programs for other databases (eg. the psql, and sqlplus command line programs, MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Basic Configuration

To enable SQL Relay to speak the PostgreSQL protocol, add the appropriate listener tag:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5433"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

In this configuration:

• The connection tag instructs the instance to log in to the postgresqldb database on host postgresqlhost as user postgresqluser with password postgresqlpassword.
• The listener tag instructs SQL Relay to load the postgresql protocol module, and listen on port 5433.

The instance can be started using:

sqlr-start -id example

and accessed from the local machine using:

psql -h localhost -p 5433 -U sqlruser

( NOTE: By default, the user and password used to access SQL Relay are the same as the user and password that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag. )

By default, SQL Relay listens on all available network interfaces, and can be accessed remotely by hostname. For example, if the server running SQL Relay is named sqlrserver then it can be accessed from another system using:

psql -h sqlrserver -p 5433 -U sqlruser

The instance can be stopped using:

sqlr-stop -id example

Since this instance speaks the PostgreSQL client-server protocol, any client that wishes to use it must also speak the PostgreSQL client-server protocol. This means that most software written using the PostgreSQL API, or written using a database abstraction layer which loads a driver for PostgreSQL can access this instance. However, it also means that SQL Relay's standard sqlrsh client program and client programs for other databases (eg. the mysql, and sqlplus command line programs, MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

Listener Options

In the example above, the instance is configured to listen on port 5433. The MySQL/MariaDB database typically listens on port 5432, so port 5433 was chosen to avoid collisions with the database itself.

However, if SQL Relay is run on a different server than the database, then it can be configured to run on port 5432. This is desirable when using SQL Relay as a transparent proxy.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

If the server has multiple network interfaces, SQL Relay can also be configured to listen on specific IP addresses.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" addresses="192.168.1.50,192.168.1.51" port="5432"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

In the example above, it can be accessed on 192.168.1.50 and 192.168.1.51 but not on 127.0.0.1 (localhost).

It can also be configured to listen on a unix socket by adding a socket attribute to the listener tag.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5432" socket="/tmp/.s.PGSQL.5432"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

( NOTE: The user that SQL Relay runs as must be able to read and write to the path of the socket.)

If the socket option is specified but the port option is not, then SQL Relay will only listen on the socket. If port and socket options are both specified then it listens on both.

Authentication/Encryption Options

When using the PostgreSQL protocol, there are several options for controlling which users are allowed to access an instance of SQL Relay.

Connect Strings Auth

The default authentication option is connect strings auth.

When connect strings authentication is used, a user is authenticated against the set of user/password combinations that SQL Relay is configured to use to access the database. That is, they are the values of the user and password parameters of the string attribute of the connection tag.

In the following example, SQL Relay is configured to access the database using postgresqlsuer/postgresqlpassword credentials.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

In this case, clients would also use postgresqluser/postgresqlpassword credentials to access SQL Relay.

Though it is not necessary, it is possible to explicitly configure SQL Relay to load the connect strings auth module as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<auths>
			<auth module="postgresql_connectstrings"/>
		</auths>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

User List Auth

Another popular authentication option is user list auth.

When user list authentication is used, a user is authenticated against a static list of user/password combinations, which may be different than the user/password used that SQL Relay uses to access the database.

To enable user list auth, you must provide a list of user/password combinations, as follows:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<auths>
			<auth module="postgresql_userlist">
				<user user="oneuser" password="onepassword"/>
				<user user="anotheruser" password="anotherpassword"/>
				<user user="yetanotheruser" password="yetanotherpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

When using SQL Relay as a transparent proxy, it is desirable to define the same set of users that are defined in the database itself. While this may sound inconvenient, in practice, most apps use a single user to access the database, and it's not uncommon for multiple apps to share a user. So, the list is typically short.

If the database has a lot of users, or users are added, deleted, or updated regularly, then it might be inconvenient to maintain a duplicate list of users in the configuration file. Unfortunately there isn't currently a postgresql_database auth module that can be used to auth users directly against the database, so for now this is the only option.

TLS/SSL Encryption and Authentication

SQL Relay supports TLS/SSL encryption and authentication between the PostgreSQL client and SQL Relay Server.

When TLS/SSL encryption and authentication is used:

• All communications between the PostgreSQL client and SQL Relay server are encrypted.
• PostgreSQL clients and servers may optionally validate each other's certificates and identities.

When using TLS/SSL encryption and authentication, at minimum, the SQL Relay server must be configured to load a certificate. For highly secure production environments, this certificate should come from a trusted certificate authority. In other environments the certificate may be self-signed, or even be borrowed from another server.

Encryption Only

The following configuration enables TLS/SSL encryption for an instance of SQL Relay:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="postgresql">
		<listeners>
			<listener protocol="postgresql" port="5433" tls="yes" tlscert="/usr/local/firstworks/etc/sqlrserver.pem"/>
		</listeners>
		<connections>
			<connection string="user=postgresqluser;password=postgresqlpassword;db=postgresqldb;host=postgresqlhost"/>
		</connections>
	</instance>
</instances>

Foreign Backend

Since SQL Relay supports a variety of database back-ends, the app can also be redirected to any of these databases, instead of the PostgreSQL database it was originally written to use.

This is as simple as specifying a different database using the dbase attribute in the instance tag and specifying the appropriate database connection string in the string attribute of the connection tag.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<listeners>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this example, a PostgreSQL front-end provides PostgreSQL applications access to an Oracle back-end database.

Depending on the back-end, it might also be necessary to make some other configuration changes, as well, for example:

Limitations

The implementation of the PostgreSQL protocol is likely sufficient for most applications, but it isn't 100% complete. Some notable limitations follow:

• Kerberos authentication and encryption are not supported.
• Multi-statement queries are not supported.

Multiple Protocols

A single instance of SQL Relay can also be configured to speak multiple protocols, granting access to the same database to applications which speak SQLRClient, MySQL, and/or PostgreSQL.

Basic Configuration

To enable SQL Relay to speak multiple protocols, add the appropriate listener tags:

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<listeners>
			<listener protocol="sqlrclient" port="9000"/>
			<listener protocol="mysql" port="3306"/>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this configuration:

• The connection tag instructs the instance to log in to the ora1 oracle database as user scott with password tiger.
• The first listener tag instructs SQL Relay to load the sqlrclient and listen on port 9000 (the default SQL Relay port) for native SQL Relay clients.
• The second listener tag instructs SQL Relay to load the mysql and listen on port 3306 (the default MySQL port) for MySQL clients.
• The third listener tag instructs SQL Relay to load the postgresql and listen on port 5432 (the default PostgreSQL port) for PostgreSQL clients.

The instance can be started using:

sqlr-start -id example

and accessed from the local machine using the native SQL Relay command line client, as follows:

sqlrsh -host localhost -user scott -password tiger

or from the local machine using the MySQL command line client, as follows:

mysql --host=127.0.0.1 --user=scott --password=tiger

or from the local machine using the PostgreSQL command line client, as follows:

psql -h localhost -U scott

By default, SQL Relay listens on all available network interfaces, and can be accessed remotely by hostname. For example, if the server running SQL Relay is named sqlrserver then it can be accessed from another system using the native SQL Relay command line client, as follows:

sqlrsh -host sqlrserver -user scott -password tiger

or using the MySQL command line client, as follows:

mysql --host=sqlrserver --user=scott --password=tiger

or using the PostgreSQL command line client, as follows:

psql -h sqlrserver -U scott

The instance can be stopped using:

sqlr-stop -id example

Since this instance speaks the SQLRClient, MySQL, and PostgreSQL client-server protocols, clients that wish to use it may speak any of those client-server protocol. This means that most software written using the SQL Relay, MySQL, or PostgreSQL APIs, or written using a database abstraction layer which loads a driver for SQL Relay, MySQL, or PostgreSQL can access this instance. However, client programs for other databases (eg. the sqlplus command line programs, Oracle SQL Developer, SQL Server Management Studio, etc.) cannot access this instance.

In this example, a front-end that supports MySQL and PostgreSQL protocols provides access to an Oracle back-end database. As such some queries may have to be modified from their native MySQL and PostgreSQL syntax to use the syntax of the Oracle database.

Listener Options

When configured to support multiple protocols, each listener may be configured with the full set of attributes available for that protocol module.

For example:

• All modules support the host, port, and addresses attributes.
• All modules support the various tls attributes.
• The sqlrclient module supports the various krb attributes, but other modules do not.

Authentication/Encryption Options

The default authentication option is connect strings auth. This option works the same as it does when SQL Relay is configured to use a single protocol - a user is authenticated against the set of user/password combinations that SQL Relay is configured to use to access the database.

However, other auth modules can also be used. Each auth module knows to work with the corresponding protocol module.

<?xml version="1.0"?>
<instances>
	<instance id="example" dbase="oracle">
		<listeners>
			<listener protocol="sqlrclient" port="9000"/>
			<listener protocol="mysql" port="3306"/>
			<listener protocol="postgresql" port="5432"/>
		</listeners>
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="sqlruser" password="sqlrpassword"/>
			</auth>
			<auth module="mysql_userlist">
				<user user="mysqluser" password="mysqlpassword"/>
			</auth>
			<auth module="postgresql_userlist">
				<user user="postgresluser" password="postgresqlpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=scott;password=tiger;oracle_sid=ora1"/>
		</connections>
	</instance>
</instances>

In this example:

• Native SQL Relay clients will be authenticated using the the sqlrclient_userlist auth module.
• MySQL clients will be authenticated using the the mysql_userlist auth module.
• PostgreSQL clients will be authenticated using the the postgresql_userlist auth module.

It can accessed from the local machine using the native SQL Relay command line client, as follows:

sqlrsh -host localhost -user sqlruser -password sqlrpassword

or from the local machine using the MySQL command line client, as follows:

mysql --host=127.0.0.1 --user=mysqluser --password=mysqlpassword

or from the local machine using the PostgreSQL command line client, as follows:

psql -h localhost -U postgresqluser

High Availabiltiy

In a high availability environment, SQL Relay can be deployed as a front-end to provide load balancing and failover for a set of replicated database servers or database cluster. Load balancing and failover can also be implemented over multiple SQL Relay servers.

Load Balancing and Failover With Replicated Databases or Database Clusters

In a database cluster or replication environment, SQL Relay can be configured to maintain a pool of connections to the various database nodes and distribute client sessions over the nodes. If an individual node fails, SQL Relay will attempt to reestablish connections to that node, while continuing to distribute client sessions over the remaining nodes.

In the configuration file, each connection tag defines a node to maintain connections to. In the following example, SQL Relay is configured to distribute over three Oracle nodes - ora1, ora2, and ora3

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1"/>
                        <connection string="user=scott;password=tiger;oracle_sid=ora2"/>
                        <connection string="user=scott;password=tiger;oracle_sid=ora3"/>
		</connections>
	</instance>
</instances>

Any number of connection tags may be defined.

SQL Relay also supports disproportionate distribution of load. If some nodes can handle more traffic than others, then SQL Relay can be configured to send more traffic to the more capable nodes.

SQL Relay uses the connection tag's metric attribute to decide how many connections to open to each node.

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=ora1" metric="5"/>
                        <connection string="user=scott;password=tiger;oracle_sid=ora2" metric="15"/>
                        <connection string="user=scott;password=tiger;oracle_sid=ora3" metric="30"/>
		</connections>
	</instance>
</instances>

The metric attribute doesn't specify the number of connections to open to each node, but the higher the metric relative to the other metrics, the more connections to that node will be opened and maintained. For example, if the metric for the first node is twice as large as the metric for the second node, then SQL Relay will open twice as many connections to the first node as the second.

In the example above, 15 is 3 times 5, so 3 times as many connections will be opened to ora3 as to ora1. 30 is 6 times 5, so 6 times as many connections will be opened to ora3 as ora1. Since a total of 10 connections will be opened, 1 will be opened to ora1, 3 to ora2, and 6 to ora2.

Already-Load Balanced Databases

In a typical database cluster or replicated environment, the nodes are identifiable as separate hosts. However, when the nodes are located behind a load balancing appliance or running on an application cluster, such as Oracle RAC, SQL Relay cannot identity an individual node.

In these environments, if a node goes down, SQL Relay will attempt to re-establish the connection, but rather than failing until the node comes back up, the new connection will more likely just succeed to a different node in the cluster. Over time, this can lead to disproportionate load balancing, with a bias toward nodes that have never gone down.

SQL Relay manages this by "shuffling" the connections periodically. Every so often, each database connection is re-established, giving that connection a chance to be re-established to a node that may have gone down but is now back up.

To indicate to SQL Relay that the nodes are already-load balanced, and need to be "shuffled" periodically, only one connection tag should be used, with the behindloadbalancer attribute set to "yes".

<?xml version="1.0"?>
<instances>
	<instance id="example">
		<connections>
                        <connection string="user=scott;password=tiger;oracle_sid=orarac" behindloadbalancer="yes"/>
		</connections>
	</instance>
</instances>

Master-Slave Query Routing

The load balancing scenarios described above all assume that master-master replication is being used. SQL Relay supports master-slave replication as well.

In a master-slave replication environment, SQL Relay can be configured to route DML and DDL queries to the master and distribute selects over the slaves.

This actually requires 3 instances of SQL Relay. One to connect to the master, one to connect to the slaves, and a third to route queries to the first two.

<?xml version="1.0"?>
<instances>

        <!-- This instance maintains connections to the "master" MySQL database
                on the masterdb machine.  This instance only listens on the
                unix socket /tmp/master.socket and thus cannot be connected to
                by clients from another machine. -->
        <instance id="master" socket="/tmp/master.socket" dbase="mysql">
                <connections>
                        <connection string="user=masteruser;password=masterpassword;host=masterdb;db=master;"/>
                </connections>
        </instance>


        <!-- This instance maintains connections to 4 "slave" MySQL databases
                on 4 slave machines.  This instance only listens on the unix
                socket /tmp/slave.socket and thus cannot be connected to by
                clients from another machine. -->
        <instance id="slave" socket="/tmp/slave.socket" dbase="mysql">
                <connections>
                        <connection string="user=slaveuser;password=slavepassword;host=slavedb1;db=slave;"/>
                        <connection string="user=slaveuser;password=slavepassword;host=slavedb2;db=slave;"/>
                        <connection string="user=slaveuser;password=slavepassword;host=slavedb3;db=slave;"/>
                        <connection string="user=slaveuser;password=slavepassword;host=slavedb3;db=slave;"/>
                </connections>
        </instance>


        <!-- This instance sends DML (insert,update,delete) and
                DDL (create/delete) queries to the "master" SQL Relay instance
                which, in turn, sends them to the "master" database.
                This instance sends any other queries to the "slave" SQL Relay
                instance which, in turn, distributes them over the "slave"
                databases. -->
        <instance id="router" dbase="router">
		<auths>
			<auth module="sqlrclient_userlist">
                        	<user user="routeruser" password="routerpassword"/>
			</auth>
		</auths>
		<routers>
                        <!-- send all DML/DDL queries to "master"  -->
			<router module="regex" connectionid="master">
				<pattern pattern="^drop "/>
				<pattern pattern="^create "/>
				<pattern pattern="^insert "/>
				<pattern pattern="^update "/>
				<pattern pattern="^delete "/>
			</router>
                        <!-- send all other queries to "slave" -->
			<router module="regex" connectionid="slave">
				<pattern pattern=".*"/>
			</router>
		</routers>
		<connections>
			<connection connectionid="master" string="socket=/tmp/master.socket;user=masteruser;password=masterpassword"/>
			<connection connectionid="slave" string="socket=/tmp/slave.socket;user=slaveuser;password=slavepassword"/>
		</connections>
        </instance>

</instances>

The first two instances use familiar configuration options, but the third uses a dbtype of "router" and uses router tags to define query routing rules.

(Note the module attribute of the router tag. SQL Relay is highly modular, and many advanced features, including query routing, are implemented by loadable modules.)

Front-End Load Balancing and Failover

If you are building out a high availability environment, or if your pool of application servers is just sufficiently large, you might want to set up a pool of SQL Relay servers between your application servers and the database.

SQL Relay supports two front-end load balancing and failover strategies. In the first strategy, load balancing and failover are provided by an appliance or application cluster. In the second, SQL Relay provides its own load balancing and failover, with some help from DNS.

Multiple instances of SQL Relay can be placed behind a load balancing appliance.

In this illustration, the load balancing appliance is shown as a single machine, but in a true HA environment, there would be 2 or more appliances sharing a virtual IP. Alternatively, rather than using an appliance, SQL Relay can be run on an application server cluster such as Linux Virtual Server.

Security Features

SQL Relay offers several features to enhance security.

Front-End Encryption and Secure Authentication

Back-End Encryption and Secure Authentication

SQL Relay also supports TLS/SSL Encryption and Authentication between the SQL Relay server and some databases.

The configuration details differ between databases though.

Oracle

Modern Oracle databases support the following TLS/SSL features:

• Encryption
• Certificate Validation
• Distinguished Name Validation
• Mutual Authentication

Configuring SQL Relay to use TLS/SSL encryption and authentication with an Oracle database involves:

• Configuring SQL*Net on the database server to support secure connections
• Configuring SQL*Net on the SQL Relay server to support secure connections
• Telling SQL Relay to use an oracle_sid that refers to a secure connection

Most of the configuration on the SQL Relay server is done outside of SQL Relay itself.

Microsoft SQL Server (via FreeTDS)

Modern Microsoft SQL Server databases support the following TLS/SSL features:

• Encryption
• Certificate Validation
• Common Name Validation

Microsoft SQL Server does not support Mutual Authentication over TLS/SSL.

Configuring SQL Relay to use TLS/SSL encryption and authentication with a Microsoft SQL Server database via FreeTDS involves:

• Installing a certificate and key on the database server
• Configuring FreeTDS on the SQL Relay server to support secure connections
• Telling SQL Relay to use a FreeTDS server entry that refers to a secure connection

Modern Microsoft SQL Server databases support encryption by default, and enable it upon request. However, a certificate must be installed on the database server to support authentication. This process can be tricky because SQL Server places some specific requirements on the certificate:

• The common name MUST match the fully qualified domain name of the database server (host name + primary DNS suffix).
• ...but Windows servers are commonly configured without a primary DNS suffix.
• The Enhanced Key Usage property MUST include Server Authentication.
• Or, in openssl terms: the extendedKeyUsage extension must include serverAuth in the extfile used to generate the certificate signing request.

MySQL/MariaDB

Modern MySQL/MariaDB databases support the following TLS/SSL features:

• Encryption
• Certificate Validation
• Common Name Validation

MySQL/MariaDB does not support Mutual Authentication over TLS/SSL.

Configuring SQL Relay to use TLS/SSL encryption and authentication with a MySQL/MariaDB database involves:

• Installing a certificate and key on the database server
• Configuring the SQL Relay server to support encryption and/or authentication
• Installing CA certs and revocation lists, as necessary, on the SQL Relay server

PostgreSQL

Modern PostgreSQL databases support the following TLS/SSL features:

• Encryption
• Certificate Validation
• Common Name Validation
• Mutual Authentication

Configuring SQL Relay to use TLS/SSL encryption and authentication with a PostgreSQL database involves:

• Configuring the database server to support secure connections
• Installing a certificate on the database server
• Configuring the SQL Relay server to support secure connections
• Installing a certificate on the SQL Relay server
• Configuring the database server to support encryption and/or authentication
• Installing certificate, key, CA certs, and revocation lists, as necessary, on the database server
• Configuring the SQL Relay server to support encryption and/or authentication
• Installing certificate, key, CA certs, and revocation lists, as necessary, on the SQL Relay server

Run-As User and Group

When a non-root user runs sqlr-start, the SQL Relay server runs as that user and as the primary group of that user.

When root runs sqlr-start, the SQL Relay server runs as a more secure user and group, usually nobody/nobody when built from source, or sqlrelay/sqlrelay when installed from packages.

However, the runasuser and runasgroup attributes can be used to control what user and group the SQL Relay server runs as.

<?xml version="1.0"?>
<instances>
	<instance id="example" ... runasuser="exampleuser" runasgroup="examplegroup" ...>
		...
	</instance>
</instances>

There are several important considerations when setting runasuser/runasgroup:

• runasuser is only effective if sqlr-start runs as root.
• runasgroup only effective if sqlr-start runs as a member of the group, or by root.
• All SQL Relay configuration files, and any appropriate database configuration files (such as Oracle's tnsnames.ora and SAP/Sybase's interfaces file) must be readable by the runasuser/runasgroup. Otherwise various things will fail, most notably, dynamic scaling.
• SQL Relay must be able to write to "run" and "log" directories. However, when installed from packages, the /var/run/sqlrelay and /var/log/sqlrelay directories are owned by sqlrelay/sqlrelay and have fairly restrictive 755 permissions. Thus, the permissions on these directories must be manually changed if runasuser or runasgroup are set to any value other than "sqlrelay". This is not an issue if SQL Relay is built from source, and is not an issue on Windows.

IP Filtering

By default, clients from any IP address are allowed to connect to the SQL Relay srever. However, the deinedips and allowedips attributes can be used to restrict the set of IP addresses that clients can connect from.

Password Files

Password files enable you to store passwords in external files, rather than directly in the config file. Passwords can then be secured by setting a more restrictive set of permissions on the files containing the passwords than on the config file itself.

Password Encryption

Password encryption enables you to store passwords in the configuration file in a manner that makes them not directly readable. Passwords for both SQL Relay users and database users may be encrypted.

Encryption and decryption are implemented by loadable modules. The passwordencryptions section of the configuration file indicates which modules to load, and attributes in the user and connection tags indicate which module to use with the password defined in that same tag.

For example, to use the rot module, which encrypts by performing a simple character rotation:

<?xml version="1.0"?>
<instances>
	<instance ...>
		<passwordencryptions>
			<passwordencryption module="rot" id="rot13enc" count="13"/>
		</passwordencryptions>
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="sqlruser" password="fdyecnffjbeq" passwordencryptionid="rot13enc"/>
			</auth>
		</auths>
		<connections>
			<connection connectionid="db" string="user=oracleuser;password=benpyrcnffjbeq;..." passwordencryptionid="rot13enc"/>
		</connections>
	</instance>
</instances>

The module attribute specifies which module to load.

Module configurations may have attributes and/or nested tags. How these are interpreted is module-specific.

In this example, the id and count attributes configure the rot module. "13" tells the module to rotate by 13 characters. The id attribute assigns this particular module configuration an id that will be referenced by user and connection tags. The id attribute is mandatory.

Note that the password in the user tag is encrypted (unencrypted, it would just be "sqlrpassword") and that the password in the string attribute of the connection tag is also encrypted (unencrypted, it would just be "oraclepassword"). A command line program (described below) is provided to encrypt passwords.

Note also that the passwordencryptionid attribute in both tags refers to the id of the module as set using the id attribute in the passwordencryption tag ( rot13enc ), not the module name ( rot ).

Password encryption modules may be "stacked". It is possible to load multiple modules and use each one with a different password. For example, you might want to use the rot module with a count of 13 for the SQL Relay password and a count of 10 for the database password.

<?xml version="1.0"?>
<instances>
	<instance ...>
		<passwordencryptions>
			<passwordencryption module="rot" id="rot13enc" count="13"/>
			<passwordencryption module="rot" id="rot10enc" count="10"/>
		</passwordencryptions>
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="sqlruser" password="fdyecnffjbeq" passwordencryptionid="rot13enc"/>
			</auth>
		</auths>
		<connections>
			<connection connectionid="db" string="user=oracleuser;password=ybkmvozkccgybn;..." passwordencryptionid="rot10enc"/>
		</connections>
	</instance>
</instances>

Encryption modules may be either two-way or one-way. Two-way encryption modules can both encrypt and decrypt a password. One-way encryption modules can only encrypt a password, not decrypt it.

As such, SQL Relay can use either one-way or two-way encryption to encrypt passwords for SQL Relay users. However, only two-way encryption can only be used to encrypt passwords for database users.

The command line tool sqlr-pwdenc is provided to help encrypt passwords for inclusion in the configuration file. Given an encryption module and password, it will print out the encrypted password.

sqlr-pwdenc [-config configfile] -id id -pwdencid passwordencryptionid -password password

• configfile - optional and refers to the configuration file
• id - the instance within the configuration file to look for the specified password encryption module definition
• passwordencryptionid - the id of the password encryption module to use
• password - the password to encrypt

For example:

$ sqlr-pwdenc -id example -pwdencid rot13enc -password oraclepassword
benpyrcnffjbeq

The resulting string "benpyrcnffjbeq" can now be put in the configuration file as the password.

There is one final thing to note. SQL Relay command line client programs like sqlrsh and sqlr-import take an -id option. The -id option causes the program to open the configuration file and extract the port, socket, user and password from the specified instance. If the password is two-way encrypted then this will still work. However, if the password is one-way encrypted, then this will fail. So, when using the -id option with a one-way encrypted password, you must also use the -user and -password option.

For example, rather than just using:

sqlrsh -id example

You should use:

sqlrsh -id example -user sqlruser -password sqlrpassword

Currently, the following password encryption modules are available in the standard SQL Relay distribution:

• rot
• md5
• sha1
• sha256
• des

rot

md5

sha1

sha256

des

aes128

The aes128 module is a two-way encryption module that performs AES128 (CBC) encryption/decryption.

<?xml version="1.0"?>
<instances>
	<instance ...>
		<passwordencryptions>
			<passwordencryption module="aes128" id="aes128enc" key="000102030405060708090a0b0c0d0e0f"/>
		</passwordencryptions>
		<auths>
			<auth module="sqlrclient_userlist">
				<user user="sqlruser" password="1f7be0255e24783696a1783114e28667320ecef9537b5da48c19dbfa793fbcaf" passwordencryptionid="aes128enc"/>
			</auth>
		</auths>
		<connections>
			<connection connectionid="db" string="user=oracleuser;password=153b3125b0ce225e06c3f61eb68a2b062eb6bdc17db4894620f8067cfec4c1bf;..." passwordencryptionid="aes128enc"/>
		</connections>
	</instance>
</instances>

Since the aes128 algorithm is two-way, it can be used to encrypt passwords for both SQL Relay users and database users.

The key attribute of the passwordencryption element contains a hexidecimal representation of the 128 bit (16 byte) key. The password attribute of the user element, and password parameter of the string attribute of the connection element contain hexidecimal representations of the encrypted passwords.

Note that multiple runs of sqlr-pwdenc with the same key and password will generate different results each time. This is the correct behavior. The first 128 bits (16 bytes) of the encrypted password are the "initialization vector" - a randomly generated string used in conjunction with the key to encrypt/decrypt the password. This vector is generated each time sqlr-pwdenc is run. Successive runs of sqlr-pwdenc generate different initialization vectors, and thus different encrypted passwords.

Connection Schedules

Connection schedules enable the SQL Relay server to control when users are allowed to access the database.

Connection schedules are implemented by loadable modules. The schedules section of the configuration file indicates which modules to load and what parameters to use when executing them.

<?xml version="1.0"?>
<instances>
	<instance ...>
		...
		<schedules>
			<!-- allow these users during business hours -->
			<schedule module="cron_userlist" default="deny">
				<users>
					<user user="dmuse"/>
					<user user="kmuse"/>
					<user user="imuse"/>
					<user user="smuse"/>
				</users>
				<rules>
					<allow when="* * * 2-5 8:00-11:59,13:00-16:59"/>
				</rule>
			</schedule>
		</schedules>
		...
	</instance>
</instances>

The module attribute specifies which module to load.

Module configurations may have attributes and/or nested tags. How these are interpreted is module-specific.

All schedule modules have an enabled attribute, allowing the module to be temporarily disabled. If enabled="no" is configured, then the module is disabled. If set to any other value, or omitted, then the module is enabled.

All schedule modules have a debug attribute. If debug="yes" is configured then debugging information is written to standard output. If debug="no" is configured, or if the debug attribute is omitted, then no debugging information is written.

Connection schedule modules can be "stacked". Multiple different modules may be loaded, and multiple instances of the same type of module, with different configurations, may also be loaded.

<?xml version="1.0"?>
<instances>
	<instance ...>
		...
		<schedules>
			<!-- allow these users during business hours -->
			<schedule module="cron_userlist" default="deny">
				<users>
					<user user="imuse"/>
					<user user="smuse"/>
				</users>
				<rules>
					<allow when="* * * 2-5 8:00-11:59,13:00-16:59"/>
				</rule>
			</schedule>

			<!-- allow these users at any time -->
			<schedule module="cron_userlist" default="deny">
				<users>
					<user user="dmuse"/>
					<user user="kmuse"/>
				</users>
				<rules>
					<allow when="* * * * *"/>
				</rule>
			</schedule>
		</schedules>
		...
	</instance>
</instances>