Encrypting Connections to SQL Server
SQL Server supports Secure Sockets Layer (SSL) and is compatible with Internet Protocol security (IPSec).
Secure Sockets Layer (SSL)
Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients except DB Library and MDAC 2.53 clients.
SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server. Encryption with a self-signed certificate is possible and is described in the following section, but a self-signed certificate offers only limited protection.
The level of encryption used by SSL, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers.
Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance. When all traffic between SQL Server and a client application is encrypted using SSL, the following additional processing is required:
An extra network roundtrip is required at connect time.
Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.
Configuring SSL for SQL Server
The following procedure describes how to configure SSL for SQL Server.
To configure SSL
Install a certificate in the Windows certificate store of the server computer.
Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties.
Note
This is the Protocols for<instance_name> section in the left pane of the tool, not a specific protocol in the right pane.
On the Certificate tab, configure the Database Engine to use the certificate.
On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted.
When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.
SQL Server must be restarted after you change the ForceEncryption setting.
Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide protection against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate
Warning
SSL connections that are encrypted by using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.
Certificate Requirements
For SQL Server to load a SSL certificate, the certificate must meet the following conditions:
The certificate must be in either the local computer certificate store or the current user certificate store.
The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.
SQL Server Native Client Certificate Requirements
Applications that use "SERVER=shortname; ENCRYPT=yes" with certificate whose Subjects specify Fully Qualified Domain Names (FQDN's) have connected in the past due to relaxed validation. SQL Server 2008 R2 enhances security by enforcing an exact match of the subjects for certificates. Applications that rely upon relaxed validation must take one of the following actions:
Use the FQDN in the connection string.
This option does not require recompiling the application if the SERVER keyword of the connection string is configured outside the application.
This option does not work for applications that have their connection strings hardcoded.
This option does not work for applications that use Database Mirroring since the mirrored server replies with a simple name.
Add an alias for the shortname to map to the FQDN.
This option works even for applications that have their connection strings hardcoded.
This option does not work for applications that use Database Mirroring since the providers don’t look up aliases for received failover partner names.
Have a certificate issued for shortname.
- This option works for all applications.
Encryption on a Cluster
If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for <server> property box of SQL Server Network Configuration.
Internet Protocol Security (IPSec)
SQL Server data can be encrypted during transmission by using IPSec. IPSec is provided by the client and server operating systems and requires no SQL Server configuration. For information about IPSec, see your Windows or networking documentation.