Microsoft Docs

SQL Server Always Encrypted: PHP Client Drivers

This document provides guidance on using PHP applications with SQL Server Always Encrypted, enabling transparent, client-side encryption of sensitive data.

Introduction to Always Encrypted

Always Encrypted is a client-side encryption technology that protects sensitive data in SQL Server. It ensures that data is always encrypted, both at rest and in transit, and that the database engine never sees the data in plaintext. This document focuses on the integration of Always Encrypted with PHP applications.

Prerequisites

• SQL Server 2016 or later with Always Encrypted configured.
• A column master key and column encryption key defined in SQL Server.
• The appropriate SQL Server PHP drivers installed.

Supported PHP Drivers and Versions

Always Encrypted support in PHP is primarily provided through the following drivers:

• Microsoft Drivers for PHP for SQL Server: This is the recommended driver for full Always Encrypted support. Ensure you are using a recent version that includes Always Encrypted capabilities.

Driver Installation

To install the Microsoft Drivers for PHP for SQL Server, follow the official installation guide for your operating system and PHP version. This typically involves downloading the drivers and configuring your php.ini file.

Example php.ini configuration:

extension=php_sqlsrv_81_nts_x64.dll
extension=php_pdo_sqlsrv_81_nts_x64.dll

Note: Replace DLL names with the appropriate ones for your PHP version and architecture.

Connecting to SQL Server with Always Encrypted in PHP

When connecting to a SQL Server instance with Always Encrypted enabled, you need to specify the appropriate connection options. The primary mechanism involves using the Encrypt=1 and TrustServerCertificate=0 (or 1 depending on your certificate setup) options, along with ensuring the client machine has access to the column master key.

Using PDO_SQLSRV Driver

The PDO_SQLSRV driver offers a straightforward way to connect. You'll typically use a DSN (Data Source Name) that includes encryption settings.

try {
    $serverName = "your_server_name";
    $connectionOptions = [
        "Database" => "your_database",
        "Uid" => "your_username",
        "PWD" => "your_password",
        "Encrypt" => 1, // Enable encryption
        "TrustServerCertificate" => 0 // Set to 1 if using a self-signed certificate and want to trust it
    ];
    $conn = new PDO("sqlsrv:Server=$serverName;Database=your_database", $connectionOptions["Uid"], $connectionOptions["PWD"], $connectionOptions);

    // Always Encrypted specific configurations might be needed in more complex scenarios
    // For basic column encryption, the driver often handles it automatically if keys are accessible.

    echo "Connected successfully using PDO_SQLSRV.";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

Using SQLSRV Native Driver

The native SQLSRV driver can also be used for Always Encrypted connections.

try {
    $serverName = "your_server_name";
    $connectionOptions = [
        "Database" => "your_database",
        "Uid" => "your_username",
        "PWD" => "your_password",
        "Encrypt" => 1,
        "TrustServerCertificate" => 0
    ];

    $conn = sqlsrv_connect($serverName, $connectionOptions);

    if ($conn === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    echo "Connected successfully using SQLSRV driver.";

} catch (Exception $e) {
    die("Connection failed: " . $e->getMessage());
}

Working with Encrypted Data

When you query data from columns encrypted with Always Encrypted, the PHP driver transparently decrypts the data. Similarly, when you insert or update data into encrypted columns, the driver encrypts it before sending it to the database.

Example: Reading Encrypted Data

Assume you have a table Customers with an encrypted column CreditCardNumber.

// Assuming $conn is a valid PDO or SQLSRV connection
$sql = "SELECT CustomerID, Name, CreditCardNumber FROM Customers WHERE CustomerID = 1";
$stmt = $conn->query($sql); // For PDO
// $stmt = sqlsrv_query($conn, $sql); // For SQLSRV

if ($stmt) {
    // For PDO
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Credit Card Number: " . $row['CreditCardNumber']; // Data is automatically decrypted

    // For SQLSRV
    // if (sqlsrv_has_rows($stmt)) {
    //     $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    //     echo "Credit Card Number: " . $row['CreditCardNumber'];
    // }
} else {
    echo "Error executing query.";
}

Example: Writing Encrypted Data

When writing to an encrypted column, simply provide the plaintext value. The driver handles the encryption.

// Assuming $conn is a valid PDO or SQLSRV connection
$customerId = 2;
$newCreditCardNumber = '4111-1111-1111-1111'; // Plaintext value

$sql = "UPDATE Customers SET CreditCardNumber = ? WHERE CustomerID = ?";
$stmt = $conn->prepare($sql); // For PDO
// $sql = "UPDATE Customers SET CreditCardNumber = ? WHERE CustomerID = ?"; // For SQLSRV

if ($stmt) {
    // For PDO
    $success = $stmt->execute([$newCreditCardNumber, $customerId]);

    // For SQLSRV
    // $params = array(&$newCreditCardNumber, &$customerId);
    // $success = sqlsrv_query($conn, $sql, $params);

    if ($success) {
        echo "Credit card number updated successfully.";
    } else {
        echo "Error updating credit card number.";
    }
} else {
    echo "Error preparing statement.";
}

Managing Column Master Keys

For Always Encrypted to work, the PHP application (and the client drivers) must be able to access the Column Master Key (CMK). This typically involves:

• Windows Certificate Store: If the CMK is stored in the Windows Certificate Store on the machine running the PHP application, the driver can access it directly.
• Azure Key Vault: For more robust key management, CMKs can be stored in Azure Key Vault. PHP applications can authenticate with Azure Key Vault to retrieve the keys. This requires the Azure SDK for PHP and appropriate permissions.

Troubleshooting

• "Encryption scheme is not supported" error: This usually indicates that the driver version is too old, or Always Encrypted is not properly configured on the server or client.
• Key access errors: Verify that the CMK is accessible from the client machine where the PHP application is running and that the correct permissions are in place.
• Driver issues: Ensure the SQLSRV and PDO_SQLSRV extensions are correctly installed and enabled in php.ini.

Performance Considerations

Always Encrypted adds overhead due to client-side encryption and decryption. For performance-critical applications, consider the following:

• Encrypt only sensitive columns.
• Use parameterized queries to allow for efficient processing.
• Ensure efficient key retrieval mechanisms, especially when using Azure Key Vault.

By following these guidelines, you can effectively implement Always Encrypted in your PHP applications to enhance data security.