With Column Encryption, insertion requires the column_list for tables with identity columns

[yitam]

When connecting with Column Encryption enabled, insertion into any table with an identity column requires explicit specification of the column_list.

Steps to reproduce:

1. Connect with ColumnEncryption=Enabled

    $conn = new PDO( "sqlsrv:server=$server;Database = $DB;ColumnEncryption=Enabled;", 
                            $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

2. Create a new table with an identity column, like this CREATE TABLE testDescParam (ID int identity(1,1), Col1 NVARCHAR(100))

3. Insert a value by binding param

    $val = 'BlahBlahBlah';
    $sql = "INSERT INTO testDescParam VALUES (:value)";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':value', $val);
    $stmt->execute();

Expected behavior:

Expect the above to succeed. That is, 'BlahBlahBlah' should be inserted into the first row with ID = 1

Actual behavior:

Caught this exception: SQLSTATE[22018]: [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification

To work around this, modify the above INSERT sql to $sql = "INSERT INTO testDescParam (Col1) VALUES (:value)";

[v-chojas]

This is a server-side limitation. With column encryption, the sp_describe_* method of getting parameter metadata doesn't work on encrypted columns, but the old method of FMTONLY can't identify IDENTITY columns either.

Regardless, it is always a good idea to specify the column list explicitly in an INSERT.