0

ENCRYPTBYPASSPHRASE in T-SQL takes a passphrase as a parameter. I'm wanting to encrypt some data in the same way outside of SQL server, in such a way as said data may be decrypted using the DECRYPTBYPASSPHRASE function once ingested into SQL Server.

I can't however find any reference to how SQL Server (>= 2017) derives the key from the passphrase.

Does anyone know how this is done (e.g. using PBKDF2, what salt, how many iterations)? or where I can easily find this info?

Improve this question
6
  • 1
    Doesn't it say so on the tin? SQL Server 2017 and later versions encrypts data with a passphrase using an AES256 key Commented May 8, 2024 at 7:46
  • 1
    @siggemannen But it doesn't say how it derives that key. There are many KDFs, each of which can have different parameters. Commented May 8, 2024 at 12:05
  • 2
    Hmm, i guess so, @Charlieface, but i'm guessing it doesn't do any derivations so there's no point of mentioning it? But this looks promising anyway: github.com/krcs/SQLServerCrypto Commented May 8, 2024 at 12:09
  • 1
    @siggemannen Yes appears to be SHA256 hashing. But in the actual ancryption part, there are also some magic numbers involved and a bunch of other stuff with headers and what not. Commented May 8, 2024 at 12:22
  • @Charlieface yeah, true, perhaps one should add a note for microsoft to document the algo. It doesn't seem to be secure enough for anything serious though, at least i wouldn't use it for password hashing Commented May 8, 2024 at 12:37
Related questions
4342
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
1868
Insert results of a stored procedure into a temporary table
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.