1

I understand why SQL won't allow this, I want to know if there is some way around this that will allow me to do a where on an encrypted column with multiple values?

I have tried to use a TVP, but SQL does not allow me to join on an encrypted column:

ALTER PROCEDURE GetUsers
    @Users [dbo].[Users_Type] readonly
AS
BEGIN
    SELECT * 
    FROM [dbo].[Users] u1
    JOIN    @Users u2
    ON      u1.[Username] = u2.[Users]
END
GO

I've tried creating the TVP with an encrypted column, but SQL won't allow me to do so:

CREATE TYPE [dbo].[Users_Type] AS TABLE(
    [Users] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
)
GO

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'ENCRYPTED'.

Is there any way to get around this limitation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.