Is it possible to determine ENCRYPTBYKEY maximum returned value by the clear text type?

Question

I am going to encrypted several fields in existing table. Basically, the following encryption technique is going to be used:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'sm_long_password@'
GO

CREATE CERTIFICATE CERT_01
WITH SUBJECT = 'CERT_01'
GO

CREATE SYMMETRIC KEY SK_01
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE CERT_01
GO


OPEN SYMMETRIC KEY SK_01 DECRYPTION
BY CERTIFICATE CERT_01

SELECT ENCRYPTBYKEY(KEY_GUID('SK_01'), 'test')

CLOSE SYMMETRIC KEY SK_01

DROP SYMMETRIC KEY SK_01
DROP CERTIFICATE CERT_01
DROP MASTER KEY

The ENCRYPTBYKEY returns varbinary with a maximum size of 8,000 bytes. Knowing the table fields going to be encrypted (for example: nvarchar(128), varchar(31), bigint) how can I define the new varbinary types length?

Answer 1

You can see the full specification here

So lets calculate:

• 16 byte key UID
• _4 bytes header
• 16 byte IV (for AES, a 16 byte block cipher)

Plus then the size of the encrypted message:

• _4 byte magic number
• _2 bytes integrity bytes length
• _0 bytes integrity bytes (warning: may be wrongly placed in the table)
• _2 bytes (plaintext) message length
• _m bytes (plaintext) message
• CBC padding bytes

The CBC padding bytes should be calculated the following way:

16 - ((m + 4 + 2 + 2) % 16)

as padding is always applied. This will result in a number of padding bytes in the range 1..16. A sneaky shortcut is to just add 16 bytes to the total, but this may mean that you're specifying up to 15 bytes that are never used.

---

We can shorten this to 36 + 8 + m + 16 - ((m + 8) % 16) or 60 + m - ((m + 8) % 16. Or if you use the little trick specified above and you don't care about the wasted bytes: 76 + m where m is the message input.

---

Notes:

• beware that the first byte in the header contains the version number of the scheme; this answer does not and cannot specify how many bytes will be added or removed if a different internal message format or encryption scheme is used;
• using integrity bytes is highly recommended in case you want to protect your DB fields against change (keeping the amount of money in an account confidential is less important than making sure the amount cannot be changed).
• The example on the page assumes single byte encoding for text characters.