Skip to content

Files

Latest commit

DavidCBerry13DavidCBerry13
Update DevLang Metadata - golang
Dec 16, 2021
75a5de9 · Dec 16, 2021

History

History
207 lines (178 loc) · 9.12 KB

howto-configure-ssl.md

File metadata and controls

207 lines (178 loc) · 9.12 KB
title description author ms.author ms.service ms.topic ms.date ms.devlang ms.custom
Configure SSL - Azure Database for MariaDB
Instructions for how to properly configure Azure Database for MariaDB and associated applications to correctly use SSL connections
savjani
pariks
mariadb
how-to
07/08/2020
csharp, golang, java, php, python, ruby
devx-track-csharp

Configure SSL connectivity in your application to securely connect to Azure Database for MariaDB

Azure Database for MariaDB supports connecting your Azure Database for MariaDB server to client applications using Secure Sockets Layer (SSL). Enforcing SSL connections between your database server and your client applications helps protect against "man in the middle" attacks by encrypting the data stream between the server and your application.

Obtain SSL certificate

Download the certificate needed to communicate over SSL with your Azure Database for MariaDB server from https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem and save the certificate file to your local drive (this tutorial uses c:\ssl for example). For Microsoft Internet Explorer and Microsoft Edge: After the download has completed, rename the certificate to BaltimoreCyberTrustRoot.crt.pem.

See the following links for certificates for servers in sovereign clouds: Azure Government, Azure China, and Azure Germany.

Bind SSL

Connecting to server using MySQL Workbench over SSL

Configure MySQL Workbench to connect securely over SSL.

  1. From the Setup New Connection dialogue, navigate to the SSL tab.

  2. Update the Use SSL field to "Require".

  3. In the SSL CA File: field, enter the file location of the BaltimoreCyberTrustRoot.crt.pem.

    Save SSL configuration

For existing connections, you can bind SSL by right-clicking on the connection icon and choose edit. Then navigate to the SSL tab and bind the cert file.

Connecting to server using the MySQL CLI over SSL

Another way to bind the SSL certificate is to use the MySQL command-line interface by executing the following commands.

mysql.exe -h mydemoserver.mariadb.database.azure.com -u Username@mydemoserver -p --ssl-mode=REQUIRED --ssl-ca=c:\ssl\BaltimoreCyberTrustRoot.crt.pem

Note

When using the MySQL command-line interface on Windows, you may receive an error SSL connection error: Certificate signature check failed. If this occurs, replace the --ssl-mode=REQUIRED --ssl-ca={filepath} parameters with --ssl.

Enforcing SSL connections in Azure

Using the Azure portal

Using the Azure portal, visit your Azure Database for MariaDB server, and then click Connection security. Use the toggle button to enable or disable the Enforce SSL connection setting, and then click Save. Microsoft recommends to always enable the Enforce SSL connection setting for enhanced security. enable-ssl for MariaDB server

Using Azure CLI

You can enable or disable the ssl-enforcement parameter by using Enabled or Disabled values respectively in Azure CLI.

az mariadb server update --resource-group myresource --name mydemoserver --ssl-enforcement Enabled

Verify the SSL connection

Execute the mysql status command to verify that you have connected to your MariaDB server using SSL:

status

Confirm the connection is encrypted by reviewing the output, which should show: SSL: Cipher in use is AES256-SHA

Sample code

To establish a secure connection to Azure Database for MariaDB over SSL from your application, refer to the following code samples:

PHP

$conn = mysqli_init();
mysqli_ssl_set($conn,NULL,NULL, "/var/www/html/BaltimoreCyberTrustRoot.crt.pem", NULL, NULL) ; 
mysqli_real_connect($conn, 'mydemoserver.mariadb.database.azure.com', 'myadmin@mydemoserver', 'yourpassword', 'quickstartdb', 3306, MYSQLI_CLIENT_SSL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

Python (MySQLConnector Python)

try:
    conn = mysql.connector.connect(user='myadmin@mydemoserver',
                                   password='yourpassword',
                                   database='quickstartdb',
                                   host='mydemoserver.mariadb.database.azure.com',
                                   ssl_ca='/var/www/html/BaltimoreCyberTrustRoot.crt.pem')
except mysql.connector.Error as err:
    print(err)

Python (PyMySQL)

conn = pymysql.connect(user='myadmin@mydemoserver',
                       password='yourpassword',
                       database='quickstartdb',
                       host='mydemoserver.mariadb.database.azure.com',
                       ssl={'ca': '/var/www/html/BaltimoreCyberTrustRoot.crt.pem'})

Ruby

client = Mysql2::Client.new(
        :host     => 'mydemoserver.mariadb.database.azure.com', 
        :username => 'myadmin@mydemoserver',      
        :password => 'yourpassword',    
        :database => 'quickstartdb',
        :sslca => '/var/www/html/BaltimoreCyberTrustRoot.crt.pem'
        :ssl_mode => 'required'
    )

Ruby on Rails

default: &default
  adapter: mysql2
  username: username@mydemoserver
  password: yourpassword
  host: mydemoserver.mariadb.database.azure.com
  sslca: BaltimoreCyberTrustRoot.crt.pem
  sslverify: true

Golang

rootCertPool := x509.NewCertPool()
pem, _ := ioutil.ReadFile("/var/www/html/BaltimoreCyberTrustRoot.crt.pem")
if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
    log.Fatal("Failed to append PEM.")
}
mysql.RegisterTLSConfig("custom", &tls.Config{RootCAs: rootCertPool})
var connectionString string
connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true&tls=custom",'myadmin@mydemoserver' , 'yourpassword', 'mydemoserver.mariadb.database.azure.com', 'quickstartdb')	
db, _ := sql.Open("mysql", connectionString)

Java (JDBC)

# generate truststore and keystore in code
String importCert = " -import "+
    " -alias mysqlServerCACert "+
    " -file " + ssl_ca +
    " -keystore truststore "+
    " -trustcacerts " + 
    " -storepass password -noprompt ";
String genKey = " -genkey -keyalg rsa " +
    " -alias mysqlClientCertificate -keystore keystore " +
    " -storepass password123 -keypass password " + 
    " -dname CN=MS ";
sun.security.tools.keytool.Main.main(importCert.trim().split("\\s+"));
sun.security.tools.keytool.Main.main(genKey.trim().split("\\s+"));

# use the generated keystore and truststore 
System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file");
System.setProperty("javax.net.ssl.keyStorePassword","password");
System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file");
System.setProperty("javax.net.ssl.trustStorePassword","password");

url = String.format("jdbc:mysql://%s/%s?serverTimezone=UTC&useSSL=true", 'mydemoserver.mariadb.database.azure.com', 'quickstartdb');
properties.setProperty("user", 'myadmin@mydemoserver');
properties.setProperty("password", 'yourpassword');
conn = DriverManager.getConnection(url, properties);

Java (MariaDB)

# generate truststore and keystore in code
String importCert = " -import "+
    " -alias mysqlServerCACert "+
    " -file " + ssl_ca +
    " -keystore truststore "+
    " -trustcacerts " + 
    " -storepass password -noprompt ";
String genKey = " -genkey -keyalg rsa " +
    " -alias mysqlClientCertificate -keystore keystore " +
    " -storepass password123 -keypass password " + 
    " -dname CN=MS ";
sun.security.tools.keytool.Main.main(importCert.trim().split("\\s+"));
sun.security.tools.keytool.Main.main(genKey.trim().split("\\s+"));

# use the generated keystore and truststore 
System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file");
System.setProperty("javax.net.ssl.keyStorePassword","password");
System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file");
System.setProperty("javax.net.ssl.trustStorePassword","password");

url = String.format("jdbc:mariadb://%s/%s?useSSL=true&trustServerCertificate=true", 'mydemoserver.mariadb.database.azure.com', 'quickstartdb');
properties.setProperty("user", 'myadmin@mydemoserver');
properties.setProperty("password", 'yourpassword');
conn = DriverManager.getConnection(url, properties);

.NET (MySqlConnector)

var builder = new MySqlConnectionStringBuilder
{
    Server = "mydemoserver.mysql.database.azure.com",
    UserID = "myadmin@mydemoserver",
    Password = "yourpassword",
    Database = "quickstartdb",
    SslMode = MySqlSslMode.VerifyCA,
    CACertificateFile = "BaltimoreCyberTrustRoot.crt.pem",
};
using (var connection = new MySqlConnection(builder.ConnectionString))
{
    connection.Open();
}

Next steps

To learn about certificate expiry and rotation, refer certificate rotation documentation