In this post I’ll continue on the topic of data encryption (see my previous post on Encrypting Data At Rest).
Network communications between the client and the database server should always be secured. Historically relational database management system (RDBMS) network communication protocols are in clear text by default. This allows attackers sniffing on the wire to intercept sensitive data like logins/passwords, object names and even actual table data coming back from database server to client. That’s why it is important to secure network communications in addition to encrypting data at rest. Most modern RDBMS rely on PKI (public key infrastructure) to implement network encryption. SSL protocol is the de-facto standard in Oracle, Microsoft SQL Server, and Sybase ASE for encrypting network communications. While SSL provides more capabilities than encryption (e.g. authentication) I will highlight only the encryption aspect. There are additional options for data encryption in transit:secure tunnels and IPSec, but I will focus on the encryption features provided in the database.
Oracle 11g R2
The Oracle Advanced Security Option (ASO) provides two ways to encrypt data flow: using Network Data Encryption and using SSL. The former is built on symmetric key encryption, the latter uses PKI. To configure secure communication via SSL in an Oracle Database we will create a Wallet to store authentication and signing credentials (e.g. certificates) and make changes to several configuration files (
listener.ora). Provided that the ASO option is installed on both the server and the client, you can configure encryption via SSL by:
- Using the Oracle Wallet Manager create a Wallet. The tool will suggest setting the Wallet password and preparing a certificate request. Next, a certificate must be obtained for the request from Certification Authority or (CA) and then added to the Wallet. For testing purposes a self-signed certificate may be sufficient: see Oracle documentation how to do this with the Oracle PKI Tool (
- Using Oracle Net Manager specify the Wallet location.
- Create a Listening Endpoint that uses TCP/IP with SSL on the server. Oracle recommends port
2484to be used for it. The listener configuration file will look like:
These steps will add configuration information to the
sqlnet.ora files that looks like this snippet:
SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\app\Administrator\product\11.2.0\dbhome_1\admin\orcl\wallet) ) )
Now to should configure the client: add an entry to the
tnsnames.ora file so that the client will use
tcps protocol for connections. Next, import the trusted root certificate into the client Wallet. This step is required especially in the self-signed scenario because otherwise the client won’t be able to trust the server certificate. Again the Oracle PKI Tool can be used:
orapki wallet add -wallet . -trusted_cert -cert root_certificate.txt -pwd Secr3tPassw0rd
Once the client is configured, you can test the connection via
sqlplus. This is the packet capture for
SELECT * FROM DBA_USERS server response without SSL:
This is the same query ran with SSL turned on:
You’ll see that the data is no longer visible in plain text. Of course there are much more SSL configuration options in Oracle Database – please consult your user documentation for details.
Microsoft SQL Server 2008
Microsoft SQL Server 2008 provides connection encryption for all editions, including Express. By default, the server generates a self-signed certificate during startup even when encryption isn’t configured. This makes it possible to always encrypt login sequence so that logins/passwords aren’t visible. However, subsequent data exchange will expose clear text data so it’s necessary to configure SSL properly. Here is how:
- Obtain a server certificate. The certificate can be self-signed or from a real CA.
- Install the certificate on the server. It can be placed in the local computer certificate store or in the SQL Server service user certificate store.
- Set the option to encrypt all communications on the server via the SQL Server Configuration Manager. The option name is
ForceEncryption, it should be set to
Yes. This will encrypt all client-server communication. If a client cannot process encryption, the connection will fail.
Assume we have a simple query like
SELECT name FROM syslogins. When no encryption is used the query and response are sent in clear text:
The same packet with a query after
ForceEncryption is set to Yes and the server is restarted:
It should be noted that self-signed certificates are suitable for testing purposes only because they are susceptible to man-in-the-middle attacks. In a production environment only certificates from trusted authorities should be deployed.
For additional details see Microsoft’s overview on Encrypting Connections to SQL Server
Sybase ASE 15
The Sybase ASE client-server communication is by default, in clear text. SSL or Kerberos can be used for the network encryption. The following details apply only to SSL.
As with Microsoft SQL Server, we must provide the server with a valid server certificate for SSL to function. Steps to enable SSL encryption on Sybase ASE 15 (also see the Sybase System Administration Guide PDF):
- Generate a certificate for the server. Tools provided with the ASE could be used to get the certificate for testing purposes (
certauth). Take into account this excerpt from Sybase ASE documentation: "To create a server certificate file that Adaptive Server understands, append the certificate requestor’s private key to the end of the signed certificate file."
- Place the trusted root certificate file under
$SYBASE/$SYBASE_ASE/certificateson UNIX) and name it after the server:
- Issue the T-SQL command:
sp_configure 'enable ssl', 1
- Add the SSL filter to the interfaces file: to do so, add ",ssl" at the end of
- Use the
sp_ssladminstored procedure to add a certificate to the certificates file created in step 1.
- Shut down and restart Adaptive Server for the changes to take effect.
To connect to the server now one must update the interfaces configuration file on the client to include the ssl filter and repeat step 4. When the self-signed server certificate is used the trusted certificate file must be specified via
-x option to
isql -S SYBASE155ESD2 -U sa -x trusted.cert
For Interactive SQL one must import the trusted certificate file into keystore first with the
Now let’s compare the traffic before and after SSL option. Here is how the login sequence looks like without encryption:
Note that the username and password is embedded into the packet.
select name from syslogins query results are coming back:
Next, let’s enable SSL using steps provided. The login exchange packets no longer contains username and password in clear text and looks like this capture:
The response packet to the
SELECT query looks like this:
To examine what cipher suite is used for the current connection use
@@ssl_ciphersuite T-SQL global variable:
SELECT @@ssl_ciphersuite ---------------------------- TLS_RSA_WITH_AES_256_CBC_SHA
If the result is non-null, the current connection is SSL-encrypted.
As always security comes at cost. There is more pressure on memory since the server needs additional space to encrypt/decrypt the data. Performance also decreases because extra CPU time is spent in crypto code. Therefore thorough testing is required before putting encryption to production.