0

I have a database that is being converted to use always-encrypted encryption. I have an Insert inside a stored procedure in the format:

CREATE PROCEDURE CreateUser
    (@Username NVARCHAR(50)
    ,@PaymentMethod NVARCHAR(50))
AS 
BEGIN
    INSERT INTO Users
    (   Username
    ,   PaymentMethod
    )
    VALUES
    (   @Username
    ,   ISNULL(@PaymentMethod,'Credit Card')
    )
END

This is an over simplification, we are actually passing in multiple parameters, and some are encrypted, some aren't.

This code falls over because 'Credit Card' is not encrypted and as such cannot be inserted into an encrypted field, and you cannot encrypt a field with a default constraint. While it is possible to configure a "defaultPaymentMethod" and pass that in too it will make the call very messy.

Does anyone have a more graceful solution than passing in defaults? I'm sure someone has had to do something similar already.

Many thanks.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • If you don't know the date of birth, [why don't you just leave it `NULL`](https://dba.stackexchange.com/a/5227/1186)? Why not use `date` (3 bytes) instead of `datetime` (8 bytes) when time is obviously not important? What does this have to do with Always On? – Aaron Bertrand May 01 '18 at 14:06
  • This is just one example, I picked dob at random, but may as well have been a payment method. i.e. if you haven't configured payment it is credit card only. We also use the same technique for updates which would have a format of DOB=ISNULL(@DOB,DOB). When you have multiple parameters it doesn't make sense to perform an update for each separately. – Matthew Baker May 01 '18 at 14:11
  • As to what it has to do with always on encryption, as I stated above "This code falls over because '1900-01-01' is not encrypted and as such cannot be inserted into an encrypted field." – Matthew Baker May 01 '18 at 14:13
  • Ok, so you mean Always Encrypted, not "Always on Encryption." – Aaron Bertrand May 01 '18 at 14:14
  • For the comparison of DATE vs DATETIME, I typed up a quick example to demonstrate the problem. I don't post exact company specific data online. But thank you for the suggestion, I'm sure someone will find it relevant. – Matthew Baker May 01 '18 at 14:15
  • Yes always encrypted. As in the title and the tag, Type in the main body I'll edit out now. – Matthew Baker May 01 '18 at 14:19
  • The problem with having the default on the server side is either a) it now has to have access to the encryption key, defeating the whole point of using Always Encrypted or b) has to have the already encrypted default value stored on the server, which then means that you can tell which rows have had the encrypted default value applied to them without decrypting. – Damien_The_Unbeliever May 01 '18 at 14:26
  • @Damien_The_Unbeliever I understand the issue and the reasons for not defaulting data in the database, however this is a legacy system I've inherited. In this instance, even if I passed in the payment method from outside I would see multiple instances of the same encrypted data. Still open to suggestions though. – Matthew Baker May 01 '18 at 14:34
  • Actually you would potentially see different values for all rows if you use random vs. deterministic for that column. But even with deterministic, just looking at the table, I don't think you will be able to tell which ones are 1900-01-01, except by the fact that there are a lot of them (and that depends on data distribution and how often DOB is not supplied). Anyway, whether that information is useful to anyone, not sure. – Aaron Bertrand May 01 '18 at 15:07

0 Answers0