Prefix on Encrypted Columns

Question

I read a previous post that Raul responded to on the format of the data prefixing Symmetric key encrypted columns, is it possible to reproduce these from a client? Given I know the key name, i can pull back the first 16 for the GUID, for now 01000000 will work for the version, the IV can be created or read, but the last 8 bytes were marked as 'header', can I get an explanation on what this is or if it is required?

 

The purpose is in being able to do SQL Server compatible encryption on the client side, given a shared certificate for the public password.  I certainly can do this with a CLR function and use my own encryption, but comparably it is dog slow, the built in SQL functions will encrypt/decrypt 100k rows in about 20 seconds or less on my test box, where as the CLR function takes 5.5 minutes.  This performance difference is too huge to ignore.

 

I would like to be able to generate a SQL Server compatible prefix for a Symmetric keyed column or find a way to improve the CLR function performance of an AES_256 (rijndael) up to something at least remotely close to the built in functions.

 

Any help would be appreciated.

John Gooding

Answer 1

   As you already mentioned, the prefix is there for key-ring performance (key GUID matching) and the 4bytes “constant” block for versioning. Besides this prefix, there is also a header (8 bytes) required for internal usage and that is part of the plaintext before encryption takes place.

 

    Unfortunately we really don’t have support for SQL Server-compatible encryption in SQL Server 2005 using symmetric keys, mainly because we don’t have any infrastructure to support import/export of symmetric keys in SQL Server 2005.

 

  I truly apologize for not being able to give more help on this particular issue at this point, but we really appreciate this feedback as it helps us to design a better product in the future.

 

  Thanks a lot,

-Raul Garcia

  SDE/T

  SQL Server Engine