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

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]

CodePudding user response：

Try changing ColumnEncryption to Column Encryption Setting. I do not use node.js but all my SQL server connection strings use Column Encryption Setting=Enabled when the database has Always Encrypted columns.

You might also check if your app has the correct security access to the Always Encrypted Certificate that was created by the SQL Sever to encrypt the data in your Manage Computer Certificates store. I know this has been my case a couple of times where I forgot to give permission to the app trying to query the encrypted columns.

CodePudding user response：

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