Connecting to MSSQL from PHP securely with encryption?

Question

I need to connect to a MSSQL database from PHP. However, as a server on a remote site is connected, I require the connection to be encrypted.

Is it possible to use encrypt the connection to the MSSQL server using only mssql extension for PHP or alternatively PDO?

Answer 1

There is 3 things that are important when implementing a secure (encrypted) connection to MSSQL:

1. The options Encrypt and TrustServerCertificate are often used together.
2. By default the SQL server installs a self-signed certificate that it will use to encrypt connections - the self signed certificate are however open to attacks. So it should be replaced with one from a certificate authority (CA).
3. After replacing your certificate, you then set Encrypt = true and TrustServerCertificate = false (TrustServerCertificate = true will also work, but your connection will then be vulnerable to attacks)

Code-example from article *1:

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

If you use PDO create an object and pass the relevant params. For a more detailed explanation please see the following article:

*1 - http://blogs.msdn.com/b/brian_swan/archive/2011/03/08/sql-server-driver-for-php-connection-options-encrypt.aspx