0

I have a SQL Server table with 1 column containing encrypted data.

The data was encrypted using the answer provided by @CraigTP to this question... Encrypting & Decrypting a String in C#

I need to retrieve & decrypt this data using only T-SQL. Is that possible? How?

cymorg
  • 534
  • 2
  • 10
  • 27
  • 2
    Honestly, the easiest way would be to import the managed code as a CLR assembly and just invoke it. Most of that code can *technically* be replicated in T-SQL, but certainly not efficiently. If you have an actual need for processing the encrypted data in the database itself, consider using T-SQL's own encryption mechanisms (`ENCRYPTBY...`/`DECRYPTBY...`). If not, consider Transparent Data Encryption, Always Encrypted or leaving decryption to the client. – Jeroen Mostert Mar 22 '19 at 14:34
  • See msdn documentation : https://learn.microsoft.com/en-us/sql/t-sql/functions/cryptographic-functions-transact-sql?view=sql-server-2017 – jdweng Mar 22 '19 at 14:51
  • There appears to be such a function in TSQL: DECRYPTEDBYPASSPHRASE. https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbypassphrase-transact-sql?view=sql-server-2017 Unfortunately the algorithm is 3DES which is not what you referenced. You would have to adjust your code that encrypts it. Or simply use the TSQL method to encrypt too and save yourself a lot of trouble. https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-2017 – Timothy John Laird Mar 22 '19 at 15:16
  • @Jeroen Mostert - I think you're right - the only realistic option is to import the code assembly and invoke it from T-SQL. I was hoping to avoid that through the magic of StackOverflow's combined knowledge but not all questions have easy answers. Ces't la vie. Thanks to all who've commented. – cymorg Mar 22 '19 at 22:09

0 Answers0