Hello. Does anybody have experience with Mssql alw...

# exposed

[Filip Lastic]

Hello. Does anybody have experience with Mssql always encrypted feature and how does it work with Exposed?

I have following table

object Person : Table("persons") {
    val id: Column<Long> = long("id")

    val surname: Column<String?> = varchar("surname", 255).nullable()
}

And insert is failing with

com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MY_KEY_CEK', column_encryption_key_database_name = 'my-db') collation_name = 'Latin1_General_100_CI_AS_SC_UTF8' is incompatible with varchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MY_KEY_CEK', column_encryption_key_database_name = 'my-db')

Table definition

CREATE TABLE [dbo].[persons](
	[id] [bigint] NOT NULL,
	[surname] [varchar](255) COLLATE Latin1_General_100_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [MY_KEY_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)

[Chantal Loncle]

Hi @Filip Lastic I don't have much experience with this feature, but I can try to troubleshoot it with you.

I initially thought that maybe the inserted values were not being encrypted, but it seems more like a data type mismatch when setting the statement parameters. Which is odd because your table definition should ensure that the parameter is set as VARCHAR(255) if Exposed's insert() is used.

Could you confirm how the insert operation is being performed? Is it just a simple:

Person.insert {
    it[id] = 1
    it[surname] = "Surname"
}

If so, could you confirm that the generated SQL should work at a lower level, for example using either exec() or the JDBC connection directly. For example:

exec(
    "INSERT INTO persons (id, surname) VALUES (?, ?)",
    args = listOf(
        LongColumnType() to 1,
        VarCharColumnType(255) to "Surname"
    )
)

// OR

val cx = (connection.connection as java.sql.Connection)
val stmt = cx.prepareStatement("INSERT INTO persons (id, surname) VALUES (?, ?)")
stmt.setObject(1, 1, java.sql.Types.BIGINT)
stmt.setObject(2, "Surname", java.sql.Types.VARCHAR, 255)
stmt.executeUpdate()

Do either of those methods succeed?

I'm trying to understand what the insert should look like without using Exposed, because I'm seeing different ways, like using a plain INSERT (example) or using DECLARE with parameters (example).

[Filip Lastic]

Hello. Thank you for help! @Chantal Loncle (I'm using Slovak diacritics)

1. insert using Exposed DTO

Person.insert {
        it[id] = 1
        it[surname] = "ľščťžýáíé"
    }

2025-02-03 16:39:25.666 DEBUG [,,,] --- [main] Exposed INSERT INTO persons (id, surname) VALUES (1, 'ľščťžýáíé')
2025-02-03 16:39:25.666 WARN  [,,,] --- [main] Exposed Transaction attempt #0 failed: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ABIS_DEV_CEK', column_encryption_key_database_name = 'abis') collation_name = 'Latin1_General_100_CI_AS_SC_UTF8' is incompatible with varchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ABIS_DEV_CEK', column_encryption_key_database_name = 'abis') collation_name = 'Latin1_General_100_BIN2'. Statement(s): INSERT INTO persons (id, surname) VALUES (?, ?)
org.jetbrains.exposed.exceptions.ExposedSQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ABIS_DEV_CEK', column_encryption_key_database_name = 'abis') collation_name = 'Latin1_General_100_CI_AS_SC_UTF8' is incompatible with varchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ABIS_DEV_CEK', column_encryption_key_database_name = 'abis') collation_name = 'Latin1_General_100_BIN2'
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:99)

2. insert using exec()

exec(
        "INSERT INTO persons (id, surname) VALUES (?, ?)",
        args = listOf(
            LongColumnType() to 1,
            VarCharColumnType(255) to "ľščťžýáíé"
        )
    )

Throws the same error

3. insert using prepareStatement

val cx = (connection.connection as java.sql.Connection)
    val stmt = cx.prepareStatement("INSERT INTO persons (id, surname) VALUES (?, ?)")
    stmt.setObject(1, 1, java.sql.Types.BIGINT)
    stmt.setObject(2, "ľščťžýáíé", java.sql.Types.VARCHAR, 255)
    stmt.executeUpdate()

Throws the same error

I think it has something with this documentation https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16#errors-due-to-[…]crypted-values but I'm struggling a bit how to solve the issue


Only example when it works is when I changed column type to NVARCHAR(MAX) and used following code (I didn't find NVarcharColumnType())

[surname] [nvarchar](max) COLLATE Latin1_General_100_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ABIS_DEV_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

    val cx = (connection.connection as java.sql.Connection)
    val stmt = cx.prepareStatement("INSERT INTO persons (id, surname) VALUES (?, ?)")
    stmt.setObject(1, 1, java.sql.Types.BIGINT)
    stmt.setObject(2, "ľščťžýáíé", java.sql.Types.NVARCHAR)
    stmt.executeUpdate()

[Filip Lastic]

@Chantal Loncle another strange finding is that it works with collation Latin1_General_100_BIN2_UTF8 . All inserts are successful, but now I have the issue with querying data

1. Works ✅

Person.insert {
        it[id] = 10
        it[surname] = "surname"
    }
    Person.selectAll().where { Person.id eq 10 }.map {
        println(it[Person.surname])
    }

2. Works ✅

Person.insert {
        it[id] = 11
        it[surname] = "abcdšefgh"
    }
    Person.selectAll().where { Person.id eq 11 }.map {
        println(it[Person.surname])
    }

3. Error on select ❌

Person.insert {
        it[id] = 12
        it[surname] = "abcdššššššefgh"
    }
    Person.selectAll().where { Person.id eq 12 }.map {
        println(it[Person.surname])
    }

This time it fails with

2025-02-04 17:31:18.186 DEBUG [,,,] --- [main] Exposed SELECT persons.id, persons.surname FROM persons WHERE persons.id = 12
2025-02-04 17:31:18.189 WARN  [,,,] --- [main] Exposed Transaction attempt #0 failed: Specified ciphertext has an invalid authentication tag. . Statement(s): null
com.microsoft.sqlserver.jdbc.SQLServerException: Specified ciphertext has an invalid authentication tag. 
	at com.microsoft.sqlserver.jdbc.SQLServerAeadAes256CbcHmac256Algorithm.decryptData(SQLServerAeadAes256CbcHmac256Algorithm.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerAeadAes256CbcHmac256Algorithm.decryptData(SQLServerAeadAes256CbcHmac256Algorithm.java:192)
	at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.decryptWithKey(SQLServerSecurityUtility.java:294)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3729)

It seems that the issue is more probably with JDBC driver than Exposed but I'm not able to figure out combination of collation and DSL configuration

[Filip Lastic]

I also found following github issue https://github.com/Microsoft/mssql-jdbc/issues/658 but nothing happens when I tried to set sendStringParametersAsUnicode to false

[Filip Lastic]

Hello @Chantal Loncle do you have any ideas or updates regarding to this question? 🙏