how to extract data from hashbyte text after hash or encrypted on sql server 2017 ?

ahmed salah 3,216 Reputation points
2022-05-09T10:53:03.943+00:00

I work on sql server 2017 i have field nvarchar(max) store values hashbytes

suppose i have text as username:sayed password:321
and i hash it by using hashbyte

so after hashing by using hashbyte
i need to extract data from it

meaning i need to get data of user as
username:sayed password:321
so how to extract data from field hashed by using hashbyte sql server 2017

meaning
How to get data password:321

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2022-05-09T10:58:42.32+00:00

    so after hashing by using hashbyte i need to extract data from it

    Hashbyte is use to compare data content, but you can not extract anything from a hash value.

    0 comments No comments

  2. Viorel 114.7K Reputation points
    2022-05-09T11:05:19.94+00:00

    Write loops that generate passwords, compute hashbytes and compare with the stored hashbytes. Wait for matches. One of the results will be “sayed, 321”.


  3. LiHong-MSFT 10,046 Reputation points
    2022-05-10T02:42:56.4+00:00

    Hi @ahmed salah
    I gonna say 'No', as far as i known, there is no such function to convert Hashbyte back to string.
    The purpose of computing a hash value is never to be able to re-construct the data, as it's utility as a security mechanism would drop to zero.

    By the way,if you have something like TableB with a column containing a hash of columns from TableA. You could join rows in TableB back to the related row in TableA. Like this:

    SELECT B.HashID, A.*  
    FROM TableA A JOIN TableB B ON B.HashID = HASHBYTES ('SHA2_512', A.Column );  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  4. LiHong-MSFT 10,046 Reputation points
    2022-05-10T09:23:05.86+00:00

    Hi @ahmed salah
    Maybe you need this: ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE
    Check this sample:

    declare @encrypt varbinary(200)   
    select @encrypt = EncryptByPassPhrase('key', 'password:321' )  
    select @encrypt   
       
    select convert(varchar(100),DecryptByPassPhrase('key', @encrypt ))  
    

    Best regards,
    LiHong

    0 comments No comments

  5. Tom Phillips 17,721 Reputation points
    2022-05-10T14:33:11.09+00:00

    A "hash" is not reversible. You cannot get the original value back and you don't want too.

    In your case, you would hash the password in your app and store the hashed value in the database. Then you hash the user input and compare it to the stored hashed value. That way you never need to know the actual password.

    0 comments No comments