Provision to a MS SQL Server with encryption enabled

We are using a JDBC connector to connect to a MS SQL server which is storing encrypted data within some columns. The encryption is enabled at the server level. We were able to establish connection and even fetch data using select query but we’re stuck on provisioning part or rather fetching or populating data from the encrypted user_id column.

We are getting below error for single account aggregation and account creation on the user_id column. user_id is setup as Account ID in our source.

We’ve set columnEncryptionSetting=Enabled within source configurations.

Single account aggregation query-

SELECT user_id, name FROM users WHERE user_id = $(identity)

Error -

The data types nvarchar(15) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘ColumnEncryptionKey’, column_encryption_key_database_name = ‘XXXX’) and varchar(10) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘ColumnEncryptionKey’, column_encryption_key_database_name = ‘XXXX’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’ are incompatible in the equal to operator.

We’ve tried the below queries which actually work fine when executed in SQL Developer but from SailPoint we keep getting the error above.

DECLARE @EncryptedUserID VARCHAR(7) = '$(identity)';
SELECT user_id, name FROM users WHERE user_id = @EncryptedUserID

We also tried using stored procedure to achieve this but we got same error when executing the stored procedure.

Has anyone encountered the same issue? Any ideas to resolve this?

Have you tried to encrypt the value using the same level of encryption as the table requires before attempting to write it to the table?

Wait…
While I was researching this and preparing to build it in my test environment, something in your message caught my eye…

You’re declaring @EncryptedUserID as VARCHAR(7)...

But the error message is kind of confusing…

It says data types nvarchar(15) and varchar(10) are incompatible with the equal operator…

At first I assumed this was your declaration in the ISC JDBC connector that was causing this error, but your varchar is 7 and the error is 10.

My remaining three brain cells are struggling with this one, but it seems like there are three different data type declarations happening here…

Can you confirm what the data type is on the MS SQL side for the encrypted userID column and verify you don’t have any other JDBC Provisioning logic targeting that database with different varchar data types?

The database type is nvarchar(15). You can ignore the declaration above as typo.

We’ve modified all the datatypes and varchar declarations to match the DB but the error is consistent.

@sharvari

With SQL Server Always Encrypted, encryption/decryption happens entirely client-side in the JDBC driver — SQL Server itself never sees or touches plaintext. This means:

  • The JDBC driver must encrypt the parameter before sending it to SQL Server
  • This only happens with parameterized queries (PreparedStatement with ? placeholders)
  • SQL Server receives pre-encrypted bytes and compares them against the encrypted column (DETERMINISTIC encryption allows this)

if your table has any non-encrypted unique identifier (e.g., an integer id, employee_number, email), make that the Account ID in SailPoint, and surface user_id as a regular attribute. The WHERE clause on the non-encrypted column works fine with string substitution. This avoids the problem entirely.

or

If your stored procedure parameter is declared as NVARCHAR(15), calling it via PreparedStatement will also work — the driver encrypts the parameter at the call site:

– SQL Server SP definition (parameter type must EXACTLY match: NVARCHAR(15), not VARCHAR)
CREATE PROCEDURE sp_GetUserById
@UserId NVARCHAR(15)
AS
SELECT user_id, name FROM users WHERE user_id = @UserId;

// In the BeanShell rule:
ps = connection.prepareCall(“{call sp_GetUserById(?)}”);
ps.setNString(1, identity);
rs = ps.executeQuery();

Yes, we are trying to do this using a rule. I will update this thread if it works out.

Thank you.

Ah… got it.
And I’m sure you already know that varchar can’t be passed to nvarchar in the database and since the ISC JDBC Connector doesn’t support the nvarchar data type, you have to convert it somehow.
Hence why you are trying a rule…
got it.

I wonder if you could use this instead?

On the Account Query Settings you use this:

SELECT
user_id, 
name
FROM
users

This will decrypt and aggregate all users from the database.

Then when you need to update SQL based on that encrypted userID, you use something like this.

 ......
String username = (String) accountRequest.getNativeIdentity();
String status = (String) accountRequest.getAttributeRequest("Status").getValue();

String updateSQL = "UPDATE users SET isActive = ? WHERE user_id = ?";

PreparedStatement statement = connection.prepareStatement(updateSQL);

statement.setNString(1, accountStatus);
statement.setNString(2, username);

int updatedRows = statement.executeUpdate();

The setNString property should allow the data to be sent as Unicode which should theoretically match the data type in the SQL database.

Let me know if that works!

:sweat_smile:

Here an update on this topic for anyone facing the same issue:

  1. The select query options didn’t work
  2. The stored procedure route doesn’t work
  3. The BPR rule approach also didn’t work

We worked around this by using APIs for creating accounts.