How to decrypt a password from SQL server

Question

I have this query in sql server 2000   select pwdencrypt  AAAA     which outputs an encrypted string of  AAAA     0x0100CF465B7B12625EF019E157120D58DD46569AC7BF4118455D12625EF019E157120D58DD46569AC7BF4118455D   How can I convert  decrypt  the output from its origin  which is  AAAA

User · Answer

You cannot decrypt this password again but there is another method named  pwdcompare   Here is a example how to use it with SQL syntax   USE TEMPDB GO declare  hash varbinary  255  CREATE TABLE tempdb  h  id num int  hash varbinary  255   SET  hash   pwdencrypt  123   -- encryption INSERT INTO tempdb  h  id num hash  VALUES  1  hash  SET  hash   pwdencrypt  123   INSERT INTO tempdb  h  id num hash  VALUES  2  hash  SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   2 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h INSERT INTO tempdb  h  id num hash   VALUES  3 CONVERT varbinary  255   0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8   SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   3 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h DROP TABLE tempdb  h GO

User · Answer

The SQL Server password hashing algorithm   hashBytes   0x0100   fourByteSalt   SHA1 utf16EncodedPassword fourByteSalt    For example  to hash the password  correct horse battery staple   First we generate some random salt   fourByteSalt   0x9A664D79    And then hash the password  encoded in UTF-16  along with the salt    SHA1  correct horse battery staple    0x9A66D79    SHA1 0x63006F007200720065006300740020006200610074007400650072007900200068006F00720073006500200073007400610070006C006500 0x9A66D79   0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3   The value stored in the syslogins table is the concatenation of       header     salt     hash    0x0100 9A664D79 6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3   Which you can see in SQL Server   SELECT     name  CAST password AS varbinary max   AS PasswordHash FROM sys syslogins WHERE name    sa   name  PasswordHash                                                              sa    0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3    Version header  0100 Salt  four bytes   9A664D79 Hash  6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3  SHA-1 is 20 bytes  160 bits    Validation  You validate a password by performing the same hash    grab the salt from the saved PasswordHash  0x9A664D79   and perform the hash again   SHA1  correct horse battery staple    0x9A66D79     which will come out to the same hash  and you know the password is correct   What once was good  but now is weak  The hashing algorithm introduced with SQL Server 7  in 1999  was good for 1999    It is good that the password hash salted   It is good to append the salt to the password  rather than prepend it     But today it is out-dated  It only runs the hash once  where it should run it a few thousand times  in order to thwart brute-force attacks   In fact  Microsoft s Baseline Security Analyzer will  as part of it s checks  attempt to bruteforce passwords  If it guesses any  it reports the passwords as weak  And it does get some   Brute Forcing  To help you test some passwords   DECLARE  hash varbinary max  SET  hash   0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3 --Header  0x0100 --Salt    0x9A664D79 --Hash    0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3  DECLARE  password nvarchar max  SET  password    password   SELECT      password AS CandidatePassword       hash AS PasswordHash       --Header     0x0100           --Salt     CONVERT VARBINARY 4   SUBSTRING CONVERT NVARCHAR MAX    hash   2  2             --SHA1 of Password   Salt     HASHBYTES  SHA1    password   SUBSTRING CONVERT NVARCHAR MAX    hash   2  2     SQL Server 2012 and SHA-512  Starting with SQL Server 2012  Microsoft switched to using SHA-2 512-bit   hashBytes   0x0200   fourByteSalt   SHA512 utf16EncodedPassword fourByteSalt    Changing the version prefix to 0x0200   SELECT     name  CAST password AS varbinary max   AS PasswordHash FROM sys syslogins  name  PasswordHash ----  -------------------------------- xkcd  0x02006A80BA229556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38    Version  0200  SHA-2 256-bit  Salt  6A80BA22 Hash  64 bytes   9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38   This means we hash the UTF-16 encoded password  with the salt suffix    SHA512  correct horse battery staple  6A80BA22    SHA512 63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500   6A80BA22    9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38

User · Answer

You shouldn t really be de-encrypting passwords   You should be encrypting the password entered into your application and comparing against the encrypted password from the database   Edit - and if this is because the password has been forgotten  then setup a mechanism to create a new password

User · Answer

You shouldn t really be de-encrypting passwords   You should be encrypting the password entered into your application and comparing against the encrypted password from the database   Edit - and if this is because the password has been forgotten  then setup a mechanism to create a new password

User · Answer

You realise that you may be making a rod for your own back for the future  The pwdencrypt   and pwdcompare   are undocumented functions and may not behave the same in future versions of SQL Server   Why not hash the password using a predictable algorithm such as SHA-2 or better before hitting the DB

User · Answer

You shouldn t really be de-encrypting passwords   You should be encrypting the password entered into your application and comparing against the encrypted password from the database   Edit - and if this is because the password has been forgotten  then setup a mechanism to create a new password

User · Answer

A quick google indicates that pwdencrypt   is not deterministic  and your statement select pwdencrypt  AAAA   returns a different value on my installation   See also this article http   www theregister co uk 2002 07 08 cracking ms sql server passwords

User · Answer

I believe pwdencrypt is using a hash so you cannot really reverse the hashed string - the algorithm is designed so it s impossible    If you are verifying the password that a user entered the usual technique is to hash it and then compare it to the hashed version in the database   This is how you could verify a usered entered table  SELECT password field FROM mytable WHERE password field pwdencrypt userEnteredValue    Replace userEnteredValue with  big surprise  the value that the user entered

User · Answer

You cannot decrypt this password again but there is another method named  pwdcompare   Here is a example how to use it with SQL syntax   USE TEMPDB GO declare  hash varbinary  255  CREATE TABLE tempdb  h  id num int  hash varbinary  255   SET  hash   pwdencrypt  123   -- encryption INSERT INTO tempdb  h  id num hash  VALUES  1  hash  SET  hash   pwdencrypt  123   INSERT INTO tempdb  h  id num hash  VALUES  2  hash  SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   2 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h INSERT INTO tempdb  h  id num hash   VALUES  3 CONVERT varbinary  255   0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8   SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   3 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h DROP TABLE tempdb  h GO

User · Answer

I believe pwdencrypt is using a hash so you cannot really reverse the hashed string - the algorithm is designed so it s impossible    If you are verifying the password that a user entered the usual technique is to hash it and then compare it to the hashed version in the database   This is how you could verify a usered entered table  SELECT password field FROM mytable WHERE password field pwdencrypt userEnteredValue    Replace userEnteredValue with  big surprise  the value that the user entered

User · Answer

I believe pwdencrypt is using a hash so you cannot really reverse the hashed string - the algorithm is designed so it s impossible    If you are verifying the password that a user entered the usual technique is to hash it and then compare it to the hashed version in the database   This is how you could verify a usered entered table  SELECT password field FROM mytable WHERE password field pwdencrypt userEnteredValue    Replace userEnteredValue with  big surprise  the value that the user entered

User · Answer

You realise that you may be making a rod for your own back for the future  The pwdencrypt   and pwdcompare   are undocumented functions and may not behave the same in future versions of SQL Server   Why not hash the password using a predictable algorithm such as SHA-2 or better before hitting the DB

User · Answer

You realise that you may be making a rod for your own back for the future  The pwdencrypt   and pwdcompare   are undocumented functions and may not behave the same in future versions of SQL Server   Why not hash the password using a predictable algorithm such as SHA-2 or better before hitting the DB

User · Answer

A quick google indicates that pwdencrypt   is not deterministic  and your statement select pwdencrypt  AAAA   returns a different value on my installation   See also this article http   www theregister co uk 2002 07 08 cracking ms sql server passwords

User · Answer

You realise that you may be making a rod for your own back for the future  The pwdencrypt   and pwdcompare   are undocumented functions and may not behave the same in future versions of SQL Server   Why not hash the password using a predictable algorithm such as SHA-2 or better before hitting the DB

User · Answer

You cannot decrypt this password again but there is another method named  pwdcompare   Here is a example how to use it with SQL syntax   USE TEMPDB GO declare  hash varbinary  255  CREATE TABLE tempdb  h  id num int  hash varbinary  255   SET  hash   pwdencrypt  123   -- encryption INSERT INTO tempdb  h  id num hash  VALUES  1  hash  SET  hash   pwdencrypt  123   INSERT INTO tempdb  h  id num hash  VALUES  2  hash  SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   2 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h INSERT INTO tempdb  h  id num hash   VALUES  3 CONVERT varbinary  255   0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8   SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   3 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h DROP TABLE tempdb  h GO

User · Answer

A quick google indicates that pwdencrypt   is not deterministic  and your statement select pwdencrypt  AAAA   returns a different value on my installation   See also this article http   www theregister co uk 2002 07 08 cracking ms sql server passwords

User · Answer

You cannot decrypt this password again but there is another method named  pwdcompare   Here is a example how to use it with SQL syntax   USE TEMPDB GO declare  hash varbinary  255  CREATE TABLE tempdb  h  id num int  hash varbinary  255   SET  hash   pwdencrypt  123   -- encryption INSERT INTO tempdb  h  id num hash  VALUES  1  hash  SET  hash   pwdencrypt  123   INSERT INTO tempdb  h  id num hash  VALUES  2  hash  SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   2 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h INSERT INTO tempdb  h  id num hash   VALUES  3 CONVERT varbinary  255   0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8   SELECT TOP 1  hash   hash FROM tempdb  h WHERE id num   3 SELECT pwdcompare   123    hash  AS  Success of check  -- Comparison SELECT   FROM tempdb  h DROP TABLE tempdb  h GO

User · Answer

You shouldn t really be de-encrypting passwords   You should be encrypting the password entered into your application and comparing against the encrypted password from the database   Edit - and if this is because the password has been forgotten  then setup a mechanism to create a new password

User · Answer

A quick google indicates that pwdencrypt   is not deterministic  and your statement select pwdencrypt  AAAA   returns a different value on my installation   See also this article http   www theregister co uk 2002 07 08 cracking ms sql server passwords

User · Answer

The SQL Server password hashing algorithm   hashBytes   0x0100   fourByteSalt   SHA1 utf16EncodedPassword fourByteSalt    For example  to hash the password  correct horse battery staple   First we generate some random salt   fourByteSalt   0x9A664D79    And then hash the password  encoded in UTF-16  along with the salt    SHA1  correct horse battery staple    0x9A66D79    SHA1 0x63006F007200720065006300740020006200610074007400650072007900200068006F00720073006500200073007400610070006C006500 0x9A66D79   0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3   The value stored in the syslogins table is the concatenation of       header     salt     hash    0x0100 9A664D79 6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3   Which you can see in SQL Server   SELECT     name  CAST password AS varbinary max   AS PasswordHash FROM sys syslogins WHERE name    sa   name  PasswordHash                                                              sa    0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3    Version header  0100 Salt  four bytes   9A664D79 Hash  6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3  SHA-1 is 20 bytes  160 bits    Validation  You validate a password by performing the same hash    grab the salt from the saved PasswordHash  0x9A664D79   and perform the hash again   SHA1  correct horse battery staple    0x9A66D79     which will come out to the same hash  and you know the password is correct   What once was good  but now is weak  The hashing algorithm introduced with SQL Server 7  in 1999  was good for 1999    It is good that the password hash salted   It is good to append the salt to the password  rather than prepend it     But today it is out-dated  It only runs the hash once  where it should run it a few thousand times  in order to thwart brute-force attacks   In fact  Microsoft s Baseline Security Analyzer will  as part of it s checks  attempt to bruteforce passwords  If it guesses any  it reports the passwords as weak  And it does get some   Brute Forcing  To help you test some passwords   DECLARE  hash varbinary max  SET  hash   0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3 --Header  0x0100 --Salt    0x9A664D79 --Hash    0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3  DECLARE  password nvarchar max  SET  password    password   SELECT      password AS CandidatePassword       hash AS PasswordHash       --Header     0x0100           --Salt     CONVERT VARBINARY 4   SUBSTRING CONVERT NVARCHAR MAX    hash   2  2             --SHA1 of Password   Salt     HASHBYTES  SHA1    password   SUBSTRING CONVERT NVARCHAR MAX    hash   2  2     SQL Server 2012 and SHA-512  Starting with SQL Server 2012  Microsoft switched to using SHA-2 512-bit   hashBytes   0x0200   fourByteSalt   SHA512 utf16EncodedPassword fourByteSalt    Changing the version prefix to 0x0200   SELECT     name  CAST password AS varbinary max   AS PasswordHash FROM sys syslogins  name  PasswordHash ----  -------------------------------- xkcd  0x02006A80BA229556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38    Version  0200  SHA-2 256-bit  Salt  6A80BA22 Hash  64 bytes   9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38   This means we hash the UTF-16 encoded password  with the salt suffix    SHA512  correct horse battery staple  6A80BA22    SHA512 63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500   6A80BA22    9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38

User · Answer

I believe pwdencrypt is using a hash so you cannot really reverse the hashed string - the algorithm is designed so it s impossible    If you are verifying the password that a user entered the usual technique is to hash it and then compare it to the hashed version in the database   This is how you could verify a usered entered table  SELECT password field FROM mytable WHERE password field pwdencrypt userEnteredValue    Replace userEnteredValue with  big surprise  the value that the user entered

[sql-server] How to decrypt a password from SQL server?

Examples related to sql-server

Examples related to encryption

Examples related to hash

Examples related to passwords