Secure Socket Layer (SSL)
Secure Socket Layer (SSL) is a protocol for providing secure transactions between servers and clients. It uses a certificate to identify one or both ends of the transaction. It can be useful for database communications to protect any authentication information, such as usernames and passwords, as well as the actual data itself.
Some databases support SSL and some do not. To know if your database supports SSL, you need to study the documentation for your database and work with the administrator of the database. If your database can be configured to use SSL with JDBC, it is possible to configure DatabaseLink to communicate with the database using SSL.
One database that does support SSL is PostgreSQL, and it is possible for DatabaseLink to communicate with a PostgreSQL database using SSL. You will need to configure the database to provide SSL communications and generate a certificate. To do this you will need to work with the administrator of your database.
There are typically four stages to setting up SSL to work with a MySQL database.
1. Get a certificate of authority.
2. Generate a truststore file.
3. Configure Java to use the truststore.
4. Configure the connection to use SSL.
The administrator of the server can provide the certificate of authority; for testing purposes, openssl may be used to generate a self-signed certificate. Suppose the certificate is called CA.crt.
Next generate the truststore file based on the certificate. This can be done with the keytool executable that is part of a Java Runtime Environment (JRE). You can use the version included in the JRE that ships with the Wolfram Language. To generate the truststore file, execute the following in some type of shell (e.g. a command prompt on Windows).
This will generate the file truststore. You will be asked to supply a password.
The next stage is to modify your Java command line for J/Link to refer to the truststore file. This can be done by adding the following settings, in which you need to give the full pathname to the truststore file that was generated.
If you are running the Wolfram Language inside a web server, such as webMathematica, you will need to add these settings to the server that launches Java by following your server documentation. If you are running the Wolfram Language in a standalone fashion, you can add the settings to the options of Java by executing the following before you load DatabaseLink.
Needs["JLink`"];
SetOptions[InstallJava, JVMArguments -> "-Djavax.net.ssl.trustStore=c:\java-examples\truststore -Djavax.net.ssl.trustStorePassword=mypassword"]
Finally, you need to modify the URL that connects to the database. This can be done by placing an extra parameter with a "?", as shown in the following.
OpenSQLConnection[
JDBC[
"org.postgresql.Driver",
"jdbc:postgresql://server.domain.com/database?ssl=true"
],
"Username" -> "Me", "Password" -> "$Prompt"
]
Here is the same connection with a named JDBC configuration. The ssl parameter could again be appended to the url, but is passed here as a connection property.
OpenSQLConnection[
JDBC["PostgreSQL","server.domain.com/database"],
"Properties" -> {"ssl""true"},
"Username" -> "Me", "Password" -> "$Prompt"
]
It should be noted that not all databases support SSL, and that SSL databases other than PostgreSQL may require different configuration to work with DatabaseLink.