Writing query output to encrypted excel file

Question

Can a SQL query write to an encrypted excel file?

If not, how can we query data and send it in files while ensuring the data is always encrypted.

This is a big deal because it contains PII relating to legal matters.

Background

I am not a SQL user - I am a sysadmin and I don't know much inside SQL.

We are custodians of data that belongs to other orgs, and we will soon need to regularly send encrypted files containing data from a query to the various owners.

One of the owners raised an issue that if the data is queried from SQL, and the file is encrypted afterwards (but before sending), then the data must have been written to disk in plaintext (and that is not acceptable).

If the SQL query can write to an encrypted file (we currently plan to send as Excel with the native AES256 encryption), then I need to point our data team to this method and have them adopt it.

If a SQL query cannot write to an encrypted file, then the solution I see is to have the query run from a dedicated VM with full disk encryption. That way we can show the data is always in an encrypted container: initially the virtual hdd, then the encrypted Excel file.

Thanks

Answer 1

Correct, Always Encrypted will not meet your needs. There may be reason to implement Always Encrypted for other reasons, but it would not help here.

I think your best bet is to have a client-side program, could be a PowerShell script, that writes an encrypted Excel file. I don't have the details on how to do this, but the Excel VBA Reference should tell you. Or ask a question with an Excel tag.

Note that for the encryption to be meaningful, you will need to share the encryption keys with the receiver of the Excel files.

Answer 2

Writing the output of a SQL query directly to an encrypted Excel file is not currently supported. However, there are other options for querying data and ensuring that it remains encrypted.

One option is to use Always Encrypted, a SQL Server feature that allows you to encrypt sensitive data at rest and in transit. With Always Encrypted, you can query encrypted data and send it securely to other organizations without exposing the data in plaintext.

Another option is to run your query from a VM with full disk encryption, as you suggested. This will ensure that the data is always stored and transmitted in an encrypted form.

Once you have retrieved the data using one of these methods, you could then use a separate tool to write the data to an encrypted Excel file. This could be a script that encrypts the data on-the-fly as it is being written to disk, or it could be a third-party tool that supports writing to encrypted Excel files.

It's important to note that sending encrypted data via email or other methods of transmission outside your organization may require additional security measures, such as secure file transfer protocols or password-protected archives.

References:

• Query columns using Always Encrypted with Azure Data Studio: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-query-columns-ads
• Protecting Your SQL Server Intellectual Property: https://learn.microsoft.com/en-us/sql/relational-databases/security/protecting-your-sql-server-intellectual-property#nature-of-the-problem