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')?
In stored procedure, we have to declare the variable (Password) which should be Encrypted and Decrypted. To encrypt the word used in password, write the query given below. To decrypt the word used in password, write the query given below. As a result, the password has been encrypted and decrypted in SQL SP.
SQL Server encrypts passwords with pwdencrypt, an undocumented system function that accepts a string variable and returns an encrypted version of the string.
The following steps detail how this process occurs within SQL Server: A database master key is created. A self-signed certificate is created which will be protected by the database master key. A symmetric encryption key to be used for the column level encryption is created from the certificate.
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
0100
9A664D79
6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3
(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 512-bit)
6A80BA22
9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38
This means we hash the UTF-16 encoded password, with the salt suffix:
6A80BA22
)63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500
+ 6A80BA22
)9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38
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 :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With