I manage databases for financial organisation. These databases have confidential data of our customers such as credit card numbers, security related data etc. This confidential data must be encrypted when it is transmitted between the client and the server over the network. That way, if anyone is reading our network traffic, will not be able to interpret the data.
In this blog post, I’m going to discuss the different options available for encrypting SQL Server connections.
You have two choices for encrypting data on the network:
- Internet Protocol Security (IPSec)
- Secure Sockets Layer (SSL)
Internet Protocol Security (IPSec)
Internet Protocol Security (IPSec) is implemented by the operating system. It’s supported on the following operating systems:
- Windows 2000
- Windows Server 2003
- Windows Server 2008 /2008 R2
- Windows Server 2012
- Windows XP Professional
- Windows 7
Both the client and server operating system must support IPSec. IPSec:
- Is configured by local security policy or through Group Policy.
- Supports authentication using Kerberos, certificates, or pre-shared key.
- Provides advanced protocol filtering to block traffic by protocol and port.
Secure Sockets Layer (SSL)
Secure Sockets Layer (SSL) is implemented by SQL Server. It’s most commonly used to support Web clients, but it can also be used to support native SQL Server clients. The main two advantages to SSL over IPSec are:
- Minimal client configuration.
- Configuration on the server is straightforward.
To configure SSL on the server:
Obtaining and installing certificate is beyond the scope of this article, but you can obtain an SSL certificate from a third-party certificate authority e.g. VeriSign or you can install Windows Certificate services and supply own. (SQL Server can issue a self-signed certificate, but it isn’t meant for production use.)
- On the Start menu, click Run; then in the Open box type MMC, and click OK.
- In Microsoft Management Console (MMC), on the File menu, click Add/Remove Snap-in.
- In Add/Remove snap-in dialog box, click Add.
- In the Add Standalone Snap-in dialog box, click Certificates, then click Add.
- In the Certificates Snap-in dialog box, click Computer account, and then click Finish.
- In the Certificates MMC snap-in dialog box, expand Certificates, expand Personal, and then right-click Certificates; then point All Tasks, Import. The Certificate Import Wizard appears.
- Click Next, then Browse and locate the certificate file; then click OK.
- Click Next; again click Next to accept the default store; and then click Finish.
- Click OK to close the success dialog.
After you have installed certificate on the server, you need to configure the server to accept encrypted connections. Here is how to do that:
- Launch SQL Server Configuration Manager.
- Expand SQL Server Network Configuration.
- Right-click Protocols For<instance_name> and choose Properties.
- Activate Certificate tab, select the certificate from the list, and then click OK.
- Activate Flags tab, If you want all clients to connect using encryption, change ForceEncryption to Yes. If you want to support encrypted and unencrypted connections, keep it set to No.
- Click OK.
- Restart the SQL Server service.
You also need to configure the client computer. To do so:
- If necessary, install the root certificate for the certificate authority that issued the certificate you installed on SQL Server.
- Launch SQL Server Configuration Manager.
- Select SQL Native Client Configuration.
- Right-click in the Console pane and choose Properties.
- Set Force protocol encryption to Yes.
- Click OK.
One drawback to the ForceEncryption option is that it encrypts all data. Encryption causes performance degradation, so performance can suffer during communications. This can be noticeable when very large amounts of data are involved.
Encrypting a single connection
You can encrypt a single connection. For example, you might need to connect to a remote SQL Server to create login or user accounts. To do so:
- Open SQL Server Management Studio.
- Click Connect and choose Database Engine.
- Click Options.
- Check Encrypt connection.
- Click Connect.
This isn’t totally correct. When setting up SSL encryption between the SQL Server and a client connection, if the force encryption checkbox is setup on the server there is no client configuration change which is needed. The SQL Server Native Client and the SQL Server will automatically negotiate a secure connection.
The only time you need to configure anything on the client is if encryption on the server is an option and you want to require it. In SSMS this is done via a checkbox. In other applications this is done through a connection string attribute.
Thanks for commenting on the post.
My understanding is that client computer must trust the server certificate when they request SSL encryption. To do this you use MMC snap-in to export the Trusted Root Certification Authority used by the certificate and then import the certificate on the client computer. After that you need to use SQL Server Client Network Utility to force protocol encryption option.
Microsoft Reference: http://support.microsoft.com/kb/316898
The second note in that article specifically says not to force encryption on the server and client.
Well, I’ve not specifically said to not to force encryption on the server and client. I said, “If you want to support encrypted and un encrypted connections then keep ForceEncryption option to No”
Is this for encrypting traffic on databases within the LAN or public facing databases?
Yes that’s right.
If force encryption is set to No in sql server ….will it be needed to install certificates in client machine to have secure channel
Nice article. I’ve been searching for info on “Encrypting a single connection”
I want to force all external connections to be encrypted using SSL, essentially I want to encrypt only one of the network interfaces.
However, it seems to me that either all connections are forced to use encryption or all connections may be able to not use encryption at all. I want all external connections to be encrypted (no choice) and all internal connections (behind the firewall) to not be encrypted.
Can this be done at all?
Pingback: SQL Using SSL to encrypt a single connection
Hi, I followed the steps in your article until trying to configure the server to accept encrypted connections. I successfully a certificate in the MMC under Personal, but in SQL server configuration manager, in Properties of the protocol, no certificate was showing in the Certificate tab. I have tried many times but I still cannot see any certificate that I have imported. Any suggestion on how to fix it? Thanks a lot in advance.
What version of windows are you using? You cannot configure it on desktop OS.
Hi Basit, I’m using Windows Server 2008 Enterprise Edition R2.