Hibernate/JPA and MS SQL Server - Open Symmetric Key before DecryptByKey

Question

In a SQL Server 2012 database, I have a table with a varbinary(128) column that stores data encrypted by a certificate (AppCert) backed key (Secret_Key) and authenticator using a SHA2_512 hash of the primary key:

CREATE TABLE [Lookup].[SecretStuff] (
    [Id] tinyint NOT NULL, 
    [Secret] varbinary(128) NOT NULL
        CONSTRAINT [PK_Lookup-SecretStuff_Id] PRIMARY KEY CLUSTERED ([Id] ASC), 
        CONSTRAINT [UN_Lookup-SecretStuff_Secret] UNIQUE NONCLUSTERED ([Secret])
);

OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION BY CERTIFICATE [AppCert];
INSERT INTO [Lookup].[SecretStuff] ([Id], [Secret]) VALUES (1, ENCRYPTBYKEY(KEY_GUID('Secret_Key'), CONVERT(nvarchar(512), 'I have a secret'), 1, HASHBYTES('SHA2_512', CONVERT(varbinary(128), CONVERT(tinyint, 1)))));
CLOSE SYMMETRIC KEY [Secret_Key];

OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION BY CERTIFICATE [AppCert];
SELECT [Id], CONVERT(nvarchar, DECRYPTBYKEY([Secret], 1, HASHBYTES('SHA2_512', CONVERT(varbinary, [Id])))) AS [Secret] FROM [Lookup].[SecretStuff];
CLOSE SYMMETRIC KEY [Secret_Key];

This all works beautifully. Now, I have a Spring Boot application using JPA/Hibernate that is wired up and tested/verified to work with this database. The SecretStuff class:

@Entity
@Table(name="SecretStuff", schema="Lookup")
public class SecretStuff {

    @Id
    @Column(name = "Id", 
            nullable = false)
    private Integer id;

    @Column(name = "Secret", 
            nullable = false, 
            unique = true)
    @Size(min = 1, max = 255)
    @ColumnTransformer(
            read = "CONVERT(nvarchar(89), DECRYPTBYKEY([Secret], 1, 
                    HASHBYTES('SHA2_512', CONVERT(varbinary(128), [Id]))))")
    private String secret;
// getters/setter omitted
}

When I test the SecretStuff class, I see the following Hibernate generated SQL:

select
    secretstuf0_.Id as Id1_7_0_,
    CONVERT(nvarchar(89), DECRYPTBYKEY(secretstuf0_.[Secret],
    1,
    HASHBYTES('SHA2_512',
    CONVERT(varbinary(128),
    secretstuf0_.[Id])))) as Secret2_7_0_ 
from
    Lookup.SecretStuff secretstuf0_ 
where
    secretstuf0_.Id=?

Perfectly reasonable query that executes and returns 1 row with Id = 1 Secret = NULL, because the SYMMETRIC KEY is not opened before the query is executed.

My Question: How can I execute the OPEN SYMMETRIC KEY... command in the same transaction before the query and the CLOSE SYMMETRIC KEY... command in the same transaction after the query?

I have used Hibernate.initialize inside service class methods to lazy fetch to-many relations inside of transactions. Would I use a similar approach here? How?

I saw this article as I was typing up this question, but it is a couple of years old and uses the EntityManager approach with NativeQuery. Is there an updated way to manage this with JPA/Hibernate?

score 2 · Answer 1 · edited May 23 '17 at 12:08

While not the exact solution I was looking for, the article I referenced in my original question and some help from @TrevorAbell led me to the following code which seems to suit my needs. I post it here in case someone else is looking for a similar solution. I am still open to other solutions.

The key was using the EntityManager to run the SYMMETRIC KEY commands around the query. I'll include the @Repository interface and the @Service class for completeness, but the key was the @Service class:

@Repository
public interface SecretStuffRepository extends JpaRepository<SecretStuff, Integer> {}

@Service
@Transactional(readOnly = true)
public class SecretStuffService {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private SecretStuffRepository repository;

    public Iterable<SecretStuff> findAll() {

        this.entityManager.createNativeQuery("
              OPEN SYMMETRIC KEY [Secret_Key] 
              DECRYPTION BY CERTIFICATE [AppCert];
          ").executeUpdate();
        Iterable<SecurityQuestion> questions = this.repository.findAll();
        this.entityManager.createNativeQuery("
              CLOSE SYMMETRIC KEY [Secret_Key];
          ").executeUpdate();
        return stuff;
    }
}

Now, when I test the SecretStuffService, I see the following Hibernate generated SQL:

Hibernate: 
    OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION 
BY
    CERTIFICATE [AppCert];
Hibernate: 
    select
        secretstuf0_.Id as Id1_5_0_,
        CONVERT(nvarchar(89),
        DECRYPTBYKEY(secretstuf0_.[Secret],
        1,
        HASHBYTES('SHA2_512',
        CONVERT(varbinary(128),
        secretstuf0_.[Id])))) as Secret2_5_0_ 
    from
        Lookup.SecretStuff secretstuf0_ 
    where
        secretstuf0_.Id=?
Hibernate: 
    CLOSE SYMMETRIC KEY [Secret_Key];

Calls to SecretStuffService.findAll() now opens the key, calls the repository findAll() method, and closes the key inside the same transaction. Remember that the NativeQuery in this case is specific to Microsoft SQL Server, so you'll need to substitute the appropriate command for your database vendor.

Hibernate/JPA and MS SQL Server - Open Symmetric Key before DecryptByKey

1 Answers1