How to Enable Secure SQL Database Connection String with XenDesktop

This article describes the options available for encrypting the SQL traffic between a XenDesktop Controller and an SQL Server.

Background

By default, the SQL traffic between a XenDesktop Controller and an SQL Server is unencrypted, and because of the nature of SQL, it is largely in plain text. If all the site’s Controllers and SQL Server are on a secure network this is not a concern, however in some cases it might be required to encrypt the SQL traffic using SSL. This can be accomplished in a number of ways as described in the following section.

Note:A XenDesktop 7 site might communicate with upto three separate SQL Servers (for its Site database, Monitoring data and Configuration Logging data); in this case the following discussion applies independently to each server used.

Encryption with Validation

Microsoft SQL Server provides three basic configurations described in the following section for encryption of SQL traffic between the Controllers (clients) and an SQL Server.

In all cases, a suitable certificate must be installed on the SQL Server that can be verified by an appropriate certificate chain on each Controller. This requires the root certificate of the server certificate's Certificate Authority (CA) be exported and installed on each Controller. Where connections to multiple SQL Servers are to be encrypted, the appropriate CA root certificate for each server must be installed on all Controllers.

Use of SSL in this way encrypts the data flowing through the SQL connections and authenticates the SQL Server identity to the XenDesktop Controllers.

See the following Microsoft articles for details of configuring encryption:

http://msdn.microsoft.com/en-us/library/ms191192(v=sql.110).aspx

http://support.microsoft.com/kb/316898

Following are the three possible encryption configurations as they relate to XenDesktop:

Forcing Encryption at the SQL Server

Encryption can be specified as a requirement at the SQL Server. In this case, all incoming SQL connections from any client (XenDesktop Controller or otherwise) are encrypted. This is configured by specifying Force Encryption on the SQL Server.

If encryption is forced at the SQL Server then the appropriate CA root certificate must also be provisioned to any machine used for performing database management operations through Studio (initial database configuration, adding controllers, or schema upgrades for example).

Forcing Encryption at the Controller (client)

Encryption can be specified as a requirement at each XenDesktop Controller. In this case, all outgoing connections from a Controller to any SQL Server are encrypted. This is configured by specifying Force Protocol Encryption on each Controller (client) machine.

If encryption is forced at the Controllers for a XenDesktop 7 site using multiple SQL Servers, appropriate certificates must be provisioned for all SQL Servers because all Controllers connect to all the servers.

Forcing Encryption for each Service

Encryption can be specified as a requirement on each individual SQL connection through a setting in a service’s SQL connection string. In this case, neither the Force Encryption option on the server nor the Force Protocol Encryption option on the Controllers should be specified.

在这个配置需要启用加密s the connection strings of the XenDesktop services on all Controllers to be modified by the addition of the ‘Encrypt=True’ option. That is, a connection string such as:
“Data Source=sqlserver.mydomain.net; Initial Catalog=CitrixXDSite; Integrated Security=True”
Becomes:
“Data Source=sqlserver.mydomain.net; Initial Catalog=CitrixXDSite; Integrated Security=True; Encrypt=True”

Warning!Setting the Encrypt=True option in a connection string where an appropriate certificate and CA root certificate have not been provisioned results in the connection failing; this prevents the impacted XenDesktop service from functioning.

加密没有验证

It is possible to configure encryption without provisioning appropriate server and client certificates. In this case, a self-signed certificate is used by the SQL Server. This style of encryption can be used in all three configurations listed above by setting the Trust Server Certificate option to True on the SQL Server, Controller (client) or in the connection string respectively.

While the resulting connections are encrypted, this is less secure than using provisioned certificates. It is vulnerable to man-in-the-middle attacks and is thus not recommended for production environments.

The interaction of the Force Protocol Encryption/Encrypt and Trust Server Certificate options on the client and within a connection string, and the use of self-signed certificates, is discussed in the following Microsoft article:
http://msdn.microsoft.com/en-us/library/ms131691(SQL.100).aspx

Testing Encryption with XenDesktop SDK

Using the XenDesktop 7 SDK it is possible to test whether a service’s SQL connection will work once encryption is enabled without interfering with the current configuration of the site. To do this, provision the required server and client certificates without enabling encryption, then on one Controller run the following commands in a PowerShell window:

$cs = Get-BrokerDBConnection
Test-BrokerDBConnection "$cs;Encrypt=True" | fl

If the certificates are correct and the encrypted connection is working then this should be displayed:

ServiceStatus : OK
ExtraInfo : {}

If however there is a problem, the ServiceStatus might for example display as:

ServiceStatus : Unknown ExtraInfo : {[SqlErrorText, A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)], [SqlErrorNumber, -2146893019], [ConnectionString, Data Source=sqlserver.mydomain.net;Initial Catalog=CitrixSiteDB;Integrated Security=True;Encrypt=True;Application Name=Citrix:SchemaProbe], [DataSource, ]}

Where an SQL Server is mirrored, clustered, or part of an SQL Server 2012 AlwaysOn availability group, other issues might be considered when enabling encryption, for example:http://msdn.microsoft.com/en-us/library/hh213417.aspx#SSLcertificates