DataArchitecture

CONFIGURE SQLYOG TO CONNECT TO A DATABASE THROUGH AN SSH TUNNEL

KEYWORDS
sqlyog, sql yog, ssh, encryption, vpn alternative, ssh tunnel, mariadb, mysql, authentication

SUMMARY
Outside connections to databases can be a real pain as databases should not be directly addressed by the general internet. Databases sit behind a firewall and are accessed normally through web browsers that connect to web servers, which in turn connect to app servers, which then connect to databases.

To access databases from the general internet, one must bypass the firewall. A couple of ways of doing that (and there are other ways) are to use a VPN (Virtual Private Network) or use an SSH (Secure SHell) Tunnel. Tunnel implies a secure connection.

Both of these methods bypass the firewall and encrypts the connection to insure the connection remains secure. Both methods take some upfront setup. VPN in installing the software and the VPN keys. SSH is generating the SSH keys.

The scope here is how to get started with SSH within SQLyog. The SSH Tunnel is really just the first of two connections to the database. The first connection is a network connection from the client side to the SSH Relay Host (SSH Tunnel Host, or several other names) to bypass the firewall. Then there is a normal database connection from the SSH Relay Host to the database server/s.

GENERATE SSH KEYS

Download Putty

Right click on on it in the windows launch tray, then select ‘Run PuTTYgen’

Select ‘Parameters’, ‘Type of key to generate’ of ‘SSH-2 RSA’. 2048 bits in the generated key. Click the [Generate] button.

Browse to a directory to permanently save your keys. Right click and select ‘New Text File’,  rename the file to ‘public_key.pem’, double click on the file; if necessary, ‘select program from a list  of installed programs, and select ‘notepad’. On the PuTTY Key Generator screen, right click in the ‘Public key for pasting…’,  choose ‘select all’, then right click again and choose ‘copy’, then paste that into (notepad) public_key.pem. File/Save, Close notepad.

Enter a pass phrase twice, Click [Save public key], enter the proper directory and name (public_key.ppk). Click [Save private key], enter the proper directory and name (private_key.ppk)

CONFIGURE SSH TUNNEL SERVER

• This is as exercise outside the scope of this blog.

TEST SSH TUNNEL

• To test the ssh tunnel, launch putty
• Enter the SSH Host Address in the Host Name (or IP address) field
• In the Connection/Data screen, add an Auto-login username.
• In the Connection/SSH/Auth screen, add a path to Private key file for authentication that references your private key (.ppk).
• In the Session screen, click [Open]
• You should be logged in after optionally entering the password that encrypts your private key.

SQLYOG CONNECTION

Start SQLyog Choose File/Connect, then click the [New] button in the upper left. Enter a connection name. Click OK. Click the SSH tab, check the ‘Use SSH Tunneling’ chekbox, enter the SSH Host address, the username, SS port (normally 22),  choose the ‘public key’ radio button, enter the pass phrase for you private key (see above), and the path to the private key file (private_key.ppk)

Click the MySQL tab, enter the database host, database username, database password, enter the database port, and the database name to complete the database connection.  Click the [Save] button at the top of the window.

WHICH OPEN SOURCE LICENSE SHOULD I USE

Software licenses is a legal document that spell out what a recipient of software and/or source code can do, and usually waves any and all liability experienced from use. I know of no licenses that guarantee or provide a warranty for software, or accept liability for anything that goes wrong with the use of the software. I have seen some licenses that warrant the media (ie, tape, floppy) or that there are no patents infringed, and the like).

First, let’s look at patents, trademarks, service marks, trade secrets, and copyrights to determine which is the best instrument to use for our open source license.

PATENTS
A patent is granted by the federal government (is the US). It is a property right to an invention (ie, a specific mouse trap) that is given in exchange for making the details of the invention public. There have been some very specific situations where an algorithm represented by software, or a screen display feature implemented in software has been patented. The Supreme Court has injected itself into the eligibility requirements relative to the Patent Act (referred to as judicial overreach) thereby creating the concept that software is an ‘abstract ideas’ idea which is not patentable.

Note, some lower courts rule differently despite the Supreme Court ruling, but in general, avoid patents for open source software licensing for the following reasons…

• the length of time and the expense incurred to obtain a patent
• limited time the patent can be enforced (20 years)
• as implied above, the potential for ending up in court with the high probability of discovering the software (aka, abstract idea) is not patentable. 

TRADEMARKS AND SERVICE MARKS
A trademark is granted by the federal government (in the US) and is a name or other designation for a product or goods. A servicemark is a name or other designation for a service. Neither a trademark nor a servicemark are appropriate for software.

TRADE SECRETS
A trade secret, is something kept as a secret usually for a competitive advantage, like the formula for Coke. Although an abstract idea like a recipe can not be patented, a trade secret is kind of the opposite of a patent in the sense that a patent is a public disclosure whereas a trade secret is a secret.

Trade secrets do not really work for software for which you are providing source code, nor does it work for software you are providing to others in general. Given that trade secrets do not work for distributed software, note that trade secrets can be copyrighted forbidding publication if they happen to be disclosed.

COPYRIGHT
A copyright is granted by the federal government (in the US). There are treaties in place that may also provided some international protection. Copyrights protect original creative works like software, and provides legal recourse for infringing use. I’d suggest that copyrights are the best solution for an open source software license as…

• Copyrights are obtained instantaneously and they are free
• They may be enforced for 95 (or more) years. (‘or more’ means that congress keeps extending copyright protection as Disney’s copyright of the Mickey Mouse likeness copyrights is nearing expiration)
• The courts have indicated that copyrights are the proper way to protect software.

There are several categories of Open Source license. They determine what a recipient of source code can do with that code. Categories include Public Domain, Permissive, Restrictive/Protective, Proprietary, and Roll Your Own.

PUBLIC DOMAIN
Public domain software is software that has no ownership and has been placed in the public domain. No ownership implies no patent, no copyright, etc.

PERMISSIVE
Permissive licenses allow the recipient of your software and/or software distribution the freedom to redistribute that software freely with only minimal restrictions. Some common permissive licenses include Apache, BSD, ISC, and MIT.

RESTRICTIVE/PROTECTIVE
Restrictive/protective licenses are commonly called ‘copyleft‘ (as a pun on copyright), GNU (GNU’s Not Unix), or GPL (GNU Public License) as the Free Software Foundation (FSF) has created a ‘movement’ (actually created GNU, GPL, and the Restrictive/Protective copyright concept). They have paid staff dedicated to enforcement of software licenses, people with titles like ‘Campaigns Manager’ and ‘Outreach & Communications Coordinator’ to sell the concept of Restrictive or Protective licenses. So much so that when people think of open source licenses without researching, they may think of ‘GPL’.

Here are some of the problems with Restrictive/Protective open source licenses.

They are quite lengthy as they have to spell out not only what freedoms you have, but also details of what restrictions you have.

They can be just as restrictive as Proprietary licenses depending upon the needs of the licensee.

PROPRIETARY
Not considered an Open Source Software license. The proprietary license type is used for software where the user is expected to only use the software, and not review its source code.

ROLL-YOUR-OWN
DO NOT ROLL YOUR OWN OPEN SOURCE SOFTWARE LICENSE. This is such a problems that lawyers don’t even roll-their-own software license. It’s the same thing as with cryptology, use what has been time tested, peer reviewed, and debated. Lawyers and the law like to be black and white. You do something dumb legally in a software license, then it may invalidated, leaving you with no license. Take for example the ‘roll your own’ JSON License (also see this). Secondly, for businesses to embrace your software, the license must be clearly understandable. It’s much easier for a legal department to say that you can/can’t use software licensed under specific models. The ‘Roll-your-own’ may take years.

WHICH OPEN SOURCE LICENSE SHOULD I USE
Please seek legal council if you have questions. I’m not an attorney.

First of all is the question of Open Source or Propitiatory. If I want people to use my software under specific circumstances, and not to have the source code, I’d choose a Proprietary license.

If I’m wanting to build a community around my software, or if I’m seeking assistance from internet users in making my software better, of course I’m OK with people having my source code. I’d choose an Open Source License.

Should I choose a RESTRICTIVE/PROTECTIVE or a PERMISSIVE license? I’d choose RESTRICTIVE/PROTECTIVE Open Source license if I wanted to add restrictions that required the software remain open, meaning that if even a small piece of the software is copied/pasted in another application, the other application becomes RESTRICTIVE/PROTECTIVEly Open Source. This is especially useful when I want to sell my software, and want to require anyone else modifying it (ie, for a sales advantage), provide a copy that I can obtain such that I can add in those same changes to my software. Note that many software vendors forbid RESTRICTIVE/PROTECTIVE Open Source software as they’re afraid that a small chunk of that code may accidentally become a poison pill for their commercial software.

I’d choose a PERMISSIVE Open Source license if you want your software to be truly Open Source with as few restrictions as possible.

Should I choose PUBLIC DOMAIN? No; if you’re considering PUBLIC DOMAIN, don’t. Your best alternative is to use a PERMISSIVE Open Source License. It’s very difficult to actually put something in the PUBLIC DOMAIN. You’re bound to spark questions about the software… did he just forget to add a copyright? Is this automatically copyrighted with all rights reserved? If you want the software to be of uses to someone else, skip the PUBLIC DOMAIN route until Congress formalizes the process. Google ‘implied copyright’, ‘copyright instantaneously’ and read with the eye of desiring to opt out of licensing.

HOW DO YOU LICENSE YOUR SOFTWARE
Licensing your software is very simple. On every source code file, add the text of the license. Standard licenses can be copied/pasted and edited to reflect the proper YEAR and COPYRIGHT HOLDER. Feel free to add a comment character to the beginning of the lines or around the copyright such such that a compiler ignores the text.

TO FOUNDATION OR NOT TO FOUNDATION
Foundations take control of software out of the hands of a single individual and passes control to the board of the foundation. If your project is a huge high profile project that is cash flowing, you may want to consider a foundation. Setting up a foundation may be expensive, but contributors would be more likely to give to a foundation than to an individual. Given the expense, I’d suggest waiting on the foundation until your software has raised to that level. Google ‘software foundation’ for details.

Keywords: FLOSS, Free Libra Open Source Software, licenses