Features

Azure Active Directory
Emulate prepare in PDO_SQLSRV driver
Idle Connection Resiliency
Querying sql_variant columns
Connection pooling in Linux and Mac
lastInsertID
Using Always Encrypted with the PHP driver
Always Encrypted: Limitations
Optional "Driver" Connection option
Binding Decimals as Parameters
Using Azure Key Vault for Column Encryption
Retrieving DateTime values as strings or PHP DateTime objects
Configurable options to format fetched decimal or numeric values
Language Selection
Data Classification Sensitivity Metadata
PHP Extended String Types for PDO
Always Encrypted with Secure Enclaves

Azure Active Directory

Azure Active Directory (Azure AD) is a central user ID management technology that operates as an alternative to SQL Server authentication. Azure AD allows connections to Microsoft Azure SQL Database and SQL Data Warehouse with federated identities in Azure AD using a username and password or using Windows Integrated Authentication.

Starting with 5.6.0, the support for Azure AD access token (requires ODBC Driver version 17 or above in Linux or macOS) and managed identities for Azure resources (requires ODBC Driver version 17.3.1.1 or above) are introduced. Please check Connect Using Azure Active Directory Authentication for more details.

Emulate prepare in PDO_SQLSRV driver

When PDO::ATTR_EMULATE_PREPARES is on for a prepared statement, the PDO_SQLSRV driver internally replaces the placeholders in a SQL query prepared by PDO::prepare() with parameters that are bounded with PDOStatement::bindParam(), and a complete SQL string with no placeholders left is sent to the database.

Please check the online docs for details and an example of how to use Emulate Prepare

Idle Connection Resiliency

Connection resiliency is the idea that a connection that is broken can be reestablished, within certain constraints. If a connection to Microsoft SQL Server fails, connection resiliency allows the client to automatically attempt to reestablish the connection. Connection resiliency is a property of the data source; only SQL Server 2014 and later and Azure SQL Database support connection resiliency.

Please check the online docs for Connection resiliency.

Querying sql_variant columns

sql_variant is a data type that stores values of various SQL Server-supported data types. A column of type sql_variant may contain rows of different data types. For instance, a column defined as sql_variant can store int, binary, and char values.

For an example for SQLSRV driver, please check sqlsrv_query. Likewise, the PDO_SQLSRV example can be found in PDO::query.

Connection pooling in Linux and Mac

A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all connections are being used, a new connection is made and added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

For more details and examples about connection pooling, please check online docs Connection Pooling

Transparent NetworkIP Resolution (TNIR)

Transparent Network IP Resolution is a revision of the existing MultiSubnetFailover feature that affects the connection sequence of the driver in the case where the first resolved IP of the hostname does not respond and there are multiple IPs associated with the hostname. It interacts with MultiSubnetFailover to provide different connection sequences.

For details, please check online docs about Transparent Network IP Resolution (TNIR)

lastInsertID

Version 5.0 of the Microsoft PHP Drivers for SQL Server changes the behaviour of PDO::lastInsertID to be consistent with the behaviour outlined in the PDO documentation. PDO::lastInsertID returns the ID of the last inserted sequence or row, and accepts an optional string $name as its only parameter:

public string PDO::lastInsertId ([ string $name = NULL ] )

The $name argument is the identifier of the sequence object for which the ID is returned.

Before version 5.0 of the drivers, the behaviour was as follows. The $name argument was treated as a table name and not a sequence name. Therefore, passing a sequence object to lastInsertID produced no output. Passing a table name produced the last inserted ID for that table.

For more information and an example, please check PDO::lastInsertId

Using Always Encrypted with the PHP driver

Always Encrypted allows client application to encrypt data and never reveal the encryption keys or data to the SQL Server. To understand it or for more background information, please see Always Encrypted.

Prerequisites

Configure Always Encrypted in your database. Follow the directions in Getting Started with Always Encrypted to configure Always Encrypted in your database.
A keystore that houses the master and column encryption keys is required. Currently the PHP drivers support the Windows Certificate Store or Azure Key Vault. Always Encrypted is supported on Windows, Linux, and macOS through Azure Key Vault.
MSODBC driver 17, available here.

For details of how to use the Always Encrypted feature, please check the online docs Using Always Encrypted with the PHP Drivers for SQL Server.

Always Encrypted: Limitations

For the lists of limitations in SQL Server and the SQLSRV and PDO_SQLSRV drivers regarding Always Encrypted, please check online docs here.

Optional "Driver" connection option

SQLSRV and PDO_SQLSRV rely on Microsoft ODBC Driver for SQL Server to communicate with SQL Server. If you want to specify which ODBC Driver to use, you can set the "Driver" option with these possible values are:

ODBC Driver 17 for SQL Server ODBC Driver 13 for SQL Server ODBC Driver 11 for SQL Server (Windows only).

By default, when the Driver keyword is not specified, the Microsoft Drivers for PHP for SQL Server attempt to find the supported Microsoft ODBC driver(s) in the system, starting with the latest version of ODBC and so on. If none found, the exception "This extension requires the Microsoft ODBC Driver for SQL Server." is thrown.

SQLSRV:

$connectionOptions = array("Database"=>$database,"UID"=>$userName, "PWD"=>$userPassword, "Driver"=>"ODBC Driver 17 for SQL Server");
$conn = sqlsrv_connect($server, $connectionOptions);

PDO_SQLSRV:

 $connectionOptions = "Driver = ODBC Driver 17 for SQL Server";
 $conn = new PDO("sqlsrv:server = $server; $connectionOptions", $uid, $pwd);

Binding Decimals as Parameters

Both SQLSRV and PDO_SQLSRV support binding decimals as parameters (see the examples in sqlsrv_query and PDOStatement::bindParam).

However, PHP has warned that floating point numbers have limited precision. To illustrate, see the following scenario:

<?php

$number = 999999999999.99999;
printf("%f", $number);
echo PHP_EOL;

$number = 9223372036854.80000;
printf("%f", $number);
echo PHP_EOL;
?>

The output is

1000000000000.000000
9223372036854.800781

Therefore, to ensure that the accuracy is preserved when binding decimals as parameters, it is recommended to use strings as inputs. Below are some examples:

PDO_SQLSRV

$input = 9223372036854.80000;
$stmt = $conn->prepare("INSERT INTO $tbname (col) VALUES (?)");
// by default it is PDO::PARAM_STR, rounding of the input value 
// occurs if PDO::PARAM_INT is specified
$stmt->bindParam(1, $input);    
$stmt->execute();

SQLSRV

$input = "9223372036854.80000";
$params = array($input);
$stmt = sqlsrv_prepare($conn, "INSERT INTO $tbname (col) VALUES (?)", $params);
sqlsrv_execute($stmt);

The above suggestion applies to decimal and numeric data types in SQL Server.

Using Azure Key Vault for Column Encryption

You can use Azure Key Vault (AKV) to store the master and column encryption keys required for Always Encrypted functionality to work. With AKV, Always Encrypted is supported on Windows, Linux, and macOS. To get started with AKV, see the documentation. To connect to AKV from a PHP script, you can use the credentials for an Azure account consisting of either an Azure Active Directory username and password, or an application client ID and client secret. The encryption keys can be easily created in SQL Server Management Studio or using PowerShell; details here. Support for AKV is provided by the underlying ODBC Driver (version 17 is required), as detailed here.

To use AKV from a PHP script, please check the online docs Using Azure Key Vault.

Retrieving DateTime values as strings or PHP DateTime objects

When using SQLSRV, by default, datetime, Date, Time, DateTime2, and DateTimeOffset types are returned as PHP DateTime types. To retrieve these data types as strings, set the connection option ReturnDatesAsStrings to true. The same option is added to the statement level in 5.6.0, which always override the corresponding connection option.

On the other hand, when using PDO_SQLSRV, datetime, Date, Time, DateTime2, and DateTimeOffset types are by default returned as strings. Starting with 5.6.0, the new flag PDO_STMT_OPTION_FETCHES_DATETIME_TYPE is introduced such that these types can be retrieved as PHP DateTime objects. This flag can be set as a connection attribute or statement attribute, and the latter always overrides the corresponding connection attribute.

Note that for both drivers, retrieving DateTime as objects are only allowed for regular fetching, not as output parameters. In addition, neither PDO::ATTR_STRINGIFY_FETCHES nor PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE affects whether DateTime data types are returned as strings or not. For details, please read

Configurable options to format fetched decimal or numeric values

Unlike integers and floats, which can be retrieved as numbers, in order to preserve the accuracy of decimal or numeric types, their values are always fetched as strings with exact precisions. However, if the values are less than 1, the leading zeroes are missing. Note that the same applies to money and smallmoney fields because they are a subset of decimal fields with fixed precisions and scales.

Starting with 5.6.0, the option FormatDecimals is added to sqlsrv connection and statement levels, which allows the user to format decimal strings. This option expects a boolean value (i.e. true or false) and only affects the decimal / numeric data of fetched results. In other words, the FormatDecimals option has no effect on other operations like insertion or update.

By default, FormatDecimals is false. If set to true, the leading zeroes to decimal strings will be added, if missing. In addition, another option is provided at connection and statement levels, DecimalPlaces, for users to configure the number of decimal places when displaying money and smallmoney data. It accepts integer values in the range of [0, 4]. The underlying data remains the same, but rounding may occur when shown. For this reason, it is not recommended to use the formatted results as inputs to any calculations.

Note that DecimalPlaces option only affects money data, and FormatDecimals must be set to true for it to take effect. In other words, if FormatDecimals is set to false, formatting will be turned off, regardless of DecimalPlaces value. Also, since money or smallmoney fields have scale 4, setting DecimalPlaces value to any negative number or any value larger than 4 will be ignored.

The equivalent connection and statement attributes in pdo_sqlsrv are PDO::SQLSRV_ATTR_FORMAT_DECIMALS and PDO::SQLSRV_ATTR_DECIMAL_PLACES. For both drivers, the statement attributes always override the corresponding connection attributes. For details, please check

Language Selection

The SQLSRV and PDO_SQLSRV drivers allow the user to specify a preferred language using the Language connection option. The effect of this option is similar to executing SET LANGUAGE. The available languages come from the server's syslanguages table. To see the full list of available languages, execute the following query: SELECT name,alias FROM sys.syslanguages.

Note that this option only affects the language of messages returned from the server. It does not affect the language used by the drivers themselves, as they are currently available only in English, and it does not affect the language of the underlying ODBC driver, whose language is determined by the localised version installed on the client system. Therefore, it is possible that changing the Language setting will result in messages being returned in different languages, depending on whether they come from the PHP driver, the ODBC driver, or SQL Server.

The following example sets Language to German:

$conn = sqlsrv_connect('myserver', array('uid'=>'sa', 
                                        'pwd'=>'mypassword',
                                        'CharacterSet'=>'UTF-8',
                                        'Language'=>'German'));

$stmt = sqlsrv_query($conn, "select *, BadColumn from sys.syslanguages");
if (!$stmt) {
    print_r(sqlsrv_errors());
}

The output is:

Array
(
    [0] => Array
        (
            [0] => 42S22
            [SQLSTATE] => 42S22
            [1] => 207
            [code] => 207
            [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ungültiger Spaltenname "BadColumn".
            [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ungültiger Spaltenname "BadColumn".
        )

)

Data Classification Sensitivity Metadata

Data classification includes two metadata attributes:

Sensitivity Labels - define the sensitivity level of the data stored in the column
Information Types - provide additional granularity into the type of data stored in the column

We can use the classification engine of SQL Server Management Studio (SSMS) to scan the database for columns containing potentially sensitive data, which then provides a list of recommended column classifications. We can also manually classify columns as an alternative, or in addition to the recommendation-based classification.

Another option is to use Transact-SQL to classify sensitivity data. Theoretically, each column may have more than one tuple of (label, information type), but for simplicity the following examples use only one tuple per column.

Take a Patients table for example:

CREATE TABLE Patients 
      [PatientId] int identity,
      [SSN] char(11),
      [FirstName] nvarchar(50),
      [LastName] nvarchar(50),
      [BirthDate] date)

We can classify the SSN and BirthDate columns as shown below:

ADD SENSITIVITY CLASSIFICATION TO [Patients].SSN WITH (LABEL = 'Highly Confidential - GDPR', INFORMATION_TYPE = 'Credentials')
ADD SENSITIVITY CLASSIFICATION TO [Patients].BirthDate WITH (LABEL = 'Confidential Personal Data', INFORMATION_TYPE = 'Birthdays')

To access the classification metadata defined in SQL Server 2019, run the following query:

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
    information_type,
    label
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id

The results are

schema_name	table_name	column_name	information_type	label
dbo	Patients	BirthDate	Birthdays	Confidential Personal Data
dbo	Patients	SSN	Credentials	Highly Confidential - GDPR

Starting in version 5.7.0-preview of PHP drivers, new statement attributes (options) have been added to the query / prepare methods to specifically request sensitivity classification metadata using the existing metadata functions. The query is a SELECT statement that includes the table columns of interest, which may or may not have sensitivity classification metadata defined.

SQLSRV driver

The sqlsrv_field_metadata() function will return data classification sensitivity metadata if the new DataClassification option is true (false by default). Only SSN and Birthdate columns contain sensitivity metadata.

$tableName = 'Patients';
$tsql = "SELECT * FROM $tableName";
$stmt = sqlsrv_prepare($conn, $tsql, array(), array('DataClassification' => true));
if (sqlsrv_execute($stmt)) {
    $fieldmeta = sqlsrv_field_metadata($stmt);

    foreach ($fieldmeta as $f) {
        if (count($f['Data Classification']) > 0) {
            echo $f['Name'] . ": \n";
            print_r($f['Data Classification']); 
        }
    }
}

The output will be:

SSN: 
Array
(
    [0] => Array
        (
            [Label] => Array
                (
                    [name] => Highly Confidential - GDPR
                    [id] => 
                )

            [Information Type] => Array
                (
                    [name] => Credentials
                    [id] => 
                )

        )

)
BirthDate: 
Array
(
    [0] => Array
        (
            [Label] => Array
                (
                    [name] => Confidential Personal Data
                    [id] => 
                )

            [Information Type] => Array
                (
                    [name] => Birthdays
                    [id] => 
                )

        )

)

If the user wishes to see the metadata of each column using sqlsrv_query, the user can modify the above script as follows, using json_encode:

$tableName = 'Patients';
$tsql = "SELECT * FROM $tableName";
$stmt = sqlsrv_query($conn, $tsql, array(), array('DataClassification' => true));
$fieldmeta = sqlsrv_field_metadata($stmt);

foreach ($fieldmeta as $f) {
    $jstr = json_encode($f);
    echo $jstr . PHP_EOL;
}

Please note that by default, Data Classification is NOT included, unless the user sets the option DataClassification to true.

{"Name":"PatientId","Type":4,"Size":null,"Precision":10,"Scale":null,"Nullable":0,"Data Classification":[]}
{"Name":"SSN","Type":1,"Size":11,"Precision":null,"Scale":null,"Nullable":1,"Data Classification":[{"Label":{"name":"Highly Confidential - GDPR","id":""},"Information Type":{"name":"Credentials","id":""}}]}
{"Name":"FirstName","Type":-9,"Size":50,"Precision":null,"Scale":null,"Nullable":1,"Data Classification":[]}
{"Name":"LastName","Type":-9,"Size":50,"Precision":null,"Scale":null,"Nullable":1,"Data Classification":[]}
{"Name":"BirthDate","Type":91,"Size":null,"Precision":10,"Scale":0,"Nullable":1,"Data Classification":[{"Label":{"name":"Confidential Personal Data","id":""},"Information Type":{"name":"Birthdays","id":""}}]}

PDO_SQLSRV driver

Similarly, one of the fields returned by PDOStatement::getColumnMeta() is "flags", which specifies the flags set for the column (always 0). Beginning with version 5.7.0-preview, the user can set the new statement attribute PDO::SQLSRV_ATTR_DATA_CLASSIFICATION to true, like so:

$options = array(PDO::SQLSRV_ATTR_DATA_CLASSIFICATION => true);
$tableName = 'Patients';
$tsql = "SELECT * FROM $tableName";
$stmt = $conn->prepare($tsql, $options);
$stmt->execute();
$numCol = $stmt->columnCount();

for ($i = 0; $i < $numCol; $i++) {
    $metadata = $stmt->getColumnMeta($i);
    $jstr = json_encode($metadata);
    echo $jstr . PHP_EOL;
}

The output of metadata for all columns is shown below:

{"flags":{"Data Classification":[]},"sqlsrv:decl_type":"int identity","native_type":"string","table":"","pdo_type":2,"name":"PatientId","len":10,"precision":0}
{"flags":{"Data Classification":[{"Label":{"name":"Highly Confidential - GDPR","id":""},"Information Type":{"name":"Credentials","id":""}}]},"sqlsrv:decl_type":"char","native_type":"string","table":"","pdo_type":2,"name":"SSN","len":11,"precision":0}
{"flags":{"Data Classification":[]},"sqlsrv:decl_type":"nvarchar","native_type":"string","table":"","pdo_type":2,"name":"FirstName","len":50,"precision":0}
{"flags":{"Data Classification":[]},"sqlsrv:decl_type":"nvarchar","native_type":"string","table":"","pdo_type":2,"name":"LastName","len":50,"precision":0}
{"flags":{"Data Classification":[{"Label":{"name":"Confidential Personal Data","id":""},"Information Type":{"name":"Birthdays","id":""}}]},"sqlsrv:decl_type":"date","native_type":"string","table":"","pdo_type":2,"name":"BirthDate","len":10,"precision":0}

If PDO::SQLSRV_ATTR_DATA_CLASSIFICATION is false (the default case), the output of all metadata will be:

{"flags":0,"sqlsrv:decl_type":"int identity","native_type":"string","table":"","pdo_type":2,"name":"PatientId","len":10,"precision":0}
{"flags":0,"sqlsrv:decl_type":"char","native_type":"string","table":"","pdo_type":2,"name":"SSN","len":11,"precision":0}
{"flags":0,"sqlsrv:decl_type":"nvarchar","native_type":"string","table":"","pdo_type":2,"name":"FirstName","len":50,"precision":0}
{"flags":0,"sqlsrv:decl_type":"nvarchar","native_type":"string","table":"","pdo_type":2,"name":"LastName","len":50,"precision":0}
{"flags":0,"sqlsrv:decl_type":"date","native_type":"string","table":"","pdo_type":2,"name":"BirthDate","len":10,"precision":0}

PHP Extended String Types for PDO

New PDO constants were introduced in PHP 7.2 to add support for binding Unicode and non-Unicode strings. That is, Unicode strings can be surrounded with quotes with an N as a prefix. (i.e. N'string' instead of 'string'.)

PDO::PARAM_STR_NATL - a new type for Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
PDO::PARAM_STR_CHAR - a new type for non-Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
PDO::ATTR_DEFAULT_STR_PARAM - set to either PDO::PARAM_STR_NATL or PDO::PARAM_STR_CHAR to indicate a value to bitwise-OR to PDO::PARAM_STR by default

When using the pdo_sqlsrv driver, these new string constants affect how PDO::quote() works, and affect emulated prepared statements. They do not affect real prepared statements.

PDO::quote()

$db->quote('über', PDO::PARAM_STR | PDO::PARAM_STR_NATL); // N'über'
$db->quote('foo'); // 'foo'

$db->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);
$db->quote('über'); // N'über'
$db->quote('foo', PDO::PARAM_STR | PDO::PARAM_STR_CHAR); // 'foo'

Emulate prepare statements

The PDO::ATTR_EMULATE_PREPARES attribute is false by default, so set PDO::ATTR_EMULATE_PREPARES to true when using PDO::prepare() to emulate prepare the statement. For more details, please check the emulate prepare examples.

Note that pdo_sqlsrv behavior is different from pdo_mysql and pdo_dblib, as emulate prepared statements is the default for those drivers.

To ensure that the N prefix is used to quote the string values, previously we can use the UTF8 encoding constant, PDO::SQLSRV_ENCODING_UTF8. With PHP 7.2 and above, we can also use the new constant PDO::PARAM_STR_NATL.

Using driver option PDO::SQLSRV_ENCODING_UTF8 when binding

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->bindParam(':value', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_UTF8);
$stmt->execute();

Using the PDO::SQLSRV_ATTR_ENCODING attribute

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true, PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8);
$stmt = $conn->prepare($sql, $options);
$stmt->execute([':value' => $p]);

Using the new constant PDO::PARAM_STR_NATL

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->bindParam(':value', $p, PDO::PARAM_STR | PDO::PARAM_STR_NATL);
$stmt->execute();

Setting the default string param type PDO::PARAM_STR_NATL

$conn->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);
$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->execute([':value' => $p]);

Real prepare statements

The emulate prepare attribute is false by default, so the extended string types will be ignored.

Note that the default encoding for pdo_sqlsrv driver is UTF-8. Thus, there is no need to do anything extra for Unicode values. On the other hand, if the bound data is targeted for CHAR/VARCHAR columns rather than NCHAR/NVARCHAR ones, the user can use the option PDO::SQLSRV_ENCODING_SYSTEM to avoid implicit conversions.

$options = array(PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM);
$statement = $pdo->prepare(
  'SELECT *
   FROM myTable
   WHERE myVarcharColumn = :myVarcharValue',  
  $options
);

$statement->bindValue(':myVarcharValue', 'my data', PDO::PARAM_STR);

Below shows an alternative:

$statement = $pdo->prepare(
  'SELECT *
   FROM myTable
   WHERE myVarcharColumn = :myVarcharValue'
);
$p = 'my data';
$statement->bindParam(':myVarcharValue', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_SYSTEM);

Always Encrypted with Secure Enclaves

Always Encrypted with Secure Enclaves is the second iteration of the Always Encrypted feature for SQL Server. Always Encrypted with Secure Enclaves allows users to perform rich computations against encrypted data by creating a secure enclave - a region of memory on the server where encrypted data in a database is decrypted so that computations may be performed. Support for Always Encrypted with Secure Enclaves is available in the PHP Driver for SQL Server starting with 5.7.1-preview. Always Encrypted with Secure Enclaves requires SQL Server 2019 or later and version 17.4+ of the ODBC driver.

Always Encrypted with Secure Enclaves ensures the security of encrypted data by attesting the enclave - that is, verifying the enclave against an external attestation service. To use secure enclaves, the ColumnEncryption keyword must identify the attestation type and protocol along with associated attestation data, separated by a comma. Version 17.4 of the ODBC driver supports only Virtualization-Based Security (VBS) and the Host Guardian Service (HGS) protocol for the enclave type and protocol. The associated attestation data is the URL of the attestation server. Thus, the following would be added to the connection string:

ColumnEncryption=VBS-HGS,http://attestationserver.mydomain/Attestation

If the protocol is incorrect, the driver will not recognise it, connection will fail, and an error will be returned. If only the attestation URL is incorrect, connection will succeed and an error will be thrown when an enclave-enabled computation is attempted, but otherwise the behaviour will be identical to the original Always Encrypted behaviour. Setting ColumnEncryption to enabled will provide regular Always Encrypted functionality, but attempting an enclave-enabled operation will return an error.

Full details for configuring your environment to support Always Encrypted with Secure Enclaves, including setting up the Host Guardian Service and creating the required encryption keys, can be found here.

Example

The following examples, one for SQLSRV and one for PDO_SQLSRV, create a table with several data types in plaintext, then encrypt it and carry out comparisons and pattern matching. Note the following:

When encrypting a table with ALTER TABLE, only one column may be encrypted for each call to ALTER TABLE, so multiple calls are required to encrypt multiple columns.
When passing the comparison threshold as a parameter for comparing char and nchar types, the column width must be specified in the corresponding SQLSRV_SQLTYPE_*, or the error HY104, Invalid precision value, will be returned.
For pattern matching, the collation must be specified as Latin1_General_BIN2 using the COLLATE clause.
When passing the pattern matching string as a parameter for matching char and nchar types, the SQLSRV_SQLTYPE_* passed to sqlsrv_query or sqlsrv_prepare should specify the length of the string to be matched and not the size of the column because char and nchar types pad whitespace on the end of the string. For example, when matching the string %abc% against a char(10) column, specify SQLSRV_SQLTYPE_CHAR(5). If you instead specify SQLSRV_SQLTYPE_CHAR(10), the query will match %abc% (with five spaces appended), and any data in the column with fewer than five spaces appended will not match (so abcdef would not match %abc% because it has four spaces of padding). For Unicode strings, use the mb_strlen or iconv_strlen functions to get the number of characters.
The PDO interface does not allow specifying the length of a parameter. Instead, specify a length of 0 or null in PDOStatement::bindParam. If the length is explicitly set to another number, the parameter is treated as an output parameter.
Pattern matching does not work against non-string types in AE.
Error checking is excluded for clarity. What follows is common data for both examples:

<?php
// Data for testing - integer, datetime2, char, nchar, varchar, and nvarchar
// String data is random, showing that we can match or compare anything
$testValues = array(array(1, "2019-12-31 01:00:00", "abcd", "㬚㔈♠既", "abcd", "㬚㔈♠既"),
                    array(-100, "1753-01-31 14:25:25.25", "#e@?q&zy+", "ઔܛ᎓Ե⅜", "#e@?q&zy+", "ઔܛ᎓Ե⅜"),
                    array(100, "2112-03-15 23:40:10.1594", "zyxwv", "㶋㘚ᐋꗡ", "zyxwv", "㶋㘚ᐋꗡ"),
                    array(0, "8888-08-08 08:08:08.08", "7t", "㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ", "7t", "㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ"),
                    );

// Queries to create the table and insert data
$createTable = "DROP TABLE IF EXISTS $myTable; 
                CREATE TABLE $myTable (c_integer int NULL, 
                                       c_datetime2 datetime2(7) NULL, 
                                       c_char char(32) NULL, 
                                       c_nchar nchar(32) NULL, 
                                       c_varchar varchar(32) NULL, 
                                       c_nvarchar nvarchar(32) NULL);";
$insertData = "INSERT INTO $myTable (c_integer, c_datetime2, c_char, c_nchar, c_varchar, c_nvarchar) VALUES (?, ?, ?, ?, ?, ?)";

// This is the query that encrypts the table in place
$encryptQuery = " ALTER TABLE $myTable
                      ALTER COLUMN [c_integer] integer
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER TABLE $myTable
                      ALTER COLUMN [c_datetime2] datetime2(7)
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER TABLE $myTable
                      ALTER COLUMN [c_char] char(32) COLLATE Latin1_General_BIN2
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER TABLE $myTable
                      ALTER COLUMN [c_nchar] nchar(32) COLLATE Latin1_General_BIN2
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER TABLE $myTable
                      ALTER COLUMN [c_varchar] varchar(32) COLLATE Latin1_General_BIN2
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER TABLE $myTable
                      ALTER COLUMN [c_nvarchar] nvarchar(32) COLLATE Latin1_General_BIN2
                      ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
                      WITH (ONLINE = ON);
                  ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;";
?>