1

Always Encrypted/SS 2017 and ASP Classic. For those few of us still administrating ASP/VBscript applications, this post gets us to where I'm at... How to read SQL Always-encrypted column from classic ASP

My ODBC Driver 17 is successfully decrypting my AE encrypted column at the instruction of my ASP!

But, thus far in my journey, I am now experiencing the following error (fairly common error, apparently) when attempting to:

  1. 'authenticate' users using my existing vbScript/SQL or
  2. 'add records' using my existing vbScript/SQL or
  3. 'make changes to the encrypted column size' using SSMS.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AE_CEK', column_encryption_key_database_name = 'XXXX_db') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

After confirming AE should support my needs with several resources ("Using deterministic encryption allows equality searches"),

I'm thinking this has something to do with:

  1. 'collation' of the encrypted column being changed by AE to Latin1_General_BIN2...

  2. my encrypted column "nvarchar(15)" perhaps being interpreted as varchar(8000) when I declared it (15)...

  3. Something basic, when my ASP attempts to present raw data, to be encrypted with my driver, for equality comparison or insertion to the encrypted column......

I've spent many hours on AE and any guidance or support would be greatly appreciated? In a word, help.

Joe Latiolait
  • 11
  • 3

1 Answers1

0

Well, thanks for the help! For those who find this thread and need an answer... above, it's 3) Something basic.

Make sure you're passing a 'variable parameter' and not an input value or something else. A simple fix from A to B fixed my issue.

A) MM_rsUser_cmd.Parameters.Append MM_rsUser_cmd.CreateParameter("param2", 200, 1, 15, Request.Form("pwd"))

B) PwdVar = Request.Form("pwd") MM_rsUser_cmd.Parameters.Append MM_rsUser_cmd.CreateParameter("param2", 200, 1, 15, PwdVar)

Joe Latiolait
  • 11
  • 3