SQL Server Driver for PHP Connection Options: Encrypt

This short post adds to my series on connection options in the SQL Server Driver for PHP. I’ll go into a bit more detail on the Encrypt and TrustServerCertificate options than the driver documentation does. I’ll start with three important points related to these options, then I’ll go into a couple of hypothetical situations that should shed more light on what these options actually do.

The first thing to note is that these two options, Encrypt and TrustServerCertificate, are often used together. The Encrypt option is used to specify whether or not the connection to the server is encrypted (the default is false). The TrustServerCertificate option is used to indicate whether the certificate on the SQL Server instance should be trusted (the default is false).

Note: Setting the Encrypt option does not mean that data is stored in an encrypted form. It simply means that data is encrypted while in transport to the server. Also note that this setting does not apply to authentication credentials such as a SQL Server password – authentication credentials are always encrypted. For information about storing encrypted data, see Encryption Hierarchy in the SQL Server documentation.

The second thing to note is that, by default, when SQL Server is installed it creates a self-signed certificate that it will use to encrypt connections. Of course, self-signed certificates are not ideal for secure connections (they are vulnerable to man-in-the-middle attacks), so it is best to replace this certificate with one from a certificate authority (CA).

The third thing to know is how to use these options when connecting to SQL Server. If you are using the SQLSRV API, your connection code might look something like this:

$serverName = "serverName";
$connectionInfo = array( "Database"=>"DbName",
                         "UID"=>"UserName",
                         "PWD"=>"Password",
                         "Encrypt"=>true,
                         "TrustServerCertificate"=>false);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

If you are using the PDO_SQLSRV API, your connection code might look something like this:

$serverName = "serverName";
$conn = new PDO("sqlsrv:Server = $serverName;
                 Database = DBName;
                 Encrypt = true;
                 TrustServerCertificate = false",
                 "UserName",
                 "Password");

Now, with those three things in mind, let’s look at a couple of examples. First suppose the following:

1. You did not replace the self-signed certificate created when SQL Server was installed.
2. You set Encrypt = true.
3. You set TrustServerCertificate = false.

In this scenario, your connection will fail. When you set TrustServerCertificate = false, you are asking for some 3rd party verification of the certificate. Because this is a self-signed certificate, there is no 3rd party to do the verification. However, if you set TrustServerCertificate = true, then your connection will succeed because you are trusting the certificate. (Note that, as mentioned earlier, this connection would be vulnerable to man-in-the-middle attacks.)

Now consider the following:

1. You replaced the self-signed certificate created when SQL Server was installed with a certificate from a certificate authority (CA).
2. You set Encrypt = true.

In this case (assuming there are no problems with your certificate), regardless of your setting for TrustServerCertificate, your connection will succeed. However, for a more secure connection (one not vulnerable to man-in-the-middle attacks), you should set TrustServerCertificate = false. Doing so will force third party verification of the certificate.

For information about installing a certificate on SQL Server, see How to: Enable Encrypted Connections to the Database Engine. Note that that topic references setting the Force Encryption option on the database server. Setting this option to Yes on the server does the same thing as setting Encryption = true on the client – it forces the connection to be encrypted using the server’s certificate.

That’s it for today. Thanks.

-Brian