Call a stored procedure on an always encrypted SQL Server database

68 Views Asked by At

When calling any stored procedure from an Always Encrypted SQL Server database I get the following error:

SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (""): Data type 0xC0 is unknown.

The table and the stored procedure was created like this:

CREATE TABLE [dbo].[test_table] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [encrypted_col] [nvarchar](255) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [MY_KEY], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
)
GO

CREATE PROCEDURE [dbo].[test_sp]
    @test_param nvarchar(255)
AS
    SELECT * from test_table where encrypted_col = @test_param
GO

The java code looks like this:

try(CallableStatement callableStatement = connection.prepareCall("{call test_sp(?)}")) {
    callableStatement.setNString(1, "TEST VALUE");
    ResultSet rs = callableStatement.executeQuery(); <-- Throws the Exception
}

My connection url looks like this:

jdbc:sqlserver://localhost:1433;applicationName=MY-APP;databaseName=test_encryption;sendStringParametersAsUnicode=false;encrypt=true;trustServerCertificate=true;columnEncryptionSetting=enabled;enclaveAttestationUrl=http://localhost/Attestation;enclaveAttestationProtocol=HGS;

What causes this error?

Notes:

  • I am using SQL Server 2019 and latest mssql driver (12.6), which are compatible. I also tried downgrading the driver but results in the same error.
  • Reading and writing data from/to the database tables works using Hibernate, which makes me think the connection url and encryption key are setup correctly. I only get this error on stored procedures
  • The stored procedure can be called with success from SQL Server Management Studio, which makes me think the procedure and encryption was created correctly at the database level
  • I have tried several other ways of calling the stored procedure, such as using Hibernate Query, or Spring Jpa Query, but they result in the same error.
  • If I recreate the database table without using encryption, calling the store procedure works
  • I have tried to use the database connection parameter sendStringParametersAsUnicode with both false and true values
0

There are 0 best solutions below