AlphaDAO - Secure Database Connections

TLS (Transport Layer Security) and SSL (Secure Sockets Layer)
Common TLS/SSL Features
Connection String Parameters for TLS/SSL
SQL::Connection::IsSecurelyConnected
Driver Specific Support for TLS/SSL
TLS/SSL Connections to Amazon RDS (Relational Database Service)
TLS/SSL Connections to Amazon Redshift

TLS (Transport Layer Security) and SSL (Secure Sockets Layer)

Access to remote databases is generally done by connecting to the server using the socket services provided over TCP/IP. These socket connections are inherently insecure, meaning that someone with the right tools can actually see your user name, password and private data being sent from the client to the database server. The first protocol introduced to address this concern was known as SSL or Secure Sockets Layer. SSL has evolved and matured, but along the way, a newer protocol called TLS or Transport Layer Security has taken its place. Although we generally refer to secure connections as SSL connections, the minimum protocol level generally supported is TLS 1.0 (or better TLS 1.1). In this and related documentation We will attempt to refer to the secure connections consistently as TLS/SSL connections.

Each database client that supports TLS/SSL does so in a slightly different way. Microsoft SQL Server clients use the Microsoft Certificate Service and the Microsoft TLS/SSL stack, for example. Most other vendors connect using OpenSSL, but each has a unique approach to setting connection parameters, as we shall see.

Note: Alpha Anywhere integrates SSL support, making setting up and connecting securely to databases easier. If your ODBC or ADO.NET driver supports TLS/SSL, you can use the underlying facility to connect securely. Alpha Anywhere may not be aware that the connection is secure and you will need to manage the connection yourself.

Common TLS/SSL Features

The most common features implemented for TLS/SSL are:

Secure/encrypted communication.

TLS/SSL connections encrypt data as it is transmitted fromthe client to the server.
This is often referred to as data being 'encrypted in transit'.
Verification of the server certificate.
- X509 certificates are issued by a certificate authority who vouches for a certificate by signing it with their private key. Using the public key of the certificate authority, a client can verify that they have 'signed' the server certificate.
- A certificate authority can delegate to another authority. As a result, a certificate from a server may actually have a 'chain' of authorizations that go back to a well-known certificate authority - one that our system trusts. When a server returns a certificate to the client, it will generally include the chain of authorities - one of which should match a certificate you trust.
- You may need to, install one or more certificates as a trusted certificates either in the Microsoft Certificate Store or in a discrete file to verify the chain of authorization.
Verification of the server host name.

Although a certificate may in fact be genuine, it is possible to "spoof" the client by providing a valid certificate from a different host name than was requested. This additional verification compares the host name in the certificate provided by the server with the certificate requested by the client to be sure that the certificate was issued for the host requested.
Verification of the client.

The same process of verification can also be used to authenticate an SSL client. A certificate is installed on the client (along with a private key). The certificate is signed and the signed certificate is sent to the server to confirm the identity of the client.
Client certificates are less common, but are used in more secure installations.

Alpha Anywhere connection strings may support only encryption, or both client and server certificates See the driver by driver explanation below for specific implementations.

Connection String Parameters

Alpha Anywhere connection strings reserve the following connection parameters for managing TLS/SSL Connections. Each Alpha DAO native or extension driver that explicitly supports TLS/SSL will use a different subset of parameters. This is explained in the driver specific discussion below.

A5SSL - can be Y or N. This parameter enables or disables SSL. If A5SSL is set to 'Y', and the connection cannot be established securely, an error is returned.
A5TLSMode - can be Disabled, Allowed, Preferred, Required, Verify-CA, or Verify-Full This parameter sets the mode of TLS support on drivers that support it.
A5TLSCertificateAuthorityFile - The name of a file that contains one or more certificates in PEM format with which to verify the server certificate.
A5TLSTrustedCertificatePath - The path to a folder where separate certificates are stored in individual PEM files. This folder will be searched when resolving certificates. You will generally use either A5TLSCertificateAuthorityFile or A5TLSTrustedCertificatepath.
A5TLSClientKeyFile - The full path and file name of the private key file for the client certificate.
A5TLSClientCertificateFile - The full path and file name of the client certificate stored in PEM format. As explained above, the client key and client certificate are used to verify the identity of the client.
A5TLSCipherList - An optional list of ciphers to be offered to the server when negotiating an encryption cipher. This list is generally optional.
A5TLSCertificateRevocationFile - The name of a file that contains a certificate revocation list in PEM format. These are certificates that are no longer valid.
A5TLSServerCertificateDistinguishedName - Defines the expected distinguished name expected for a server.

For example:

C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=myinstance.myaccounthash.us-east-1.rds.amazonaws.com
A5TrustServerCertificate - Should be set to "N" unless you have installed a self-signed certificate. Setting this value to "Y" bypasses checking the host name on the server certificate and could expose your server to man-in-the-middle attacks. This connection string property is only used by the SQL Server extension driver.

Note: If you are publishing your application to a remote server, you can place certificate files in a folder under your web project called "App_Data\AlphaSoftware\Databases\Certificates" so they will be published and can be found relative to the root of the deployed application folder. This is necessary, for example, when publishing to Alpha Cloud. Be sure to omit the path and set ONLY the certificate file name in the connection string values.

SQL::Connection::IsSecurelyConnected

You can use the IsSecurelyConnected property of SQL::Connection to determine whether a connection is open and securely connected. IsSecurelyConnected will return true (.t.) if a connection for one of the drivers below confirms a secure connection. Note that ODBC drivers or Oracle TNS connections may in fact connect securely, but if Alpha Anywhere does not know this has happened, the value of IsSecurelyConnected may still return false (.f.).

Note: If you request a secure connection, SQL::Connection::Open() will fail if a secure connection can not be established.

You can check an open connection from Xbasic to confirm that it was opened securely by referencing the IsSecurelyConnected property of SQL::Connection

Here is an example of connecting and then checking to see if the connection is secure:

dim cn as SQL::Connection
?cn.Open("{...}")
?cn.IsSecurelyConnected

Driver Specific Support for TLS/SSL

MariaDB
MySQL
Oracle
PostgreSQL
Microsoft SQL Server

MariaDB

Using the Alpha DAO MariaDB native driver, you can to connect securely with MySQL , MariaDB or Aurora (from Amazon) databases.

The Alpha DAO MariaDB native driver is built on MariaDB Connector/C version 2.2.3. The connection string builder dialog includes an additional tab for SSL settings.

Note: Alpha Anywhere ships with libmariadb.dll version 2.2.3. This library has been modified slightly to support SNI (Server Name Indication) and to allow for longer certificate chains when verifying a server certificate. Amazon RDS certificates have a slightly longer chain than is typical.

The Alpha DAO MariaDB native driver supports the following connection string parameters:

A5SSL
A5TLSCertificateAuthorityFile
A5TLSTrustedCertificatePath
A5TLSClientKeyFile
A5TLSClientCertificateFile
A5TLSCertificateRevocationFile
A5TLSCipherList

MySQL

The alpha DAO MySQL native driver has been replaced by an updated driver built on MySQL Connector/C version 6.1.6 in order to support TLS/SSL and other newer features. The connection string builder dialog includes an additional tab for SSL settings.

The Alpha DAO MariaDB native driver supports the following connection string parameters:

A5SSL
A5TLSCertificateAuthorityFile
A5TLSTrustedCertificatePath
A5TLSClientKeyFile
A5TLSClientCertificateFile
A5TLSCertificateRevocationFile
A5TLSCipherList

All of the options for connection strings discussed above are available for MySQL on the SSL/TLS tab of the connection string dialog.

Note: Alpha Anywhere ships with libmysql.dll version 6.1.6. This library has been modified slightly to support disabling IPV6 connections for MySQL servers versions prior to 5.6 which do not default to IPV6 support. MySQL Server version 5.5.3 first introduced (optional) support for IPV6. Attempting to connect via Connector/C 6.1.6 via IPV6 can result in long connect times.

libmysql.dll provides secure communication and will verify a server, but limits the length of the certificate chain, so some environments (such as Amazon RDS) are not suitable for using it. You may want to use the Alpha DAO MariaDB native driver for Amazon RDS MySQL databases.

Note: The old Alpha DAO MySQL native driver is still available for compatibility reasons. It has been renamed MySQLV4. MySQLV4is built with MySQL statically linked in and is compatible with MySQL versions 3.0 and 4.0.

If you are running a release of MySQL on your server prior to MySQL 5.0, you can fall back to the older driver by changing the connection string parameter A5API.

The Alpha DAO MySQLV4 native driver was last updated around 2005 and still uses version 5.0.18 of MySQL Connector/C, so it has no support for SSL, prepared statements and other mainstream features. MySQL version 5.0 was in beta at that time. The current release of MySQL is 5.7 and was released in October of 2015.

Oracle

Oracle TLS/SSL connection definitions without the requirement of a tnsnames.ora file. This can make deploying to an application server easier in cases where a secure connection is required and you do not want to maintain individual TNS names.

Oracle Connection String Dialog

In the Oracle connection string dialog, there is a tab labeled TLS/SSL that includes a checkbox for enabling TLS/SSL.

There is also a text box for optionally entering the distinguished name of the server certificate. Providing this entry results in the clause (SECURITY=SSL_SERVER_CERT_DN=...) being added to the connection description. The distinguished name you provide is used to verify the server certificate.

The connection string dialog sets the following connection string attributes:

A5SSL=Y
A5TLSServerCertificateDistinguishedName='<the value you provide>'

Connecting to Amazon RDS

Note: If you are connecting to an Amazon RDS server, leave the Distinguished Name entry blank. Alpha Anywhere will provide the correct distinguished name automatically.

Be sure to import the Amazon RDS certificate on your application server (the client for Amazon RDS). Alpha Anywhere installs a copy of of the certificate authority file for Amazon RDS (rds-combined-ca-bundle.pem) in the path <installation directory>\SQLDatabases\Certificates\Amazon RDS.

For more information on Amazon RDS SSL support for Oracle see Options for Oracle DB Instances

For help on importing a certificate on Microsoft Windows Servers see Import a Certificate

sqlnet.ora

Important: Oracle requires the configuration file sqlnet.ora for the most common SSL settings. The example entries below show the most common values. Consult Oracle documentation for details.

WALLET_LOCATION = (SOURCE = (METHOD = MCS))
Note: The MCS (Microsoft Certificate Service) option causes Oracle to read certificates from the Windows certificate store
Consult Oracle documentation for other options, including using Oracle wallet files.
SSL_VERSION = 1.0
The version numbers here begin with 3.0 for SSL version 3.0, but move to 1.0 and 1.1 for TLS 1.0 and TLS 1.1 respectively.
SSL_CLIENT_AUTHENTICATION = FALSE
Setting client authentication to false (a required entry) means that client's will not need to authenticate with a client side certificate.
SSL_SERVER_DN_MATCH = Yes
You can disable distinguished name matching, but this is discouraged, as it leaves room for spoofing attacks.
SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_256_CBC_SHA)
This entry sets the allowable ciphers to be negotiated by the TLS/SSL layer.

Notes:

The TLS/SSL port will NOT be 1521 as Oracle requires multiple ports. The TLS/SSL port is typically 2484, but this is configurable.
If you select TLS/SSL (A5SSL=Y) and a connection cannot be established securely, SQL::Connection::Open will fail.
If you do not select SSL or do not provide a distinguished name, no value will be added to the connection string.
A new property of SQL::Connection objects called IsSecurelyConnected will return true (.t.) if an SSL connection has been established.

PostgreSQL

The PostgreSQL driver for Alpha Anywhere currently uses the 32-bit ODBC driver for PostgreSQL. You can install the standard distribution of the 32-bit PostgreSQL ODBC driver. The Alpha Anywhere installation includes a modified version of the PostgreSQL 9.5.3 ODBC driver to enable overriding file based SSL parameters (sslcert, sslkey, sslrootcert, and sslcrl) without the need for setting environment variables.

The installer for this modified driver is copied to the folder <install directory>\SQLDatabases\ClientInstallers\PostgreSQL when Alpha Anywhere is installed. Look for the file psqlodbc_x86.msi in this folder

Connection Strings

The Alpha DAO PostgreSQL extension driver supports the following connection string parameters:

A5SSL
A5TLSMode
A5TLSCertificateAuthorityFile
A5TLSClientKeyFile
A5TLSClientCertificateFile
A5TLSCertificateRevocationFile

The PostgreSQL Connection String Dialog

The Alpha DAO PostgreSQL extension driver connection string dialog has a separate tab entitled TLS/SSL. The checkbox at the top of this page enables TLS/SSL (setting the parameter A5SSL to 'Y'). You can also select the level of security you want for your TLS/SSL connection and provide the names of certificate files to authenticate the client and the server.

For most SSL connections, simply checking the box labeled Use TLS/SSL is sufficient to insure an encrypted exchange of data. It does not, however, protect you from certain types of attacks where a server intercepts your initial connection requests. For the most complete protection you will want to select a TLS mode of Verify-Full, as explained below.

If you select a mode of "Disable" a TLS/SSL connections will not be allowed.
If you select a mode of "Allow" a TLS/SSL connection will be allowed if the server requires it.
If you select a mode of "Prefer" a TLS/SSL connection will be made if the server supports it.
The default TLS mode is "Require", which guarantees an encrypted connection.
If you select a mode of "Verify-CA", the server certificate will be verified against a certificate authority that you provide. You do so by including a file containing certificates (including intermediate files) required to validate the server certificate. Set the Certificate Authority File text box to the full path of the file.
If you select a mode of "Verify-Full", the certificate authority verification above will be performed. The host name of the server will also be matched against the name of the server you provided for your connection.

You can run the installer for the PostgreSQL ODBC driver with modifications by clicking the button on the TLS/SSL tab page of the PostgreSQL connection string dialog entitled Install ODBC Driver.

Microsoft SQL Server

Client Configuration

The Alpha Anywhere SQL Server extension driver supports TLS/SSL connections using the underlying facility of the SQL Server native client driver.

The Alpha DAO SQL Sever extension driver supports the following connection string parameters:

A5SSL=Y

The Alpha DAO SQL Server extension driver connection string dialog has a single checkbox for TLS/SSL support entitled Encrypt. Checking this box sets the A5SSL connection string parameter to 'Y', enabling TLS/SSL.
A5TrustServerCertificate=N

Setting this value to "Y" causes the client to bypass checking for a matching server name in the certificate returned by the server. This should only be done if you are testing with a self-signed certificate. Setting the value to "Y" can expose your server to man-in-the-middle attacks.

Note: Update your connection string to use the actual fully qualified DNS name for the server property. Using the IP address will interfere with validating the certificate presented by the server during TLS negotiation.

Install any certificate authority files as trusted certificates in the Windows Certificate Store on your client machine. The Microsoft SQL Server native client will find them there. For help on importing a certificate on Microsoft Windows Servers see Import a Certificate

Server Configuration

In order to maximize the security of connections to your SQL Server database, you will want to purchase a TLS (SSL) certificate for your server and configure SQL Server to recognize it. The instructions below describe the steps required to install a certificate. This is not comprehensive documentation. Please see the Microsoft documentation for SQL Server for detailed instructions.

Set the host and domain names on your server machine in the proper registry entries for SQL Server. Set the values Hostname and Domain for the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to the hostname and the domain name respectively.

Note: Be sure that the name of your computer matches the host name.
Purchase a TLS(SSL) certificate for your server.

Note: This certificate must meet the following criteria:
- Use a fully qualified DNS name; not just a host name.
- Include Server Authentication in the Enhanced Key Usage property.
- Have a KeySpec option of AT_KEYEXCHANGE.
- The KEY_USAGE property should include CERT_KEY_ENCIPHERMENT_KEY_USAGE.
Install the certificate along with the private key on the machine where your server is installed. for detailed instructions on installing your certificate, see Installing a Certificate on a Single Server

Note: Grant the user account under which the SQL Server service is running permission to read the certificate and the private key while you are installing it.
Select the installed certificate in the SQL Server configuration dialog.

Note: If you have purchased a wild card certificate, you may not see the certificate in the SQL Server configuration manager.
1. You will need to open the certificate in the certificate manager and find the thumbprint attribute.
2. Edit the thumbprint to remove all of the spaces between values. Make sure there are no trailing spaces or carriage returns.
3. Set the Certificate value of the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib in the registry to the edited thumbprint value.
In the SQL Server configuration manager, under Protocols, select ForceEncryption and set it to Yes.
Restart the SQL Server service.
You can verify that the server is secure by executing the following SQL in SQL Server Management Studio or Alpha Anwyhere

Note: The user running the query must have VIEW SERVER_STATE permissions.

SELECT session_id, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections

The encrypt_option column should contain the value TRUE

You can find more information on installing a TLS certificate and configuring SQL Server to use it at the following links:

TLS/SSL Connections to Amazon RDS (Relational Database Service)

Using Amazon RDS (Relational Database Service), you can create a managed instance for any of the following databases:

Amazon Aurora
MariaDB
MySQL
Oracle
PostgreSQL
Microsoft SQL Server

In order to secure communication to these databases, Amazon generates a certificate for the database and instance that is presented by the server when a secure link is requested.

Alpha Anywhere installs a copy of of the certificate authority files for Amazon RDS in with the path <installation directory>\SQLDatabases\Certificates\Amazon RDS.

We recommend the following when attempting to communicate securely with an Amazon RDS instance:

Amazon Aurora - Use the MariaDB extension driver in Alpha Anywhere.

Check the Use TLS/SSL checkbox on the TLS/SSL tab of the connection string dialog.

You do not need to set the certificate authority file name and path. Alpha Anywhere will find the PEM file automatically under the installation directory when it recognizes and Amazon RDS DNS name.
MariaDB - Use the MariaDB extension driver in Alpha Anywhere.

Check the Use TLS/SSL checkbox on the TLS/SSL tab of the connection string dialog.

You do not need to set the certificate authority file name and path. Alpha Anywhere will find the PEM file automatically under the installation directory when it recognizes and Amazon RDS DNS name.
MySQL - Use the MariaDB extension driver in Alpha Anywhere.

Check the Use TLS/SSL checkbox on the TLS/SSL tab of the connection string dialog.

You do not need to set the certificate authority file name and path. Alpha Anywhere will find the PEM file automatically under the installation directory when it recognizes and Amazon RDS DNS name. Note: There is an issue with the MySQL Connector/C 6.1.6 library that causes server verification to fail on Amazon RDS MySQL instances. For this reason, we recommend using the MariaDB client driver to connect to Amazon RDS instances.
Oracle - Use the Oracle native driver in Alpha Anywhere.

You have two options for creating an SSL connection:
- Create a TNS connection definition following the Oracle instructions in the Oracle and Amazon documentation and reference that TNS connection from the Alpha DAO connection string. Using this option gives you the flexibility to use Oracle Wallets.
- Create a non-TNS connection string using Alpha DAO Oracle native driver.
  
  To do this, you must:
  - Create or edit your sqlnet.ora file with the setting described above (Oracle Specific Settings).
  - Install the Amazon RDS bundle file in the Windows Certificate Store.
  - Check the TLS/SSL check box on the Alpha DAO Oracle connection string dialog.
    
    Note:The Alpha DAO Oracle native driver will automatically add the SECURITY entry to the oracle connection descriptor when it recognizes an Amazon RDS server host name.
PostgreSQL - Use the PostgreSQL extension driver in Alpha Anywhere.

Just check the Use TLS/SSL box. Alpha Anywhere will provide the certificate authority files and force Verify-Full for the most secure connection.
Microsoft SQL Server - Use the SQL Server extension driver in Alpha Anywhere.

Install the file entitled "rds-combined-ca-bundle.p7b" found under the folder where you installed Alpha anywhere (<install directory>\SQLDatabases\Certificates\AmazonRDS) into the Windows certificate store as a trusted certificate. SQL Server will find the certificate chain there.

TLS/SSL Connections to Amazon Redshift

To connect to Amazon Redshift from Alpha Anywhere, use the PostgreSQL extension driver.

In order to secure communication to Amazon Redshift, Amazon generates a certificate for the cluster that is presented by the server when a secure link is requested.

Alpha Anywhere installs a copy of of the certificate authority files for Amazon Redshift in with the path <installation directory>\SQLDatabases\Certificates\Amazon RDS.

Just check the Use TLS/SSL box on the TLS/SSL page of the connection string dialog. Alpha Anywhere will provide the certificate authority files and force Verify-Full for the most secure connection.

Notes:

SQL::Connection::IsSecurelyConnected will always return false (.f.) when using Amazon Redshift. Redshift does not support the ssl_cipher() function used to determine whether a connection to a PostgreSQL database is secure.
Be sure to create and edit a parameter group for your cluster setting the require_ssl parameter to true. When require_ssl is set to true, insecure connections will be rejected.

Report an issue with this page