I am using Hibernate 5.1.0. “Always Encrypt” feature which is with SQLServer 2016 and above. I am not able to use the feature with hibernate. Does hibernate Supports always encrypt?
I am not able to use the feature with hibernate.
You didn’t tell what exactly is not working. You need to provide a lot of context info, code examples, configurations, stack traces in order for others to be able to help you.
This article explains how to setup SQL Server Always Encrypt with JDBC.
Make sure everything is set up properly since Hibernate uses JDBC behind the scenes, so if it works via JDBC, it should work with Hibernate.
With some connection string i am able to get the data from standalone jdbc program but when i am trying the same that doesn’t seem to work. Please find exception trace of hibernate application#
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2115)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1874)
at org.hibernate.loader.Loader.doQuery(Loader.java:919)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
at org.hibernate.loader.Loader.doList(Loader.java:2610)
at org.hibernate.loader.Loader.doList(Loader.java:2593)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2422)
at org.hibernate.loader.Loader.list(Loader.java:2417)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at com.kbs.dao.hibernate.DataProviderDaoImplRoot.getAll(DataProviderDaoImplRoot.java:158)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:125)
... 137 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Encryption scheme mismatch for columns/variables 'itemNumber'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'vl') and the expression near line '1' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQueryInternal(SQLServerPreparedStatement.java:344)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterEncryptionMetadata(SQLServerPreparedStatement.java:695)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:447)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:353)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
Please find below standalone code:
String filterRecord="SELECT [itemId] ,[version] ,[employeeId] FROM [vl].[dbo].[employee] where [vl].[dbo].[employee].employeeId=?;";
try (PreparedStatement selectStatement = sourceConnection.prepareStatement(filterRecord))
{
selectStatement.setString(1, "7");
ResultSet rs = selectStatement.executeQuery();
while(rs.next())
{
System.out.println("itemId: " +rs.getString("itemId") +
", version: " + rs.getString("version") +
", itemNumber:"+ rs.getString("itemNumber"));
}
}
}
Please find connection url
jdbc:sqlserver://localhost\\kbs:1433;databaseName=kbs;columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;keyStoreLocation=CurrentUser/my/7643A79D4BF8DEC9763191F38C812;keyStoreSecret=employee;user=kbs;password=employee1;
Please let me know if you need any further info.
That’s not Hibernate code. If you cannot make it work with plain JDBC, it means you haven’t followed the steps provided in that article.
Therefore, this is not a Hibernate issue.
You should ask this question on the SQL Server forums. They can better help you with this issue.
I have not pasted the hibernate code just the stacktrace for it. The plain JDBC is working fine. With the same connection properties being passed.
You can debug the JDBC and Hibernate code and see why JDBC works and Hibernate does not. It’s the easiest way to solve this issue.
Was doing that 
was looking for some quick resolution. thank you
Doing a comparative debug is the quickest way to fix it.
Unless you have a very specific problem which you can clearly explain and provide all context in the question, it’s impossible for sosmeone else to provide a solution.
2 Likes
I understand. Once I have some solution i will update on the same.
Great. Looking forward to seeing your solution.
The first problem was i was using encrypted column in order by clause that is not being supported.
The second issue seems to be while inserting record. here is my finding as of now# In org.hibernate.persister.entity.AbstractEntityPersister# it gives anonymous implementation of Binder which internally calls org.hibernate.type.AbstractStandardBasicType and String data type of a POJO is being converted to org.hibernate.type.descriptor.sql.VarcharTypeDescriptor. So while setting the properties in insert statement in PreparedStatement it will use setString() method for “nvarchar” data type db columns instead of setNString(), which results in exception# com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varchar(3) 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 = 'employee') collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with nvarchar(255) 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 = 'employee').
this is clearly mentioned in# https://docs.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver
The setter method used for the parameter targeting the SSN column is setString(), which maps to the char/varchar SQL Server data type. If, for this parameter, the setter method used was setNString(), which maps to nchar/nvarchar, the query would fail, as Always Encrypted does not support conversions from encrypted nchar/nvarchar values to encrypted char/varchar values.
I guess we will need to make some changes in Hibernate or i might be missing some properties which could have done this.
If you need to use NVARCHAR, just annotate the entity properties with the @Nationalized annotation.
Check out the User Guide for more details.
I used# hibernate.use_nationalized_character_data but that doesn’t seems to be working.
The hibernate.use_nationalized_character_data setting enables nationalized character support on
ALL string / clob based attributes.
Are you sure you don’t use any VARCHAR column? Probably @Nationalized is a much better idea because it allows you to control which properties arae to be saved as NVARCHAR.
Yes, we do not use any VARCHAR column. @Nationalized will be lot of change. We want to use the property for application that handles it, but hibernate.use_nationalized_character_data is not working.
<bean id="sessionFactory"
class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mappingResources" ref="hibernateModels"/>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.use_nationalized_character_data">true</prop>
</props>
</property>
</bean>
can you please let me know if i am missing anything.I can still see String is being converted to VARCHAR type only
It’s working just fine on Hibernate ORM. Just fork the Hibernate ORM and run the UseNationalizedCharDataSettingTest. It’s working like a charm.
It didn’t worked for me. But i changed the columns to VARCHAR for testing and Hibernate works perfectly with AlwaysEncrypted sql db
i also have same error with BigDecimal(18,5) datatype
when column not encrypted it will work and
when i add column encryption then decimal(18,5) not inserted in database and show me error
like, Opearnd type clash decimal(18,2) with incomapatibility with decimal(18,5)
what is solution??
currently i m using hibernate 5.0.1.Final,spring 4.3.18.RELEASE and mssql version 6.1.6.jre8-preview