Encrypting Connections to SQL Server
SQL Server supports Secure Sockets Layer (SSL) and is compatible with Internet Protocol security (IPSec).
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.
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
Caution |
---|
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. |
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.
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.
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.
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.
Article ID: 137, Created On: 2/8/2018, Modified: 2/8/2018