1

I am currently investigating the use of the Always Encrypted feature for Microsoft SQL Server. I'm trying to simply store a blob object in a column encrypted table ('randomised') using pyodbc. Where the code works perfectly fine on non-encrypted columns for inserting arbitrary binary objects, it fails when running the same code on a column that is encrypted. Even more strange is the fact that it works for non-image files, but whenever I'm trying to upload a PDF, JPEG, PNG or similar, it fails.

The code looks like this.

import pyodbc

server   = 'tcp:XXXXX-XXXXXX-XXXXX-XXXXX-XXXXX.windows.net,1433'
database = 'db-encryption'
username = 'XXXXXX@dbs-always-encrypted'
password = 'XXXXXXXXX'

connection_string = [
    'DRIVER={ODBC Driver 17 for SQL Server}',
    'Server={}'.format(server),
    'Database={}'.format(database),
    'UID={}'.format(username),
    'PWD={}'.format(password),
    'Encrypt=yes',
    'TrustedConnection=yes',
    'ColumnEncryption=Enabled',
    'KeyStoreAuthentication=KeyVaultClientSecret',
    'KeyStorePrincipalId=XXXXX-XXXXXX-XXXXX-XXXXX-XXXXX',
    'KeyStoreSecret=XXXXX-XXXXXX-XXXXX-XXXXX-XXXXX'
]

cnxn = pyodbc.connect( ';'.join(connection_string) )
cursor = cnxn.cursor()

insert = 'insert into Blob (Data) values (?)'
files = ['Text.txt', 'SimplePDF.pdf']

for file in files:
    # without hex encode
    bindata = None
    with open(file, 'rb') as f:
        bindata = pyodbc.Binary(f.read())

    # insert binary
    cursor.execute(insert, bindata)
    cnxn.commit()

The error message I receive when trying to run the code on the encrypted 'Data' column (VARBINARY(MAX)) is the following

pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: image is incompatible with varbinary(max) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'db-encryption') (206) (SQLExecDirectW)")

It seems like the driver reads the bytes and sees that it is a 'known type' and treats the data as 'image'

Is there any way I can prevent this from happening? I simply wanna store any arbitrary byte object in said column.

mschmieder
  • 73
  • 8

1 Answers1

0

It might be late but the issue is with your driver. You must install the ODBC 17 driver or use {ODBC Driver 13 for SQL Server} or you can also try {SQL Server}. Download the driver from here

Kshitiz Tiwari
  • 31
  • 1
  • 2
  • As you can see in my post I’m using ODBC 17 driver already. I did try it with ODBC 13 driver as well and had no luck. – mschmieder Sep 23 '18 at 13:44