GETDATE() Function error on TSQL with Always Encrypted Column

Question

I am working with SQL Server hosted on Azure. I have a table named "ERROR" with a column named "DATETIME" with Always Encrypted feature enabled.

CREATE TABLE [dbo].[ERROR]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DATETIME] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [CODE] [varchar](10) NOT NULL,
    [MESSAGE] [varchar](500) NULL,

    CONSTRAINT [PK_ERROR] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)

I'm running a stored procedure to add a row in another table, but if an error is caught it add the error to this table with the following statement:

INSERT INTO [dbo].[ERROR] ([DATETIME], [CODE], [MESSAGE])
VALUES (GETDATE(), @@ERROR, ERROR_MESSAGE())

My issue is when I try to enter GETDATE() value into the encrypted column, I get this error

Operand type clash: datetime is incompatible with datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Aeropuerto')

Following some recommendations on the web I enabled PARAMETERIZATION FOR ALWAYS ENCRYPTED on SSMS and declared it first like this DECLARE @RAISEDTIME DATETIME = '20181130'. after this no error was raised and it is inserted successfully, but when assing GETDATE() as value [DECLARE @RAISEDTIME DATETIME = GETDATE()] I get this error:

Encryption scheme mismatch for columns/variables '@RAISEDTIME'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Aeropuerto') (or weaker).

I have tried to format GETDATE() value with ISO8601 but the same error below is raised.

Can you recommend me a way to insert the actual datetime value with this stored procedure?

Have you tried DECLARE @RAISEDTIME DATETIME = getdate() and inserting the variable? — user1443098, Nov 30 '18 at 20:18
Yes INSERT INTO [dbo].[ERROR]( [DATETIME], [CODE], [MESSAGE]) VALUES(@RAISEDTIME, @CODE, @MESSAGE) — Neitroyint, Nov 30 '18 at 20:28
use that. That little bit of indirection makes it deterministic — user1443098, Nov 30 '18 at 20:33
Unrelated tips: there is no reason to be using DateTime any more: [prefer datetime2](https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server). Also, why name the `DateTime` column after the type: give it a name representing what it contains: e.g. `OccurredAt`. — Richardissimo, Nov 30 '18 at 22:06
@user1443098 sorry, I wanted to say that I have already tried it. — Neitroyint, Nov 30 '18 at 22:09
@Richardissimo thanks for your advise, I really apreciate those types of comments. — Neitroyint, Nov 30 '18 at 22:10
What's the point of using Always Encrypted on this date column? It's overkill. — Andrey Nikolov, Dec 01 '18 at 11:07

score 0 · Answer 1 · answered Nov 30 '18 at 20:39

You need to leverage Parameterization for Always Encrypted columns. You have to understand that with an AE column, the server does not have the encryption key, so it cannot possibly insert a value declared in a batch script. The value must be encrypted by the client, which has the encryption key.

With SQL Server 2017, Always Encrypted column parameterization is possible, see the linked article. But it works only under certain conditions. And it definitely not works in SQL Server 2016.

You'll be much better to send the value from the client, as with most Always Encrypted column use cases.

GETDATE() Function error on TSQL with Always Encrypted Column

1 Answers1