SQL Server Always on Encryption / Column Encryption with Node.js

Question

Has anyone been successfully with SQL Server 2019 using Column Encryption and Node.js? If so, what packages are you using? The closest I've gotten is a connection with ODBC Driver 17, with ColumnEncryption=Enabled. I can query tables with this connection, but when I try to query an EncryptedColumn all I get is [nodemon] app crashed... no errors no exceptions or anything.

I've also tried several different versions of the ODBC Driver without success.

import { SqlClient } from "msnodesqlv8";

const sql: SqlClient = require("msnodesqlv8");

const connectionString = `server=SERVER\\DEV;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server};encrypt=yes;trustServerCertificate=yes;ColumnEncryption=Enabled;`;

// queryA results in [nodemon] app crashed - ...
const queryA = `select *  from [database].[dbo].[table]; `;
// queryB results in returning the id of the record in the table
const queryB = `select id from [database].[dbo].[table]; `; 

export default () => {
  try {
    sql.query(connectionString, queryA, (err, rows) => {
        console.log({ err });
        console.log(rows);
      }
    );
  } catch (error) {
    console.log({ error });
  }
};

Dependencies

[email protected]
[email protected]

Answer 1

Being new to Encrypted Columns, this has been a learning experience for me.

The solution as it turns out is that a copy of the Encryption Certificate 'Always Encrypted Certificate' needs to be on the local computer making the connection to the Server with the encrypted column. This was not clear to me, but it make sense in hindsight though.

After importing the certificate onto the local computer, into the localMachine store (not the user store) buth my .Net test app and my Node.js app are able to access and decrypt encrypted columns.

Always Encryption: Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE' by lucasreta

Addendum This only works for reading from an encrypted column. Still after two weeks I have been unable to insert a record into a table with an encrypted column.