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: