Need Resolution for SQL query using Always encrypted field

Question

We are working on Always Encrypt SQLServer 2017

To start with Followed following instructions to create tables

https://www.mssqltips.com/sqlservertip/4011/sql-server-2016-always-encrypted/

So for example :

Query 1 : Need information to query data on a table using Always Encrypted field using WHERE

1. Created table as :

exec sp_addtype SmStringReqN, "nvarchar(255)", "NOT NULL"
go

CREATE TABLE SmUser (
UserID int
ENCRYPTED WITH
(
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = ColumnKey
) NOT NULL,
Name int
ENCRYPTED WITH
(
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = ColumnKey
) NOT NULL,
Password SmStringReqN,
LastName SmStringReqN,
FirstName SmStringReqN,
EmailAddress SmStringReqN,
TelephoneNumber SmStringReqN,
Disabled SmStringReqN,
PIN SmStringReqN,
Mileage int NOT NULL,
PasswordData varchar(2000) NOT NULL,
PRIMARY KEY (UserID)
)
go

Populated Data as :

DECLARE @UserID AS int = 1 ; DECLARE @DeezNutz AS int = 105 ; INSERT INTO .[dbo].[SmUser] VALUES (@UserID,@DeezNutz ,'user1','User','Super','******@mycompany.com','100','0', '1111', 1234,' ');
go

Now We want to retrieve data using the encrypted field say Name using WHERE

So as we try something like this :

SELECT * FROM .[dbo].[SmUser] WHERE [Name]=105, CONVERT(int, DecryptByKey('ColumnKey'));

Observing error :

Line 24, column 47
Incorrect syntax near ,.

Completion time: 2022-04-12T14:59:47.5175128+05:30

Or if we try something like this

We Observe error :

SELECT Name FROM .[dbo].[SmUser] WHERE ([Name]=105);
OR
SELECT * FROM .[dbo].[SmUser] WHERE ([Name]=105);
Msg 206, Level 16, State 2, Line 26
Operand type clash: tinyint is incompatible with tinyint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'sampleuser')
Msg 206, Level 16, State 2, Line 26

Need Information on how we can achieve this.

Query 2 :

We observe Always Encrypted does not supports strings , user defined variables as mentioned in following link

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

We also tried to test it by trying to create tables with string type and usedefined types , but we observe error in doing so.

Summarizing : Do confirm some information on this.

1. Does Always Encrypts supports only int type or does it supports varchar and other types also. Please send the list of supported types
2. We want to fetch data from table using WHERE clause with encrypted column name. To fetch data using encrypted columns, please suggest/provide single query

Answer 1

I suggest you read this, it describes your exact issue:
https://www.mssqltips.com/sqlservertip/4011/sql-server-2016-always-encrypted/

Answer 2

Always Encrypted is a feature that people often get problem with, because they have expectations that do not match how the feature work.

It is very important to understand that with Always Encrypted, all encryption and decryption occur client-side. SQL Server knows where the encryption key is located and that the value is encrypted. But you cannot decrypt or encrypt the value in SQL Server. And that's a very important promise for Always Encrypted: to hide data for the DBA.

SELECT * FROM .[dbo].[SmUser] WHERE [Name]=105, CONVERT(int, DecryptByKey('ColumnKey'));

Apart from the syntax error, the function DecryptByKey has no relation to Always Encrypted. This function relates to the old-style cell encryption in SQL 2005.

DECLARE @UserID AS int = 1 ; DECLARE @DeezNutz AS int = 105 ; INSERT INTO .[dbo].[SmUser] VALUES > (@UserID,@DeezNutz ,'user1','User','Super','******@mycompany.com','100','0', '1111', 1234,' ');

This works in SSMS, provided that you have checked "Enable Parameterization in Always Encrypted" in SSMS, and also provided the appropriate connection string key word in the Connection dialog. In this case, SSMS will re-interpet the code and replace this with parameterised statements. The encryption still occurs client-side. The precondition here is that use variables.

So this does not work:

SELECT Name FROM .[dbo].[SmUser] WHERE ([Name]=105);

It needs to be:

   DECLARE @id int = 105  
   SELECT Name FROM .[dbo].[SmUser] WHERE ([Name]=@id  

Does Always Encrypts supports only int type or does it supports varchar and other types also. Please send the list of supported types

I don't recall if there any types that are not supported, but all common types certainly are supported.

But what you need to wrap your head around is that all encryption occurs client-side. And hat is why a condition like Name = 105 does not work. You cannot compare a number to a random sequence of bytes. And SQL Server knows that it is random bytes.

And keep in mind that what you see in SSMS is just something make it possible for the DBA to perform data maintenance. In your application you cannot things like that. All values must be passed as parameters.

Answer 3

@Erland Sommarskog : Thanks for your inputs .

@Regarding restrictions for always encrypt for data types , the following article mentions the restrictions
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

I was also not able to create tables when applied always encrypt to string data types and user defined data type. I was able to create tables only with int data type .Any more information on this.

Answer 4

@Erland Sommarskog

Thanks for your inputs .

So if we conclude that it is not possible to run a single line query like to get the result where (Name is teh encrypted field) , it is correct to believe so.

SELECT * FROM .[dbo].[SmUser] WHERE [Name]=105, CONVERT(int, DecryptByKey('ColumnKey'));

or
SELECT Name FROM .[dbo].[SmUser] WHERE [Name]=105, CONVERT(int, DecryptByKey('ColumnKey'));

or
SELECT Name FROM .[dbo].[SmUser] WHERE [Name]=105

Or is it possible to have anything alternate so that we can get the required result using a single line query .

Basically need a query using WHERE name = <value > . (So going by your earlier information looks like this cannot be achieved).
Do let know if any final inputs regarding this.

Regards
Sangram