Friday, December 8, 2017

Firstworks GIT Migration

I'm fairly well known, among friends at least, for liking to get every last mile out of vehicles, equipment, gear, and just about everything else. Every last mile. The night before last, I discovered that I had gotten every last mile out of Sourceforge's CVS service.

I'd made a couple of changes, run a commit, and bam!

###############################################################################

CVS commits are NOW DISABLED! This was first announced on October 5th, 2017:

  https://sourceforge.net/blog/decommissioning-cvs-for-commits/

This project's data may be converted over to a new SCM. Use the project's
menu to see where the current code is kept:

  https://sourceforge.net/projects/PROJECT_NAME/

To access the CVS data, either use a pserver checkout or an rsync backup.
In the following commands, replace PROJ with the project name and MOD with
the module name:

  cvs -z3 -d:pserver:a.cvs.sourceforge.net:/cvsroot/PROJ co -P MOD

  rsync -aiv a.cvs.sourceforge.net::cvsroot/PROJ/ /dest/dir/

Conversion instructions for svn and git are available here:

  https://sourceforge.net/p/forge/documentation/CVS/

###############################################################################

Ha!

Well, I wasn't too upset. It's not like I didn't see it coming. A few years back I tried to get git working on all the old platforms in the compile farm. That ultimately failed, but I worked out a better solution last month and I've been trying, off and on to get my CVS repos converted to GIT ever since.

Good thing! I needed the experience of all of the failed attempts, and some advice from a buddy of mine to finally get it working today.

So, as of today, all Firstworks projects have been migrated from Sourceforge-hosted CVS to Sourceforge-hosted GIT.

Rudiments is available via:

git clone git://git.code.sf.net/p/rudiments/rudiments

SQL Relay is available via:

git clone git://git.code.sf.net/p/sqlrelay/sqlrelay

RIP CVS. Thanks for the memories!

Tuesday, November 7, 2017

SQL Relay 1.2.0 Release Announcement

Version 1.1.0 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.







ODBC Improvements

The main focus of this release is ODBC improvements.

ODBC is unimaginably complex. To illustrate this point, the ODBC Driver for SQL Relay consists of 11223 lines of code, while the SQL Relay Client Library that it wraps only consists of 8748 lines. The API also provides several different ways do to do the various things, and different apps use different ways. It's a daunting task to implement even the majority of commonly used features, and then there's always that one app that uses some particular feature. As such, the ODBC driver hasn't gotten the attention that it deserves.

This release brings many, many major and minor improvements to the ODBC Driver for SQL Relay. It is especially improved when used with an ODBC backend, and even more so when using ODBC on the backend to access MS SQL Server, a very common use case.

The improvements are far too numerous (and in most cases, subtle) to describe here. If you had an ODBC app that was having trouble with SQL Relay, try it again.

Windows Improvements

There were basically build problems with older versions of SQL Relay that prevented it from running on some systems, and prevented it from running correctly on others.

The vast majority of these problems have been vetted and solved.

As with ODBC, if you had a platform that had trouble running SQL Relay, try it again.

32-bit Output Bind Length

This release also remedies a significant oversight. On the client-side, and in the protocol, input and output bind variables have always had a 32-bit length. Until this release, though, due to an oversight, in the server API, output bind variables had 16-bit lengths. This basically made it impossible to fetch a string longer than 32678 bytes from the database. Some databases limit varchar fields to this length, so it's not a problem with those databases, but it created significant problems in some distinct cases.

This oversight has been remedied. In fact, this is the tiny, but significant, ABI-breaking change that caused the bump to 1.2.0 from 1.1.0.

Bug Fixes and Improvements

This release also includes the standard array of random bug fixes and minor improvements. Full Changelog follows.

      fixed subtle error in sqlr-import that could cause it to skip empty/NULL fields
      implemented getCurrentDatabase for odbc connections
      implemented selectDatabaseQuery for odbc connections
      added NCHAR, NVARCHAR, NTEXT, XML, and DATETIMEOFFSET types, updated odbc connection module to recognize them
      added --with-windows-version to configure.vbs
      enabled "liveconnection" test for "Lost connection to MySQL server during query" error
      made server-side string outputBind() method's valuesize 32-bits
      added missing mapColumn() call to sqlrservercontroller::getField()
      fixed var directories in Windows deployment projects
      added run-as-Admin requirement to Windows installer
      implemented ODBC SQLProcedures, SQLProcedureColumns, SQLGetTypeInfo, SQLPrimaryKeys, and SQLStatistics, though currently they only work when using the odbc connection module
      implemented ODBC SQLTables to get schema and table type lists, though currently only works when using the odbc connection module
      ODBC SQLProcedures, SQLProcedureColumns, SQLColumns, SQLTables, SQLGetTypeInfo, SQLPrimaryKeys, and SQLStatistics properly set the error now if there was one
      ODBC SQLError properly cycles through the error records now
      updated ODBC SQLGetInfo(SQL_DRIVER_ODBC_VER) to return a value corresponding to the value set by SQLSetAttrInfo(SQL_ATTR_ODBC_VERSION)
      fixed string outputBind() signature in odbc connection module
      fixed a couple of short -> int16_t/uint16_t errors in odbc and mysql connection modules
      ODBC SQLBindParameter(in/out) calls outputBind() now, to work with apps like Delphi which broadly use in/out for out parameters
      refactored odbc connection module's methods to get object lists to support dot-separated object identifiers
      various odbc driver improvements
      updated odbc driver to interpret SQL_C_(X)LONG types as (u)int32_t's rather than long's
      updated odbc connection module to use SQL_C_SBIGINT/SQL_BIGINT for integer binds, rather than SQL_C_LONG/SQL_INTEGER
      fixed uninitialized null/blob flags in sqlrclientprotocol::returnRow()
      various variables are now properly typed (SQLLEN vs. SQLINTEGER) in the odbc connection module
      added "divider on/off" command to sqlrsh
      fixed several cases where microseconds were represented by an int16_t made them all int32_t's
      parsing/generation of fractional seconds pays attention to length of value/format-string now
      fixed a bug that caused fractional seconds to be dropped sometimes
      odbc lazy-fetches now
      odbc driver fakes SQLBindParameter with data-at-exec by deferring the execute and buffering the data locally now
      bumped odbc MAX_COLUMN_COUNT to 384
      added a mars=yes/no flag to enable MS SQL Server MARS when using the odbc driver for MS SQL Server with the odbc connection module
      ODBC SQLGetInfo(SQL_USER_NAME) gets the schema from the backend when used with the odbc connection module now
      increased listen backlogs to 128 from 15/5
      normalize translation doesn't remove spaces around _'s any more
      implemented output bind null indicators properly for db2/informix
      odbc connection supports dynamic maxcolumncount/maxfieldlength now
      fixed but that caused the results of non-tree-based query translations to be concatenated
      updated odbc connection to fetch blob columns in chunks and not be bound by maxitembuffersize
      fixed a bug that could cause a buffer overrun when using maxitembuffersize with odbc, db2, and informix
      configure script finagles the multiarch dir for platforms where gcc -print-multiarch doesn't return anything, but the multiarch dir is necessary to find python3
      configure script can find php-7 on openbsd now
      node is used to run node-gyp.js now
      configure script looks for /etc/php7 now
      configure script looks for node-gyp under /usr/lib*/node_modules/nmp[6789] now
      php pdo api uses zend_long in place of ssize_t now
      fixed uninitialized ncols in mysql connection module

Rudiments 1.0.6 Release Announcement

Version 1.0.6 of Rudiments, the C++ class library for developing systems and applications, is now available.


This is mainly a bug-fix release, and most of the bugs were on older Windows systems.

ChangeLog follows:

  • fixed url user/password parsing
  • added setValues() methods to dictionary class
  • added --with-windows-version to configure.vbs
  • added definition for va_copy for older Windows
  • added some GSS/TLS ifdefs for older Windows
  • added HCRYPTPROV_OR_NCRYPT_KEY_HANDLE typedef for older Windows
  • added run-as-Admin requirement to Windows installer
  • implemented process::getEffectiveUser/GroupId to just return getUser/GroupId on Windows
  • SSIZE_MAX in the filedescriptor class is more accurate on Windows now
  • filedescriptor limits buffer sizes to SSIZE_MAX
  • fixed GNU strerror_r usage
  • worked around strerror_r bugs in older freebsd
  • fixed clobbering of errno in filedescriptor write/printf
  • prompt class assigns result of ftruncate for platforms where it's declared with attribute warn_unused_result

Friday, August 4, 2017

Visual Studio Missing "Open Project/Solution"

Background

A very weird thing happened to me the other day...

I have a client that was having trouble running SQL Relay to run on Windows 7 x86. After getting a Windows 7 x86 VM working, I ran into some of the same problems.

Hmm...

Well, my x86 packages are built on Windows 8.1, with the correct switches for Windows 7 compatibility, but who knows, maybe I should just build it on Windows 7 to see if I get better results.

The Problem...

So, I installed Visual Studio 2013 Community, which is what I use on Windows 8.1, and got everything to compile and run. But, to build packages, I needed to use Visual Studio Pro 2010, because it actually supports building deployment packages, unlike the free versions.

No problem, I own a copy of that, so I installed it too.

Post-install, the UI looked weird and the File menu had an "Open File..." option but no Open Project/Solution, or New Project/Solution, or anything like it. Opening the deployment project with Open File just opened the file itself in a text editor.

What the heck?

The web suggested Tools -> Import and Export Settings... -> Reset All Settings.

This did nothing.

The Solution...

I ultimately discovered that installing VS 2013 first had caused the problem.

To uninstall 2013 completely, I used:


vs_community.exe /uninstall /force

...from the install DVD image.

To uninstall VS 2010 and start fresh, I used the "Uninstall a program" link in the Control Panel. I also removed the various things that I'd seen the VS 2010 installer install the first time, like Dotfuscator, Tools for Office Runtime, various SQL Server-related things, and Silverlight. Unfortunately I don't have an exact list of them now, and looking at it now, it's not clear what was installed by VS 2010 and what was installed alongside something else. It's also not clear how important removing them is, so it might not matter. I just did it to be thorough.

After that, I re-installed VS 2010 and the UI looked correct. Installing VS 2013 afterward didn't hose 2010 either.

All of this took hours and hours.

Why...

Why does it matter what order you install them in?

After much digging...

It appears that every time you install a new version of VS, it attempts to emulate your UI settings from any existing version. This works OK when upgrading from an older version of VS because it already existed prior to the invention of the newer version. If you already have a newer version installed though, the old version might get confused trying to make sense of its newfangled configuration. Apparently this happens when 2010 Pro tries to read the settings from 2013 Community.

Ha!

Well, at least I'll know to avoid that in the future.

Sketchy Windows 7 x86 Activation

Man!

I bought a copy of Windows 7 Home Premium (32-bit) from a guy on eBay, and what I got in the mail was nothing like I'd hoped I'd get. I'd hoped for a new, in-the-box copy. Instead, I got a disc that said something like "Intended for distribution with a refurbished PC" on it, and an obviously peeled-off product key stuck to the back of the sleeve.

Great.

In hindsight, it was probably foolish to expect more for $27.99. Would it even work?

Well, kind-of. I was able to install it in VMware and the key worked at installation time, but when I tried to Activate Windows, it failed with an error saying that Microsoft had blocked that key!

Wow.

So, maybe the guy bought a refurbished laptop which eventually died, and he resold the disc that came with it, along with the product key that he peeled off of the laptop???

Conceptually, that seems like the kind of thing that something one ought to be able to do. But, of course, logistically, if Windows 7 had already been activated on the laptop, then after it died, there's no good way for Microsoft to know that, or any process that I know of to deactivate a key, and it ends up being up to the new owner to sort it out.

Fortunately, I was able to do so, very easily.

MS has a new (well, new as of August 2017) online chat thing that you can use for customer support at https://support.microsoft.com/en-us/contactus/. I typed in my problem, it gave me a potential solution, which didn't work, and then asked me if it solved the problem. Clicking No gave me the option of chatting with a guy, and the guy was able to get everything going.

It did require a bit of legwork though. I had to take a photo of the product key and a screenshot of the eBay order and put them on the VM's desktop, basically as proof-of-purchase. Then, I had to go through some steps to let him take over the PC. He looked at the images, "convinced his supervisor" that I had legitimately purchased the product, generated a new key, activated Windows with that key, and left me a copy of the key on the desktop.

Excellent!

So, if you find yourself in a similar situation, you may not just be out $27.99. There is hope.

Windows 7 - Get Genuine!

I'm sure this has happened to someone else...

Years ago I bought a cheap laptop from WalMart, an Acer Aspire One 725 - 0802 to be exact, which came with Windows 7 Home Premium. Given how inexpensive it was, I expected to have to replace it a year later, but no, it just kept bumping along until the paint was worn off of almost every key. One day though, it just wouldn't turn on any more.

Between backups and the cloud I was able to move the software and data to a new laptop, but I didn't want the copy of Windows 7 to go to waste, so I did a P2V conversion by pulling out the hard drive, putting it in an external case, dd'ing the drive image, etc. I also saved the product key, just in case.

I ran it in VMware for a while, and then one day the screen turned black and I got "Get Genuine" messages every time I'd try to do anything. I tried to Get Genuine, over and over, but every time it failed, telling me to contact Acer. Attempts to Activate Windows just sent me to a Windows 10 FAQ.

No good!

For a long time, I tolerated the messages, but eventually called customer support to try to sort it out. I explained everything to them, and the fix was remarkably simple.

Open a command prompt and run:


slui 3

Then, enter the product key.

That's all.

At least on my side. What I don't know is whether they had to do something on their side too, to allow it.

Either way though, it was ultimately quite simple. So, if you've run into the same problem, try slui by itself, and if that doesn't work, call customer support and walk through it with them. They don't appear to just outright reject these kinds of activations because of the OEM key.

The only remaining problem was a black background. That was easily fixed by right-clicking on the desktop, selecting Personalize, and selecting the Windows 7 theme.

Good as new!

Wednesday, May 31, 2017

SQL Relay 1.1.0 Release Announcement

Version 1.1.0 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.







Ubuntu Linux and MariaDB Fixes

Probably the most significant improvements in this release are a set of fixes for modern Ubuntu Linux platforms and the MariaDB LGPL client library.

There were some subtle issues that prevented the configure script from detecting various things correctly on Ubuntu Linux 16 and 17, mainly Python 3 and MariaDB. Systemd/sysvinit detection also failed on Ubuntu 16 and 17. These issues are now fixed.

Also, the MariaDB LGPL client library has/had bugs that caused SQL Relay to either crash when attempting to fetch LOB values, and to report an obtuse error when attempting to reuse a statement handle upon which a previous query failed to prepare (eg. because the query was malformed). Workarounds are now in place for both of these issues, and SQL Relay works well with the MariaDB LGPL client now.

MySQL Front-End Modules

Another significant improvement in this release is to the MySQL Front-End Modules. They are a LOT faster now, and now support getting column info after a statement is prepared, but before the statement is executed, which is the standard paradigm for MySQL apps.

This update significantly increases the number of native MySQL apps which can use SQL Relay as a transparent proxy.

If you tried the modules before and had trouble, then you should definitely try them again.

Other new Features and Enhancements

Of course, this release includes various other new features and enhancements.

Versioning

First off, versioning has been switched from Semantic Versioning to "intuitive versioning", as follows:

Given a version number MAJOR.MINOR.PATCH, increment the:

  • 1. MAJOR when you make very significant changes and set MINOR and PATCH to 0
  • 2. MINOR when you make backwards-incompatible changes and set PATCH to 0
  • 3. PATCH when you make backwards-compatible changes

Strict Semantic Versioning would have required this release to be 2.0.0, and the changes just weren't intuitively significant enough to warrant that. So, we're going with this versioning scheme for now.

Python 3

The configure script now prefers Python 3 to Python 2. So, if you have both installed, Python 3 will be detected.

Router Updates

The query router (dbase="router") supports a fetchatonce connect-string option now.

Two new router modules have been included in this release too: clientinfolist, and usedatabase.

The clientinfolist module routes sessions based on the free-form value sent to the server by the sqlrconnection::setClientInfo() method, available in the native SQL Relay APIs. See the configuration guide for more detail.

The usedatabase module allows you to switch between databases hosted by multiple instances with a "use database" command. See the configuration guide for more detail.

PostgreSQL Performance Improvement

The PostgreSQL database connection module (dbase="postgresql") uses async functions and fetches one row at a time now, if possible. This yields a performance improvement of about 10% or so.

Connect-String Improvements

The maxselectlist and maxitembuffersize database connect-string parameters have been renamed to maxcolumncount and maxfieldlength to make their purpose more understandable, though the old parameter names still work too.

The MySQL database connection module supports sslmode, tlsversion, sslcrl, and sslcrlpath connect-string options. Though, some of these are only supported by the newest versions of the MySQL client library.

Filter and Trigger Module Improvements

Filter modules can be configured to run before or after the query translation modules have been run now. Trigger modules default to "after" if not specified now.

Selecting and Reporitng the Current Database

Selecting a different database at runtime works with PostgreSQL, Firebird, MDBTools, and SQLite now.

Firebird, MDBTools, and SQLite database connection modules can also report the current database now.

Clean-up

A Rudiments version requirement has been added to the configure script. If it finds a version of the Rudiments library that is too old to use, it will fail now.

The libsqlrclient and libsqlrserver libraries have distinct SONAME_VERSION_INFO now. Eg. libsqlrclient is at 4.0.0 and libsqlrserver is at 5.0.0.

Bug Fixes

The FreeTDS database connection module was apparently concatenating error messages over and over. That's fixed now.

The PDO driver was missing the "quote" method. It's now implemented.

There was a subtle login/ping bug in the router that could cause various obscure problems. That's fixed.

A memory leak was fixed in the sqlrclient protocol module.

The MySQL database connection module checks for valid statement before resetting it now. This fixes potential crashes with certain versions of the MySQL/MariaDB client library.

When faketransactionblocks=yes, there was a bug that could cause begin/commit/rollback to try to send nonexistent column info to the client sometimes. It's not clear what actual problems this caused, but it wasn't correct, and it's fixed now.

The "client info" is properly reset at end-of-session now.

The configure script can find openjdk-1.9.0 correctly now.

PHP ini files are automatically installed on FreeBSD now.

Modern versions of the GNU linker embed the RUNPATH flag rather than RPATH when -R is used. The runtime loader doesn't search the RUNPATH for dependent libraries though, only libraries that were directly linked in. This prevented the Oracle, SAP, DB2, and Informix libs from being loaded unless their installation paths were in the LD_LIBRARY_PATH. To work around this, the -Wl,--disable-new-dtags linker option is used (if supported) now when linking Oracle, SAP, DB2, and Informix database connection modules.

The configure script was ignoring --with-mysql-prefix, and --enable-mysql-rpath didn't work. Both of those bugs are now fixed.

Rudiments 1.0.5 Release Announcement

Version 1.0.5 of Rudiments, the C++ class library for developing systems and applications, is now available.


This release mainly adds support for storing TLS certs/keys in the Windows Certificate Store and for storing keys in separate files from certificates on non-Windows platforms.

A few bugs were fixed too.

ChangeLog follows:

  • added asprintf()/vasprintf() analogs to charstring
  • added #define PSAPI_VERSION 1 to filesystem.cpp so GetMappedFileName will be correctly mapped to K32GetMappedFileName on 32-bit Windows 7 and Server 2008
  • added EAGAIN handling to filedescriptor::safeWrite()
  • added IPC_PRIVATE support to semaphores on windows
  • added configure test for char16_t type
  • added support for separate TLS cert/key files on non-Windows platforms
  • added missing byte-order translation for read/write of signed integers
  • added clearNativeError and setNativeError methods to error class
  • added support for loading certificates/keys from windows cert stores for certs and ca certs to the tls class

Friday, April 7, 2017

TLS/SSL Encryption with MS SQL Server and FreeTDS

Introduction

This tutorial walks through enabling TLS/SSL encryption between an application and an Microsoft SQL Server 2014 database, including basic encryption, certificate validation, and common name validation.

Unfortunately MS SQL Server doesn't support mutual authentication via TLS/SSL; that is, the database cannot validate the application's certficate and common name. This is just as well though, as there is also no obvious way to configure FreeTDS to use a private key and certificate on the client-side.


Assumptions

For the purposes of this tutorial, the following assumptions are made:

  • An MS SQL Server 2014 database is running on a Windows system named db.firstworks.com.
  • The database contains an instance named EXAMPLEDB which is accessible using exampleuser/examplepass credentials.
  • An application which accesses the database is installed on a linux server named app.firstworks.com and currently configured to access the database without encryption.
  • The application uses FreeTDS to access the database.
  • A private key and certificate for the database don't currently exist, but will be created, and will be named db.key and db.crt.
  • A certificate chain for the certificate authority which will sign db.crt exists, named ca.pem.

Basic Encryption

First, we will configure basic TLS/SSL encryption of the communications between the database and application server.


Database Server Configuration

By default, MS SQL Server 2015 supports encryption, and enables it if the application so requests. We can configure the database to require encrypted connections, though, as follows.

  • Open the Start menu.
  • Select the Microsoft SQL Server 2014 program group.
  • Select the SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-Click Protocols for EXAMPLEDB and select Properties.
  • Select the Flags Tab.
  • Change Force Encryption to Yes.
  • Click OK.
  • Click OK.
  • Exit the SQL Server Configuration Manager.

Restart the database server.

  • Press Windows-key-R.
  • Type "services.msc" and hit return.
  • Scroll down to SQL Server (EXAMPLEDB) and select it.
  • Click Restart the service.

That's all on the database server.


Application Server Configuration

MS SQL Server speaks versions 7.0 - 7.4 of the TDS (Tabular Data Stream) protocol. Version 7.0 doesn't support encryption, but versions 7.1 and higher do.

FreeTDS requests encryption when it's configured to use TDS version 7.1 or higher. Just to be sure, though, we can also configure the connection to require encryption, rather than just request it.

Edit the FreeTDS configuration file (usually /etc/freetds.conf), find the server entry that you use to connect to the EXAMPLEDB, verify that the TDS version is set to 7.1 or higher, and add a line to require encryption, as follows:

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require

Note that in this example, "tds version" is set to 7.3. I have had trouble with 7.4 resulting in "Unexpected EOF from the server" errors. Your mileage may vary.

That's all on the application server.


Testing With tsql

Connect to the database from the application server using the FreeTDS-supplied tsql program as follows:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

Unfortunately there is no easy way to verify that the connection is encrypted. If you access the database as the "sa" user, then you can run:

1> select encrypt_option from sys.dm_exec_connections where session_id = @@SPID
2> go
encrypt_option
TRUE
(1 row affected)

But non-admin users don't typically have access to sys.dm_exec_connections.

Another way to verify that the connection is encrypted is to run tsql with TDSDUMP enabled:

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

If you see lines in the output like:

  REC[0x55a2216316b0]: Decrypted Packet[1] Application Data(23) with length: 421

then the connection is encrypted.

If the connection fails, it will most likely fail with:

  • Error 20017 (severity 9): Unexpected EOF from the server
    • The server didn't like something about the protocol and hung up.
    • Verify that you are using tds version 7.1 - 7.3 in your freetds.conf server entry.

Updating Your Application

Your application shouldn't actually require any changes. As long as it is configured to use the EXAMPLEDB server entry, all communications between it and the database will be encrypted.


Adding Database Certificate Validation

So far, we have encrypted communications between the application and the database, but we can make the application more secure by installing a certificate on the database server, and configuring the application to validate the certificate.


Database Server Configuration

The database server must have has host name "db" and primary DNS suffix "firstworks.com". But, Windows servers are commonly configured without a primary DNS suffix.

To set the primary DNS suffix:

  • Press Windows-key-R.
  • Type "control panel" and hit return.
  • Click System and Security.
  • Click System.
  • Under "Computer name, domain name, and workgroup settings", click Change settings.
  • Next to "To rename this computer or change its domain or workgroup, click Change", click Change.
  • Click More.
  • Under "Primary DNS suffix of this computer" enter "firstworks.com".
  • Click OK.
  • Click OK.
  • Click OK.
  • Reboot the database server.

Generate the certificate for the database server.

The certificate can be generated by a well-known certificate authority, generated by an in-house certificate authority, or can even be self-signed.

In any case, MS SQL Server places some specific requirements on the certificate:

  • The common name MUST match the fully qualified domain name of the database server. In this case: db.firstworks.com.
  • The Enhanced Key Usage property MUST include Server Authentication.
    • In openssl terms, the extendedKeyUsage extension must include serverAuth in the extfile used to generate the certificate signing request.

Combine this certificate, its associated private key, and certificate for the certificate authority that signed it into a PKCS#12 certificate chain file named db.pfk.

There are various ways to do this, but with openssl you can run:

PASSWORD="" openssl pkcs12 -export -passout env:PASSWORD -in db.crt -inkey db.key -certfile ca.crt -out db.pfx

When the pfk is created, it MUST be created with KeySpec set to AT_KEYEXCHANGE. Or, in openssl terms, the -keyext option must be used when creating the pfk file. Actually though, -keyext is the default, so just DON'T create the pfk file using -keysig.


The C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys folder often has the wrong permissions. Verify that both Administrators and Everyone have Full Control. If they don't, then give them both Full Control. You may get an error when you do this, indicating that permissions couldn't be changed on an individual file, but this seems safe to ignore.


Now that the prerequisites are taken care of...


Copy db.pfk to the database server, and install it in the Windows certificate store:

  • Press Windows-key-R.
  • Type "mmc" and hit return.
  • Click File.
  • Click Add/Remove Snap-in.
  • From Available snap-ins, double-click Certificates.
  • Select Computer Account.
  • Click Next.
  • Select Local Computer.
  • Click Finish.
  • Click OK.
  • Expand Certificates (Local Computer).
  • Right-Click Personal.
  • Click All Tasks.
  • Click Import...
  • Click Next.
  • Click Browse.
  • Select All Files *.* from the file-types pulldown.
  • Select db.pfk
  • Click OK.
  • Enter a private key password, if your private key has one.
  • Click Next.
  • Click Next.
  • Click Finish.
  • Exit
  • Don't save settings.

Configure MS SQL Server to use the certificate.

  • Open the Start menu.
  • Select the Microsoft SQL Server 2014 program group.
  • Select the SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-Click Protocols for EXAMPLEDB and select Properties.
  • Select the Certificates Tab.
  • Select db.firstworks.com from the pulldown list.
    • If db.firstworks.com is missing from the list, then either:
      • The common name in the certificate is something other than "db.firstworks.com".
        • The best solution is to generate a new certificate.
      • The database server's host name is something other than "db".
        • See instructions above for setting the host name.
      • The database server's primary DNS suffix is something other than "firstworks.com".
        • See instructions above for setting the primary DNS suffix.
  • Click OK.
  • Click OK.
  • Exit the SQL Server Configuration Manager.

Restart the database server.

  • Press Windows-key-R.
  • Type "services.msc" and hit return.
  • Scroll down to SQL Server (EXAMPLEDB) and select it.
  • Click Restart the service.
    • If the service fails to start, then either:
      • The Enhanced Key Usage property of the certificate is missing or doesn't include Server Authentication.
        • Generate a new certificate with the parameter set correctly.
        • Replace the bad certificate.
        • Restart SQL Server (EXAMPLEDB)
      • C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys has the wrong permissions.
        • See instructions above for setting the permissions.
        • Restart SQL Server (EXAMPLEDB)
  • Exit the Services manager.

That's all on the database server.


Application Server Configuration

Configuring the application server is much simpler.

Copy ca.pem to the application server and install it in a convenient place, like /etc.

FreeTDS will validate the database's certificate if it is provided with a certificate chain to validate it against.

On the application server, edit the FreeTDS configuration file (usually /etc/freetds.conf), find the server entry that you use to connect to the EXAMPLEDB, and add a ca file line, as follows:

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require
 ca file = /etc/ca.pem
 check certificate hostname = no

Note that "check certificate hostname" is set to no. At this point, we're just validating that the database server is a host that we trust. We're not worried about whether it's the exact host we intend to be talking to.

That's all on the application server.


Testing With tsql

Connect to the database from the application server using the FreeTDS-supplied tsql program as follows:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

If the connection fails, it will most likely fail with:

  • Error 20002 (severity 9): Adaptive Server connection failed

Which isn't very helpful. Running tsql with TDSDUMP enabled...

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

...may be slightly more revealing.

It may say:

tls.c:484:handshake failed: Error in the certificate.

That usually means that the ca.pem's certificate authority didn't sign the database's certificate. Double-check it.

If it also says something like this:

tls.c:368:Certificate hostname does not match

Then "check certificate hostname" is probably misspelled.


Adding Database Certificate Common Name Validation

So far, we are encrypting communications between the application and the database and validating the database server's certificate, but we can also make the application more secure by validating the common name in the database server's certificate.

This is actually pretty simple. In fact we had to do additional work to disable it earlier.

On the application server, modify /etc/freetds.conf and set "check certificate hostname" to yes.

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require
 ca file = /etc/ca.pem
 check certificate hostname = yes

Or, optionally just remove the parameter altogether, as checking the certificate's common name is the default behavior.

You can test the configuration using tsql:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

Again, it may fail with the not-so-helpful:

  • Error 20002 (severity 9): Adaptive Server connection failed

Running tsql with TDSDUMP enabled...

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

...might be more revealing.

It may say:

...
tls.c:368:Certificate hostname does not match
...
tls.c:484:handshake failed: Error in the certificate.
...

This means that the common name in the database's certificate didn't match the host parameter in the freetds.conf file.

  • Verify the common name in the freetds.conf file and in the database's certificate.
    • If the host name in the freetds.conf file is wrong then change it and try again.
    • If the common name in the certificate is wrong, then:
      • Ideally the certificate should be regenerated and re-deployed.
      • If that's not an option, then you can add a DNS entry (or /etc/hosts entry) for the name that is in the certificate, and update the freetds.conf file with that name.

Thursday, April 6, 2017

Oracle TLS/SSL Encryption Tutorials

So, you want to secure communications between an application (such as SQL Relay) and an Oracle database with TLS/SSL...

Most TLS/SSL-capable servers are secured by generating a private key, getting a certificate, and adding a few lines to a config file to tell the server the locations of the cert and key files. Of course, if you generate the certificate yourself, then you also have to tell the server about your fake certificate authority. But, for most apps, that's it. Three parameters in a file somewhere: cert, key, and ca.

With Oracle, the idea is similar, but the details are very different.

Rather than storing certificates and keys in files and providing the paths to them in a config file, you store them in an Oracle "wallet" and provide the path to the wallet.

While Oracle wallets are more secure than flat files, the security features introduce some interesting quirks that are worth reviewing before attempting an implementation.

When you're ready to take the plunge, tutorials follow:

Oracle TLS/SSL Encryption Tutorials

So, you want to secure communications between your application and an Oracle database with TLS/SSL...

Most TLS/SSL-capable servers are secured by generating a private key, getting a certificate, and adding a few lines to a config file to tell the server the locations of the cert and key files. Of course, if you generate the certificate yourself, then you also have to tell the server about your fake certificate authority. But, for most apps, that's it. Three parameters in a file somewhere: cert, key, and ca.

With Oracle, the idea is similar, but the details are very different.

Rather than storing certificates and keys in files and providing the paths to them in a config file, you store them in an Oracle "wallet" and provide the path to the wallet.

While Oracle wallets are more secure than flat files, the security features introduce some interesting quirks that are worth reviewing before attempting an implementation.

When you're ready to take the plunge, tutorials follow:

Friday, February 24, 2017

SQL Relay 1.0.1 Release Announcement

Version 1.0.1 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.

This release features lots of small improvements, mainly for issues discovered during attempts to make it compliant with Redhat's packaging guidelines, and for a set of issues reported by users.



Thread Fix

This is fairly important.

There was a long-standing bug in the sqlr-listener process that caused multiple threads to access the same variable, without coordination. The symptom was that the sqlr-listener process might crash, randomly, every few days. The variable was a buffer used to build log/notification messages, so the error could only occur if logging or notifications were configured, but it could happen if there were logger or notifications tags in the config file, even if the modules themselves were disabled. The workaround was to comment out the entire logger and notification tag groups, or to configure the sqlr-listener to be multi-process instead of multi-thread (using sessionhandler="process").

None of that is necessary now though. The bug is fixed. Local variables are used to build the messages in the parts of the sqlr-listener that can be run by multiple threads.

sqlrsh Fixes

You can now hit ctrl-D to abort sqlrsh without generating a segfault. Also, errors are now printed to stderr and if an error occurs (in non-interactive mode) then sqlrsh exits with status 1 instead of 0.

ODBC Bug

1.0.0 featured an ODBC fix which itself created a new bug. Between the two, the first line of the result set could be duplicated or skipped. Both bugs should be fixed now.

PDO on 32-bit Systems

The PHP PDO driver compiles correctly for PHP 7 on 32-bit systems now.

Ruby Updates

Ruby semi-recently consolidated Fixnum and Bignum classes into the Integer class. The Ruby API has been updated to support this change.

Router Sequence Fix

There was a bug that made it necessary to start all of the instances that a router instance would route to prior to starting the router itself. Eg. if you had 2 instances: routermaster and routerslave, and a third instance: router, then routermaster and routerslave had to be started prior to router. This wouldn't always happen if the instances were configured to start at boot though. Now, they can be started in any order.

Normalize Translation

Fixed a bug in the "normalize" query translation that caused it to remove spaces before unary ! and - when they followed a keyword. For example, it will now return "select !a,!b from mytable" instead of "select!a,!b from mytable".

Upates to the Drop-in Replacement Libraries

As recommended by rpmlint, the .so links aren't installed for these libraries any more. Now, the major version must be included in the LD_PRELOAD command. Eg: LD_PRELOAD=/usr/local/firstworks/lib/libmysql51sqlrelay.so.4

The Drop-in Replacement Library for MySQL now pays attention to the SQLR_MYSQL_MAP_DATE_TO_DATETIME environment variable. If it's set to "yes" then DATE types are mapped to DATETIME. This is useful if you're mapping a MySQL app to an Oracle database, have NLS_DATE_FORMAT set to include the time, and your app truncates the time if the type of the column is DATE instead of DATETIME.

configure Script Options

The --disable-wall and --disable-werror options have been added to the configure script to work around quirks on systems who's header files generate warnings or particular errors.

The --with-abs-max-connections option has been added to set the absolute maximum number of connections that can be configured at compile time. It defaults to 4096, up from 512 in previous releases.

The --with-fortify option has been added to enable -O -D_FORTIFY_SOURCE=2 -fstack-protector-strong compiler options.

The --enable-oracle-at-runtime (and similar options for sap, db2, and informix) have been added to enable runtime loading of the database libraries rather than linking them in at compile time.

Error Messages

Various error messages have been improved to suggest solutions.

Erlang Reference

An API reference exists for the Erlang API now.

Cache Manager

The mysterious sqlr-cachemanager process isn't enabled by default any more. This process manages the client-side result set cache, only needs to be run on SQL Relay clients, and only if an app caches result sets. As such, it is installed when building from source, but isn't enabled by default. Also, in the RPM distribution it has been split out into its own RPM. The docs have also been updated to explain when to run the process.

"run", "log", and "cache" directories and file names

This is more of an internal change, but...

In the past there were various directories under .../var/sqlrelay/tmp that contained files used at runtime by the various instances of SQL Relay. These have been consolidated into .../var/run/sqlrelay. The "log" directory has been changed from .../var/sqlrelay/log to .../var/log/sqlrelay. The "cache" directory has been changed from .../var/sqlrelay/cache to .../var/cache/sqlrelay. These changes were motivated by Redhat packaging guidelines, but I think they have roots in the Linux Standard Base.

Sockets created by the sqlr-listener process to communicate with the sqlr-connection processes now have .sock extensions, and 700 permissions rather than erroneous 711 permissions). Files used by ftok() to get IPC ids now have .ipc extensions. "Up-down" files used to indicate whether the database is up now have .up extensions. The sockseq file is no longer used. Instead, the file names of sockets used to resume suspended sessions are based on the process id of the sqlr-connection process.

Full Changelog follows:

  • updated sqlr-bench to create "db"-bench.png
  • added edoc-generated API docs for the Erlang API
  • updated some module-load error verbage
  • added initial support for loading Oracle, SAP/Sybase, DB2, and Informix libraries on-demand at runtime
  • overhauled spec file
  • fixed PDO driver for PHP 7 on 32-bit platforms
  • added permissions to all open() with O_CREAT calls
  • added --with-fortify configure option
  • fixed a segfault on ctrl-D in sqlrsh fixed uncoordinated access to shared debugstr among listener threads by using local instances instead
  • added ping-loop to router connection module's logIn() method to manage cases where routed-to instances are started after the router
  • moved .../var/sqlrelay/tmp/*/* to .../var/run/sqlrelay
  • added .sock extension to sockets, .ipc extension to ipc key files, .pid extension to pid files, and .up extension to files which indicate that the db is up
  • changed mode of listener-connection sockets to 700 from 711
  • moved .../var/sqlrelay/log to .../var/log/sqlrelay moved .../var/sqlrelay/cache to .../var/cache/sqlrelay
  • libmysql*sqlrelay.so and libpqsqlrelay.so are no longer installed
  • replaced sockseq-generated unix socket with pid-based unix socket
  • updated ruby api to support unification of Fixnum and Bignum classes into Integer class
  • added --with-abs-max-connections configure script parameter
  • increased default absolute max connections to 4096 (from 512)
  • updated "can't open ... file" errors to mention user/group
  • added SQLR_MYSQL_MAP_DATE_TO_DATETIME option to mysql drop-in replacement library
  • fixed normalize translation not to remove spaces before unary - and !
  • updated sqlrsh to set error code correctly and write errors to stderr
  • sqlr-cachemanager is no longer enabled at boot by default
  • fixed another subtle bug in the ODBC driver that caused the first row to be duplicated if columns were not bound
  • added --disable-wall and --disable-werror configure options

Rudiments 1.0.4 Release Announcement

Version 1.0.4 of Rudiments, the C++ class library for developing systems and applications, is now available.


This release primarily replaces the dependency on readline with a dependency on libedit. I'd used readline directly in SQL Relay for years and one day decided it would be better if it were pushed up into Rudiments as a prompt class. However, I didn't notice that readline was GPL rather than LGPL, so using rudiments with readline wasn't necessarily GPL compliant. Actually, I guess it would only not be compliant if the application it was ultimately linked into wasn't a GPL application... But, to eschew any such doubts, Rudiments now uses libedit (which has a BSD license) instead of readline.

Some other, extremely obscure, features were added too.

Full Changelog follows.

  • added support for EOT to codetree
  • added loop-avoidance to xmldomevents class
  • added alias support to codetree class
  • replaced readline with libedit
  • included implementation of history_truncate_file for versions of libedit that don't provide it
  • added --disable-wall and --disable-werror configure options

Sunday, January 22, 2017

Rudiments 1.0.3 Release Announcement

Version 1.0.3 of Rudiments, the C++ class library for developing systems and applications, is now available.


This release mainly fixes some packaging issues, and a really subtle issues in the prompt class. The file::open() method also fails with EINVAL if it is used to create a file but no permissions are given, which would otherwise cause the method to crash on systems where FORTIFY_SOURCE is used. No pressing need to update unless these issues sound familiar.