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')?
This question is related to
sql-server
encryption
hash
passwords
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 :)
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
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.
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/
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]
0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3
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
01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3 (SHA-1 is 20 bytes; 160 bits)You validate a password by performing the same hash:
PasswordHash: 0x9A664D79and 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.
The hashing algorithm introduced with SQL Server 7, in 1999, was good for 1999.
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.
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))
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
0200 (SHA-2 256-bit)6A80BA229556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38This means we hash the UTF-16 encoded password, with the salt suffix:
6A80BA22) 63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500 + 6A80BA22) 9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38 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
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
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.
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]
0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3
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
01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3 (SHA-1 is 20 bytes; 160 bits)You validate a password by performing the same hash:
PasswordHash: 0x9A664D79and 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.
The hashing algorithm introduced with SQL Server 7, in 1999, was good for 1999.
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.
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))
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
0200 (SHA-2 256-bit)6A80BA229556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38This means we hash the UTF-16 encoded password, with the salt suffix:
6A80BA22) 63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500 + 6A80BA22) 9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38 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/
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?
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/
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
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?
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.
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?
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.
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?
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/
Source: Stackoverflow.com