Retrieve Encrypted data from SQL in MS Excel

• Raxso1

  SSC Veteran

  Points: 270

  More actions

  May 12, 2021 at 10:16 am

  #3882264

  Hi

  I have a customer who has an SQL database and would like to encrypt columns i.e bank details. but they want to retrieve the data using Excel. Is this possible? I cannot find any articles to support this.

  Thanks in Advance

  Regards

  Raxs01

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736172

  More actions

  May 12, 2021 at 3:25 pm

  #3882385

  You can. If you use Transparent Data Encryption, then everything is encrypted and Excel can just query.

  If you want just specific columns, you likely will want a stored procedure to decrypt the data and return the result set. This might help: https://www.c-sharpcorner.com/article/get-data-using-sql-stored-procedure-into-excel/

  As for the decryption, this is fairly well doc'd from MS: https://docs.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql?view=sql-server-ver15

  You need similar code to encrypt data when it is added.