dotConnect for MySQL allows you to establish secure network connections. You can do it using SSL and SSH protocols. This article describes basic concepts for these technologies and how to use it in dotConnect for MySQL.
The article consists of following sections:

• Why Using Secure Connections?
• Using SSL Connections
• Setting Up SSL Connection
• Using SSH Connections
• Setting Up SSH Connection

Why Using Secure Connections?

When MySQL client communicates with MySQL server, all communication (with the exception of the user password) is done in plain text. This means that anyone who gets between your client and the server can have full access to all information transmitted, and even change the data while it is in transit. In order to protect your information you need to encrypt communications between the MySQL server and the client application.

The two following alternatives are available to you:

• using SSL (Secure Socket Layer) connections;
• using SSH (Secure SHell) connections.

Using SSL Connections

The Secure Sockets Layer protocol enables two parties to negotiate a 'secure' communications channel, ensuring the privacy, authenticity, and integrity of message data. The negotiation mechanism follows:

1. The client requests a SSL connection from the server.
2. The two parties negotiate a common ciphersuite, which consists of a key exchange algorithm, a certificate verification algorithm, an encryption algorithm, and an integrity check.
3. The server provides its certificate so it can be authenticated by the client. Optionally, the client may provide its certificate to be authenticated by the server.
4. The two parties compute the cryptographic parameters used in the ciphersuite, such as the secret keys needed for data encryption.
5. The two parties exchange application data, using the generated session key, negotiated algorithms and computed cryptographic parameters.

To check whether your MySQL server supports SSL you can examine the value of the have_openssl system variable:

    SHOW VARIABLES LIKE 'have_openssl'

Setting Up SSL Connection

Once server is ready to use SSL connections you only have to setup client properly. You must have 3 additional files reachable: authority certificate (used to verify identity of client and server), client certificate and private key (used to encrypt and decrypt data during connection). Please refer to OpenSSL site for information about these files. The later part assumes you have valid certificates and private key.

When you create MySqlConnection object you have to add following parameter to connection string:

    Protocol=SSL

MySqlConnection.SslOptions property points to object that holds all information necessary to establish SSL connection. Here is brief explanation on what you have to specify in this object:

You may store and load the necessary keys or certificates in various ways in your applications: as files or as compiled resources or in a certificate store. dotConnect for MySQL also offers a convenient way to store certificates and keys in memory, so you can load them once, during application start, and then use these certificates from the in memory storage. For this, you can use the MemCryptStorage class.

You can add certificates to a memory storage in the following way:

MemCryptStorage.AddCa("my_ca", File.ReadAllBytes("D:\\Temp\\root.crt"));  
MemCryptStorage.AddCert("my_cert", File.ReadAllBytes("D:\\Temp\\mysql.crt"));  
MemCryptStorage.AddKey("my_key", File.ReadAllBytes("D:\\Temp\\mysql.key"));

MemCryptStorage.AddCa("my_ca", File.ReadAllBytes("D:\Temp\root.crt"))
MemCryptStorage.AddCert("my_cert", File.ReadAllBytes("D:\Temp\mysql.crt"))
MemCryptStorage.AddKey("my_key", File.ReadAllBytes("D:\Temp\mysql.key"))

The first parameter here is the certificate or key id, that will be used to reference the certificate.

Certificates and keys are passed to the MemCryptStorage class in the PEM format as binary raw or string. They can contain LF, CR/LF, or no line break. We don't recommend changing the original format of certificates. It is better to pass them as is.

Here is how you can specify the locations of certificates:

• As a file in the system: file://C:\Temp\Example.pem
• As an item of certificate store: storage://Name.Id
• As an item of the memory store: memory://Id
• As a compiled resource: resource://Example.pem

You also may specify the certificate file content in this parameter: binary://<certificate content>

Private key can be specified only as file in the system or as compiled resource.

The property SslOptions.CipherList by default is empty, which means that client agrees to use any of available ciphers. The following ciphers are allowed in dotConnect for MySQL:

• DES(40)
• DES or DES(56)
• 3DES or DES(168)
• AES(128)
• AES or AES(256) (used by default)
• RC4(40)
• RC4 or RC4(128)
• RC2 or RC2(40)

You can use ALL keyword to indicate whole set of ciphers. To exclude certain cipher from the set use "-" sign. For instance, value of SslOptions.CipherList property "ALL:-RC4" means that any cipher but RC4 can be used. This property allows you to control performance-security trade-off using this property. To gain better performance you can use RC2 cipher. To achieve maximal traffic security pay attention at 3DES or AES ciphers. By default dotConnect for MySQL tries to use ciphers beginning with most secure rather than fast ones.

The sample code below illustrates establishment of SSL connection.

MySqlConnection myConn = new MySqlConnection("host=server;protocol=SSL;user=root;password=root;database=test");
myConn.SslOptions.CACert = "file://D:\\Temp\\CA-cert.pem";
myConn.SslOptions.Cert = "file://D:\\Temp\\SSL-client-cert.pem";
myConn.SslOptions.Key = "file://D:\\Temp\\SSL-client-key.pem";
MySqlCommand myCommand = new MySqlCommand("select count(*) from dept",myConn);
myConn.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
myConn.Close();
Console.ReadLine();

Dim myConn As MySqlConnection = New MySqlConnection("host=server;protocol=SSL;user=root;password=root;database=test")
myConn.SslOptions.CACert = "file://D:\\Temp\\CA-cert.pem"
myConn.SslOptions.Cert = "file://D:\\Temp\\SSL-client-cert.pem"
myConn.SslOptions.Key = "file://D:\\Temp\\SSL-client-key.pem"
Dim myCommand As MySqlCommand = New MySqlCommand("select count(*) from dept", myConn)
myConn.Open()
Dim count As Int64 = Convert.ToInt64(myCommand.ExecuteScalar())
Console.WriteLine(count)
myConn.Close()
Console.ReadLine()

Using SSH Connections

SSH connection is established between client and SSH server. SSH server in turn communicates with MySQL server in an unencrypted mode. This is called SSH tunneling. A benefit of SSH tunneling is that it allows you to connect to a MySQL server from behind a firewall when the MySQL server port is blocked. MySQL server does not need to be attuned for this type of connection and functions as usual. To connect to MySQL server a client must first be authorized on SSH server.

You can download free SSH server at www.openssh.org. The server can run either on the same machine with MySQL server or on a different one. Note that you have to create a user on SSH server to be authorized.

Setting Up SSH Connection

When you create MySqlConnection object you have to add following parameter to connection string:

    Protocol=SSH

MySqlConnection.SshOptions property points to object that holds all information necessary to connect to SSH server. Alternatively, you may specify the corresponding parameters in the connection string. Here is brief explanation on what you have to specify in this object:

Locations of private key can be specified in three ways:

• As file in the system: file://C:\Temp\client.key
• As item of certificate store: storage://Name.Id (examples RSA.Client.key or DSA.Client.key)
• As compiled resource: resource://client.key
• Loaded to memory, using MemCryptStorage class: memory://key_id

MySQL server address that you specify in connection string is the address for SSH server to refer. For instance, if both servers are running on the same machine you have to specify "host=localhost" in the connection string.

The property SshOptions.CipherList contains the list of the ciphers that client agrees to use, separated by colons. By default it is empty, which means that client agrees to use any of available ciphers. The appropriate values for the CipherList property are listed below, highlighted in bold.

dotConnect for MySQL supports two modes of block ciphering: Cipher-block chaining (CBC) and Counter (CTR). The following ciphers are available for SSH connections in the CBC mode:

• 3DES or 3DES(168) - Triple Data Encryption Algorithm. Key size 168 bits.
• Blowfish - Symmetric-key block cipher, designed in 1993 by Bruce Schneier. Key size 128 bits.
• AES(128) - Advanced Encryption Standard. Key size 128 bits.
• AES(192) - Advanced Encryption Standard. Key size 192 bits.
• AES or AES(256) - Advanced Encryption Standard. Key size 256 bits.

In the CTR mode the AES ciphers are used.

• AES(128)-CTR - Advanced Encryption Standard. Key size 128 bits.
• AES(192)-CTR - Advanced Encryption Standard. Key size 192 bits.
• AES-CTR or AES(256)-CTR - Advanced Encryption Standard. Key size 256 bits.

      // Password Authentication
      MySqlConnection myConn = new MySqlConnection("host=server;protocol=SSH;user=root;password=root;database=test");
      myConn.SshOptions.AuthenticationType = SshAuthenticationType.Password;
      myConn.SshOptions.User = "sshUser";
      myConn.SshOptions.Host = "sshServer";
      myConn.SshOptions.Password = "sshPassword";
      MySqlCommand myCommand = new MySqlCommand("select count(*) from dept", myConn);
      myConn.Open();
      Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
      Console.WriteLine(count);
      myConn.Close();

      // Public Key Authentication
      MySqlConnection myConn = new MySqlConnection("host=localhost;protocol=SSH;user=root;password=root;database=test");
      myConn.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
      myConn.SshOptions.User = "sshUser";
      myConn.SshOptions.Host = "sshServer";
      myConn.SshOptions.PrivateKey = "E:\\WORK\\client.key";
      MySqlCommand myCommand = new MySqlCommand("select count(*) from dept", myConn);
      myConn.Open();
      Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
      Console.WriteLine(count);
      myConn.Close();

      // Keyboard-Interactive Authentication
      MySqlConnection connection = new MySqlConnection("host=mysql_host;port=3306;user id=mysql_user;password=mysql_password;");
	    connection.Protocol = MySqlProtocol.Ssh;
	    // sets ssh options
	    connection.SshOptions.Host = "ssh_host";
	    connection.SshOptions.Port = 22;
	    connection.SshOptions.User = "ssh_user";
	    connection.SshOptions.AuthenticationType = SshAuthenticationType.KeyboardInteractive;
	    // Associate the AuthenticationPrompt event with your event handle
	    connection.AuthenticationPrompt += new MySqlAuthenticationPromptHandler(connection_AuthenticationPrompt);

	    connection.Open();
	    Console.WriteLine(connection.State);

	    connection.Close();
	    Console.ReadKey();
      
      ...

      static void connection_AuthenticationPrompt(object sender, MySqlAuthenticationPrompEventArgs e) {

	      foreach (string prompt in e.Prompts) {
		      if (prompt.Contains("Password"))
			      e.Responses[0] = "testPassword";
      		else
	  	    	if (prompt.Contains("UserId"))
		  	    e.Responses[1] = "testUserId";
	      }
      }

	    ' Password Authentication
	    Dim myConn As MySqlConnection = New MySqlConnection("host=server;protocol=SSH;user=root;password=root;database=test")
	    myConn.SshOptions.AuthenticationType = SshAuthenticationType.Password
	    myConn.SshOptions.User = "sshUser"
	    myConn.SshOptions.Host = "sshServer"
	    myConn.SshOptions.Password = "sshPassword"
	    Dim myCommand As MySqlCommand = New MySqlCommand("select count(*) from dept", myConn)
	    myConn.Open()
	    Dim count As Int64 = Convert.ToInt64(myCommand.ExecuteScalar())
	    Console.WriteLine(count)
	    myConn.Close()

	    ' Public Key Authentication
	    Dim myConn As MySqlConnection = New MySqlConnection("host=server;protocol=SSH;user=root;password=root;database=test")
	    myConn.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey
	    myConn.SshOptions.User = "sshUser"
	    myConn.SshOptions.Host = "sshServer"
	    myConn.SshOptions.PrivateKey = "E:\WORK\client.key"
	    Dim myCommand As MySqlCommand = New MySqlCommand("select count(*) from dept", myConn)
	    myConn.Open()
	    Dim count As Int64 = Convert.ToInt64(myCommand.ExecuteScalar())
	    Console.WriteLine(count)
	    myConn.Close()

	    ' Keyboard-Interactive Authentication
	    Dim connection As MySqlConnection = New MySqlConnection("host=mysql_host;port=3306;user id=mysql_user;password=mysql_password;")
	    connection.Protocol = MySqlProtocol.Ssh
	    ' sets ssh options
	    connection.SshOptions.Host = "ssh_host"
	    connection.SshOptions.Port = 22
	    connection.SshOptions.User = "ssh_user"
	    connection.SshOptions.AuthenticationType = SshAuthenticationType.KeyboardInteractive
	    ' Associate the AuthenticationPrompt event with your event handle
	    AddHandler connection.AuthenticationPrompt, AddressOf connection_AuthenticationPrompt

	    connection.Open()
	    Console.WriteLine(connection.State.ToString)

	    connection.Close()
	    Console.ReadKey()

      ...

	    Sub connection_AuthenticationPrompt(ByVal sender As Object, ByVal e As MySqlAuthenticationPrompEventArgs)

	    	For Each prompt As String In e.Prompts
			    If prompt.Contains("Password") Then
				    e.Responses(0) = "testPassword"
			    ElseIf prompt.Contains("UserId") Then
				    e.Responses(1) = "testUserId"
			    End If
		    Next
	    End Sub

See Also

 MySqlConnection Class  | SshOptions Class  | SslOptions Class