Developers

World class technical support for all Omnis programmers.

At TigerLogic we recognize that development support is as important as the software we provide,
vital in allowing you to develop, deploy and maintain your Omnis applications as quickly and as easily as possible.

Making SSL Connections using the MySQL DAM

for Omnis Studio 4.3.2 (and later)
by Gary Ashford

Introduction
This technote brings together excerpts from several web articles which describe how to make encrypted connections to a MySQL server using SSL; the Secure Sockets Layer protocol implemented by OpenSSL- and some recent changes to the MySQL DAM for Studio 5.1.

To make a secure connection using the MySQL DAM, you need a version of the DAM containing an SSL-enabled client library. This means you need a minimum of Omnis Studio 4.3.2 (Mac and Linux) or Omnis Studio 5.1 (Win32)

To determine whether the server supports connections via SSL:
Either from the MySQL Administration utility or from a client- using a standard user account, execute the following:

show variables like 'have_ssl'

If the result comes back empty, this implies that the MySQL server does not support SSL and should be upgraded accordingly. If the result returns the following:

have_ssl DISABLED

this implies that the server does support SSL, but one or more configuration options are missing or incorrect.
If the result returns the following:

have_ssl YES

then you are ready to make SSL connections from suitably configured client machines.

To configure the MySQL server to support SSL connections:
To configure a disabled MySQL server to support SSL connections, you need to edit your server configuration file (named my.cnf for Unix systems and my.ini for Windows systems) and specify the locations of the signing request certificate (ca-cert), the server certificate and the server key. These should all appear inside the [mysqld] section as shown:

[mysqld]
ssl-ca=/mysqlcerts/ca-cert.pem
ssl-cert=/servercerts/server-cert.pem
ssl-key=/mysqlcerts/server-key.pem
...

for Linux and Mac (using my.cnf), or

[mysqld]
ssl-ca=c:/mysqlcerts/ca-cert.pem
ssl-cert=c:/mysqlcerts/server-cert.pem
ssl-key=c:/mysqlcerts/server-key.pem
...

for a Windows server (using my.ini)

Restart the server (or Windows service) for the changes to take effect. Note that in the above example, no quotes are used around paths and windows paths must be specified using forward slashes.

Configuration files can be used both for the server and for client connection options. They can contain many other settings (that are beyond the scope of this text) and these are discussed further on the MySQL website.
Dependant on the SSL configuration at the MySQL server, you may be able to omit the ca-cert.pem if you do not need to verify the signing request certificate.

Creating an SSL-enabled user account
To create a user account that can only connect via SSL, use the MySQL Administrator to create a standard user account, or create the user account via SQL, for example:

CREATE USER 'jeffrey'@'my_ip' IDENTIFIED BY 'mypass';

The following SQL is then used to enable SSL connection (and disable standard/unencrypted connection):

GRANT ALL PRIVILEGES on *.* to 'jeffrey' IDENTIFIED BY 'mypass' REQUIRE SSL;

Making a secure connection using the MySQL DAM

to initiate a secure connection using the MySQL DAM and the SSL protocol, you need to set the required connect options and this can be done in two ways:

• Using a default options file, for example:

Do sessObj.$connectoption(kMySQLOptReadDefaultFile,'/mysqlcerts/my.cnf') Returns #F
for Linux and Mac, or

Do sessobj.$connectoption(kMySQLOptReadDefaultFile,'c:/mysqlcerts/my.ini') Returns #F
for Windows, where a Unix style config file contains similar to:

[client]
ssl-ca=/mysqlcerts/ca-cert.pem
ssl-cert=/mysqlcerts/client-cert.pem
ssl-key=/mysqlcerts/client-key.pem
...

and a Windows style config file contains similar to:

[client]
ssl-ca=c:/mysqlcerts/ca-cert.pem
ssl-cert=c:/mysqlcerts/client-cert.pem
ssl-key=c:/mysqlcerts/client-key.pem
...

Note that the same 'ca-cert.pem' file is used in the server and copied to the client machine.
'server-cert'pem' and 'server-key.pem' are used by the server only.
'client-cert.pem' and 'client-key.pem' are used by the client machine only.

• Using the $sslset() method (available only in Studio 5.1)

The $sslset() method allows these and other SSL attributes to be assigned directly if required.

SessObj.$sslset([cKey, cCert, cCA, cCAPath, cCipher]) is used for establishing a secure connection using SSL. It must be called before $logon().
cKey is the path name to the key file.
cCert is the path name to the certificate file.
cCa is the path name to the certificate authority file.
cCAPath is the path name to a directory that contains trusted SSL CA certificates in pem format.
cCipher is a list of permissible ciphers to use for SSL encryption.
Any unused SSL parameters will be passed as NULL, i.e. thay will take on default values or will be ignored.

Example using the above options:

Do cSess.$sslset('c:/mysqlcerts/client-key.pem','c:/mysqlcerts/client-cert.pem','c:/mysqlcerts/ca-cert.pem') Returns #F
Do cSess.$logon('myserver.com','jeffrey','mypass','session1') Returns #F

If the logon fails, there are two types of error to be aware of:

1045: Access denied for user 'user'@'clientmachine' (using password: YES)

This indicates that the client did not attempt an SSL connection, i.e. that the SSL connect options were not read or not understood by the MySQL client library. This will occur if you are using a DAM that has not been linked with an SSL-enabled client library.
It will also occur if the user has not been configured to connect via SSL or if the MySQL server does not support SSL connections or if SSL connections are disabled or mis-configured.

2026: SSL connection error

This intentionally generic error inidates that the supplied SSL connection options were read and passed to the server, but there was something wrong with the certificates; one or more of the certificates could not be found, were not valid or generated correctly or may have expired. In this situation, the best approach is probably to backtrack and/or revert to the sample certificates provided with the MySQL developer source code distribution.

Other types of error may indicate a more general problem with the connection and you should verify that a standard/non-SSL connection to the MySQL server is possible. Technote TNSQ0012 provides general information on establishing a connection to a MySQL server.

Using a session Pool
To use secure connections with a session pool, the modifications to be made to each new session object must be placed inside the session pool's initialisation method. For example:

Do $extobjects.MYSQLDAM.$objects.MYSQLSESS.$makepool('myPool', 10, '192.168.0.10', 'ssluser', 'xxxxxx', 'Startup_Task.$myinit') Returns #F
OK message {[#ERRTEXT]}

where Startup_Task.$myinit contains similar to:

;; Parameter pRef is of type Object Reference
Do pRef.$connectoption(kMySQLOptReadDefaultFile,'/mysqlcerts/my.cnf') Returns #F
;; ...perform any other modifcations, if required
Do default

Determining whether a connection is secure (Studio 5.1 only)

To determine whether an established connection is using SSL, Studio 5.1 supports an $sslcipher read-only session property. When an SSL connection has been established, $sslcipher contains the name of the SSL cipher being used. Thus, a non-empty value inside this property indicates a secure connection to MySQL.