This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 77%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
I am using Microsoft SQL Azure (RTM) - 12.0.2000.8 , where I configured the secure enclave with VBS where I am using keyvalut for creating column master key. All the operators are working fine except like. Able to insert, update with where clause.
Tried with DETERMINISTIC and RANDOMIZED both encryption type.
Below code is working:
EXEC sp_describe_parameter_encryption
N'select * from test1 where gender like (@c1)',
N'@c1 varchar(10)';
Below code is failing
declare @c1 varchar(10)='%Male%'
select Name from test1 where gender like @c1
error:
An error occurred while executing batch. Error message is: Error occurred when reading 'sp_describe_parameter_encryption' resultset. Attestation URL has not been specified in the connection string, but the query requires enclave computations. Enclave type is 'VBS'.
Note: I have used all types of datatype for the column/variable as nvarchar,varchar,char
The error message says that Attestation URL is missing from the connection string. Select the Advanced button in the Connection dialog. In the old dialog, it's under the Always Encrypted tab. In the dialog, I have marked which one to fill in below:
In VBS case, no attestation required:
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
when using Secure Enclave, only direct equality is supported (if determinist encryption used), because only the encrypted values can be compared, as the server can not decrypt the stored data to do a like wildcard compare.
As I mentioned in the previous comment, I have tried with randomized also, but didn't work however the given link shared by you says:
| LIKE (Transact-SQL) | Supported |
|---|---|
| LIKE (Transact-SQL) | Supported |
For equality, not pattern matching. As sql can not decrypt the column value, it can only compare the two encrypted values. It has no way of knowing there is a wild card in the compare value. If you use non deterministic encryption, then there can not be any comparison.
Bruce, Ashish is asking about Always Encrypted with Enclaves where you can actually can run LIKE operations in SQL Server. This operations runs in enclave inside the processor which cannot be access by sysadmin or anyone else without the encryption keys. It's a fairly mind-blowing technology. You can read more about it here: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver17
@Erland Sommarskog Thanks for explaining. I have used the same given link to configure the secure enclave with VBS. Still, I am unable to use like operator to run my query.
Is there any SSMS tool (20.2) issue or any specific configuration missing.
@Erland Sommarskog Thanks for explaining. I have used the same given link to configure the secure enclave with VBS. Still, I am unable to use like operator to run my query.
That explanation was more aimed at Bruce who was barking up the wrong tree.
Is there any SSMS tool (20.2) issue or any specific configuration missing.
Did you look at my Answer below? I've added a version for SSMS 20 (and which should be good for SSMS 21 with the classic connection dialogue as well).
I was able to reproduce the error. It is another setting that is wrong. You need to set Attestation Protocol to None. When I left it as "Not specified" I got the same error as you.