MSSQL Server & Always Encrypted columns

Ivan · November 14, 2025, 1:49pm

Hi,
has anyone worked with Lucee and MSSQL Server using Always Encrypted columns?

I can’t get the datasource to work after encrypting the column. My CFML app gives me this error: 'byte[] com.microsoft.sqlserver.jdbc.AuthenticationJNI.DecryptColumnEncryptionKey(java.lang.String, java.lang.String, byte[])'

I don’t know where to start because I can’t find any documentation on how to set up Lucee for this.

lucee.runtime.exp.NativeException: 'byte[] com.microsoft.sqlserver.jdbc.AuthenticationJNI.DecryptColumnEncryptionKey(java.lang.String, java.lang.String, byte[])' at com.microsoft.sqlserver.jdbc.AuthenticationJNI.DecryptColumnEncryptionKey(Native Method) at com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionCertificateStoreProvider.decryptColumnEncryptionKeyWindows(SQLServerColumnEncryptionCertificateStoreProvider.java:91) at com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionCertificateStoreProvider.decryptColumnEncryptionKey(SQLServerColumnEncryptionCertificateStoreProvider.java:61) at com.microsoft.sqlserver.jdbc.SQLServerSymmetricKeyCache.getKey(SQLServerSymmetricKeyCache.java:101) at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.decryptSymmetricKey(SQLServerSecurityUtility.java:248) at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.decryptWithKey(SQLServerSecurityUtility.java:294) at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3732) at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:254) at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:190) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2105) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2091) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject(SQLServerResultSet.java:2429) at lucee.runtime.query.caster.OtherCast.toCFType(OtherCast.java:39) at lucee.runtime.type.QueryImpl.fillResult(QueryImpl.java:735) at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:315) at lucee.runtime.type.QueryImpl.<init>(QueryImpl.java:244) at lucee.runtime.tag.Query.executeDatasoure(Query.java:1124) at lucee.runtime.tag.Query._doEndTag(Query.java:685) at lucee.runtime.tag.Query.doEndTag(Query.java:551) at models.test_cfc$cf.udfCall1_000003(/models/test.cfc:521) at models.test_cfc$cf.udfCall1(/models/test.cfc:287) at models.test_cfc$cf.udfCall(/models/test.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:111) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:356) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:829) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:858) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:2101) at models.test_cfc$cf.udfCall1(/models/test.cfc:59) at models.test_cfc$cf.udfCall(/models/test.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:111) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:356) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:831) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:2261) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:858) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:2101) at controllers.ajax.test_cfm$cf.call(/controllers/ajax/test.cfm:17) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1113) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1007) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:988) at controllers.ajax.index_cfm$cf.call(/controllers/ajax/index.cfm:17) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1113) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1007) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:988) at index_cfm$cf.call(/index.cfm:24) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1113) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1007) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:988) at core.framework._onrequest_cfm$cf$3g.call(/core/framework/_onrequest.cfm:106) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1113) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1007) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:988) at application_cfc$cf$3d.udfCall(/Application.cfc:52) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:111) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:356) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:830) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:2242) at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:503) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:238) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:43) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2800) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2787) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2758) at lucee.runtime.engine.Request.exe(Request.java:45) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1171) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1128) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:99) at lucee.loader.servlet.jakarta.CFMLServlet.service(CFMLServlet.java:41) at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:710) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:130) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:109) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:79) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:116) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:420) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:903) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1744) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:637) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:59) at java.base/java.lang.Thread.run(Unknown Source) Caused by: java.lang.UnsatisfiedLinkError: 'byte[] com.microsoft.sqlserver.jdbc.AuthenticationJNI.DecryptColumnEncryptionKey(java.lang.String, java.lang.String, byte[])' ... 92 more

Ivan · November 15, 2025, 10:38am

I’m adding some details I forgot:

Lucee 7
Java 21.0.7 (Eclipse Adoptium) 64-bit
SQL Server 2019
ODBC Driver 12.4.2.jre11
Windows Server 2012 R2

How I set up the certificate on Lucee:

Exporting the certificate from Windows

Open certmgr.msc (Current User Certificate Manager)
Go to Certificates - Current User → Personal → Certificates
Find the certificate named CMK_Auto1
Right-click on the certificate → All Tasks → Export…
In the export wizard:
- Select “Yes, export the private key”
- Format: PKCS #12 (.PFX)
- Password: set a password (e.g., MyPass123)
- Save as: C:\temp\CMK_Auto1.pfx

Import into Java keystore:

keytool -importkeystore ^
  -srckeystore C:\temp\CMK_Auto1.pfx ^
  -srcstoretype PKCS12 ^
  -srcstorepass MyPass123 ^
  -destkeystore C:\lucee\cmk_keystore.jks ^
  -deststoretype JKS ^
  -deststorepass MyPass123

Configure the Lucee datasource:

Key Store Authentication: JavaKeyStorePassword
Key Store Location: C:\lucee\cmk_keystore.jks
Key Store Principal Id: leave empty
Key Store Secret: MyPass123

Am I doing something wrong? Should I try testing it with different settings?

Ivan · November 17, 2025, 8:27am

Here is my datasource from Lucee admin:

this.datasources["xxx"] = {
	class: "com.microsoft.sqlserver.jdbc.SQLServerDriver", 
	bundleName: "org.lucee.mssql", 
	bundleVersion: "12.4.2.jre11",
	connectionString: "jdbc:sqlserver://xxx:1433;DATABASENAME=xxx_TEST;sendStringParametersAsUnicode=true;keyStoreSecret=xxx;trustServerCertificate=true;encrypt=true;keyStorePrincipalId=lp-098f1743-5f1b-4a0b-a37d-ad277c4e2861;keyStoreLocation=D:\certs\xxx_test.jks;keyStoreAuthentication=JavaKeyStorePassword;columnEncryptionSetting=Enabled;SelectMethod=direct",
	username: "xxx",
	password: "encrypted:xxx",
	
	// optional settings
	connectionLimit:100, // default:-1
	liveTimeout:60, // default: -1; unit: minutes
	validate:false // default: false
	
};

I’ve tried both adding and removing keyStorePrincipalId, but it doesn’t make any difference…

dawesi · December 7, 2025, 2:26am

How did you go, we’re looking to use this also soon, but with sql 2025:

Copilot suggested this:

Key Considerations for SQL Server 2019 + Always Encrypted

Driver support: The Microsoft JDBC Driver for SQL Server added Always Encrypted support starting with version 6.0, but later versions (8.x, 9.x, 12.x) improved compatibility with newer Java runtimes. Ivan is using 12.4.2.jre11, but his Java runtime is Java 21, which may cause mismatches.
Column Encryption Setting: The connection string must include columnEncryptionSetting=Enabled. Ivan has this correctly set.
Key Store Options: SQL Server 2019 supports both:
- Windows Certificate Store (requires native libraries, often where JNI errors occur).
- Java Keystore (JKS/PKCS12), which avoids native dependencies but requires correct setup.
JNI Error: The UnsatisfiedLinkError in his stack trace suggests the driver is trying to use Windows Certificate Store provider (AuthenticationJNI) but failing to load native DLLs. This can happen if:
- The driver defaults to Windows provider instead of Java keystore.
- The keystore configuration is incomplete or mis-specified.

The bottom line: SQL Server 2019 supports Always Encrypted fine, but the JDBC driver + Java runtime combination is the likely culprit. Ivan’s setup is close, but he may need a newer driver or to force the Java keystore provider instead of the Windows one.