By default, the connection between the database server and the client is not encrypted, making it possible to tap into the communication channel and read data. This poses a great security risk, even for non-critical data.

The connection can be encrypted by employing VPN connections between the server and the client, but SQL Server itself has some encryption mechanisms that prevent anauthorized access. Two methods will be covered here. The first is simpler, while the second is more complex, but offers more control over the level of security.

Simple Method without Security Certificate

Run SQL Server Configuration Manager. In SQL Server 2005 Network Configuration, open the Properties  for Protocols for SQL2005. Set ForceEncryption to Yes.

Set ForceEncryption to Yes

Restart the SQL Server instance for the change to take effect.

Now SQL Server will automatically generate a certificate. Because clients using Windows operating systems already have the required Microsoft keys installed, their connections to that SQL Server instance will be encrypted.

The downside of this method is that you cannot configure the encryption mode. Furthermore, all connections to the SQL Server instance are encrypted, which might not be desirable for local connections.

When using this method, make sure that you are not running PANTHEON with the /SSL switch. This is used only for the second method.

Complex Method with Security Certificate

With this method, the ForceEncryption property mentioned above must be No.

In the Certificate tab, select a certificate, which you have to acquire from a Certificate Authority (CA), VeriSign for example. Specify the FQDN (Fully qualified domain name) of the SQL Server instance if clients outside the local network will be connecting to it. For local use the local name of the SQL Server instance is sufficient.

Note that if you get a certificate from one of the established certificate authorities, clients will most likely have the relevant keys already installed.

If you already have a certificate, run PANTHEON with the /SSL switch and its connection to the server will be encrypted. More information about the /SSL switch can be found in Command Line Parameters.

You can prepare a certificate and matching keys yourself. For this you need Certificate Services installed, which is part of Windows 2003 Server. In Internet Explorer, navigate to http://Local/CertSrv which opens a certificate management page. In this example, the certificate authority is called ProdIT, which is also the certificate issuer.

Click Request a certificate.

Then click advanced certificate request.

And finally Create and submit request to this CA.

On the Advanced Certificate Request page, enter the server instance's fully qualified domain name if you want it to be accessible from outside the local network. Otherwise, the local name is sufficient. In Type of Certificate Needed, select Server Authentication Certificate. Also select a key size. Make sure you select Store certificate in the local computer certificate store. You can also specify a friendly name for the certificate (MS SQL Certificate in this example).

Click Submit after you have set all the options.

After submitting the request, open the Microsoft Managment Console (MMC), add the Certification Authority snap-in, and activate it by selecting Issue in the context menu.

After activating the certificate in the Management Console, return to Certificate Services. Click View the status of a pending certificate request to check the certificate's status.

Select your certificate.

If the certificate has been issued successfully, click Install this certfificate.

After the certificate has been installed, you can select it in SQL Server Configuration Manager.

In order for clients to be able to establish a secure connection, they have to install a certificate key, which you have to export first.

Open the Microsoft Managment Console (MMC), add the Certificates snap-in, find your certificate in the Personal folder, and export it from the context menu.

Make sure to select Cryptographic Message Syntax Standard- PKCS #7 Certificates (.P7B) and enable the option Include all certificates in the certification path if possible.

Clients can then install the exported certificate, either by right-clicking the file and selecting Install Certificate, or through Internet Explorer. In the Certificate Import Wizard, it is recommended to select  the option Automatically select the certificate store based in the type of certificate. Alternatively, you can select the certificate storage manually. If you do, select Trusted Root Certification Authorities.

If import is successful, clients should see your certificate authority (ProdIT in this example) among Trusted Root Certification Authorities.

Clients starting PANTHEON with the /SSL switch should now eastablish an encrypted connection to the server.