Secure your database interaction with SSL

By Tony Patton

July 21, 2005, 7:00am PDT

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

The details of securing the communicationsbetween a client and Web application are often covered, but whatabout the communication between the application and databaseserver? After all, the database server contains the most valuabledata. One approach to securing this link is using SSL (SecureSockets Layer). Let's take a closer look at securing the databaseinteraction with SSL.

SSL security setup

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

Using SSL to communicate between a clientmachine and SQL Server 2000 requires both machines running at leastWindows 2000. In addition, a SSL certificate is necessary. Thedetails of certificate generation are beyond this article, but acertificate authority is necessary to generate certificates. Hereare two sites that offer free certificates:

If you've ever utilized SSL on a Web site, youprobably remember that you may apply a certificate to a site viaIIS Internet Services Manager. In addition, you can use the WindowsCertificates mmc snap-in to apply it to a SQL Server 2000 machine.After installing a certificate on the SQL Server 2000 machine, youmust restart SQL Server. At this point, you may utilize SSL tocommunicate with the server.

The client machine(s) that will communicatewith SQL Server must be set up to trust the certificate installedon SQL Server. In addition, the client machine must install MDAC 2.6 or the SQL Server 2000 connectivity libraries. A SSL Certificate maybe trusted via the Certificate Services available within MicrosoftWindows 2000. (Here are directions for using the Certificate Services.)

The last step involves deciding whether youmust encrypt communications with SQL Server. The Service NetworkUtility, available on the SQL Server machine, allows you to makethis decision. It contains a checkbox to signal whether you shouldencrypt all communications. This is a great option, but it forcesall clients to install MDAC 2.6 or the SQL Server 2000 connectivitylibraries. If the option isn't selected, this allows nonsecurecommunications. That defeats the purpose of this article, but youshould know that it's an option.

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

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

You can easily test encryption by using theprevious connection string (or a version of it) to connect to theSQL 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'tguarantee that the communication process was encrypted. You cantake a closer look at the interaction between the client and SQLServer with a network monitoring tool. Windows 2000 offers the Network Monitor, which allows you to capture certain networktraffic.

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

Safeguard data when possible

The communication between an application andSQL Server isn't protected, but you can secure it with SSLencryption. There are also other options available such as securingthe IP connection between the two (IPSec)or using X.509certificates 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.

Source URL:-http://www.techrepublic.com/article/secure-your-database-interaction-with-ssl/5794420