0

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

msnodesqlv8@2.6.0
mssql@9.0.1
jarlh
  • 42,561
  • 8
  • 45
  • 63
Geoff B
  • 33
  • 7

2 Answers2

0

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.

ProdigalTechie
  • 188
  • 1
  • 8
  • Swapping `ColumnEncryption` for `Column Encryption Setting` resulted in the app not crashing, but the column is still encrypted. This is an on-prem server not azure, does the 'App Permission' still apply in that instance (pun intended)? – Geoff B Sep 29 '22 at 20:19
  • Yes it would. You'll need to go into the servers Manage Computer Certificates and locate the Always Encrypted Certificate and add the correct user. I've only had to worry about IIS Apps which meant adding IIS AppPool\AppPoolName, I'd imagin that whatever is serving you app needs permissions added to the certificate to be able to decrypt the columns. – ProdigalTechie Sep 29 '22 at 21:43
0

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.

Geoff B
  • 33
  • 7