Networking

Secure your database interaction with SSL

The details of securing the communications between a client and Web application are often covered, but what about the communication between the application and database server? Here's a look at securing the database interaction with Secure Sockets Layer (SSL).

The beginning of 2005 started on a bad note, as I had to deal with a stolen credit card. The issue led me to consider security in the world of online transactions and data access.

The details of securing the communications between a client and Web application are often covered, but what about the communication between the application and database server? After all, the database server contains the most valuable data. One approach to securing this link is using SSL (Secure Sockets Layer). Let's take a closer look at securing the database interaction with SSL.

SSL security setup

Netscape developed SSL many years ago to transmit data privately over the Internet. It works by using a private key to encrypt data over the SSL connection. By convention, URLs that require an SSL connection start with https as opposed to http.

Using SSL to communicate between a client machine and SQL Server 2000 requires both machines running at least Windows 2000. In addition, a SSL certificate is necessary. The details of certificate generation are beyond this article, but a certificate authority is necessary to generate certificates. Here are two sites that offer free certificates:

If you've ever utilized SSL on a Web site, you probably remember that you may apply a certificate to a site via IIS Internet Services Manager. In addition, you can use the Windows Certificates mmc snap-in to apply it to a SQL Server 2000 machine. After installing a certificate on the SQL Server 2000 machine, you must restart SQL Server. At this point, you may utilize SSL to communicate with the server.

The client machine(s) that will communicate with SQL Server must be set up to trust the certificate installed on SQL Server. In addition, the client machine must install MDAC 2.6 or the SQL Server 2000 connectivity libraries. A SSL certificate may be trusted via the Certificate Services available within Microsoft Windows 2000. (Here are directions for using the Certificate Services.)

The last step involves deciding whether you must encrypt communications with SQL Server. The Service Network Utility, available on the SQL Server machine, allows you to make this decision. It contains a checkbox to signal whether you should encrypt all communications. This is a great option, but it forces all clients to install MDAC 2.6 or the SQL Server 2000 connectivity libraries. If the option isn't selected, this allows nonsecure communications. That defeats the purpose of this article, but you should know that it's an option.

Applications may still utilize encryption by specifying it in the connection string, like the following:

Server=Test;Integrated Security=SSPI;Persist Security
 Info=False;Database=Northwind;Encypt=True

You can easily test encryption by using the previous connection string (or a version of it) to connect to the SQL Server:

using System;
using System.Data;
using System.Data.SqlClient;
namespace Builder {
class SQLServerEncryption {
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password;Integrated
 Security=SSPI;Encrypt=True";
[STAThread]
static void Main(string[] args) {
SqlConnection conn = new SqlConnection(sConn);
SqlCommand cmd = new SqlCommand("SELECT * FROM Orders", conn);
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read()) {
Console.WriteLine(sdr.GetString(1));
}
sdr.Close();
} catch (SqlException e){
Console.WriteLine("SQL Exception: " + e.Message);
} catch (Exception e) {
Console.WriteLine("Exception: "+ e.Message);
} finally {
conn.Close();
}

Here's the VB.NET equivalent:

Imports System.Data
Imports System.Data.SqlClient
Module Builder
Sub Main()
Dim sConn As String = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password;Integrated
 Security=SSPI;Encrypt=True"
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim sdr As SqlDataReader
Try
conn = New SqlConnection(sConn)
cmd = New SqlCommand("SELECT * FROM Orders", conn)
conn.Open()
sdr = cmd.ExecuteReader()
While (sdr.Read())
Console.WriteLine(sdr.GetString(1))
Wend
sdr.Close()
Catch e As SqlException
Console.WriteLine("SQL Exception: " + e.Message)
Catch e As Exception
Console.WriteLine("Exception: " + e.Message)
Finally
conn.Close()
End Try

Proper code execution and results don't guarantee that the communication process was encrypted. You can take a closer look at the interaction between the client and SQL Server with a network monitoring tool. Windows 2000 offers the Network Monitor, which allows you to capture certain network traffic.

One downside to encrypting data is that there's a bit more overhead to the communication process: the data is encrypted, sent, and decrypted. However, this is often a small price to pay to ensure sensitive data is protected.

Safeguard data when possible

The communication between an application and SQL Server isn't protected, but you can secure it with SSL encryption. There are also other options available such as securing the IP connection between the two (IPSec) or using X.509 certificates as opposed to SSL.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox